Data Operations with SQLite Database via Python
insights Stats
SQLite is one of the most commonly used embedded file databases. All the mainstream programming language/framework provides APIs to interact with SQLite database. In my previous article SQLite in .NET Core with Entity Framework Core, code snippet is provided to interact with SQLite via Microsoft Entity Framework. In this article, I'm going to show you how to perform data operations through Python.
Prerequisites
Package sqlite3 is pre-built with Python distribution thus there is no need to install it.
You can also use other wrapper Python packages listed in PyPi.
Data operations with SQLite database
Establish connection
Import sqlite3 package and then create a database connection:
import sqlite3 conn = sqlite3.connect('example.sqlite')
If the database file doesn't exist, it will be created automatically.
This connection object will be used in the following code snippets.
Create table
Use CREATE TABLE command to create a table:
conn.execute('''CREATE TABLE Customer (ID int, Name text, Age int)''')
The above SQL statement create a table named Customer.
Check table details
Use system tables to check the tables in the database:
cursor = conn.execute('''select * from sqlite_master''') print(cursor.fetchall()) cursor.close()
The above code create a cursor using execute function and then fetch all the records back.
The output looks like the following:
[('table', 'Customer', 'Customer', 2, 'CREATE TABLE Customer (ID int, Name text, Age int)')]
Insert data into table
Use INSERT statement to insert data into the table.
conn.execute("INSERT INTO Customer VALUES (1,'Customer 1',30)")
Verify the result using:
print(conn.execute("select * from Customer").fetchall())
The output looks like this:
[(1, 'Customer 1', 30)]
Insert multiple records
You can insert multiple records from Python array into the table:
customers = [(2,'Customer 2',20), (3,'Customer 3', 25)] conn.executemany('INSERT INTO Customer VALUES (?,?,?)', customers) print(conn.execute("select * from Customer").fetchall())
The output now has three records:
>>> print(conn.execute("select * from Customer").fetchall()) [(1, 'Customer 1', 30), (2, 'Customer 2', 20), (3, 'Customer 3', 25)]
Update record
User UPDATE statement to update records:
conn.execute("Update Customer set Name='Customer Two' where ID =2") print(conn.execute("select * from Customer").fetchall())
Output:
[(1, 'Customer 1', 30), (2, 'Customer Two', 20), (3, 'Customer 3', 25)]
Delete record
Similarly, DELETE command can be used to delete record:
# Delte record conn.execute("delete from Customer where ID =3") print(conn.execute("select * from Customer").fetchall())
Output:
[(1, 'Customer 1', 30), (2, 'Customer Two', 20)]
* Record 3 is deleted successfully.
Drop table if exists
Use DROP TABLE statement to drop it if it exists:
# Drop table if exists conn.execute('''DROP TABLE if exists Customer''')
Commit changes
Call commit function to commit changes in the current transaction:
conn.commit()
Close connection
Remember to close cursor and connection:
conn.close()
Full API reference
Refer to the official documentation about all the APIs available to use:
View SQLite database
You can also use GUI tools like DB Browser for SQLite to view the content of database or run queries: