Python SQLite Tutorial With Examples [Latest]

Python SQLite

Python sqlite3 is an excellent module with which you can perform all possible DB operations with in-memory and persistent database in your applications.

This module implements the Python DB API interface to be a compliant solution for implementing SQL related operations in a program.

Using sqlite3 module

In this section, we will start using the sqlite3 module in our application so that we can create databases and tables inside it and perform various DB operations on it. Let’s get started.

Python SQLite Create Database

When we talk about databases, we’re looking at a single file which will be stored on the file system and its access is managed by the module itself to prevent corruption when multiple users try to write to it.

Here is a sample program which creates a new database before opening it for operations:

import os
import sqlite3
db_filename="journaldev.db"
db_exists = not os.path.exists(db_filename)
connection = sqlite3.connect(db_filename)
if db_exists:
    print('No schema exists.')
else:
    print('DB exists.')
connection.close()

We will run the program twice to check if it works correctly. Let’s see the output for this program:

As expected, second time we run the program, we see the output as DB exists.

Python SQLite Create Table

To start working with the database, we must define a table schema on which we will write our further queries and perform operations. Here is the schema we will follow:

For the same schema, we will be writing related SQL Query next and these queries will be saved in book_schema.sql:

CREATE TABLE book (
    name        text primary key,
    topic       text,
    published   date
);
CREATE TABLE chapter (
    id           number primary key autoincrement not null,
    name         text,
    day_effort   integer,
    book         text not null references book(name)
);

Now let us use the connect() function to connect to the database and insert some initial data using the executescript() function:

import os
import sqlite3
db_filename="journaldev.db"
schema_filename="book_schema.sql"
db_exists = not os.path.exists(db_filename)
with sqlite3.connect(db_filename) as conn:
    if db_exists:
        print('Creating schema')
        with open(schema_filename, 'rt') as file:
            schema = file.read()
        conn.executescript(schema)
        print('Inserting initial data')
        conn.executescript("""
        insert into book (name, topic, published)
        values ('JournalDev', 'Java', '2011-01-01');
        insert into chapter (name, day_effort, book)
        values ('Java XML', 2,'JournalDev');
        insert into chapter (name, day_effort, book)
        values ('Java Generics', 1, 'JournalDev');
        insert into chapter (name, day_effort, book)
        values ('Java Reflection', 3, 'JournalDev');
        """)
    else:
        print('DB already exists.')

When we execute the program and check what all data is present in chapter table, we will see the following output:

See how I was able to request the db file directory from the command line. We will be querying data from sqlite3 module itself in next section.

Python SQLite Cursor Select

Now, we will retrieve data in our script by using a Cursor to fetch all chapters which fulfil some criteria:

import sqlite3
db_filename="journaldev.db"
with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()
    cursor.execute("""
    select id, name, day_effort, book from chapter
    where book = 'JournalDev'
    """)
    for row in cursor.fetchall():
        id, name, day_effort, book = row
        print('{:2d} ({}) {:2d} ({})'.format(
            id, name, day_effort, book))

Let’s see the output for this program:

This was a simple example of fetching data from a table where one column matches a specific value.

Getting Metadata of Table

In our programs, it is also important to get metadata for a table for documentation purposes and much more:

import sqlite3
db_filename="journaldev.db"
with sqlite3.connect(db_filename) as connection:
    cursor = connection.cursor()
    cursor.execute("""
    select * from chapter where book = 'JournalDev'
    """)
    print('Chapter table has these columns:')
    for column_info in cursor.description:
        print(column_info)

Let’s see the output for this program:

Due to the reason while creating schema, we didn’t provided the column anything apart from their names, most of the values are None.

Using Named Parameters

With named parameters, we can pass arguments to our scripts and hence, the SQL Queries we write in our programs. Using Named Parameters is very easy, let’s take a look at how we can do this:

import sqlite3
import sys
db_filename="journaldev.db"
book_name = sys.argv[1]
with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()
    query = """
    select id, name, day_effort, book from chapter
    where book = :book_name
    """
    cursor.execute(query, {'book_name': book_name})
    for row in cursor.fetchall():
        id, name, day_effort, book = row
        print('{:2d} ({}) {:2d} ({})'.format(
            id, name, day_effort, book))

Let’s see the output for this program:

See how easy it was to pass a named parameter and substitute it in the query right before we execute it.

Python SQLite3 Transaction Management

Well, Transactions are a feature for which relational databases are known for. The sqlite3 module is completely capable of managing the internal state of a transaction, the only thing we need to do is letting it know that a Transaction is going to happen.

Here is a sample program which describes how we write transactions in our program by explicitly calling the commit() function:

import sqlite3
db_filename="journaldev.db"
def show_books(conn):
    cursor = conn.cursor()
    cursor.execute('select name, topic from book')
    for name, topic in cursor.fetchall():
        print('  ', name)
with sqlite3.connect(db_filename) as conn1:
    print('Before changes:')
    show_books(conn1)
    # Insert in one cursor
    cursor1 = conn1.cursor()
    cursor1.execute("""
    insert into book (name, topic, published)
    values ('Welcome Python', 'Python', '2013-01-01')
    """)
    print('nAfter changes in conn1:')
    show_books(conn1)
    # Select from another connection, without committing first
    print('nBefore commit:')
    with sqlite3.connect(db_filename) as conn2:
        show_books(conn2)
    # Commit then select from another connection
    conn1.commit()
    print('nAfter commit:')
    with sqlite3.connect(db_filename) as conn3:
        show_books(conn3)

Let’s see the output for this program:

When the show_books(...) function is called before conn1 has been committed, the result depends on which connection is being used. As the changes were made from the conn1, it sees the made changes but conn2 doesn’t. Once we committed all the changes, all connections were able to see the made changes, including the conn3.

Conclusion

In this lesson, we studied the basics of the sqlite3 module in Python and committed transactions as well. When your program wants to work with some relational data, sqlite3 module provides an easy way to deal with data and obtain results across the life of the program as well.

Download the Source Code