Python sqlite3: From execute() to fetchall()

Python makes working with SQLite straightforward through the built-in sqlite3 module. SQLite does not require a separate database server, and an entire database can be stored in a single file, which makes it especially convenient for learning, small applications, testing, and local data storage.

In this article, we will focus on the Python-side workflow of working with SQLite: creating a connection, creating and using a cursor, executing SQL statements with execute(), executemany(), and executescript(), and retrieving query results with fetchone(), fetchmany(), and fetchall.

An important point is that Python’s sqlite3 module is compatible with the Python Database API Specification v2.0,(DB-API 2.0, defined in PEP 249).Many concepts you learn here — such as connections an cursors and their methods — are part of a common database programming interface used by many Python database drivers.

To work with the database, we use connection object, but not directly. We need to create something called cursor object from the database. It is a kind of handler, just like we create a file handler in Python, and pass commands to handler, not to file directly. You can say, there are multiple layers: database file, connection, cursor.

Cursor is the agent we are communicating with- giving it commands and receiving results from operation. Here, we will create a table, with id, name and age columns, a typical SQL query, with additional options to make id column primary key and to autoincrement it, if omitted. We use execute() method of cursor object to execute query, either directly or from a string variable. At the end, we need to use the same object’s commit() method, which acts as an Apply button — nothing is really executed until we commit it.

import sqlite3
conn = sqlite3.connect("users.db")
cur = conn.cursor()
cur.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER NOT NULL
)
""")
conn.commit()

Let us add some data to the table, so we can work with it:

query = "INSERT INTO users values  (1, 'Alice', 25)"
cur.execute(query)

execute() has one caveat: it can execute only a single SQL query. So what do we do if we need to execute multiple INSERT statements? Do we need to call execute() every single time? Fortunately, we have method called executescript(). This method accepts a string that may contain multiple SQL statements, separated by a semicolon.

query = '''INSERT INTO users values  (2, 'Tanya', 30);
           INSERT INTO users values  (3, 'Peter', 33);'''
cur.executescript(query)
There is also a method named executemany(). You may think that it means “execute many lines,” but that is not the case. The name is slightly misleading. What it actually does is execute the same query multiple times, using different values

There is also a method named executemany(). You may think that it means “execute many lines,” but that is not the case. The name is slightly misleading. What it actually does is execute the same query multiple times, using different values taken from a list or another sequence.

query = "INSERT INTO users VALUES (?, ?, ?)"
users = [
    (4, "Sara", 28),
    (5, "Mike", 35)
]

cur.executemany(query, users)

Do you see the pattern? It is as if we started a for loop and, in each iteration, passed a tuple from the users list.

At this point, if you run the program many times, you might have existing database, and inserting data with the same id will yield an error. Clearing the database is as simple as deleting a users.db file from the file manager in PyCharm or even programmatically with the os module. You might want to delete the file after running program so you don’t try inserting duplicate data.

import os
os.remove("users.db")

These are different ways we can execute SQL commands. But when we read data, with SELECT, how do we obtain results?

The most common SQL SELECT command is as follows, returning all data from the table.

cur.execute("SELECT * FROM users")

Don’t forget to put conn.commit() after inserts, and not cur.commit(). Interestingly, cur contains results as well, and we don’t need commit() after SELECT.

Get Jasenko Krejić’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

We can get all rows of select with fetchall() method:

results = cur.fetchall()

rows is a regular list, not some exotic iterable as you might have expected. We can iterate over it to see its values.

for result in results:
    print(result)
(1, 'Alice', 25)
(2, 'Tanya', 30)
(3, 'Peter', 33)
(4, 'Sara', 28)
(5, 'Mike', 35)

Cursor is similar to iterator, because it knows where its position is. Since we fetched every row, calling fetchall() again will return an empty list.

results = cur.fetchall()
print(results)
[]

To fetch the rows again into cursor, execute the SELECT again:

cur.execute("SELECT * FROM users")

Before we move one, let me show you a hidden ability of cursor-you don’t even need to use fetchall() method, cur itself can be iterated over, row by row. Change the previous line:

crows = cur.execute("SELECT * FROM users")

Now, you can use for loop on this list directly:

for row in crows:
    print(row)
(1, 'Alice', 25)
(2, 'Tanya', 30)
…

So why use fetchall(), when we can directly iterate over rows? I will reveal it after we learn yet another way to read results.

There is another method, called fetchone(), which will do exactly what it says: fetch one row from the result.

execute("SELECT * FROM users")
row = cur.fetchone()

row now contains, you guessed it, the first database row:

print(row)
(1, 'Alice', 25)

If we run it again, it will fetch the second row, and so on. When it reaches the end, it returns None.

If you think these are all methods for retrieving rows from the result list, you are wrong. There is another one, called fetchmany(). It returns a specified number of rows, starting from the cursor current position. And in our case, cursor position is at the second row(still not returned yet).

rows = cur.fetchmany(2)
for row in rows:
    print(row) 
(2, 'Tanya', 30)
(3, 'Peter', 33)

If we don’t specify the number it will default to a variable cur.arraysize, which is usually 1, and can be changed.

And at the end, let me tell you why use fetchall(). At this point, we have cursor at position 4. If we execute a fetchall(), it will return all remaining rows, from the current cursor position to the end.

rows = cur.fetchall()
for row in rows:
    print(row)
(4, 'Sara', 28)
(5, 'Mike', 35)

After you finish, be kind enough to close the connection:

conn.close()

To conclude: SQLite’s execute(), executemany(), and executescript() methods give you flexible ways to run SQL statements, while fetchone(), fetchmany(), and fetchall() let you retrieve a single row, a batch of rows, or the entire remaining result set. Together, these methods provide the core workflow for executing queries and reading results through SQLite’s cursor interface, which largely follows Python’s DB-API 2.0 specification.

If you found this article helpful, consider supporting my work with a small donation on Ko-fi:
https://ko-fi.com/jasenko/donate

If you are learning Python, and want to follow the PCEP Python exam syllabus, check my newest Udemy Course, it is a video tutorial packed with hands-on example, quizzes, coding exercises and two full mock exams:

https://www.udemy.com/course/pass-python-pcep-30-0x-video-lessons-and-mock-tests-2025/

If you want to learn more about AWS Lightsail service, check my course on Udemy on AWS Lightsail.

https://www.udemy.com/course/aws-lighsail-a-different-perspective

Also, if you want to prepare for the basic AWS Certified Cloud Practitioner exam, check another one of my courses, also on Udemy:

https://www.udemy.com/course/aws-certified-cloud-practitioner-crash-course