Data Operations with SQLite Database via Python

access_time 7 months ago visibility107 comment 0

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()
infosqlite_master is a system object that has all the information of all the objects in the database incl. tables, views, etc. 

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:

SQLite 3 API References in Python

View SQLite database

You can also use GUI tools like DB Browser for SQLite to view the content of database or run queries:

info Last modified by Administrator at 3 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer python local_offer python-file-operations

visibility 1123
thumb_up 0
access_time 7 months ago

CSV is a common data format used in many applications. It's also a common task for data workers to read and parse CSV and then save it into another storage such as RDBMS (Teradata, SQL Server, MySQL). In my previous article  PySpark Read Multiple Lines Records from CSV I demonstrated how to ...

local_offer Azure local_offer python local_offer spark local_offer pyspark

visibility 7011
thumb_up 1
access_time 2 years ago

The page summarizes the steps required to run and debug PySpark (Spark for Python) in Visual Studio Code. Install Python from the official website: https://www.python.org/downloads/ . The version I am using is 3.6.4 32-bit. Pip is shipped together in this version. Download Spark 2.3.3 from ...

local_offer python local_offer spark local_offer pyspark

visibility 4554
thumb_up 0
access_time 2 years ago

When running pyspark or spark-submit command in Windows to execute python scripts, you may encounter the following error: PermissionError: [WinError 5] Access is denied As it’s self-explained, permissions are not setup correctly. To resolve this issue you can try different approaches: Run ...

About column