SQLite3 database operations related to Python development [connection, query, insert, update, delete, close, etc.], pythonsqlite3

SQLite3 database operations related to Python development [connection, query, insert, update, delete, close, etc.], pythonsqlite3

This article describes how to develop a SQLite3 database using Python. We will share this with you for your reference. The details are as follows:

'''Sqlite database is a very small embedded open source database software. That is to say, there is no independent maintenance process, and all maintenance comes from the program itself. In python, sqlite3 is used to create a database connection. When the specified database file does not exist, the connection object will automatically create a database file. If the database file already exists, the connection object will not create a database file, but will open the database file directly. The connection object can be a database file on the hard disk or a database file created in the memory. After the database in the memory performs any operation, no transaction needs to be committed (commit) created on the hard disk: conn = sqlite3.connect ('C: \ test. db') is created on the memory: conn = sqlite3.connect ('"memory:') the following example shows how to create a database file on a hard disk: conn = sqlite3.connect ('C: \ test \ hongten. db') where the conn object is the database link object, for the database link object, it has the following operations: commit () -- transaction commit rollback () -- transaction rollback close () -- close a database link cursor () -- create a cursor cu = conn. cursor () Then we create a cursor object: cu IN sqlite3, the execution of all SQL statements must complete the cursor object cu with the participation of the cursor object, execute () -- execute an SQL statement executemany () -- execute multiple SQL statements close () -- The cursor closes fetchone () -- fetch a record fetchall () from the result -- fetch multiple records from the result fetchall () -- retrieve all records from the result scroll () -- cursor scroll '''

Below is my demo. In the demo, I made a detailed demonstration of annotations and functions. The details are as follows:

WhenSHOW_ SQL = FalseWhen:

