Python SQLite
last modified May 9, 2026
This is a Python programming tutorial for the SQLite database. It covers the basics of SQLite programming with the Python language. ZetCode has a complete e-book for Python SQLite: Python SQLite e-book.
Source code for the examples is available at the author's https://github.com/janbodnar/Python-SQLite-Examples repository.
$ python Python 3.14.0 (main, Oct 14 2025, 21:27:55) [Clang 20.1.4 ] on linux Type "help", "copyright", "credits" or "license" for more information. >>> import sqlite3 >>> sqlite3.sqlite_version '3.50.4'
In the shell, we launch the Python interactive interpreter. We can see the
Python version. In our case it is Python 3.14.0. The sqlite3.sqlite_version
gives us the version of the SQLite database library. In our case the version is
3.50.4.
SQLite
SQLite is an embedded relational database engine designed to be small, fast, and extremely reliable. The official documentation describes it as a self-contained, serverless, zero-configuration, transactional SQL database engine — meaning it requires no separate server process, no installation, and no administration. An entire database lives in a single file on disk, yet it still supports most of the SQL features you expect from larger systems. Because of its simplicity and portability, SQLite is used in hundreds of millions of devices and applications, from mobile phones and desktop software to web browsers and IoT systems. Many programming languages, including Python, PHP, Go, and Java, provide built-in or standard-library support for working with SQLite databases.
SQLite Python driver
Python ships with a lightweight but fully functional SQLite driver in its standard library, available as the sqlite3 module. This module provides a DB-API 2.0-compliant interface, giving you familiar concepts such as connections, cursors, parameterized queries, and transactions. Because it is included with every CPython installation, no external dependencies are required — you can start working with databases immediately. The driver exposes most of SQLite's features, including prepared statements, row factories, custom functions, and transaction control, making it suitable for everything from quick scripts to small-scale applications. Its tight integration with SQLite's C library ensures good performance while keeping the API simple and Pythonic.
Creating SQLite database
Now we are going to use the sqlite3 command line tool to create
a new database.
sqlite3 ydb.db SQLite version 3.37.2 2022-01-06 13:25:41 Enter ".help" for usage hints. sqlite>
We provide a parameter to the sqlite3 tool; ydb.db is
a database name. It is a file on our disk. If it is present, it is opened. If
not, it is created.
sqlite> .tables sqlite> .exit $ ls ydb.db
The .tables command gives a list of tables in the ydb.db
database. There are currently no tables. The .exit command
terminates the interactive session of the sqlite3 command line tool.
The ls Unix command shows the contents of the current working
directory. We can see the ydb.db file. All data will be stored
in this single file.
Python SQLite version example
In the first code example, we will get the version of the SQLite database.
#!/usr/bin/env python3
import sqlite3
import sys
con = None
try:
con = sqlite3.connect('ydb.db')
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
data = cur.fetchone()[0]
print(f"SQLite version: {data}")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
finally:
if con:
con.close()
In the above Python script we connect to the previously created
ydb.db database. We execute an SQL statement which returns the
version of the SQLite database.
import sqlite3
We import the sqlite3 module.
con = None
We initialise the con variable to None. In case we
could not create a connection to the database (for example the disk is full), we
would not have a connection variable defined. This would lead to an error in the
finally clause.
con = sqlite3.connect('ydb.db')
We connect to the ydb.db database. The connect
method returns a connection object.
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
From the connection, we get the cursor object. The cursor is used to traverse
the records from the result set. We call the execute method of
the cursor and execute the SQL statement.
data = cur.fetchone()[0]
We fetch the data. Since we retrieve only one record, we call the
fetchone method.
print(f"SQLite version: {data}")
We print the data that we have retrieved to the console.
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
In case of an exception, we print an error message and exit the script with an error code 1.
finally:
if con:
con.close()
In the final step, we release the resources.
In the second example, we again get the version of the SQLite
database. This time we will use the with keyword.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
db_name = "ydb.db"
try:
with closing(sqlite3.connect(db_name)) as con:
cur = con.cursor()
cur.execute("SELECT SQLITE_VERSION()")
data = cur.fetchone()[0]
print(f"SQLite version: {data}")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
The entire operation is wrapped in a try/except block that catches
sqlite3.Error, the base class for all SQLite-related exceptions. This means
any failure — whether a missing file, a corrupt database, or a malformed query —
will be handled gracefully rather than producing a raw Python traceback.
Inside the try block, the with keyword is used as a
context manager. It guarantees that setup and cleanup code runs reliably around
a block of operations — in this case, opening and closing a database connection.
No matter how the with block exits, whether normally or due to an
exception, the cleanup step is always executed. Without it, you would need to
manually call con.close() and carefully handle every possible error
path yourself to ensure the connection is never leaked.
closing(sqlite3.connect(db_name)) does two things at once: it opens
a connection to ydb.db (creating the file if it doesn't exist), and
wraps it in closing() so the connection is automatically and
reliably shut down when the with block exits.
A cursor is then created from the connection. The cursor is the object
responsible for executing SQL statements and retrieving their results.
cur.execute("SELECT SQLITE_VERSION()") sends a query to SQLite
asking for its version string. cur.fetchone() retrieves the first
(and only) row of the result as a tuple, and [0] extracts the first
column value from that tuple — the version string itself.
Finally, the version is printed using an f-string. If anything triggers the
except block, the error message is printed and
sys.exit(1) terminates the process with a non-zero exit code,
signaling failure to the calling shell or process.
Note: SQLite connections behave differently from most Python
objects when used with the with keyword. For the majority of
resources — files, network sockets, and so on — with handles the
full lifetime of the resource, both opening and closing it. You would naturally
expect the same to be true of a database connection.
However, when you use a SQLite connection as a context manager with with
con:, it only manages transactions — committing on success and
rolling back on failure — and does not close the connection when the
block exits. This is a well-known SQLite quirk that often catches developers off
guard, since it breaks the intuitive expectation that with fully
cleans up the resource.
This is precisely why closing() is used explicitly in this script.
It fills the gap that SQLite's context manager leaves open, ensuring the
connection is actually closed when the block exits. The two work at different
levels: with con: manages transaction integrity, while
closing() manages the connection's lifetime. In this script, only
closing() is needed since there are no writes and therefore no
transactions to manage — but understanding the distinction is important whenever
you move on to writing data.
Python SQLite execute
We create a cars table and insert several rows to it.
We use the execute method.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
try:
with closing(sqlite3.connect('ydb.db')) as con:
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS cars;")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)")
cur.execute("INSERT INTO cars VALUES(3,'Skoda',9000)")
cur.execute("INSERT INTO cars VALUES(4,'Volvo',29000)")
cur.execute("INSERT INTO cars VALUES(5,'Bentley',350000)")
cur.execute("INSERT INTO cars VALUES(6,'Citroen',21000)")
cur.execute("INSERT INTO cars VALUES(7,'Hummer',41400)")
cur.execute("INSERT INTO cars VALUES(8,'Volkswagen',21600)")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
The program creates the cars table and inserts eight rows into
the table.
try:
with closing(sqlite3.connect('ydb.db')) as con:
with con:
cur = con.cursor()
The outer with closing(sqlite3.connect('ydb.db')) as con: block is
responsible for the lifetime of the connection. It opens the
connection to the database and guarantees it will be closed when the block
exits, regardless of whether an exception occurs. Without this, the connection
would need to be closed manually with con.close(), and a bug or
exception could easily cause it to be skipped, leaving the connection open.
The inner with con: block is responsible for the
integrity of the transaction. If all the database operations inside
it succeed, it automatically calls con.commit(), permanently saving the
changes. If anything goes wrong and an exception is raised, it automatically calls
con.rollback(), undoing any partial changes and leaving the
database in the state it was in before the block was entered. This ensures that
a failure midway through a series of writes cannot leave the database in a
corrupt or inconsistent state.
The two blocks are necessary because, unlike most Python objects, a SQLite
connection used with with only manages transactions — it does not
close itself. So each block handles a distinct responsibility: the outer block
handles when the connection ends, and the inner block handles
whether the changes are saved or undone. The cursor is then created
from the open connection and is the object through which SQL statements are
executed.
cur.execute("DROP TABLE IF EXISTS cars;")
We drop the table if it already exists.
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
This SQL statement creates a new cars table. The table has
three columns.
cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)")
These two lines send individual INSERT statements to the database,
adding a row for Audi and a row for Mercedes to the cars table.
At this point the changes are pending — they have been staged but not yet
permanently written. It is the enclosing with con: block that
will either commit them to the database if everything succeeds, or roll them
back entirely if an exception is raised before the block exits.
sqlite> .mode column sqlite> .headers on
We verify the written data with the sqlite3 tool. First we modify
the way the data is displayed in the console. We use the column mode and turn on
the headers.
sqlite> select * from cars; id name price ---------- ---------- ---------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
This is the data that we have written to the cars table.
Python SQLite executemany
We are going to create the same table, but this time we use the convenient
executemany method. While execute runs a single SQL
statement with one set of parameters, executemany is designed for
the common case where you want to run the same statement repeatedly
with different values. Instead of calling execute in a
loop — which sends each command to SQLite one by one — you pass a sequence of
parameter tuples to executemany, and SQLite processes them in a
more efficient batch.
This makes the code shorter, easier to read, and noticeably faster when inserting
or updating many rows. It also reduces the chance of mistakes, because the SQL
statement is written only once and the data is supplied separately. In practice,
executemany is the preferred way to populate tables with initial
data or to perform bulk inserts during import tasks.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
cars = (
(1, 'Audi', 52642),
(2, 'Mercedes', 57127),
(3, 'Skoda', 9000),
(4, 'Volvo', 29000),
(5, 'Bentley', 350000),
(6, 'Hummer', 41400),
(7, 'Volkswagen', 21600)
)
try:
with closing(sqlite3.connect('ydb.db')) as con:
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
The program drops the cars table if it exists and recreates it.
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
The first SQL statement drops the cars table if it exists. The second SQL statement creates the cars table.
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
We insert eight rows into the table using the convenience executemany
method. The first parameter of this method is a parameterized SQL statement. The
second parameter is the data, in the form of tuple of tuples.
Python SQLite executescript
We provide another way to create our cars table using the
executescript method. Unlike execute or
executemany, which run a single SQL statement at a time,
executescript can execute an entire block of SQL code in one call.
This makes it ideal for tasks such as creating multiple tables, inserting
initial data, or running schema-setup scripts.
In this example, we manually commit the changes after the script runs and supply our own error handling. This gives us full control over how failures are reported and how the transaction is finalized, which is especially useful when executing several statements at once — a single mistake would otherwise leave the database in an inconsistent state.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
try:
with closing(sqlite3.connect('ydb.db')) as con:
cur = con.cursor()
cur.executescript("""
DROP TABLE IF EXISTS cars;
CREATE TABLE cars(id INT, name TEXT, price INT);
INSERT INTO cars VALUES(1,'Audi',52642);
INSERT INTO cars VALUES(2,'Mercedes',57127);
INSERT INTO cars VALUES(3,'Skoda',9000);
INSERT INTO cars VALUES(4,'Volvo',29000);
INSERT INTO cars VALUES(5,'Bentley',350000);
INSERT INTO cars VALUES(6,'Citroen',21000);
INSERT INTO cars VALUES(7,'Hummer',41400);
INSERT INTO cars VALUES(8,'Volkswagen',21600);
""")
con.commit()
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
In the above script we (re)create the cars table using the
executescript method. The connection is still closed automatically
by closing(), even though we use commit() manually.
cur.executescript("""
DROP TABLE IF EXISTS cars;
CREATE TABLE cars(id INT, name TEXT, price INT);
INSERT INTO cars VALUES(1,'Audi',52642);
INSERT INTO cars VALUES(2,'Mercedes',57127);
...
The executescript method allows us to execute the whole SQL code in
one step.
con.commit()
Without the with con: transaction context, the changes must be
committed manually with con.commit(). If an exception is raised
before that line is reached, no explicit rollback is needed in the
except block — the closing() context manager will
close the connection as it exits, and SQLite automatically rolls back any
uncommitted transaction when a connection is closed.
Python SQLite check database existence
It is not possible to check the existence of the database file using the
connect method. The method simply connects to the database, if the
given file exists. If it does not exist, the database file is created. The
existence of the database file can be checked with the standard
os.path.exists function.
#!/usr/bin/env python3
import os
import sqlite3
import sys
from contextlib import closing
if not os.path.exists('ydb.db'):
try:
with closing(sqlite3.connect('ydb.db')) as con:
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.execute("INSERT INTO cars VALUES(1,'Audi', 52642)")
cur.execute("INSERT INTO cars VALUES(2,'Mercedes', 57127)")
cur.execute("INSERT INTO cars VALUES(3,'Skoda',9000)")
cur.execute("INSERT INTO cars VALUES(4,'Volvo',29000)")
cur.execute("INSERT INTO cars VALUES(5,'Bentley', 350000)")
cur.execute("INSERT INTO cars VALUES(6,'Citroen',21000)")
cur.execute("INSERT INTO cars VALUES(7,'Hummer',41400)")
cur.execute("INSERT INTO cars VALUES(8,'Volkswagen', 21600)")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
else:
try:
with closing(sqlite3.connect('ydb.db')) as con:
cur = con.cursor()
cur.execute("SELECT * FROM cars")
rows = cur.fetchmany(2)
print(rows)
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
In the script we check for the existence of the ydb.db file. If it
does not exist, it is created and a new table is generated. If it already
exists, we retrieve two rows from the table.
if not os.path.exists('ydb.db'):
con = sqlite3.connect('ydb.db')
...
We check for the existence of the ydb.db file using the
os.path.exists method. The database file is created if it does not
exist.
else:
con = sqlite3.connect('ydb.db')
...
If the database file already exists, we connect to it and later fetch some data.
$ ls db_exists.py $ ./db_exists.py $ ./db_exists.py [(1, 'Audi', 52642), (2, 'Mercedes', 57127)]
In a directory that does not contain a ydb.db file we launch the
script twice. On the first execution the database is created and a
cars table is generated. On the second execution we
fetch and print two rows from the cars table.
Python SQLite lastrowid
Sometimes, we need to determine the id of the last inserted
row. In Python SQLite, we use the lastrowid attribute
of the cursor object.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
try:
with closing(sqlite3.connect(':memory:')) as con:
with con:
cur = con.cursor()
cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT);")
cur.execute("INSERT INTO friends(name) VALUES ('Tom');")
cur.execute("INSERT INTO friends(name) VALUES ('Rebecca');")
cur.execute("INSERT INTO friends(name) VALUES ('Jim');")
cur.execute("INSERT INTO friends(name) VALUES ('Robert');")
last_row_id = cur.lastrowid
print(f"The last Id of the inserted row is {last_row_id}")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
We create a friends table in memory. The Id is automatically
incremented.
cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT);")
In SQLite, an INTEGER PRIMARY KEY column is an alias for SQLite's
internal rowid, a 64-bit signed integer that SQLite assigns
automatically to every row. When a new row is inserted without specifying a
value for this column, SQLite picks an integer one greater than the largest
existing rowid in the table. If the table is empty, it starts at
1.
SQLite also provides an AUTOINCREMENT keyword, used as
INTEGER PRIMARY KEY AUTOINCREMENT. The difference is subtle but
important: without AUTOINCREMENT, SQLite may reuse an id that
belonged to a previously deleted row if it happens to be the largest available
value. With AUTOINCREMENT, SQLite guarantees that ids are always
strictly increasing and a previously used id is never reassigned, even after
deletion. For most use cases INTEGER PRIMARY KEY without
AUTOINCREMENT is sufficient — AUTOINCREMENT adds a
small overhead and is only necessary when id uniqueness across the lifetime of
the table, including deleted rows, must be guaranteed.
cur.execute("INSERT INTO friends(name) VALUES ('Tom');")
cur.execute("INSERT INTO friends(name) VALUES ('Rebecca');")
cur.execute("INSERT INTO friends(name) VALUES ('Jim');")
cur.execute("INSERT INTO friends(name) VALUES ('Robert');")
When using auto-increment, we have to explicitly state the column names,
omitting the one that is auto-incremented. The four statements insert four
rows into the friends table.
last_row_id = cur.lastrowid
Using the lastrowid we get the last inserted row id.
$ ./lastrowid.py The last Id of the inserted row is 4
Python SQLite retrieve data with fetchall
The fetchall method fetches all (or all remaining) rows of a query
result set and returns a list of tuples.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
try:
with closing(sqlite3.connect('ydb.db')) as con:
cur = con.cursor()
cur.execute("SELECT * FROM cars")
rows = cur.fetchall()
for row in rows:
print(f"{row[0]} {row[1]} {row[2]}")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
In this example, we retrieve all data from the cars table.
cur.execute("SELECT * FROM cars")
This SQL statement selects all data from the cars table.
rows = cur.fetchall()
The fetchall method retrieves all remaining rows from the result
set and returns them as a list of tuples. Each inner tuple represents a single
row in the table, with its values ordered according to the columns selected by
the query.
for row in rows:
print(f"{row[0]} {row[1]} {row[2]}")
We print the data to the console, row by row.
$ ./fetch_all.py 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
Python SQLite fetchone
The fetchone returns the next row of a query result set, returning
a single tuple, or None when no more data is available.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
try:
with closing(sqlite3.connect('ydb.db')) as con:
cur = con.cursor()
cur.execute("SELECT * FROM cars")
while True:
row = cur.fetchone()
if row is None:
break
print(f"{row[0]} {row[1]} {row[2]}")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
In this script we connect to the database and fetch the rows of the
cars table one by one.
while True:
We access the data from the while loop. When we read the last row, the loop is terminated.
row = cur.fetchone()
if row is None:
break
The fetchone method returns the next row from the table. If there
is no more data left, it returns None. In this case we break the
loop.
print(f"{row[0]} {row[1]} {row[2]}")
The row is returned as a tuple, with each element corresponding to a column
in the table. row[0] is the id, row[1] is the car
name, and row[2] is the price.
$ ./fetch_one.py 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
Python SQLite dictionary cursor
By default, the cursor returns rows as tuples, where values can only be accessed
by their positional index. By setting con.row_factory = sqlite3.Row,
each row is instead returned as a sqlite3.Row object, which supports
both index-based access like a tuple and name-based access like a dictionary.
This makes the code more readable, since columns can be referred to by their
names rather than their positions.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
try:
with closing(sqlite3.connect('ydb.db')) as con:
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("SELECT * FROM cars")
rows = cur.fetchall()
for row in rows:
print(f"{row['id']} {row['name']} {row['price']}")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
In this example, we print the contents of the cars table using the
dictionary cursor.
con.row_factory = sqlite3.Row
Setting row_factory to sqlite3.Row changes how each
returned row is represented. The resulting sqlite3.Row objects
behave like tuples but also allow columns to be accessed by name, as shown in
the loop below.
for row in rows:
print(f"{row['id']} {row['name']} {row['price']}")
The data is accessed by the column names.
Parameterized statements
Parameterized queries use placeholders instead of embedding values directly
into SQL strings. This is important for two reasons. First, it prevents SQL
injection attacks — a technique where malicious input is crafted to break out
of the intended query structure and execute arbitrary SQL. When placeholders
are used, sqlite3 handles the substitution internally and ensures
values are always treated as data, never as part of the SQL syntax. Second,
parameterized queries can improve performance when the same statement is
executed repeatedly, since the database can parse and cache the query template
once and reuse it with different values.
The Python sqlite3 module supports two types of placeholders:
question marks (?) for positional parameters, and named
placeholders (:name) for clarity when a query has many parameters
or the same value is used more than once.
Parameterized statements with question marks
In the first example we use the syntax of question marks.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
uId = 1
uPrice = 62300
try:
with closing(sqlite3.connect('ydb.db')) as con:
with con:
cur = con.cursor()
cur.execute("UPDATE cars SET price=? WHERE id=?", (uPrice, uId))
print(f"Number of rows updated: {cur.rowcount}")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
We update a price of one car. In this code example, we use the question mark placeholders.
cur.execute("UPDATE cars SET price=? WHERE id=?", (uPrice, uId))
The question marks serve as placeholders for the values to be substituted into
the query. The actual values are passed as a tuple in the second argument to
execute(). The sqlite3 module handles the substitution
safely, ensuring values are properly escaped and cannot alter the structure of
the SQL statement — this is what protects against SQL injection attacks.
print(f"Number of rows updated: {cur.rowcount}")
The rowcount attribute returns the number of rows affected by the
last execute() call. Here it reflects how many rows matched the
WHERE id=? condition and were updated. If no row with the given id
existed, it would return 0.
Parameterized statements with named placeholders
Named placeholders start with a colon, such as :Id. Instead of a
tuple, the values are passed as a dictionary in the second argument to
execute(), where each key corresponds to a placeholder name in the
query. This makes the statement easier to read than question mark placeholders
when there are several parameters, since the name of each value is explicit at
the point of substitution rather than relying on positional order.
with con: transaction block. Since the query
is a read-only SELECT statement, there is nothing to commit or roll
back, so transaction control is not needed. The outer closing()
block is still present to ensure the connection is properly closed when the
query is done.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
uId = 4
try:
with closing(sqlite3.connect('ydb.db')) as con:
cur = con.cursor()
cur.execute("SELECT name, price FROM cars WHERE Id=:Id", {"Id": uId})
row = cur.fetchone()
print(f"{row[0]}, {row[1]}")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
We select a name and a price of a car using named placeholders.
cur.execute("SELECT name, price FROM cars WHERE Id=:Id", {"Id": uId})
The named placeholders start with a colon character.
$ ./named_placeholders.py Volvo, 29000
Inserting image
In this section, we are going to insert an image into the SQLite database. Note that some people argue against putting images into databases. Here we only show how to do it. We do not dwell into technical issues of whether to save images in databases or not.
sqlite> CREATE TABLE images(id INTEGER PRIMARY KEY, data BLOB);
For this example, we create a new table called images. For the images, we use
the BLOB data type, which stands for Binary Large Objects.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
def readImage():
try:
with open("sid.jpg", "rb") as fin:
return fin.read()
except IOError as e:
print(e)
sys.exit(1)
try:
with closing(sqlite3.connect('ydb.db')) as con:
cur = con.cursor()
data = readImage()
binary = sqlite3.Binary(data)
cur.execute("INSERT INTO images(data) VALUES (?)", (binary,))
con.commit()
except sqlite3.Error as e:
print(e)
sys.exit(1)
In this script, we read an image from the current working directory and write it
into the images table of the SQLite ydb.db database.
def readImage():
try:
with open("sid.jpg", "rb") as fin:
return fin.read()
except IOError as e:
print(e)
sys.exit(1)
The readImage function opens the file sid.jpg in
binary read mode ("rb") and returns its contents as a
bytes object. The with block ensures the file is
closed automatically after reading, even if an error occurs. An
IOError is caught separately from the database errors below, since
it originates from the filesystem rather than SQLite.
binary = sqlite3.Binary(data)
sqlite3.Binary wraps the raw bytes to signal to SQLite that the
data should be stored as a BLOB — raw binary — rather than being
interpreted as text.
cur.execute("INSERT INTO images(data) VALUES (?)", (binary,))
con.commit()
The image data is inserted using a parameterized statement. The id
column is omitted because it is defined as INTEGER PRIMARY KEY and
is assigned automatically. The binary data is passed as a single-element tuple,
which is the standard way to supply parameters to execute(). Note
that with con: is not used here — instead, con.commit()
is called manually. If an exception is raised before this line is reached,
closing() will shut down the connection, and SQLite will
automatically roll back the uncommitted insert.
Reading image
In this section, we are going to perform the reverse operation: we read an image from the database table.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
def writeImage(data):
try:
with open('sid2.jpg', 'wb') as fout:
fout.write(data)
except IOError as e:
print(e)
sys.exit(1)
try:
with closing(sqlite3.connect('ydb.db')) as con:
cur = con.cursor()
cur.execute("SELECT data FROM images LIMIT 1")
data = cur.fetchone()[0]
writeImage(data)
except sqlite3.Error as e:
print(e)
sys.exit(1)
We read image data from the images table and write it
to another file, which we call sid2.jpg.
def writeImage(data):
try:
with open('sid2.jpg', 'wb') as fout:
fout.write(data)
except IOError as e:
print(e)
sys.exit(1)
The writeImage function opens a new file called
sid2.jpg in binary write mode ("wb") and writes the
raw bytes retrieved from the database into it. The with block
ensures the file is closed automatically after writing. As in the insert
example, IOError is caught separately since it originates from the
filesystem rather than SQLite.
cur.execute("SELECT data FROM images LIMIT 1")
data = cur.fetchone()[0]
The query retrieves the data column from the first row of the
images table. LIMIT 1 ensures only one row is
fetched at the database level. fetchone() returns that row as a
tuple, and [0] extracts the binary image data from it, which is
then passed to writeImage.
Python SQLite metadata
Metadata is information about the data in the database rather than the data itself. In SQLite, metadata includes information about tables and columns — their names, data types, and constraints — as well as runtime information such as the number of rows affected by a statement or the number of rows and columns returned in a result set.
SQLite exposes metadata through several mechanisms. The PRAGMA
command is a SQLite-specific statement that can query or modify database
settings and schema information. Cursor objects also expose metadata attributes
such as description and rowcount. Finally, the
internal sqlite_master table can be queried directly to inspect
the schema of the entire database.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
try:
with closing(sqlite3.connect('ydb.db')) as con:
cur = con.cursor()
cur.execute('PRAGMA table_info(cars)')
data = cur.fetchall()
for d in data:
print(f"{d[0]} {d[1]} {d[2]}")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
In this example, we use the PRAGMA table_info command to retrieve
column metadata for the cars table.
cur.execute('PRAGMA table_info(cars)')
PRAGMA table_info(tableName) returns one row for each column in
the specified table. Each row contains the column's order number, name, data
type, whether it allows NULL values, its default value, and
whether it is part of the primary key.
for d in data:
print(f"{d[0]} {d[1]} {d[2]}")
We print the first three fields from each row: the column's order number, its
name, and its data type. The output confirms the three columns defined when the
cars table was created.
$ ./column_names.py 0 id INT 1 name TEXT 2 price INT
In the following example we print all rows from the cars table
with their column names, aligned into columns for readability.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
try:
with closing(sqlite3.connect('ydb.db')) as con:
cur = con.cursor()
cur.execute('SELECT * FROM cars')
col_names = [cn[0] for cn in cur.description]
rows = cur.fetchall()
print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}")
for row in rows:
print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}")
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
We print the contents of the cars table to the console, including
the column names as a header row. The data values are aligned with their
respective column headings using f-string format specifiers.
col_names = [cn[0] for cn in cur.description]
After a query has been executed, the cursor's description attribute
holds a sequence of 7-item tuples, one for each column in the result set. The
first element of each tuple (cn[0]) is the column name. The list
comprehension extracts just the names into a plain list, giving us
['id', 'name', 'price'].
print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}")
The column names are printed as a header row. The format specifiers
:3, :10, and :7 set the minimum field
widths for each column, ensuring the header aligns with the data rows below it.
for row in rows:
print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}")
Each data row is printed with the same field widths as the header. The
< flag left-aligns the id and name values within their fields,
which looks natural for a numeric id and a text name. The price column uses the
default right-alignment, which is conventional for numbers.
$ ./column_names2.py id name price 1 Audi 62300 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Hummer 41400 7 Volkswagen 21600
In our last example related to metadata, we list all tables in the
ydb.db database by querying SQLite's internal schema table.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
try:
with closing(sqlite3.connect('ydb.db')) as con:
cur = con.cursor()
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
rows = cur.fetchall()
for row in rows:
print(row[0])
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
The code queries SQLite's internal schema table to retrieve the names of all user-created tables in the database and prints them to the terminal.
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
SQLite maintains an internal table called sqlite_master that stores
the schema of the entire database. Each row in this table represents a database
object — a table, index, view, or trigger. By filtering on
type='table' and selecting the name column, we
retrieve the names of all user-created tables. System tables used internally by
SQLite are not included in these results.
$ ./list_tables.py cars images
The output confirms that ydb.db contains two tables —
cars and images — which were created in the earlier
examples in this tutorial.
Python SQLite data export
We can dump data in an SQL format to create a simple backup of our database tables.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
cars = (
(1, 'Audi', 52643),
(2, 'Mercedes', 57642),
(3, 'Skoda', 9000),
(4, 'Volvo', 29000),
(5, 'Bentley', 350000),
(6, 'Hummer', 41400),
(7, 'Volkswagen', 21600)
)
def writeData(data):
with open('cars.sql', 'w') as f:
f.write(data)
try:
with closing(sqlite3.connect(':memory:')) as con:
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
cur.execute("DELETE FROM cars WHERE price < 30000")
data = '\n'.join(con.iterdump())
writeData(data)
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
In the above example, we recreate the cars table in the memory. We
delete some rows from the table and dump the current state of the table into a
cars.sql file. This file can serve as a current backup of the
table.
def writeData(data):
f = open('cars.sql', 'w')
with f:
f.write(data)
The data from the table is being written to the file.
con = sqlite3.connect(':memory:')
We create a temporary table in the memory.
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
cur.execute("DELETE FROM cars WHERE price < 30000")
These lines create a cars table, insert values and delete rows,
where the price is less than 30000 units.
data = '\n'.join(con.iterdump())
The con.iterdump returns an iterator to dump the database in an
SQL text format. The built-in join function takes the iterator and
joins all the strings in the iterator separated by a new line. This data is
written to the cars.sql file in the
writeData function.
$ cat cars.sql BEGIN TRANSACTION; CREATE TABLE cars(id INT, name TEXT, price INT); INSERT INTO "cars" VALUES(1,'Audi',52643); INSERT INTO "cars" VALUES(2,'Mercedes',57642); INSERT INTO "cars" VALUES(5,'Bentley',350000); INSERT INTO "cars" VALUES(6,'Hummer',41400); COMMIT;
The contents of the dumped in-memory cars table.
Python SQLite import data
Now we are going to perform a reverse operation. We will import the dumped table back into memory.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
def readData():
with open('cars.sql', 'r') as f:
return f.read()
try:
with closing(sqlite3.connect(':memory:')) as con:
cur = con.cursor()
sql = readData()
cur.executescript(sql)
cur.execute("SELECT * FROM cars")
rows = cur.fetchall()
for row in rows:
print(row)
except sqlite3.Error as e:
print(f"Error {e}")
sys.exit(1)
In this script, we read the contents of the cars.sql file
and execute it. This will recreate the dumped table.
def readData():
f = open('cars.sql', 'r')
with f:
data = f.read()
return data
Inside the readData method we read the contents of
the cars.sql table.
cur.executescript(sql)
We call the executescript method to launch the SQL script.
cur.execute("SELECT * FROM cars")
rows = cur.fetchall()
for row in rows:
print(row)
We verify the data.
$ ./import_table.py (1, 'Audi', 52643) (2, 'Mercedes', 57642) (5, 'Bentley', 350000) (6, 'Hummer', 41400)
The output shows that we have successfully recreated the saved cars table.
Python SQLite transactions
A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.
Python sqlite3 module by default issues a BEGIN statement
implicitly before a Data Modification Language (DML) statement
(i.e. INSERT/UPDATE/DELETE/REPLACE).
The sqlite3 used to implicitly commit an open transaction before
DDL statements. This is no longer the case.
Manual transactions are started with the BEGIN TRANSACTION statement
and finished with the COMMIT or ROLLBACK statements.
SQLite supports three non-standard transaction levels: DEFERRED,
IMMEDIATE and EXCLUSIVE. Python SQLite module
also supports an autocommit mode, where all changes to the tables are immediately
effective.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
try:
with closing(sqlite3.connect('ydb.db')) as con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS friends")
cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)")
cur.execute("INSERT INTO friends(name) VALUES ('Tom')")
cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')")
cur.execute("INSERT INTO friends(name) VALUES ('Jim')")
cur.execute("INSERT INTO friends(name) VALUES ('Robert')")
# con.commit()
except sqlite3.Error as e:
print(e)
sys.exit(1)
We create a friends table and try to fill it with data. However, as
we will see, the data is not committed.
#con.commit()
The commit method is commented. If we uncomment the line, the data
will be written to the table.
sqlite> .tables cars friends images sqlite> SELECT COUNT(id) FROM friends; COUNT(id) ---------- 0 sqlite>
The table is created but the data is not written to the table.
Python SQLite autocommit
In the autocommit mode, an SQL statement is executed immediately.
#!/usr/bin/env python3
import sqlite3
import sys
from contextlib import closing
try:
with closing(sqlite3.connect('ydb.db', isolation_level=None)) as con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS friends")
cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)")
cur.execute("INSERT INTO friends(name) VALUES ('Tom')")
cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')")
cur.execute("INSERT INTO friends(name) VALUES ('Jim')")
cur.execute("INSERT INTO friends(name) VALUES ('Robert')")
except sqlite3.Error as e:
print(e)
sys.exit(1)
In this example, we connect to the database in the autocommit mode.
con = sqlite3.connect('ydb.db', isolation_level = None)
We have an autocommit mode, when we set the isolation_level to
None.
$ ./autocommit.py sqlite> SELECT * FROM friends; id name ---------- ---------- 1 Tom 2 Rebecca 3 Jim 4 Robert
The data was successfully committed to the friends table.
Source
Python sqlite3 — DB-API 2.0 interface for SQLite
This was Python SQLite tutorial.
Author
List all Python tutorials.