Skip to Content

Python with Databases

Querying SQLite Databases with Python.

Python’s SQLite API

Python has such an extensive library of modules and methods that allow you to manage data. SQL is one of the most efficient languages when it comes to working with relational databases. What if there were some way to combine the two to increase your functionality with relational databases? The good news is that we can thanks to Python’s Database-API (DB-API). With DB-API 2.0, we can connect Python to RDBMS like PostgreSQL(psycopg2), MySQL(mysqlclient), Oracle(pyodbc), and SQLite. In this article we will explore the sqlite3 module, which allows us to create, read, update, and delete the data in our SQLite relational databases, but within our Python script, how cool is that? Let’s get started by viewing some of the API’s that are available to us within the module.

Connecting to SQLite in Python

With the sqlite3 module already included in The Python Standard Library with any version of Python 2.5 and above, we simply need to import the module like we would with any other in-house module:

# Import the SQLite3 module import sqlite3

Once we have sqlite3 imported, we will need to connect to a database. We can connect to a new or pre-existing database with the sqlite3.connect() API. Remember that an Application Programmable Interface (API) is simply a way that we can communicate between different applications, in this case we want Python and SQLite to communicate with one another. This call will either connect to the database named, or create that database if it does not already exist.

# Create connection to database conn = sqlite3.connect("first.db")

We can imagine our connection object as a cable that connects our python environment to our SQLite database.

This is a gif that shows a two sided arrow pointing between a box labeled “Python” and a box labeled “SQLite.” Above this two sided arrow is a label that says “sqlite3.connect()” symbolizing the idea that our connection object acts as a cable that connects our python environment to our SQLite database.

With that line we have established a connection to the SQLite database first.db. Next we need a way to call SQL statements on the data within the database. A cursor object represents a database cursor, and can be used to call statements to our SQLite database, and return the data in our python environment. We can create a cursor object by using the cursor method of the connection class:

# Create cursor object cursor = conn.cursor()

If we imagine the connection object as a cable that connects Python to SQLite, the cursor would use the cable to move back and forth to send messages and exchange data between the two.

This is a gif that is similar to the previous one above. It shows a box labeled “Python” and a box labeled “SQLite” connected by a yellow rectangle labeled “connection.” A small pink box labeled “cursor” continuously travels between the “Python” box and the “SQLite” box through the “connection” rectangle to simulate the idea the cursor uses the connection object to move back and forth to send messages and exchange data between our Python environment and SQLite environment.

With a cursor instantiated, we have everything we need to make queries to our SQLite database within Python.