Python 3.3.2 (v3.3.2: d047928ae3f6, May 16 2013, 00:03:43) [MSC v.1600 32 bit (Intel)] on win32Type "copyright", "credits" or "license () "for more information. >>> ================================== RESTART ======== ==========================================>>> show_ SQL: false: delete database table test... hard Disk: [c: \ test \ hongten. db] succeeded in deleting the database table [student! Database Table creation test... hard disk above: [c: \ test \ hongten. db] database table creation [student] successful! Save data test... hard Disk: [c: \ test \ hongten. db] query all data... hard Disk: [c: \ test \ hongten. db] (1, 'hongten ', 'male', 20, 'guangzhou City, Guangdong Province, '2017 *** 62') (2, 'Tom ', 'male ', 22, 'san Francisco, '1970 ***** 63 ') (3, 'jar', 'female, 18, 'guangzhou, Guangdong Province ', '2014*87 ') (4, 'cate', 'female', 21, 'guangzhou City, Guangdong Province ', '2014*32 ') ######################################## ######### query a piece of data... hard Disk: [c: \ test \ hongten. db] (1, 'hongten ', 'male', 20, 'guangzhou City, Guangdong Province', '2017 *** 62 ') ######################################## ######### update data... hard Disk: [c: \ test \ hongten. db] query all data... hard Disk: [c: \ test \ hongten. db] (1, 'hongtena', 'male', 20, 'guangzhou City, Guangdong Province, '2017 *** 62 ') (2, 'hongtenbb', 'male ', 22, 'san Francisco, '2017 *** 63 ') (3, 'hongtencc', 'female, 18, 'guangzhou, Guangdong Province ', '1970 * *** 87 ') (4, 'hongtendd', 'female', 21, 'guangzhou City, Guangdong Province ', '1970 * *** 32 ') ######################################## ######### delete data... hard Disk: [c: \ test \ hongten. db] query all data... hard Disk: [c: \ test \ hongten. db] (2, 'hongtenbb ', 'male', 22, 'san Francisco,' 15423 *** 63 ') (4, 'hongtendd', 'female ', 21, 'guangzhou City, Guangdong Province ', '2017 ***** 32')>

WhenSHOW_ SQL = TrueWhen:

Python 3.3.2 (v3.3.2: d047928ae3f6, May 16 2013, 00:03:43) [MSC v.1600 32 bit (Intel)] on win32Type "copyright", "credits" or "license () "for more information. >>> ================================== RESTART ======== ==========================================>>> show_ SQL: true delete database table test... hard Disk: [c: \ test \ hongten. db] run SQL: [DROP TABLE IF EXISTS student] The database TABLE is deleted [student] successfully! Create a database table for testing... hard Disk: [c: \ test \ hongten. db] run SQL: [CREATE TABLE 'student '('id' int (11) NOT NULL, 'name' varchar (20) NOT NULL, 'gender' varchar (4) default null, 'age' int (11) default null, 'address' varchar (200) default null, 'phone' varchar (20) default null, primary key ('id')] the database table [student] is created successfully! Save data test... on the hard disk: [c: \ test \ hongten. db] run SQL: [INSERT INTO student values (?, ?, ?, ?, ?, ?)], Parameter: [(1, 'hongten ', 'male', 20, 'guangzhou City, Guangdong Province', '2017 ***** 62 ')] Run the SQL statement: [insert into student values (?, ?, ?, ?, ?, ?)], Parameter: [(2, 'Tom ', 'male', 22, 'san Francisco,' 15423 *** 63 ')] Run the SQL statement: [insert into student values (?, ?, ?, ?, ?, ?)], Parameter: [(3, 'jar', 'female ', 18, 'guangzhou City, Guangdong Province, '2017 ***** 87')] Run the SQL statement: [insert into student values (?, ?, ?, ?, ?, ?)], Parameter: [(4, 'cate', 'female ', 21, 'guangzhou City, Guangdong Province, '2017 ***** 32')] query all data... hard Disk: [c: \ test \ hongten. db] Execute SQL: [SELECT * FROM student] (1, 'hongten ', 'male', 20, 'guangzhou City, Guangdong Province, '2017 *** 62 ') (2, 'Tom ', 'male', 22, 'san Francisco,' 15423 *** 63 ') (3, 'jar', 'female, 18, 'guangzhou City, Guangdong Province ', '2014*87') (4, 'cate', 'female ', 21, 'guangzhou City, Guangdong Province ', '1970*32 ') ######################################## ######### query a piece of data... hard Disk: [c: \ test \ hongten. db] run SQL: [SELECT * FROM st Udent where id =? ], Parameter: [1] (1, 'hongten ', 'mal', 20, 'guangzhou City, Guangdong Province', '2017 *** 62 ') ######################################## ######### update data... hard Disk: [c: \ test \ hongten. db] run SQL: [UPDATE student SET name =? Where id =? ], Parameter: [('hongtena', 1)] run SQL: [UPDATE student SET name =? Where id =? ], Parameter: [('hongtenbb ', 2)] run SQL: [UPDATE student SET name =? Where id =? ], Parameter: [('hongtencc', 3)] run SQL: [UPDATE student SET name =? Where id =? ], Parameter: [('hongtendd', 4)] query all data... hard Disk: [c: \ test \ hongten. db] run SQL: [SELECT * FROM student] (1, 'hongtena', 'male', 20, 'guangzhou City, Guangdong Province, '2017 *** 62 ') (2, 'hongtenbb ', 'male', 22, 'san Francisco, USA', '2017 *** 63 ') (3, 'hongtencc', 'female', 18, 'guangzhou City, Guangdong Province ', '2014*87') (4, 'hongtendd', 'female ', 21, 'guangzhou City, Guangdong Province ', '1970*32 ') ######################################## ######### delete data... hard Disk: [c: \ test \ hongten. db] run SQL: [DELETE FROM student WHERE NAME =? And id =? ], Parameter: [('hongtena', 1)] run SQL: [DELETE FROM student WHERE NAME =? And id =? ], Parameter: [('hongtencc', 3)] query all data... hard Disk: [c: \ test \ hongten. db] run SQL: [SELECT * FROM student] (2, 'hongtenbb ', 'male', 22, 'san Francisco, '2017 *** 63 ') (4, 'hongtendd', 'female ', 21, 'guangzhou City, Guangdong Province', '2017 ***** 32')>

Code:

# Python sqlite # Author: Hongten # Create: 2013-08-09 # Version: 1.0 # DB-API 2.0 interface for SQLite databasesimport sqlite3import OS ''' SQLite database is a very small embedded open source database software, that is to say, there is no independent maintenance process, and all maintenance comes from the program itself. In python, sqlite3 is used to create a database connection. When the specified database file does not exist, the connection object will automatically create a database file. If the database file already exists, the connection object will not create a database file, but will open the database file directly. The connection object can be a database file on the hard disk or a database file created in the memory. After the database in the memory performs any operation, no transaction needs to be committed (commit) created on the hard disk: conn = sqlite3.connect ('C: \ test. db') is created on the memory: conn = sqlite3.connect ('"memory:') the following example shows how to create a database file on a hard disk: conn = sqlite3.connect ('C: \ test \ hongten. db') where the conn object is the database link object, for the database link object, it has the following operations: commit () -- transaction commit rollback () -- transaction rollback close () -- close a database link cursor () -- create a cursor cu = conn. in this way, we create a cursor object: cu IN sqlite3 Execution must be completed with the participation of the cursor object. For the cursor object cu, perform the following operations: execute () -- execute an SQL statement executemany () -- execute multiple SQL statements close () -- cursor close fetchone () -- fetch a record from the result fetchall () -- fetch multiple records from the result fetchall () -- retrieve all records from the result scroll () -- cursor scrolling ''' # global var # database file exclusive path DB_FILE_PATH = ''# table name TABLE_NAME ='' # Whether to print sqlSHOW_ SQL = Truedef get_conn (path ): ''' gets the connection object of the database. The parameter is the absolute path of the database file. If the passed parameter exists and is a file, then, the connection object of the database file in the above path of the hard disk is returned. Otherwise, the connection object '''conn = sqlite3.con in the memory is returned. Nect (path) if OS. path. exists (path) and OS. path. isfile (path): print ('hard disk top: [{}] '. format (path) return conn else: conn = None print ('memory top: [: memory:] ') return sqlite3.connect (': memory: ') def get_cursor (conn ): ''' this method is used to obtain the database cursor object. The parameter is the database connection object. If the database connection object is not None, the cursor object created by the database connection object is returned; otherwise, a cursor object is returned, which is the ''if conn is not None: return conn. cursor () else: return get_conn (''). cursor ()############### ######################################## ########### Create | delete a table START ###################### ######################################## # def drop_table (conn, table): ''' if the table exists, delete the table. If the table contains data, use this method with caution! '''If table is not None and table! = '': SQL = 'drop table if exists' + table if SHOW_ SQL: print ('execute SQL: [{}] '. format (SQL) cu = get_cursor (conn) cu.exe cute (SQL) conn. commit () print ('deleting database table [{}] successful! '. Format (table) close_all (conn, cu) else: print (' the [{}] is empty or equal None! '. Format (SQL) def create_table (conn, SQL): ''' create a database table: student ''' if SQL is not None and SQL! = '': Cu = get_cursor (conn) if SHOW_ SQL: print ('execution SQL: [{}] '. format (SQL) cu.exe cute (SQL) conn. commit () print ('database table [student] created successfully! ') Close_all (conn, cu) else: print (' the [{}] is empty or equal None! '. Format (SQL )) ######################################## ############################ create | delete a table ####### ######################################## ############### def close_all (conn, cu): ''' closes the database cursor object and database connection object ''' try: if cu is not None: cu. close () finally: if cu is not None: cu. close () ######################################## ########################### database operation crud start ######### ############################## ######################## Def save (conn, SQL, data ): '''insert data ''' if SQL is not None and SQL! = '': If data is not None: cu = get_cursor (conn) for d in data: if SHOW_ SQL: print ('execution SQL: [{}], parameter: [{}] '. format (SQL, d) cu.exe cute (SQL, d) conn. commit () close_all (conn, cu) else: print ('the [{}] is empty or equal None! '. Format (SQL) def fetchall (conn, SQL): ''' query all data ''' if SQL is not None and SQL! = '': Cu = get_cursor (conn) if SHOW_ SQL: print ('execution SQL: [{}] '. format (SQL) cu.exe cute (SQL) r = cu. fetchall () if len (r)> 0: for e in range (len (r): print (r [e]) else: print ('the [{}] is empty or equal None! '. Format (SQL) def fetchone (conn, SQL, data): ''' query a piece of data ''' if SQL is not None and SQL! = '': If data is not None: # Do this instead d = (data,) cu = get_cursor (conn) if SHOW_ SQL: print ('run SQL: [{}], parameter: [{}] '. format (SQL, data) cu.exe cute (SQL, d) r = cu. fetchall () if len (r)> 0: for e in range (len (r): print (r [e]) else: print ('the [{}] equal None! '. Format (data) else: print (the [{}] is empty or equal None! '. Format (SQL) def update (conn, SQL, data): ''' update data ''' if SQL is not None and SQL! = '': If data is not None: cu = get_cursor (conn) for d in data: if SHOW_ SQL: print ('execution SQL: [{}], parameter: [{}] '. format (SQL, d) cu.exe cute (SQL, d) conn. commit () close_all (conn, cu) else: print ('the [{}] is empty or equal None! '. Format (SQL) def delete (conn, SQL, data): ''' delete data ''' if SQL is not None and SQL! = '': If data is not None: cu = get_cursor (conn) for d in data: if SHOW_ SQL: print ('execution SQL: [{}], parameter: [{}] '. format (SQL, d) cu.exe cute (SQL, d) conn. commit () close_all (conn, cu) else: print ('the [{}] is empty or equal None! '. Format (SQL )) ######################################## ########################### database operation crud end ######### ######################################## ######################################## ######################################## # test operation START #################################### ########################### def drop_table_test (): '''test database table deletion ''' print ('test database table deletion... ') conn = get_conn (DB_FILE_PATH) drop_table (co Nn, TABLE_NAME) def create_table_test (): ''' create database table test ''' print ('create database table test... ') create_table_ SQL = '''create table' student' ('id' int (11) NOT NULL, 'name' varchar (20) NOT NULL, 'gender' varchar (4) default null, 'age' int (11) default null, 'address' varchar (200) default null, 'phone' varchar (20) default null, primary key ('id') ''' conn = get_conn (DB_FILE_PATH) create_table (conn, create_table_ SQL) de F save_test (): ''' save data test... ''' print ('Save data test... ') save_ SQL = ''' INSERT INTO student values (?, ?, ?, ?, ?, ?) '''Data = [(1, 'hongten ', 'mal', 20, 'guangzhou City, Guangdong Province', '2017 *** 62 '), (2, 'Tom ', 'male', 22, 'san Francisco, '1970 *** 63'), (3, 'jace', 'female, 18, 'guangzhou City, Guangdong Province ', '2014*87'), (4, 'cate', 'female ', 21, 'guangzhou City, Guangdong Province ', '2014*32 ')] conn = get_conn (DB_FILE_PATH) save (conn, save_ SQL, data) def fetchall_test (): ''' query all data... '''print ('query all data... ') fetchall_ SQL = ''' SELECT * FROM student ''' conn = get_conn (DB_FILE_PATH) fetchall (conn, Fetchall_ SQL) def fetchone_test (): ''' query a piece of data... '''print ('queries a piece of data... ') fetchone_ SQL = 'select * FROM student WHERE ID =? 'Data = 1 conn = get_conn (DB_FILE_PATH) fetchone (conn, fetchone_ SQL, data) def update_test (): ''' update data... '''print ('Update data... ') update_ SQL = 'Update student SET name =? Where id =? 'Data = [('hongtena', 1), ('hongtenbb', 2), ('hongtencc', 3), ('hongtendd', 4)] conn = get_conn (DB_FILE_PATH) update (conn, update_ SQL, data) def delete_test (): ''' delete data... '''print ('delete data... ') delete_ SQL = 'delete FROM student WHERE NAME =? And id =? 'Data = [('hongtena', 1), ('hongtencc', 3)] conn = get_conn (DB_FILE_PATH) delete (conn, delete_ SQL, data) ######################################## ########################### test operation END ########## ######################################## ############ def init (): '''initialization method''' # database file layout path: global DB_FILE_PATH = 'C: \ test \ hongten. db' # database table name global TABLE_NAME = 'student '# Whether to print SQL global SHOW_ SQL = True print ('show _ SQL :{}'. format (SHOW_ SQL) # If a database table exists, delete the table drop_table_test () # create a database table student create_table_test () # insert data into the database table save_test () def main (): init () fetchall_test () print ('#' * 50) fetchone_test () print ('#' * 50) update_test () fetchall_test () print ('#' * 50) delete_test () fetchall_test () if _ name _ = '_ main _': main ()