python常用语句（附带调用google翻译）

下面的语句是我工作中常用到的一些语句使用，可能比较简单，部分参考了网上的代码，just for note

零、参考链接

https://blog.youkuaiyun.com/yingshukun/article/details/53470424

https://www.runoob.com/python/python-files-io.html

一、操作excel表格

from openpyxl import Workbook
from openpyxl import load_workbook
#初始化操作
wb = load_workbook("xxx.xlsx")
#wb = openpyxl.Workbook()
#mySheet = wb.create_sheet(index=0, title="sheet1")
#获取所有表格的名字
sheets = wb.sheetnames
#选择第二个表格：
sheets_first = sheets[1]
#获取特定的worksheet
ws = wb[sheets_first]
rows = ws.rows
columns = ws.columns

#获取当前空行
def get_current_row():
    for per_row in range(1,1024):
        if ws.cell(row=per_row,column=1).value == None:
            current_row = per_row
            return current_row

#向单元格写入内容
ws.cell(row=current_row, column=1).value = '1'
ws.cell(row=current_row, column=2).value = '2'
ws.cell(row=current_row, column=3).value = '3'
wb.save("xxx.xlsx")

#查看所有行的数据
def read_every():
    for row in rows:
        line = [col.value for col in row]
        print(line)

二、操作word中的表格

from docx import Document
document = Document("xxx.docx")
#获取word中的表格
tables = document.tables
table = tables[0]
document.save("xxx.docx")

三、获取文件md5

import hashlib
def GetFileMd5(filename):
    if not os.path.isfile(filename):
        return
    myhash = hashlib.md5()
    f = open(filename,'rb')
    while True:
        b = f.read(8096)
        if not b:
            break
        myhash.update(b)
    f.close()
    return myhash.hexdigest()

四、释放zip文件

import zipfile
def freedfile():
    zip_file = zipfile.ZipFile("xxx.zip")
    zip_file.extractall('xxx')
    #如果有密码
    zip_file.extractall('xxx',pwd="password")

五、遍历文件夹里面的文件

for (dirpath, dirnames, filenames) in os.walk('dir'):
    for filename in filenames:
        print(filename)

六、操作sqlite3

import sqlite3
def write_db():
    conn = sqlite3.connect('file_md5.db')
    c = conn.cursor()

    try:
        c.execute('drop table old_zip')
        c.execute('drop table new_zip')
    except:
        print('first run,init')
    c.execute('create table old_zip (name char(50) unique,md5 char(50))')
    c.execute('create table new_zip (name char(50) unique,md5 char(50))')

    for (dirpath, dirnames, filenames) in os.walk('old_zip'):
        for filename in filenames:
            path = dirpath + '/' + filename
            file_md5 = GetFileMd5(path)
            filename = (dirpath + '_' + os.path.splitext(filename)[0] + os.path.splitext(filename)[1])[8:]
            tmp = "insert or ignore into old_zip (name,md5) values('%s','%s')" % (filename, file_md5)
            c.execute(tmp)

    for (dirpath, dirnames, filenames) in os.walk('new_zip'):
        for filename in filenames:
            path = dirpath + '/' + filename
            file_md5 = GetFileMd5(path)
            filename = (dirpath + '_' + os.path.splitext(filename)[0] + os.path.splitext(filename)[1])[8:]
            tmp = "insert or ignore into new_zip (name,md5) values('%s','%s')" % (filename, file_md5)
            c.execute(tmp)

    conn.commit()
    conn.close()

七、sqlite3两表比较

conn = sqlite3.connect('file_md5.db')
c = conn.cursor()

# same
tmp = 'select * from old_zip inner join new_zip on old_zip.name = new_zip.name and old_zip.md5 = new_zip.md5'
cursor = c.execute(tmp)
values_same = cursor.fetchall()
print(len(values_same))
# different
tmp = 'select * from old_zip inner join new_zip on old_zip.name = new_zip.name and old_zip.md5 != new_zip.md5'
cursor = c.execute(tmp)
values_different = cursor.fetchall()
print(len(values_different))
# left(old_zip)
tmp = 'select * from old_zip where not exists(select 1 from new_zip where new_zip.name = old_zip.name)'
cursor = c.execute(tmp)
values_left = cursor.fetchall()
print(len(values_left))
# right(new_zip)
tmp = 'select * from new_zip where not exists(select 1 from old_zip where new_zip.name = old_zip.name)'
cursor = c.execute(tmp)
values_right = cursor.fetchall()
print(len(values_right))

