WEB化批量执行命令&文件上传

Computer

 

作者:wangxun

本文中将更进一步使用Tornado提供一个WEB界面,通过WEB界面操作即可实现批量命令执行、文件上传。

首先我们需要一个资料库来存放主机信息。本文中我们使用SQLite,我们建立一张表:myhost 表中创建四个栏位:ID、HOST、USER、PWD分别记录序号,主机名、主机登入用户名、登入密码。 SQL如下:

CREATE TABLE "myhost" (
"ID"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"HOST"  TEXT(36),
"USER"  TEXT(36),
"PWD"  TEXT(36)
)

Python:

# -*- coding=utf-8 -*-
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import tornado.web
import tornado.httpserver
import tornado.ioloop
import tornado.httpclient
import os.path,os,datetime
import threading
import sqlite3
import paramiko
import urllib
HTML = u""
def dbconn(sql):
    conn = sqlite3.connect("DB.db")
    conn.text_factory = str
    cur = conn.cursor()
    cur.execute(sql)
    conn.commit()
    result = cur.fetchall()
    cur.close()
    conn.close()
    return result
class ssh():
    def __init__(self,host,user,pwd,remotepath=None,localpath=None,cmd=None,TYPE=None,port=22):
        self.host = host                #Hostname
        self.user = user                #用户名
        self.pwd = pwd                  #密码
        self.remotepath = remotepath    #远程路径,上传&下载文件时需要提供此参数
        self.localpath =  localpath     #本地路径,上传&下载文件时需要提供此参数
        self.port = port                #ssh端口
        self.cmd = cmd                  #需要在远程主机执行的命令,执行命令时需要提供此参数
        self.type = TYPE                #sftp操作类型,允许的值有两个:upload和download

    def ssh(self):      #命令执行
        ssh = paramiko.SSHClient()
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        ssh.connect(self.host,self.port,self.user,self.pwd,timeout=5)
        stdin, stdout, stderr = ssh.exec_command(self.cmd)
        return stdout.read()
        ssh.close()


    def sftp(self):     #文件上传下载
        t = paramiko.Transport((self.host,self.port))
        t.connect(username = self.user, password = self.pwd)
        sftp = paramiko.SFTPClient.from_transport(t)

        if self.type=='upload':
            sftp.put(self.localpath,self.remotepath)
        elif self.type=='download':
            sftp.get(self.remotepath, self.localpath)
        else:
            raise NameError('TYPE object is invalid!')
        t.close()
settings = {
    "static_path" : os.path.join(os.path.dirname(__file__), "static"),
    "template_path" : os.path.join(os.path.dirname(__file__), "templates"),
    "login_url": "/login",
    }

