Working with SQLite in Python

Applications requiring some sort of data management capabilities would have to work with a database. Python has a great support for working with databases. Python standards for database interfaces is Database API specification which is a common interface that is required to be implemented by all Python modules that allow access to relational databases. With this DB API standard, the code in Python for communicating with a database would be the same, regardless of the database and the database module used.

In this article, we will take a look at how to perform CRUD operations with a SQLite database from Python.

What is SQLite?

SQLite is a very fast, simple relational database system developed using C language. Here is a definition of SQLite database from the SQLite website:

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.

How to create a SQLite database?

Python already comes with a module called sqlite3 to interface with SQLite databases. To create a database called books, we need to open a connection to the books.db like below. Below command either opens the books database if it already exists or creates a new one if it is not already there.

import sqlite3
connection = sqlite3.connect("books.db")

How to create a table in SQLite database?

The above command created an empty database. To store actual data, we need to create a table in the database. Cursor object is used to perform all SQL operations in Python. Here is the complete listing of create_books.py that creates books database along with an empty book table in SQLite db.

import sqlite3
connection = sqlite3.connect("books.db")

# used as an interface to execute all SQL commands
cursor = connection.cursor()

# delete (uncomment below statement if table needs to be recreated)
# cursor.execute("""DROP TABLE book;""")

#SQL commands could be defined using triple quoted string
sql_command = """
CREATE TABLE book (
book_id INTEGER PRIMARY KEY,
book_title VARCHAR(200),
author VARCHAR(100),
publication_year INTEGER);"""

#Execute command performs the actual SQL operation
cursor.execute(sql_command)

#Close connection at the end of every database use
connection.close() 

How to insert data into a SQLite database?

Below python file could be used to insert books into the book table.

import sqlite3

connection = sqlite3.connect("books.db")
cursor = connection.cursor()

book_data = [ ("Automate the Boring Stuff with Python: Practical Programming for Total Beginners", "Al Sweigart", "2015"),
               ("Python for Everybody: Exploring Data in Python 3", "Dr. Charles Russell Severance, Sue Blumenberg, Elliott Hauser, Aimee Andrion", "2016"),
               ("Dive Into Python 3", "Mark Pilgrim", "2012"),
               ("Test Book", "Test Author", "2020"), ]

for row in book_data:
    format_str = """INSERT INTO book (book_id, book_title, author, publication_year)
    VALUES (NULL, "{name}", "{author}", "{pub_year}");"""

    sql_command = format_str.format(name=row[0], author=row[1], pub_year=row[2])
    cursor.execute(sql_command)

connection.commit()
connection.close()    

• As we created the book_id column using book_id INTEGER PRIMARY KEY, SQLite will auto generate sequential id values, we don't need to populate book_id while inserting data.
• Please note that connection.commit() command needs to be executed at the end of data inserts/updates/deletes for the data to be permanently stored in the database.

How to view data stored in a table?

The fetchall() method below is used to get all of the rows from the table while fetchone() method could be used to get just the first row of a table.

import sqlite3

connection = sqlite3.connect("books.db")
cursor = connection.cursor()

cursor.execute("SELECT * FROM book") 

print("Book list:")               
results = cursor.fetchall()
for row in results:
    print(row)

connection.close()

How to update data in a SQLite database?

Below commands could be used to update the author of the book with id 4.

import sqlite3

connection = sqlite3.connect("books.db")
cursor = connection.cursor()

sql_command = """UPDATE book SET author = 'Author' WHERE book_id = 4;"""

cursor.execute(sql_command)
connection.commit()

connection.close()

How to delete data from a SQLite database?

Deletion could be performed in a similar manner with a delete SQL command like shown below:

import sqlite3

connection = sqlite3.connect("books.db")
cursor = connection.cursor()

sql_command = """DELETE FROM book WHERE book_id = 4;"""

cursor.execute(sql_command)
connection.commit()

connection.close()

References

• Click here To learn more about SQLite.
• Please visit Python Database API to know more about API specification.