Python SQLite Tutorial

If you’re looking for something with which you can use complete DB operations into your application without having to install any database server program such as MySQL, PostgreSQL, or Oracle, python sqlite3 module is for you.

Table of Contents

  • 2 Using sqlite3 module
    • 2.1 Python SQLite Create Database
    • 2.2 Python SQLite Create Table
    • 2.3 Python SQLite Cursor Select
    • 2.4 Getting Metadata of Table
    • 2.5 Using Named Parameters
    • 2.6 Python SQLite3 Transaction Management
  • 4 Download the Source Code
  • 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:

    Python SQLite Tutorial
    Python SQLite Tutorial

    Create new DB

    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:

    Python SQLite Tutorial
    Python SQLite Tutorial

    Python SQLite Table Schema

    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 ('<a href="https://www.journaldev.com/1663/java-generics-example-method-class-interface">Java Generics</a>', 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:

    Python SQLite Tutorial
    Python SQLite Tutorial

    DB with initial data

    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:

    Python SQLite Tutorial
    Python SQLite Tutorial

    Fetch data from DB

    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:

    Python SQLite Tutorial
    Python SQLite Tutorial

    Metadata of a Table

    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:

    Python SQLite Tutorial
    Python SQLite Tutorial

    Passing named parameter

    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:

    Python SQLite Tutorial
    Python SQLite Tutorial

    Running Transactions

    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