In the previous article How To Manage SQLite Database In Python we have learned how to connect, manage SQLite database in python. In this article, I will tell you how to execute insert, delete, update and query statements to SQLite database in Python.
1. Python Insert One Row Into SQLite Table Example.
- You can call the python cursor object’s execute method to insert one row into SQLite table.
import sqlite3 # The global varialbes save db name and table name. db_name = 'test-sqlite.db' table_name = 'user_account' # This method will insert one row to sqlite table. def execute_insert(stmt_str, value_tuple): # Connect to db to get db connection. conn = sqlite3.connect(db_name) # Get db cursor. cursor = conn.cursor() # Execute insert statement. cursor.execute(stmt_str, value_tuple) # Commit with the db connection object. conn.commit() # Get the inserted row id. last_row_id = cursor.lastrowid print('last_row_id: ' + str(last_row_id)) # Get the inserted row count. insert_row_count = cursor.rowcount # Should use str() to convert the int number to string, otherwise it will throw TypeError: can only concatenate str (not "int") to str. print('insert_row_count: ' + str(insert_row_count)) # Close cursor. cursor.close() # Close the db connection. conn.close() print('insert one record to SQLite DB complete.') # This method will execute DDL statement such as create table. def execute_ddl(stmt_str): conn = sqlite3.connect(db_name) cursor = conn.cursor() cursor.execute(stmt_str) conn.commit() cursor.close() conn.close() print(stmt_str + ' complete.') if __name__ == '__main__': # Create db table statement string. ddl_create_table = ''' create table ''' + table_name + '''( id integer primary key autoincrement, user_name text, passwd text, email text)''' # Create SQLite table. execute_ddl(ddl_create_table) # Insert one row in SQLite table. dml_insert = 'insert into ' + table_name + ' values( null,?,?,? )' execute_insert(dml_insert, ('jerry','12345678','jerry@gmail.com')) - Below is the above source code execution output.
create table user_account( id integer primary key autoincrement, user_name text, passwd text, email text) complete. last_row_id: 1 insert_row_count: 1 insert one record to sqlite db complete.
2. Python Insert Multiple Rows Into SQLite Table Example.
- If you want to insert multiple rows into SQLite table in one time, you can run the cursor object’s executemany method.
- You should provide the sql statement string and a tuple object that contains the insert rows data value.
import sqlite3 db_name = 'test-sqlite.db' table_name = 'user_account' ''' This function will insert multiple rows into SQLite database table. stmt_str : The insert SQL statement string. value_tuple : The multiple rows tuple object. ''' def execute_insert_many(stmt_str, value_tuple): conn = sqlite3.connect(db_name) cursor = conn.cursor() # The executemany method will insert multiple rows into SQLite table. cursor.executemany(stmt_str, value_tuple) conn.commit() last_row_id = cursor.lastrowid print('last_row_id: ' + str(last_row_id)) insert_row_count = cursor.rowcount print('insert_row_count: ' + str(insert_row_count)) cursor.close() conn.close() print('insert many records to SQLite DB complete.') def execute_ddl(stmt_str): conn = sqlite3.connect(db_name) cursor = conn.cursor() cursor.execute(stmt_str) conn.commit() cursor.close() conn.close() print(stmt_str + ' complete.') if __name__ == '__main__': insert_data_tuple = (('tom','tom123','tom@gmail.com'),('jackie','jackie123','jackie@gmail.com'),('richard','richard123','richard@gmail.com'),('hack','hack123','hack@gmail.com'),('hello','hello123','hello@gmail.com')) execute_insert_many(dml_insert, insert_data_tuple) - Below is the above source code execution output.
last_row_id: None insert_row_count: 5 insert many records to sqlite db complete.
3. Python Delete Rows From SQLite Table Example.
- The python cursor object’s execute method can execute SQL delete statement.
import sqlite3 db_name = 'test-sqlite.db' table_name = 'user_account' def execute_update(stmt_str): conn = sqlite3.connect(db_name) cursor = conn.cursor() cursor.execute(stmt_str) conn.commit() row_count = cursor.rowcount print('update or delete row count: ' + str(row_count)) cursor.close() conn.close() print(stmt_str + ' complete.') if __name__ == '__main__': dml_delte = ' delete from ' + table_name + ' where user_name = \'hack\'' execute_update(dml_delte) - Below is the above source code execution result.
update or delete row count: 1 delete from user_account where user_name = 'hack' complete.
4. Python Update Rows From SQLite Table Example.
- To update SQLite rows, we can also use the cursor object’s execute method.
import sqlite3 db_name = 'test-sqlite.db' table_name = 'user_account' def execute_update(stmt_str): conn = sqlite3.connect(db_name) cursor = conn.cursor() cursor.execute(stmt_str) conn.commit() row_count = cursor.rowcount print('update or delete row count: ' + str(row_count)) cursor.close() conn.close() print(stmt_str + ' complete.') if __name__ == '__main__': dml_update = 'update ' + table_name + ' set passwd = "hello-python" where user_name = "jerry"' execute_update(dml_update) - Below is the above source code execution result.
update or delete row count: 1 update user_account set passwd = "hello-python" where user_name = "jerry" complete.
5. Python Query Rows From SQLite Table Example.
- You should call the cursor object’s fetchone method to get one row of data in the query result.
import sqlite3 db_name = 'test-sqlite.db' table_name = 'user_account' def execute_query(stmt_str): conn = sqlite3.connect(db_name) cursor = conn.cursor() cursor.execute(stmt_str) row_count = cursor.rowcount print('search result row count: ' + str(row_count)) # Get row headers. for col in (cursor.description): # Print each row header. print(col[0], end='\t') print('\r\n------------------------------------------------') # Loop to get all rows. while True: # Fetch one row data. row = cursor.fetchone() # If the row is null the break. if not row: break # Print out the row data. print(row) cursor.close() conn.close() print(stmt_str + ' complete.') if __name__ == '__main__': dml_query = 'select * from ' + table_name + ' where id > 2' execute_query(dml_query) - Below is the execution result.
search result row count: -1 id user_name passwd email ------------------------------------------------ (3, 'jackie', 'jackie123', 'jackie@gmail.com') (4, 'richard', 'richard123', 'richard@gmail.com') (6, 'hello', 'hello123', 'hello@gmail.com') select * from user_account where id > 2 complete.
6. How To Get Query Result Row Count Number.
- From the above query example, we can see the cursor.rowcount always returns -1.
- So we can execute the SQL statement
select count(*) from ' + table_name + ' where id > 2to get the query result row count.dml_query = 'select count(*) from ' + table_name + ' where id > 2' execute_query(dml_query)
- The above source code execution result output.
search result row count: -1 count(*) ------------------------------------------------ (3,) select count(*) from user_account where id > 2 complete.
- You can also call the cursor object’s fetchall method to get the query result list, then get the list length to get the query result row count number.
import sqlite3 db_name = 'test-sqlite.db' table_name = 'user_account' def get_query_account(stmt_str): conn = sqlite3.connect(db_name) cursor = conn.cursor() cursor.execute(stmt_str) # Call the fetchall method to get all the result data in a list. row_list = cursor.fetchall() # Get the result list size to get the result row count number. row_count = len(row_list) print('row_count: ' + str(row_count)) cursor.close() conn.close() print(stmt_str + ' complete.') if __name__ == '__main__': dml_query = 'select * from ' + table_name + ' where id > 2' get_query_account(dml_query) - Below is the above source code execution result.
row_count: 3 select * from user_account where id > 2 complete.