class main(tornado.web.RequestHandler):
    def get(self):
        SerList = dbconn("select ID,HOST from myhost")
        if SerList:
            SerList = [(i[0],i[1]) for i in SerList]
        else:
            SerList = []
        self.render("test.html",SerList = SerList)

    def post(self):
        global HTML
        cmd = self.get_argument('cmd',')
        argv = self.get_argument('argv',')
        TYPE = self.get_argument('TYPE',')
        filename = self.get_argument('localpath',')
        localpath = os.path.join(os.path.join(os.path.dirname(__file__),'files'),filename)
        remotepath = '%s/%s' % (self.get_argument('url','),filename)
        print remotepath,localpath
        HOST_LIST = dbconn("select HOST,USER,PWD from myhost WHERE int in (%s)" % argv[0:-1])
        for i in HOST_LIST:
            t = execute(host = i[0],user = i[1],pwd = i[2],cmd = cmd,TYPE = TYPE,remotepath=remotepath,localpath=localpath)
            t.start()
            t.join()
        result = HTML
        HTML = u'
        self.write(result)
class upload(tornado.web.RequestHandler):
    def post(self):
        upload_path=os.path.join(os.path.dirname(__file__),'files')
        file_metas=self.request.files['Filedata']
        filename=file_metas[0]['filename']
        filepath=os.path.join(upload_path,filename)
        with open(filepath,'wb') as up:
            up.write(file_metas[0]['body'])
        self.write('finished!')
class execute(threading.Thread):    #命令执行、文件上传下载任务并发执行。
    def __init__(self,host,user,pwd,cmd=None,TYPE='CMD',remotepath=None,localpath=None):
        threading.Thread.__init__(self)
        self.host = host
        self.user = user
        self.pwd = pwd
        self.cmd = cmd
        self.TYPE = TYPE
        self.remotepath = remotepath
        self.localpath = localpath
    def run(self):
        global HTML
        try:
            if self.TYPE=='CMD':
                result = ssh(host=self.host,user=self.user,pwd=self.pwd,cmd=self.cmd).ssh()
                if not result:result=u'
                HTML = HTML + self.host + u':<br />' + result.strip() + u"<br />"
            elif self.TYPE=='UP':
                result = ssh(host=self.host,user=self.user,pwd=self.pwd,TYPE='upload',remotepath=self.remotepath,localpath=self.localpath).sftp()
                HTML = HTML + self.host + u'  :Upload successfull!<br />'
            else:
                raise NameError('TYPE object is invalid!')
        except Exception as e:
            pass

App = tornado.web.Application([
    (r'/',main),
    (r'/upload',upload),
    ],**settings)
if __name__ == "__main__":
    http_server=tornado.httpserver.HTTPServer(App)
    http_server.listen(80)
    tornado.ioloop.IOLoop.instance().start()

HTML模板(需要Jquery&jquery.uploadify)

<!DOCTYPE html>
<html>
<head>
<link href="static/css/uploadify.css" rel="stylesheet">
  <script src="http://cdn.bootcss.com/jquery/1.11.3/jquery.min.js"></script>
  <script src="static/js/jquery.uploadify.min.js"></script>
  <script type="text/javascript">
        (document).ready(function()
        {("#upload").uploadify({
                'swf': 'static/js/uploadify.swf',
                'uploader': '/upload',
                'cancelImg': 'static/js/uploadify-cancel.png',
                'buttonText': '选择文件并上传',
                'queueID': 'fileQueue',
                'fileSizeLimit':'50MB',
                'simUploadLimit' : '100',
                'formData' : {'url':("#url").val()},
                'onUploadSuccess':function(file, data, response){
                    var argv='("#select2 option").each(function(){
                    argv += (this).val() + ',';
                    }).post("/",{argv:argv,
                                'TYPE':'UP',
                                'url':("#url").val(),
                                'localpath':file.name},function(HTML){("#result").html(HTML)})
                },
                'removeCompleted' : false,
                'auto': true,
                'multi': true
            });
        });  
    </script>
</head>
<body>

 <table>
 <tr>
 <td>
     <select multiple style="width:100px;height:200px" id="select1" >
        {% for i in SerList %}
            <option value="{{ i[0] }}">{{ i[1] }}</option>
        {% end %}
    </select>
 </td>
 <td>
     <button id="add">>|</button><br />
     <button id="alladd">>></button><br />
     <button id="move">|<</button><br />
     <button id="allmove"><<</button>
 </td>
 <td>
    <select multiple style="width:100px;height:200px" id="select2">
    </select>
 </td>
 </tr>
 <table>
 <input type="file" id="upload" />
 文件上传至:<input type="text" id="url" value="/tmp" />
 <div id="fileQueue"></div>
  執行命令: <input type="text" id="cmd" /> <button id="sub">提交</button><br />
 <br />

<div id="result"></div>
</body>

  <script>  
  ("#add").click(function(){
    varoptions = ("#select1 option:selected")options.appendTo("#select2")

    })
  ("#move").click(function(){
    varoptions = ("#select2 option:selected")options.appendTo("#select1")

    })
  ("#allmove").click(function(){
    varoptions = ("#select2 option")options.appendTo("#select1")
    })
  ("#alladd").click(function(){
    varoptions = ("#select1 option")options.appendTo("#select2")
    })
  ("#sub").click(function(){
    var argv='("#select2 option").each(function(){
    argv += (this).val() + ',';
    }).post("/",{argv:argv,cmd:("#cmd").val(),'TYPE':'CMD'},function(data){("#result").html(data)})
    })
  </script>
</html>

 

svn 版本库的创建和配置

1.创建SVN版本库

  1. mkdir trunk
  2. svnadmin create /root/trunk/svntest #这里是路径和即将创建的版本库名称

复制代码

2.配置svn
cd /root/trunk/svntest/conf #配置文件所在的目录
  1. vim svnserve.conf  #anon-access:匿名用户的权限,可以为read,write和none,默认值read。不允许匿名用户访问:anon-access = none
  2.             #auth-access:认证用户的权限,可以为read,write和none,默认值write。
  3.             #password-db:密码数据库的路径
  4.             #authz-db:认证规则库的路径

复制代码

去掉相关注释
  1. vim passwd               #配置用户名字和密码  格式:用户名=密码

复制代码

  1. vim authz               #group_one是组名  myuser是属于group_one组
  2.                     #[/]配置为访问相对根目录

复制代码

3.启动svn服务
  1. svnserve -d -r /root/trunk/            #这里可以加入开机启动
  2. 注:
  3. #(如果提示该服务已经存在)
  4. #killall svnserve (或者找出该进程 然后kill)

复制代码

  1. svn co svn://ip/svntest    #例如:ip:192.168.0.115
  2. 这个时候svn的库就已经被建立了(这之中可能让你输入一些密码之类的东西,只要按照配置的用户和密码就没有问题)
  3.                 上面的svn co svn://192.168.0.115/svntest 就是 svn checkout svn://192.168.0.115/svntest

复制代码

4.svn checkout可能会出现的问题(linux 或者 windows 下可能出现链接被拒绝)
这中原因可能是linux下的防火墙没有关闭导致的
这个时候我们就要关闭linux下的防火墙,然后就可以checkout了
  1. /etc/init.d/iptables stop

复制代码

Python抓网页上的图片

# -*- coding: utf-8 -*-

import urllib, httplib, urlparse
import re
import random

"""judge url exists or not,by others"""
def httpExists(url):
    host, path = urlparse.urlsplit(url)[1:3]
    if ':' in host:
        # port specified, try to use it
        host, port = host.split(':', 1)
        try:
            port = int(port)
        except ValueError:
            print 'invalid port number %r' % (port,)
            return False
    else:
        # no port specified, use default port
        port = None
    try:
        connection = httplib.HTTPConnection(host, port=port)
        connection.request("HEAD", path)
        resp = connection.getresponse( )
        if resp.status == 200:       # normal 'found' status
            found = True
        elif resp.status == 302:     # recurse on temporary redirect
            found = httpExists(urlparse.urljoin(url,resp.getheader('location', '')))
        else:                        # everything else -> not found
            print "Status %d %s : %s" % (resp.status, resp.reason, url)
            found = False
    except Exception, e:
        print e.__class__, e, url
        found = False
    return found

"""get html src,return lines[]"""
def gGetHtmlLines(url):
    if url==None : return
    if not httpExists(url): return 
    try:
        page = urllib.urlopen(url)   
        html = page.readlines()
        page.close()
        return html
    except:
        print "gGetHtmlLines() error!"
        return
"""get html src,return string"""
def gGetHtml(url):
    if url==None : return
    if not httpExists(url): return 
    try:
        page = urllib.urlopen(url)   
        html = page.read()
        page.close()
        return html
    except:
        print "gGetHtml() error!"
        return

"""根据url获取文件名"""
def gGetFileName(url):
    if url==None: return None
    if url=="" : return ""
    arr=url.split("/")
    return arr[len(arr)-1]

"""生成随机文件名"""
def gRandFilename(type):
    fname = ''
    for i in range(16):
        fname = fname + chr(random.randint(65,90))
        fname = fname + chr(random.randint(48,57))
    return fname + '.' + type
"""根据url和其上的link,得到link的绝对地址"""
def gGetAbslLink(url,link):
    if url==None or link == None : return 
    if url=='' or link=='' : return url 
    addr = '' 
    if link[0] == '/' : 
        addr = gGetHttpAddr(url) + link 
    elif len(link)>3 and link[0:4] == 'http':
        addr =  link 
    elif len(link)>2 and link[0:2] == '..':
        addr = gGetHttpAddrFatherAssign(url,link)
    else:
        addr = gGetHttpAddrFather(url) + link 

    return addr 

"""根据输入的lines,匹配正则表达式,返回list"""
def gGetRegList(linesList,regx):
    if linesList==None : return 
    rtnList=[]
    for line in linesList:
        matchs = re.search(regx, line, re.IGNORECASE)
        if matchs!=None:
            allGroups = matchs.groups()
            for foundStr in allGroups:
                if foundStr not in rtnList:
                    rtnList.append(foundStr)
    return rtnList
"""根据url下载文件,文件名参数指定"""
def gDownloadWithFilename(url,savePath,file):
    #参数检查,现忽略
    try:
        urlopen=urllib.URLopener()
        fp = urlopen.open(url)
        data = fp.read()
        fp.close()
        file=open(savePath + file,'w+b')
        file.write(data)
        file.close()
    except IOError:
        print "download error!"+ url
        
"""根据url下载文件,文件名自动从url获取"""
def gDownload(url,savePath):
    #参数检查,现忽略
    fileName = gGetFileName(url)
    #fileName =gRandFilename('jpg')
    gDownloadWithFilename(url,savePath,fileName)
        
"""根据某网页的url,下载该网页的jpg"""
def gDownloadHtmlJpg(downloadUrl,savePath):
    lines= gGetHtmlLines(downloadUrl)
    regx = r"""src\s*="?(\S+)\.jpg"""
    lists =gGetRegList(lines,regx)
    if lists==None: return 
    for jpg in lists:
        jpg = gGetAbslLink(downloadUrl,jpg) + '.jpg'
        gDownload(jpg,savePath)
   ###     print gGetFileName(jpg)
"""根据url取主站地址"""
def gGetHttpAddr(url):
    if url== '' : return ''
    arr=url.split("/")
    return arr[0]+"//"+arr[2]
"""根据url取上级目录"""
def gGetHttpAddrFather(url):
    if url=='' : return ''
    arr=url.split("/")
    addr = arr[0]+'//'+arr[2]+ '/'
    if len(arr)-1>3 :
        for i in range(3,len(arr)-1):
            addr = addr + arr[i] + '/'
    return addr

"""根据url和上级的link取link的绝对地址"""
def gGetHttpAddrFatherAssign(url,link):
    if url=='' : return ''
    if link=='': return ''
    linkArray=link.split("/")
    urlArray = url.split("/")
    partLink =''
    partUrl = ''
    for i in range(len(linkArray)):        
        if linkArray[i]=='..': 
            numOfFather = i + 1    #上级数
        else:
            partLink = partLink + '/'  + linkArray[i]
    for i in range(len(urlArray)-1-numOfFather):
        partUrl = partUrl + urlArray[i] 
        if i < len(urlArray)-1-numOfFather -1 : 
            partUrl = partUrl + '/'
    return  partUrl + partLink

"""根据url获取其上的相关htm、html链接,返回list"""
def gGetHtmlLink(url):
    #参数检查,现忽略
    rtnList=[]
    lines=gGetHtmlLines(url)
    regx = r"""href="?(\S+)\.htm"""
    for link in gGetRegList(lines,regx):
        link = gGetAbslLink(url,link) + '.htm'
        if link not in rtnList:
            rtnList.append(link)
            print link
    return rtnList

"""根据url,抓取其上的jpg和其链接htm上的jpg"""
def gDownloadAllJpg(url,savePath):
    #参数检查,现忽略
    gDownloadHtmlJpg(url,savePath)
    #抓取link上的jpg
    links=gGetHtmlLink(url)
    for link in links:
        gDownloadHtmlJpg(link,savePath)

"""test"""
def test():
    u='http://www.cots.com.cn/index.htm'
    save='d:/tmp/'
    print 'download pic from [' + u +']'
    print 'save to [' +save+'] ...'
    gDownloadHtmlJpg(u,save)
    print "download finished"
    
test()

 

Sqlite语法

id字段自增

sql="CREATE TABLE IF NOT EXISTS MusicList (id integer primary key AutoIncrement,name varchar(20),path varchar(20))";

 

  

<br>常用Select语句

复制代码
desc <table> //查看表结构 
select * from <table> //查询所有更
select , from table ;//查看指定列
select distinct , from table ;//非重复查询
insert into users(_id,username,password) select * from users;//复制
select username from users where username like 'S%' ;//非重名字首字母为大写S的用户
select username from users where username like '__S%' ;//非重名字第三个字母为大写S的用户
select * from users where _id in(001,220,230);
select * from user order by _id;//以id的顺序排列
select * from user order by _id desc;//以id反的顺序排
复制代码

 

分页

当数据库数据量很大时,分页显示是个很简单且符合人们习惯的一种处理方法。

获取数据行总数:

 

最简单的方法是:SELECT所有的行,再调用curosr.getCount() ;取得行数,但这样效率会很低。 SQLite为我们提供了一个函数很容易查出总共有多少行。有个名为cet6_table的表格,我们想知道总共有多少行的word;可以使用如下 语句来完成:
SELECT count(word) AS number FROM cet6_table;

 

count()函数为我们返回一个Int整形,代表有多少行数据。返回的列的名字叫count(word),为了方便阅读和处理用as number给这个列取个 别名number;

SELECT [word],[explanation] FROM cet6_table ORDER BY word LIMIT 100 OFFSET 200"

 

 

上语句,返回自第200行的最多100条数据。分页时我们只要修改offset 后面的数据即可取得对应页面的数据。  

SQLite内建语法表

结构定义
CREATE TABLE

创建新表。

语法:

sql-command ::= CREATE [TEMP | TEMPORARY] TABLE table-name (
    
column-def [, column-def]*
    [, constraint]*
)
sql-command ::= CREATE [TEMP | TEMPORARY] TABLE [database-name.] table-name AS select-statement
column-def ::= name [type] [[CONSTRAINT name] column-constraint]*
type ::= typename |
typename ( number ) |
typename ( number , number )
column-constraint ::= NOT NULL [ conflict-clause ] |
PRIMARY KEY [sort-order] [ conflict-clause ] |
UNIQUE [ conflict-clause ] |
CHECK ( expr ) [ conflict-clause ] |
DEFAULT value |
COLLATE collation-name
constraint ::= PRIMARY KEY ( column-list ) [ conflict-clause ] |
UNIQUE ( column-list ) [ conflict-clause ] |
CHECK ( expr ) [ conflict-clause ]
conflict-clause ::= ON CONFLICT conflict-algorithm
CREATE VIEW

创建一个视图(虚拟表),该表以另一种方式表示一个或多个表中的数据。

语法:

sql-command ::= CREATE [TEMP | TEMPORARY] VIEW [database-name.] view-name AS select-statement

例子:
CREATE VIEW master_view AS
    SELECT * FROM sqlite_master WHERE type='view';
说明:
创建一个名为master_view的视图,其中包括sqlite_master这个表中的所有视图表。

CREATE TRIGGER

创建触发器,触发器是一种特殊的存储过程,在用户试图对指定的表执行指定的数据修改语句时自动执行。

语法:

sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ]
database-event ON [database-name .] table-name
trigger-action
sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF
database-event ON [database-name .] view-name
trigger-action
database-event ::= DELETE | 
INSERT 
| 
UPDATE 
| 
UPDATE OF 
column-list
trigger-action ::= [ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN expression ] 
BEGIN 
    
trigger-step ; [ trigger-step ; ]*
END
trigger-step ::= update-statement | insert-statement | 
delete-statement | select-statement

例子:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;
说明:
创建了一个名为update_customer_address的触发器,当用户更新customers表中的address字段时,将触发并更新orders表中的address字段为新的值。
比如执行如下一条语句:
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
数据库将自动执行如下语句:
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';

CREATE INDEX

为给定表或视图创建索引。

语法:

sql-statement ::= CREATE [UNIQUE] INDEX index-name 
ON 
[database-name .] table-name ( column-name [, column-name]* )
[ ON CONFLICT conflict-algorithm ]
column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]