conn.commit()
conn.close()

八、执行系统命令

import subprocess
subprocess.Popen('ls -al',shell=True)

九、调用google翻译

#pip install PyExecJS
import execjs  
import urllib.request
import sys
  
class Py4Js():  
      
    def __init__(self):  
        self.ctx = execjs.compile(""" 
        function TL(a) { 
        var k = ""; 
        var b = 406644; 
        var b1 = 3293161072; 
         
        var jd = "."; 
        var $b = "+-a^+6"; 
        var Zb = "+-3^+b+-f"; 
     
        for (var e = [], f = 0, g = 0; g < a.length; g++) { 
            var m = a.charCodeAt(g); 
            128 > m ? e[f++] = m : (2048 > m ? e[f++] = m >> 6 | 192 : (55296 == (m & 64512) && g + 1 < a.length && 56320 == (a.charCodeAt(g + 1) & 64512) ? (m = 65536 + ((m & 1023) << 10) + (a.charCodeAt(++g) & 1023), 
            e[f++] = m >> 18 | 240, 
            e[f++] = m >> 12 & 63 | 128) : e[f++] = m >> 12 | 224, 
            e[f++] = m >> 6 & 63 | 128), 
            e[f++] = m & 63 | 128) 
        } 
        a = b; 
        for (f = 0; f < e.length; f++) a += e[f], 
        a = RL(a, $b); 
        a = RL(a, Zb); 
        a ^= b1 || 0; 
        0 > a && (a = (a & 2147483647) + 2147483648); 
        a %= 1E6; 
        return a.toString() + jd + (a ^ b) 
    }; 
     
    function RL(a, b) { 
        var t = "a"; 
        var Yb = "+"; 
        for (var c = 0; c < b.length - 2; c += 3) { 
            var d = b.charAt(c + 2), 
            d = d >= t ? d.charCodeAt(0) - 87 : Number(d), 
            d = b.charAt(c + 1) == Yb ? a >>> d: a << d; 
            a = b.charAt(c) == Yb ? a + d & 4294967295 : a ^ d 
        } 
        return a 
    } 
    """)  
          
    def getTk(self,text):  
        return self.ctx.call("TL",text)

def open_url(url):
    headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:23.0) Gecko/20100101 Firefox/23.0'}
    req = urllib.request.Request(url=url, headers=headers)
    response = urllib.request.urlopen(req)
    data = response.read().decode('utf-8')
    return data

def translate(content, tk):
    global rules_lines
    if len(content) > 4891:
        print("翻译的长度超过限制！！！")
        return

    content = urllib.parse.quote(content)

    url = "http://translate.google.cn/translate_a/single?client=t" + "&sl=en&tl=zh-CN&hl=zh-CN&dt=at&dt=bd&dt=ex&dt=ld&dt=md&dt=qca" + "&dt=rw&dt=rm&dt=ss&dt=t&ie=UTF-8&oe=UTF-8&clearbtn=1&otf=1&pc=1" + "&srcrom=0&ssel=0&tsel=0&kc=2&tk=%s&q=%s" % (
    tk, content)

    # 返回值是一个多层嵌套列表的字符串形式，解析起来还相当费劲，写了几个正则，发现也很不理想，
    # 后来感觉，使用正则简直就是把简单的事情复杂化，这里直接切片就Ok了
    result = open_url(url)

    end = result.find("\",")
    print(result[4:end])

if __name__ == "__main__":
    js = Py4Js()
    tk = js.getTk(sys.argv[1])
    translate(sys.argv[1], tk)

十、I/O操作以及操作文件

输入

raw_input([prompt]) 函数从标准输入读取一个行，并返回一个字符串（去掉结尾的换行符）

input([prompt]) 函数和 raw_input([prompt]) 函数基本类似，但是 input 可以接收一个Python表达式作为输入，并将运算结果返回

打开文件模式

读写文件可能会遇到编码问题，可以加入encoding参数

常用的：utf-8、gbk、ansi

eg:f = open("test.txt","w+",encoding="utf-8")

十一、正则表达式

import re
#定义特征
rule = "alert tcp any any -> any $HTTP_PORTS (msg:"xxx"l sid:12345;)"
pattern = re.compile(r'sid\s*\d{3,8}\s*;')
pattern1 = re.compile(r'\d{1,10}')
#精确搜索
sid_tmp = pattern.search(rule).group()
sid = pattern1.search(sid_tmp).group