標籤：
#!/usr/bin/python
# -*- coding:utf-8 -*-
import sqlite3
import os
class SQLTest:
‘‘‘sqlite資料庫介面‘‘‘
def __init__(self,path=‘‘,verbose=False):
self.verbose = verbose
self.path = path
if os.path.isfile(path):
self.conn = sqlite3.connect(path)
if self.verbose:
print(‘硬碟上面:[{}].format(path)‘)
else:
self.conn = sqlite3.connect(‘:memory:‘)
if self.verbose:
print(‘記憶體上面:[:memory:]‘)
def setverbose(self,b):
self.verbose = b
def createtable(self,sql):
‘‘‘建立資料庫表‘‘‘
if sql is not None and sql != ‘‘:
cu = self.conn.cursor()
if self.verbose:
print(‘執行sql:[{}]‘.format(sql))
cu.execute(sql)
self.conn.commit()
if self.verbose:
print(‘建立資料庫表成功！‘)
self.close_all(cu)
else:
print(‘The [{}] is empty or equal None!‘.format(sql))
def querytable(self):
sql = ‘SELECT name FROM sqlite_master WHERE type="table" ORDER BY name‘
cu = self.conn.cursor()
cu.execute(sql)
return cu.fetchall()
def renametable(self,table,newtable):
if table is not None and talbe !=‘‘:
sql = ‘ALTER TABLE %s RENAME TO "%s" ‘ % (table,newtable)
cu = self.conn.cursor()
cu.execute(sql)
self.conn.commit()
print(‘delete table sucess!‘)
self.close_all(cu)
else:
print(‘The [{}] is empty or equal None!‘.format(sql))
def insert(self,sql,data):
if sql is not None and sql != ‘‘:
if data is not None:
cu = self.conn.cursor()
for d in data:
cu.execute(sql,d)
self.conn.commit()
if self.verbose:
print(‘插入資料庫表成功！‘)
self.close_all(cu)
else:
print(‘The [{}] is empty or equal None!‘.format(sql))
def fetchall(self,sql):
if sql is not None and sql != ‘‘:
cu = self.conn.cursor()
if self.verbose:
print(‘執行sql:[{}]‘.format(sql))
cu.execute(sql)
return cu.fetchall()
else:
print(‘The [{}] is empty or equal None!‘.format(sql))
def fetchone(self,sql,data):
if sql is not None and sql != ‘‘:
if data is not None:
cu = self.conn.cursor()
cu.execute(sql,(data,))
return cu.fetchall()
else:
print(‘The [{}] is None!‘.format(data))
else:
print(‘The [{}] is empty or equal None!‘.format(sql))
def updata(self,sql,data):
if sql is not None and sql != ‘‘:
if data is not None:
cu = self.conn.cursor()
for d in data:
cu.execute(sql,d)
self.conn.commit()
self.close_all(cu)
else:
print(‘The [{}] is empty or equal None!‘.format(sql))
def rowcount(self,table):
sql = ‘select count (*) from "%s"‘ % table
cu = self.conn.cursor()
r = cu.execute(sql)
return (r.fetchone()[0])
def delete(self,sql,data):
if sql is not None and sql != ‘‘:
if data is not None:
cu = self.conn.cursor()
for d in data:
cu.execute(sql,d)
self.conn.commit()
self.close_all(cu)
else:
print(‘The [{}] is empty or equal None!‘.format(sql))
def droptable(self,table):
if table is not None and table != ‘‘:
sql = ‘DROP TABLE IF EXISTS ‘ + table
cu = self.conn.cursor()
cu.execute(sql)
self.conn.commit()
print(‘delete table sucess!‘)
self.close_all(cu)
else:
print(‘The [{}] is empty or equal None!‘.format(sql))
def close_all(self,cu):
try:
if cu is not None:
cu.close()
finally:
if cu is not None:
cu.close()
# function
def drop_table_test(sql,table):
‘‘‘刪除資料庫表測試‘‘‘
print(‘刪除資料庫表測試 ...‘)
db.droptable(table)
def create_table_test(sql):
‘‘‘建立資料庫表測試‘‘‘
print(‘建立資料庫表測試 ...‘)
create_table_sql = ‘‘‘CREATE TABLE IF NOT EXISTS ‘table1‘(
‘id‘ integer(32) NOT NULL,
‘name‘ nvarchar(128) NOT NULL,
PRIMARY KEY(‘id‘)
)‘‘‘
sql.createtable(create_table_sql)
def insert_test(sql):
‘‘‘插入資料測試 ...‘‘‘
print(‘插入資料測試 ...‘)
insert_sql = ‘INSERT INTO table1 values(?,?)‘
data = [(1,‘aaa‘),(2,‘bbb‘)]
sql.insert(insert_sql,data)
def fetchall_test(sql):
‘‘‘查詢所有資料‘‘‘
print(‘查詢所有資料 ...‘)
fetchall_sql = ‘SELECT * FROM table1‘
r = sql.fetchall(fetchall_sql)
for e in range(len(r)):
print(r[e])
def fetchone_test(sql):
‘‘‘查詢一條資料‘‘‘
print(‘查詢一條資料 ...‘)
fetchall_sql = ‘SELECT * FROM table1 WHERE id = ?‘
data = 1
r = sql.fetchone(fetchall_sql,data)
for e in range(len(r)):
print(r[e])
def query_test(sql):
‘‘‘有幾個表‘‘‘
print(‘有幾個表 ...‘)
print(sql.query_table(db))
def update_test(sql):
‘‘‘更新資料‘‘‘
print(‘更新資料 ...‘)
update_sql = ‘UPDATE table1 SET name = ? WHERE id = ?‘
data = [(‘TestA‘,1),(‘TestB‘,2)]
sql.updata(update_sql,data)
def delete_test(db):
‘‘‘刪除資料‘‘‘
print(‘刪除資料 ...‘)
delete_sql = ‘DELETE FROM table1 WHERE name = ? and id = ?‘
data = [(‘TestA‘,1)]
sql.delete(delete_sql,data)
# self test
if __name__ == ‘__main__‘:
sql = SQLTest(verbose=True)
#建立資料庫表
create_table_test(sql)
#插入資料
insert_test(sql)
#查詢多條資料
fetchall_test(sql)
#查詢一條資料
fetchone_test(sql)
#更新資料
update_test(sql)
#查詢多條資料
fetchall_test(sql)
#刪除一條資料
delete_test(sql)
print(sql.rowcount(‘table1‘))
#查詢多條資料
fetchall_test(sql)
#==================================================================================
測試結果
python指令碼sqlite3模組的應用