例子:
CREATE INDEX idx_email ON customers (email);
说明:
为customers表中的email创建一个名为idx_email的字段。

结构删除
DROP TABLE

删除表定义及该表的所有索引。

语法:

sql-command ::= DROP TABLE [database-name.] table-name

例子:
DROP TABLE customers;

DROP VIEW

删除一个视图。

语法:

sql-command ::= DROP VIEW view-name

例子:
DROP VIEW master_view;

DROP TRIGGER

删除一个触发器。

语法:

sql-statement ::= DROP TRIGGER [database-name .] trigger-name

例子:
DROP TRIGGER update_customer_address;

DROP INDEX

删除一个索引。

语法:

sql-command ::= DROP INDEX [database-name .] index-name

例子:
DROP INDEX idx_email;

数据操作
INSERT

将新行插入到表。

语法:

sql-statement ::= INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)]VALUES(value-list) |
INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] select-statement
UPDATE

更新表中的现有数据。

语法:

sql-statement ::= UPDATE [ OR conflict-algorithm ] [database-name .] table-name
SET assignment [, assignment]*
[WHERE expr]
assignment ::= column-name = expr
DELETE

从表中删除行。

语法:

sql-statement ::= DELETE FROM [database-name .] table-name [WHERE expr]
SELECT

