Python SQLite Tutorial With Examples

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.

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:

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

Create new DB

 

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:

table-schema

 

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:

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

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

DB with initial data

 

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:

Let’s see the output for this program:

Create new DB

 

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:

Let’s see the output for this program:

Metadata of a Table

 

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:

Let’s see the output for this program:

named-parameter

 

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:

Let’s see the output for this program:

table-schema

 

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

By admin

Leave a Reply