Figure out how to use SQLite with Python

“SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language…. the [Python] sqlite module was written by Gerhard Häring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires the third-party SQLite library.”¹

Create a new database and open a connection

The call to sqlite3.connect("<database_name.db>") creates the database and a connection to it in the current working directory:

In [1]: import sqlite3

In [2]: con = sqlite3.connect("hello_sqlite.db")

The database file now exists:

projects/python/hello_sqlite via  v3.11.13 (.venv) took 2s
❯ ls
Permissions Size User    Date Modified Name
.rw-r--r--     0 scossar  8 Jan 23:33   hello_sqlite.db
.rw-r--r--    15 scossar  8 Jan 23:32   main.py

The Connection object

con is a Connection object:

In [3]: type(con)
Out[3]: sqlite3.Connection

“Each open SQLite database is represented by a Connection object…. Their main purpose is creating Cursor objects and Transactional control.”²

The Cursor object

In order to execute SQL statements and fetch results from SQL queries, you need to use a database cursor.

Create the Cursor:

In [4]: cur = con.cursor()

In [5]: type(cur)
Out[5]: sqlite3.Cursor

A Cursor instance has the following attributes and methods:

• execute(sql, parameters=()): execute a single SQL statement, optionally binding Python values using placeholders. (Note that the / third “argument” that’s shown in the documentation is a special syntax marker that indicates that everything before the / needs to be passed as positional-only parameters. I don’t think there are any additional named arguments that can be passed to the method.)
  • sql: a single SQL statement
  • parameters: (dict | sequence), values to bind to placeholders in the sql.
• fetchone(): (if row_factory is None (see docs)) return the next row query result set as a tuple. I think the reference to “next row” in the docs is the key to what I’m trying to understand. The cursor maintains an internal pointer that tracks the position in the query results. If a query is executed that returns multiple rows, the cursor doesn’t immediately give all the results. It keeps track of it’s position in the result set. fetchone() retrieves the next row, relative to that position. (Then increments the cursor? I need to add some data to the table to find out.)
• fetchmany(size=cursor.arraysize): return the next set of rows of a query result as a list. If a size parameter isn’t supplied, cursor.arraysize determines the number of rows. (cursor.arraysize seems to be 1.)
• fetchall(): fetch all available rows (from the cursor’s current position). The results are list.
• See the docs for more methods

Create a database table

In [6]: cur.execute("CREATE TABLE noun(word, category)")
Out[6]: <sqlite3.Cursor at 0x7f8df1eb74c0>

Verify that the new table has been created

In [7]: res = cur.execute("SELECT name FROM sqlite_master")

In [8]: type(res)
Out[8]: sqlite3.Cursor

sqlite_master is a table that’s built-in the SQLite. Call res.fetchone(). It should now contain an entry for the noun table:

In [12]: res.fetchone()
Out[12]: ('noun',)  # note that a tuple is returned

What is the sqlite_master table?

The sqlite_master table is the Schema Table.

“Every SQLite database contains a single “schema table” that stores the schema for that database. The schema for a database is a description of all the other tables, indexes, triggers, and views that are contained within he database.”³

The schema table looks like this:

CREATE TABLE sqlite_schema(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

The schema table can always be referenced using the name “sqlite_schema” but for historic compatibility can also be referenced with “sqlite_master”.

Insert data with an SQL literal

Add three rows of data as SQL literals:

In [27]: cur.execute("""
    ...: INSERT INTO noun VALUES
    ...: ("dog", "animal"),
    ...: ("cat", "animal"),
    ...: ("chair", "furniture")
    ...: """)

Commit the transaction

The INSERT statement implicitly opens a transaction. The transaction needs to be committed before changes are saved in the database (see Transaction control (Python docs)).

Call commit() on the Connection object (con) to commit the transaction:

In [28]: con.commit()

Select data from the table

In [29]: res = cur.execute("SELECT word FROM noun")

In [30]: res.fetchall()
Out[30]: [('dog',), ('cat',), ('chair',)]

Check to see where the cursor is at after calling fetchall()

This should return None:

In [31]: res.fetchone()

Insert data with placeholders

NOTE: always use placeholders. See: How to use placeholders to bind values in SQL queries (Python docs).

In [33]: data = [
    ...: ("moose", "animal"),
    ...: ("cow", "animal"),
    ...: ("tree", "plant")
    ...: ]

In [35]: cur.executemany("INSERT INTO noun VALUES(?, ?)", data)
Out[35]: <sqlite3.Cursor at 0x7f8df1eb7040>

In [36]: con.commit()

Select the data iteratively

In [37]: for row in cur.execute("SELECT word, category FROM noun"):
    ...:     print(row)
    ...:
('dog', 'animal')
('cat', 'animal')
('chair', 'furniture')
('moose', 'animal')
('cow', 'animal')
('tree', 'plant')

Close the database connection

Verify that the database has been written to disk by calling the Connection object’s close() method, opening a new connection, creating a new Cursor object, and querying the database:

In [38]: con.close()

In [39]: new_con = sqlite3.connect("hello_sqlite.db")

In [40]: new_cur = new_con.cursor()

In [41]: res = new_cur.execute("SELECT word, category FROM noun")

In [42]: word, category = res.fetchone()

In [43]: print(word, category)
dog animal

In [44]: word, category = res.fetchone()

In [45]: print(word, category)
cat animal

In [46]: new_con.close()

References

SQLite. https://sqlite.org/.

SQLite. “SQL As Understood By SQLite.” (Last updated: April 4, 2024.) https://sqlite.org/lang.html.

Python 3.14.2 documentation. “sqlite3 — DB-API 2.0 interface for SQLite databases.” (Last updated: January 8, 2026.) https://docs.python.org/3/library/sqlite3.html.