从表中检索数据。

语法:

sql-statement ::= SELECT [ALL | DISTINCT] result [FROM table-list]
[WHERE expr]
[GROUP BY expr-list]
[HAVING expr]
[compound-op select]*
[ORDER BY sort-expr-list]
[LIMIT integer [( OFFSET | , ) integer]]
result ::= result-column [, result-column]*
result-column ::= * | table-name . * | expr [ [AS] string ]
table-list ::= table [join-op table join-args]*
table ::= table-name [AS alias] |
( select ) [AS alias]
join-op ::= , | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN
join-args ::= [ON expr] [USING ( id-list )]
sort-expr-list ::= expr [sort-order] [, expr [sort-order]]*
sort-order ::= [ COLLATE collation-name ] [ ASC | DESC ]
compound_op ::= UNION | UNION ALL | INTERSECT | EXCEPT
REPLACE

类似INSERT

语法:

sql-statement ::= REPLACE INTO [database-name .] table-name [( column-list )] VALUES ( value-list ) |
REPLACE INTO [database-name .] table-name [( column-list )] select-statement
事务处理
BEGIN TRANSACTION

标记一个事务的起始点。

语法:

sql-statement ::= BEGIN [TRANSACTION [name]]
END TRANSACTION

标记一个事务的终止。

语法:

sql-statement ::= END [TRANSACTION [name]]
COMMIT TRANSACTION

