python 之 sqlite3

 1 # -*- coding: utf-8 -*-
 2 ''' 創建資料庫日誌，三列為時間 身份證號和備註名'''
 3 import os 
 4 import sys 
 5 import sqlite3
 6 import datetime 
 7 
 8 
 9 class sqlite3_log():
10     
11     def __init__(self):
12         pass
13     
14     #連接表,表名為參
15     def connect_db(self,db_name): 
16         conn = sqlite3.connect(db_name) 
17         return conn
18       
19     #關閉連接,表名為參
20     def close_db(self,conn): 
21         conn.close() 
22 
23      #建立一個表 表名應該使用參數確定
24     def create_table(self,conn,table_name): 
25         cu = conn.cursor() 
26         cu.execute('''create table if not exists %s
27                     (time_now     text   primary key   not null,
28                      id_card    float          not null,
29                      name      text           not null);''' %table_name)
30         conn.commit()
31         
32     #插入數據
33     def insert_values(self, table_name, datetime, idcard, name):
34         cu = conn.cursor()
35         cu.execute("insert into %s values ( ?, ?, ?)" %table_name, (datetime, idcard, name))
36         conn.commit() 
37       
38       
39     #查詢表是否存在
40     def query_table(conn,db_name,table_name): 
41         cu = conn.cursor() 
42         cu.execute("select %s from %s where type = 'table' " %db_name, table_name )
43         return cu.fetchall()
44     
45     #邏輯流程
46     def run_log(self, db_name, table_name, tim, idcard, name):
47         global conn
48         #檢測資料庫表是否存在
49         if os.path.exists(db_name):
50             print "the table is exists"
51             conn = sqlite3.connect(db_name)
52         
53         else:
54             print "######"
55             conn = self.connect_db(db_name)
56             
57         #檢測表是否存在並創建表
58         print "create table if not exists"
59         self.create_table(conn, table_name)
60         print "create table-conn"
61         #a = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
62         print "insert finished"
63         self.insert_values(table_name, tim, idcard, name)
64         #關閉連接
65         self.close_db(conn)
66          
67   
68 def test():
69     #測試代碼
70     db_name = './Peeer.db'  
71     a = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
72     g = "wei"
73     #print time1
74     sq = sqlite3_log()
75     conn = sq.connect_db(db_name)
76     #sq.create_table(conn, "talbe_sqlite")
77     
78     sq.insert_values("talbe_sqlite" , a , "05411722444554148763", g)
79     sq.insert_values("talbe_sqlite" , a, "01444122274444115", "guo")
80     sq.close_db(conn)
81 
82 if __name__ == '__main__':
83     #測試邏輯流程
84     sq = sqlite3_log()
85     a = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
86     sq.run_log('./Peeeeer.db', "talbe_sqlite", a, "0144417722274444115", "guo")

　　此代碼已經測試，可以直接調用此類把數據寫入。