#! Usrbinpython #-*-coding: UTF-8-*-importsqlite3importosclassSQLTest: sqlite database interface def _ init _ (self, path, verboseFalse): self. verboseverboseself. pathpathifos. path. isfile (path): self. connsqlite3.connect (p
#! /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 (p
#! /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 ('hard disk top: [{}]. format (path )')
Else:
Self. conn = sqlite3.connect (': memory :')
If self. verbose:
Print ('memory top: [: 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 ('execution SQL: [{}] '. format (SQL ))
Cu.exe cute (SQL)
Self. conn. commit ()
If self. verbose:
Print ('database table created successfully! ')
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.exe cute (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.exe cute (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.exe cute (SQL, d)
Self. conn. commit ()
If self. verbose:
Print ('database table inserted successfully! ')
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 ('execution SQL: [{}] '. format (SQL ))
Cu.exe cute (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.exe cute (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.exe cute (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.exe cute (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.exe cute (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.exe cute (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 ):
'''Test database table deletion '''
Print ('test the database table deletion ...')
Db. droptable (table)
Def create_table_test (SQL ):
'''Test database table creation '''
Print ('test the database table creation ...')
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 ):
'''Test data insertion ...'''
Print ('test data insertion ...')
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 ):
'''Several tables '''
Print ('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)
# Creating database tables
Create_table_test (SQL)
# Insert data
Insert_test (SQL)
# Querying multiple data entries
Fetchall_test (SQL)
# Querying a piece of data
Fetchone_test (SQL)
# Update data
Update_test (SQL)
# Querying multiple data entries
Fetchall_test (SQL)
# Deleting a piece of data
Delete_test (SQL)
Print (SQL. rowcount ('table1 '))
# Querying multiple data entries
Fetchall_test (SQL)
#===================================================== ========================================================== ===
Test Results