标志一个事务的结束。

语法:

sql-statement ::= COMMIT [TRANSACTION [name]]
ROLLBACK TRANSACTION

将事务回滚到事务的起点。

语法:

sql-statement ::= ROLLBACK [TRANSACTION [name]]
其他操作
COPY

主要用于导入大量的数据。

语法:

sql-statement ::= COPY [ OR conflict-algorithm ] [database-name .] table-name FROM filename
[ USING DELIMITERS delim ]

例子:
COPY customers FROM customers.csv;

EXPLAIN

语法:

sql-statement ::= EXPLAIN sql-statement
PRAGMA

语法:

sql-statement ::= PRAGMA name [value] |
PRAGMA function(arg)
VACUUM

语法:

sql-statement ::= VACUUM [index-or-table-name]
ATTACH DATABASE

附加一个数据库到当前的数据库连接。

语法:

sql-statement ::= ATTACH [DATABASE] database-filename AS database-name
DETTACH DATABASE

从当前的数据库分离一个使用ATTACH DATABASE附加的数据库。

语法:

sql-command ::= DETACH [DATABASE] database-name

SQLite内建函数表

算术函数
abs(X) 返回给定数字表达式的绝对值。
max(X,Y[,…]) 返回表达式的最大值。
min(X,Y[,…]) 返回表达式的最小值。
random(*) 返回随机数。
round(X[,Y]) 返回数字表达式并四舍五入为指定的长度或精度。
字符处理函数
length(X) 返回给定字符串表达式的字符个数。
lower(X) 将大写字符数据转换为小写字符数据后返回字符表达式。
upper(X) 返回将小写字符数据转换为大写的字符表达式。
substr(X,Y,Z) 返回表达式的一部分。
randstr()  
quote(A)  
like(A,B) 确定给定的字符串是否与指定的模式匹配。
glob(A,B)  
条件判断函数
coalesce(X,Y[,…])  
ifnull(X,Y)  
nullif(X,Y)  
集合函数
avg(X) 返回组中值的平均值。
count(X) 返回组中项目的数量。
max(X) 返回组中值的最大值。
min(X) 返回组中值的最小值。
sum(X) 返回表达式中所有值的和。
其他函数
typeof(X) 返回数据的类型。
last_insert_rowid() 返回最后插入的数据的ID。
sqlite_version(*) 返回SQLite的版本。
change_count() 返回受上一语句影响的行数。
last_statement_change_count()  
 
 

Options for HTML scraping

I'm thinking of trying Beautiful Soup, a Python package for HTML scraping. Are there any other HTML scraping packages I should be looking at? Python is not a requirement, I'm actually interested in hearing about other languages as well.

The story so far:

 

 

 

 

 

 

 

 

验证码识别

1:安装 http://www.pythonware.com/products/pil/
2:安装 https://code.google.com/p/pytesser/downloads/detail?name=pytesser_v0.0.1.zip&can=2&q=

测试结果 pytesser 识别率太低

1:取得50个样例图
import httplib
for i in range(50):
    url = 'http:////sysmonitor/verifyCodeServlet'
    print "download", i
    c = httplib.HTTPSConnection("1.1.1.1",8443)
    c.request("GET", "/sysmonitor/verifyCodeServlet")
    response = c.getresponse()
    #print response.status, response.reason
    #data = response.read()
    file("./code/%04d.png" % i, "wb").write(response.read())


2:去噪
from PIL import Image

for i in range(50):
 
    img = Image.open("./code/%04d.png" % i)
    img = img.convert("RGBA")
 
    pixdata = img.load()
 

    for y in xrange(img.size[1]):
        for x in xrange(img.size[0]):
            if pixdata[x, y][0] < 90:
                pixdata[x, y] = (0, 0, 0, 255)
 
    for y in xrange(img.size[1]):
        for x in xrange(img.size[0]):
            if pixdata[x, y][1] < 136:
                pixdata[x, y] = (0, 0, 0, 255)
 
    for y in xrange(img.size[1]):
        for x in xrange(img.size[0]):
            if pixdata[x, y][2] > 0:
                pixdata[x, y] = (255, 255, 255, 255)
 
    img.save("./code/input-black-%04d.gif" % i, "GIF")
 

#im_orig = Image.open('input-black.gif')
#big = im_orig.resize((1000, 500), Image.NEAREST)

3:生成字库
import os ,Image
j = 1
dir="./code/"
for f in os.listdir(dir):
    if f.endswith(".gif"):
        img = Image.open(dir+f)
        for i in range(4): 
            x = 4 + i*18
            y = 2
            img.crop((x, y, x+12, y+16)).save("fonts/%d.gif" % j)
            print "j=",j 
            j += 1 

4:识别
#!/usr/bin/env python
# ?*? coding: UTF?8 ?*?
import os, Image
 
def binary(f):
    img = Image.open(f)
    #img = img.convert('1')
    img = img.convert("RGBA")
    pixdata = img.load()
    for y in xrange(img.size[1]):
        for x in xrange(img.size[0]):
            if pixdata[x, y][0] < 90:
                pixdata[x, y] = (0, 0, 0, 255)
    for y in xrange(img.size[1]):
        for x in xrange(img.size[0]):
            if pixdata[x, y][1] < 136:
                pixdata[x, y] = (0, 0, 0, 255)
    for y in xrange(img.size[1]):
        for x in xrange(img.size[0]):
            if pixdata[x, y][2] > 0:
                pixdata[x, y] = (255, 255, 255, 255)
    return img
 
 
def division(img):
    font=[]
    for i in range(4):
        x=4 + i*18
        y=2
        font.append(img.crop((x,y,x+12,y+16)))
    return font
 
