【Python学习】操作Sqllite

 1 #!/usr/bin/env python
 2 # encoding: utf-8
 3 from ea.tests.common import *
 4 from mgr.common.logger import *
 5 from mgr.common.cmd import exec_cmd
 6 import os
 7 import sys
 8 import re
 9 import time
10 import sqlite3
11 
12 
13 class agent_db_manage:
14     def __init__(self):
15         self.conn = None
16         self.cursor = None
17         self.encode_db_path = r"C:\ProgramData\Sangfor\EDR\PDFLink\sfedomain.db"
18         self.LIST_PATH = os.path.dirname(os.path.realpath(__file__))
19         workspace = r"C:\Jenkins\workspace"
20         for floor in os.listdir(workspace):
21             if os.path.exists(os.path.join(workspace, floor, "Swiss-Knife", "tools", "DomainTool")):
22                 self.workspace = os.path.join(workspace, floor)
23                 break
24         self.toolspace = os.path.join(self.workspace, "Swiss-Knife", "tools")
25         self.uncode_path = os.path.join(self.toolspace, "uncodeTool")
26 
27     def refesh_db(self, encode_db_path):
28         """
29         再解密数据库并读取
30         encode_db_path : agent上加密的数据库位置
31         :return:
32         """
33 
34         ret = exec_cmd(r"cd %s && uncode.bat %s" % (self.uncode_path, encode_db_path))
35         time.sleep(2)
36         if self.cursor:
37             self.cursor.close()
38         if self.conn:
39             self.conn.close()
40 
41         self.conn = sqlite3.connect(os.path.join(self.uncode_path, "plaintext.db"))
42         self.cursor = self.conn.cursor()
43 
44     def close(self):
45         """
46         关闭数据库连接
47         :return:
48         """
49         if self.cursor:
50             self.cursor.close()
51         if self.conn:
52             self.conn.close()
53 
54     def search_by_sql(self, sql):
55         """
56         通过sql语句进行查询
57         :param sql:
58         :return:
59         """
60         try:
61             self.cursor.execute(sql)
62         except Exception:
63             self.refesh_db(self.encode_db_path)
64             self.cursor.execute(sql)
65         return self.cursor.fetchall()
66 
67 
68 if __name__ == "__main__":
69     am = agent_db_manage()
70     db_path = r"C:\ProgramData\Sangfor\EDR\PDFLink\sfedomain.db"
71     sql = r"select count(*) from process"
72 
73     am.refesh_db(db_path)
74     ret = am.search_by_sql(sql)
75     print ret
76     am.close()