#!/usr/bin/python
#-*-Coding:utf-8-*-
Import Sqlite3
Import OS
Class Sqltest:
"SQLite Database Interface"
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 (' hdd above: [{}].format (path) ')
Else
Self.conn = Sqlite3.connect (': Memory: ')
If Self.verbose:
Print (' Ram above: [: Memory:] ')
def setverbose (self,b):
Self.verbose = b
def createtable (Self,sql):
"CREATE DATABASE Table"
If SQL is not None and SQL! = ':
CU = Self.conn.cursor ()
If Self.verbose:
Print (' Execute sql:[{}] '. Format (SQL)
Cu.execute (SQL)
Self.conn.commit ()
If Self.verbose:
Print (' Create database table succeeded! ‘)
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 (' Insert database table succeeded! ‘)
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 (' Execute 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):
"Delete database table test"
Print (' Delete database table test ... ')
Db.droptable (table)
def create_table_test (SQL):
' Create database table test '
Print (' Create database table Test ... ')
Create_table_sql = "' CREATE table IF not EXISTS ' table1 ' (
' ID ' integer (+) is not NULL,
' name ' nvarchar (+) is not NULL,
PRIMARY KEY (' id ')
)‘‘‘
Sql.createtable (Create_table_sql)
def insert_test (SQL):
"Insert data Test ..."
Print (' Insert data test ... ')
Insert_sql = ' INSERT into table1 values (?,?) '
data = [(1, ' AAA '), (2, ' BBB ')]
Sql.insert (Insert_sql,data)
def fetchall_test (SQL):
"Query all Data"
Print (' Query all data ... ')
Fetchall_sql = ' SELECT * FROM table1 '
R = Sql.fetchall (fetchall_sql)
For e in range (Len (r)):
Print (R[e])
def fetchone_test (SQL):
"Query a piece of data"
Print (' Query a piece of data ... ')
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):
"There are several tables"
Print (' There are several tables ... ')
Print (sql.query_table (db))
def update_test (SQL):
"Update Data"
Print (' Update data ... ')
Update_sql = ' UPDATE table1 SET name =? WHERE id =? '
data = [(' TestA ', 1), (' Testb ', 2)]
Sql.updata (Update_sql,data)
def delete_test (db):
"Delete Data"
Print (' delete data ... ')
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)
#==================================================================================
Test results
Application of Python script sqlite3 module