def recognize(img):
    fontMods = []
    for i in range(10):
        fontMods.append((str(i), Image.open("./num/%d.gif" % i)))
    result=""
    font=division(img)
    for i in font:
        target=i
        points = []
        for mod in fontMods:
            diffs = 0
            for yi in range(16):
                for xi in range(12):
                    #(mod[1].getpixel((xi, yi))).save("./temp/temp.gif" % j)
                    if mod[1].getpixel((xi, yi)) != target.getpixel((xi, yi)):
                        #diffs += 1
                        if 0 in target.getpixel((xi, yi)):
                            compare = 0
                        else:
                            compare = 255

                        if mod[1].getpixel((xi, yi)) != compare:
                            diffs += 1
                            
            print "diffs:" + str(diffs)
            points.append((diffs, mod[0]))
        points.sort()
        result += points[0][1]
    return result
 
if __name__ == '__main__':
    codedir="./code/"
    for imgfile in os.listdir(codedir):
        if imgfile.endswith(".png"):
            dir="./result/"
            print(codedir+imgfile)
            img=binary(codedir+imgfile)
            num=recognize(img)
            dir += (num+".png")
            print "save to", dir
            img.save(dir)

UnicodeDecodeError: ‘ascii’ codec can’t decode byte 0xb0 in position 1: ordinal not in range(128)

UnicodeDecodeError: 'ascii' codec can't decode byte 0xb0 in position 1: ordinal not in range(128) 原因可能与注册表错误的字符集有关,可能与某些软件对注册表的改写的gbk格式导致。

 

解决方法:打开C:\Python27\Lib下的 mimetypes.py 文件,

找到大概256行的 ‘default_encoding = sys.getdefaultencoding()’。

在这行前面添加三行:

if sys.getdefaultencoding() != 'gbk':  
    reload(sys)  
    sys.setdefaultencoding('gbk')  
default_encoding = sys.getdefaultencoding()  

 

 

 

安装 paramiko 前置 PyCrypto

首先安装 PyCrypto – The Python Cryptography Toolkit
http://www.voidspace.org.uk/python/modules.shtml#pycrypto

 

https://github.com/paramiko/paramiko
setup.py install

如出现 error: Unable to find vcvarsall.bat
则:
命令行下执行 SET VS90COMNTOOLS=%VS100COMNTOOLS%
如果你安装的是 2012 版 SET VS90COMNTOOLS=%VS110COMNTOOLS%
如果你安装的是 2013版 SET VS90COMNTOOLS=%VS120COMNTOOLS%

java 的base64处理类


/***************************************************************

	Copyright (c) 1998, 1999 Nate Sammons <nate@protomatter.com>  
	This library is free software; you can redistribute it and/or
	modify it under the terms of the GNU Library General Public
	License as published by the Free Software Foundation; either
	version 2 of the License, or (at your option) any later version.

	This library is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
	Library General Public License for more details.

	You should have received a copy of the GNU Library General Public
	License along with this library; if not, write to the
	Free Software Foundation, Inc., 59 Temple Place - Suite 330,
	Boston, MA  02111-1307, USA.

	Contact support@protomatter.com with your questions, comments,
	gripes, praise, etc...

	***************************************************************/


	/***************************************************************
	  - moved to the net.matuschek.util tree by Daniel Matuschek
	  - replaced deprecated getBytes() method in method decode
	  - added String encode(String) method to encode a String to 
	    base64
	 ***************************************************************/

	/**
	 * Base64 encoder/decoder.  Does not stream, so be careful with
	 * using large amounts of data
	 *
	 * @author Nate Sammons
	 * @author Daniel Matuschek
	 * @version Id: Base64.java,v 1.4 2001/04/17 10:09:27 matuschd Exp
	 */
	public class HiBase64
	{

	  private HiBase64()
	  {
	    super();
	  }

	  /**
	   *  Encode some data and return a String.
	   */
	  public final static String encode(byte[] d)
	  {
	    if (d == null) return null;
	    byte data[] = new byte[d.length+2];
	    System.arraycopy(d, 0, data, 0, d.length);
	    byte dest[] = new byte[(data.length/3)*4];

	    // 3-byte to 4-byte conversion
	    for (int sidx = 0, didx=0; sidx < d.length; sidx += 3, didx += 4)
	    {
	      dest[didx]   = (byte) ((data[sidx] >>> 2) & 077);
	      dest[didx+1] = (byte) ((data[sidx+1] >>> 4) & 017 |
	                  (data[sidx] << 4) & 077);
	      dest[didx+2] = (byte) ((data[sidx+2] >>> 6) & 003 |
	                  (data[sidx+1] << 2) & 077);
	      dest[didx+3] = (byte) (data[sidx+2] & 077);
	    }

	    // 0-63 to ascii printable conversion
	    for (int idx = 0; idx <dest.length; idx++)
	    {
	      if (dest[idx] < 26)     dest[idx] = (byte)(dest[idx] + 'A');
	      else if (dest[idx] < 52)  dest[idx] = (byte)(dest[idx] + 'a' - 26);
	      else if (dest[idx] < 62)  dest[idx] = (byte)(dest[idx] + '0' - 52);
	      else if (dest[idx] < 63)  dest[idx] = (byte)'+';
	      else            dest[idx] = (byte)'/';
	    }

	    // add padding
	    for (int idx = dest.length-1; idx > (d.length*4)/3; idx--)
	    {
	      dest[idx] = (byte)'=';
	    }
	    return new String(dest);
	  }

	  /**
	   * Encode a String using Base64 using the default platform encoding
	   **/
	  public final static String encode(String s) {
	    return encode(s.getBytes());
	  }

	  /**
	   *  Decode data and return bytes.
	   */
	  public final static byte[] decode(String str)
	  {
	    if (str == null)  return  null;
	    byte data[] = str.getBytes();
	    return decode(data);
	  }

	  /**
	   *  Decode data and return bytes.  Assumes that the data passed
	   *  in is ASCII text.
	   */
	  public final static byte[] decode(byte[] data)
	  {
	    int tail = data.length;
	    while (data[tail-1] == '=')  tail--;
	    byte dest[] = new byte[tail - data.length/4];

	    // ascii printable to 0-63 conversion
	    for (int idx = 0; idx <data.length; idx++)
	    {
	      if (data[idx] == '=')    data[idx] = 0;
	      else if (data[idx] == '/') data[idx] = 63;
	      else if (data[idx] == '+') data[idx] = 62;
	      else if (data[idx] >= '0'  &&  data[idx] <= '9')
	        data[idx] = (byte)(data[idx] - ('0' - 52));
	      else if (data[idx] >= 'a'  &&  data[idx] <= 'z')
	        data[idx] = (byte)(data[idx] - ('a' - 26));
	      else if (data[idx] >= 'A'  &&  data[idx] <= 'Z')
	        data[idx] = (byte)(data[idx] - 'A');
	    }

	    // 4-byte to 3-byte conversion
	    int sidx, didx;
	    for (sidx = 0, didx=0; didx < dest.length-2; sidx += 4, didx += 3)
	    {
	      dest[didx]   = (byte) ( ((data[sidx] << 2) & 255) |
	              ((data[sidx+1] >>> 4) & 3) );
	      dest[didx+1] = (byte) ( ((data[sidx+1] << 4) & 255) |
	              ((data[sidx+2] >>> 2) & 017) );
	      dest[didx+2] = (byte) ( ((data[sidx+2] << 6) & 255) |
	              (data[sidx+3] & 077) );
	    }
	    if (didx < dest.length)
	    {
	      dest[didx]   = (byte) ( ((data[sidx] << 2) & 255) |
	              ((data[sidx+1] >>> 4) & 3) );
	    }
	    if (++didx < dest.length)
	    {
	      dest[didx]   = (byte) ( ((data[sidx+1] << 4) & 255) |
	              ((data[sidx+2] >>> 2) & 017) );
	    }
	    return dest;
	  }

	  /**
	   *  A simple test that encodes and decodes the first commandline argument.
	   */
	  public static final void main(String[] args)
	  {
	    if (args.length != 1)
	    {
	      System.out.println("Usage: Base64 string");
	      System.exit(0);
	    }
	    try
	    {
	      String e = HiBase64.encode(args[0].getBytes());
	      String d = new String(HiBase64.decode(e));
	      System.out.println("Input   = '" + args[0] + "'");
	      System.out.println("Encoded = '" + e + "'");
	      System.out.println("Decoded = '" + d + "'");
	    }
	    catch (Exception x)
	    {
	      x.printStackTrace();
	    }
	  }
	}

