Python How to Execute SQL Query with sqlite3 Example

Use Python's sqlite3 module by connecting to a database with sqlite3.connect(), creating a cursor with conn.cursor(), then run your SQL query using cursor.execute(sql_query).

Examples

InputSELECT sqlite_version();

Output[('3.39.2',)]

InputCREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT); INSERT INTO users (name) VALUES ('Alice'), ('Bob'); SELECT * FROM users;

Output[(1, 'Alice'), (2, 'Bob')]

InputSELECT * FROM users WHERE id = 10;

Output[]

How to Think About It

To execute an SQL query in Python, first connect to the database you want to use. Then create a cursor object to send commands. Use the cursor to run your SQL query, and fetch results if needed. Finally, close the connection to save resources.

Algorithm

1

Connect to the database using a connection object.

2

Create a cursor from the connection.

3

Execute the SQL query using the cursor.

4

If the query returns data, fetch the results.

5

Close the cursor and connection.

Code

python

import sqlite3

conn = sqlite3.connect(':memory:')  # Create in-memory database
cursor = conn.cursor()

cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cursor.execute("INSERT INTO users (name) VALUES ('Alice'), ('Bob')")
conn.commit()

cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
print(rows)

cursor.close()
conn.close()

Output

[(1, 'Alice'), (2, 'Bob')]

Dry Run

Let's trace the example where we create a table, insert data, and select all rows.

1

Connect to database

conn is a connection to an in-memory database.

2

Create cursor

cursor is created to execute SQL commands.

3

Create table

Execute SQL: CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)

4

Insert data

Execute SQL: INSERT INTO users (name) VALUES ('Alice'), ('Bob')

5

Commit changes

Save changes to the database.

6

Select data

Execute SQL: SELECT * FROM users

7

Fetch results

rows = [(1, 'Alice'), (2, 'Bob')]

8

Close resources

Close cursor and connection.

Step	SQL Command	Result
3	CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)	Table created
4	INSERT INTO users (name) VALUES ('Alice'), ('Bob')	2 rows inserted
6	SELECT * FROM users	[(1, 'Alice'), (2, 'Bob')]

Why This Works

Step 1: Connect to database

The sqlite3.connect() function opens a connection to the database file or memory.

Step 2: Create cursor

The cursor lets you send SQL commands to the database.

Step 3: Execute and fetch

Use cursor.execute() to run SQL, and cursor.fetchall() to get results.

Alternative Approaches

Using SQLAlchemy ORM

python

from sqlalchemy import create_engine, text
engine = create_engine('sqlite:///:memory:')
with engine.connect() as conn:
    conn.execute(text('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)'))
    conn.execute(text("INSERT INTO users (name) VALUES ('Alice'), ('Bob')"))
    result = conn.execute(text('SELECT * FROM users'))
    print(result.fetchall())

SQLAlchemy provides a higher-level interface and supports many databases but adds complexity.

Using pandas read_sql_query

python

import sqlite3
import pandas as pd
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cursor.execute("INSERT INTO users (name) VALUES ('Alice'), ('Bob')")
conn.commit()
df = pd.read_sql_query('SELECT * FROM users', conn)
print(df)
cursor.close()
conn.close()

Pandas can run SQL and return results as a DataFrame, useful for data analysis.

Complexity: O(n) time, O(n) space

Time Complexity

Executing an SQL query depends on the query and data size; fetching results is O(n) where n is number of rows returned.

Space Complexity

Space depends on the size of fetched data stored in memory; the connection and cursor use minimal fixed space.

Which Approach is Fastest?

Using sqlite3 directly is fastest for simple queries; SQLAlchemy adds overhead but offers flexibility; pandas is slower but great for data analysis.

Approach	Time	Space	Best For
sqlite3 module	O(n)	O(n)	Simple, direct SQL execution
SQLAlchemy ORM	O(n) + overhead	O(n)	Complex apps, multiple DBs
pandas read_sql_query	O(n) + overhead	O(n)	Data analysis with DataFrames