dea_des ecb 模式 java php c#实现

1.java

[code lang=”java”]<br />
package com.egame.fee.sdk.pc.util;
</p>

<p>
import java.io.IOException;<br />
import java.io.UnsupportedEncodingException;<br />
import java.security.InvalidKeyException;<br />
import java.security.NoSuchAlgorithmException;<br />
import java.security.SecureRandom;<br />
import java.security.spec.InvalidKeySpecException;
</p>

<p>
import javax.crypto.BadPaddingException;<br />
import javax.crypto.Cipher;<br />
import javax.crypto.IllegalBlockSizeException;<br />
import javax.crypto.NoSuchPaddingException;<br />
import javax.crypto.SecretKey;<br />
import javax.crypto.SecretKeyFactory;<br />
import javax.crypto.spec.DESKeySpec;
</p>

<p>
import sun.misc.BASE64Decoder;<br />
import sun.misc.BASE64Encoder;
</p>

<p>
public class SecretUtilTools {
</p>

<p>
public static String encryptForDES(String souce, String key) throws InvalidKeyException,<br />
NoSuchAlgorithmException, InvalidKeySpecException, NoSuchPaddingException,<br />
IllegalBlockSizeException, BadPaddingException, UnsupportedEncodingException {<br />
// DES算法要求有一个可信任的随机数源<br />
SecureRandom sr = new SecureRandom();<br />
// 从原始密匙数据创建DESKeySpec对象<br />
DESKeySpec dks = new DESKeySpec(key.getBytes(&quot;UTF-8&quot;));<br />
// 创建一个密匙工厂,然后用它把DESKeySpec转换成 一个SecretKey对象<br />
SecretKeyFactory keyFactory = SecretKeyFactory.getInstance(&quot;DES&quot;);<br />
SecretKey key1 = keyFactory.generateSecret(dks);<br />
// Cipher对象实际完成加密操作<br />
Cipher cipher = Cipher.getInstance(&quot;DES&quot;);<br />
// 用密匙初始化Cipher对象<br />
cipher.init(Cipher.ENCRYPT_MODE, key1, sr);<br />
// 现在,获取数据并加密<br />
byte encryptedData[] = cipher.doFinal(souce.getBytes(&quot;UTF-8&quot;));<br />
// 通过BASE64位编码成字符创形式<br />
String base64Str = new BASE64Encoder().encode(encryptedData);
</p>

<p>
return base64Str;<br />
}
</p>

<p>
public static String decryptForDES(String souce, String key) throws InvalidKeyException,<br />
NoSuchAlgorithmException, InvalidKeySpecException, NoSuchPaddingException, IOException,<br />
IllegalBlockSizeException, BadPaddingException {<br />
// DES算法要求有一个可信任的随机数源<br />
SecureRandom sr = new SecureRandom();<br />
// 从原始密匙数据创建DESKeySpec对象<br />
DESKeySpec dks = new DESKeySpec(key.getBytes());<br />
// 创建一个密匙工厂,然后用它把DESKeySpec转换成 一个SecretKey对象<br />
SecretKeyFactory keyFactory = SecretKeyFactory.getInstance(&quot;DES&quot;);<br />
SecretKey key1 = keyFactory.generateSecret(dks);<br />
// Cipher对象实际完成加密操作<br />
Cipher cipher = Cipher.getInstance(&quot;DES&quot;);<br />
// 用密匙初始化Cipher对象<br />
cipher.init(Cipher.DECRYPT_MODE, key1, sr);<br />
// 将加密报文用BASE64算法转化为字节数组<br />
byte[] encryptedData = new BASE64Decoder().decodeBuffer(souce);<br />
// 用DES算法解密报文<br />
byte decryptedData[] = cipher.doFinal(encryptedData);<br />
return new String(decryptedData,&quot;UTF-8&quot;);<br />
}
</p>

<p>
}<br />
[/code]

2.php

[code lang=”php”]<br />
&lt;?php
</p>

<p>
class SecretUtilTools<br />
{<br />
//加密算法<br />
function encryptForDES(input,key)<br />
{<br />
size = mcrypt_get_block_size(&#39;des&#39;,&#39;ecb&#39;);<br />input = this-&amp;gt;pkcs5_pad(input, size);<br />td = mcrypt_module_open(&#39;des&#39;, &#39;&#39;, &#39;ecb&#39;, &#39;&#39;);<br />
iv = @mcrypt_create_iv (mcrypt_enc_get_iv_size(td), MCRYPT_RAND);<br />
@mcrypt_generic_init(td,key, iv);<br />data = mcrypt_generic(td,input);<br />
mcrypt_generic_deinit(td);<br />
mcrypt_module_close(
td);<br />
data = base64_encode(data);<br />
return data;<br />
}<br />
//解密算法<br />
function decryptForDES(
encrypted,key)<br />
{<br />
encrypted = base64_decode(encrypted);<br />td = mcrypt_module_open(&#39;des&#39;,&#39;&#39;,&#39;ecb&#39;,&#39;&#39;);<br />
//使用MCRYPT_DES算法,cbc模式<br />
iv = @mcrypt_create_iv(mcrypt_enc_get_iv_size(td), MCRYPT_RAND);<br />
ks = mcrypt_enc_get_key_size(td);<br />
@mcrypt_generic_init(td,key, iv);<br />
//初始处理<br />
decrypted = mdecrypt_generic(td,encrypted);<br />
//解密<br />
mcrypt_generic_deinit(td);<br />
//结束<br />
mcrypt_module_close(
td);<br />
y=this-&amp;gt;pkcs5_unpad(decrypted);<br />
return
y;<br />
}
</p>

<p>
function pkcs5_pad (text,blocksize)<br />
{<br />
pad =blocksize – (strlen(text) %blocksize);<br />
return text . str_repeat(chr(pad), pad);<br />
}
</p>

<p>
function pkcs5_unpad(text)<br />
{<br />
pad = ord(text{strlen(text)-1});<br />
if (
pad &amp;gt; strlen(text))<br />
{<br />
return false;<br />
}<br />
if (strspn(
text, chr(pad), strlen(text) – pad) !=pad)<br />
{<br />
return false;<br />
}<br />
return substr(text, 0, -1 *pad);<br />
}<br />
}<br />
?&gt;[/code]

3.c#

[code lang=”csharp”]<br />
using System;<br />
using System.IO;<br />
using System.Security.Cryptography;<br />
using System.Text;<br />
using System.Threading;
</p>

<p>
public class SecretUtilTools<br />
{<br />
public string encryptForDES(string message, string key)<br />
{<br />
using (DESCryptoServiceProvider des = new DESCryptoServiceProvider())<br />
{<br />
byte[] inputByteArray = Encoding.UTF8.GetBytes(message);<br />
des.Key = UTF8Encoding.UTF8.GetBytes(key);<br />
des.IV = UTF8Encoding.UTF8.GetBytes(key);<br />
des.Mode = System.Security.Cryptography.CipherMode.ECB;<br />
System.IO.MemoryStream ms = new System.IO.MemoryStream();<br />
using (CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write))<br />
{<br />
cs.Write(inputByteArray, 0, inputByteArray.Length);<br />
cs.FlushFinalBlock();<br />
cs.Close();<br />
}<br />
string str = Convert.ToBase64String(ms.ToArray());<br />
ms.Close();<br />
return str;<br />
}<br />
}
</p>

<p>
public string decryptForDES(string message, string key)<br />
{<br />
byte[] inputByteArray = Convert.FromBase64String(message);<br />
using (DESCryptoServiceProvider des = new DESCryptoServiceProvider())<br />
{<br />
des.Key = UTF8Encoding.UTF8.GetBytes(key);<br />
des.IV = UTF8Encoding.UTF8.GetBytes(key);<br />
des.Mode = System.Security.Cryptography.CipherMode.ECB;<br />
System.IO.MemoryStream ms = new System.IO.MemoryStream();<br />
using (CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write))<br />
{<br />
cs.Write(inputByteArray, 0, inputByteArray.Length);<br />
cs.FlushFinalBlock();<br />
cs.Close();<br />
}<br />
string str = Encoding.UTF8.GetString(ms.ToArray());<br />
ms.Close();<br />
return str;<br />
}<br />
}
</p>

<p>
}<br />
[/code]

简单方法捕捉手机(移动设备)网络请求

1:安装 Fiddler,在其 Tools –> Fiddler Options –> Connections 选中 Allow Remote Computers to Connect.
2:记录 Fiddler 所在机器的IP地址,如192.168.0.100
3:在移动设备(手机)的WIFI网络设置处,设置HTTP代理,将服务器填为上一步中获得的IP,即192.168.0.100,端口填8888

这样Fiddler即可截获手机的网络请求.