Master SQLite in Python: From Setup to Pandas Integration

If you are a software developer, you probably know SQLite – a lightweight relational database that stores everything in a single file.

Embedded devices and IoT

Data analysis

Data transfer

File archiving or data container

Internal or temporary databases

Replacing enterprise databases during demos or tests

Education, training and testing

Experimental SQL language extensions

SQLite is also bundled with Python, so no separate installation is required – just import the library and start coding.

Import and Use

import sqlite3 as sl

1. Create a Connection

You can directly create a SQLite database and obtain a connection object: con = sl.connect('my-test.db') If the file does not exist, SQLite creates an empty database automatically.

2. Create a Table

with con:
    con.execute("""
        CREATE TABLE USER (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            age INTEGER
        );
    """)

The USER table now has three columns and supports typical RDBMS features.

3. Insert Records

sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)'
data = [
    (1, 'Alice', 21),
    (2, 'Bob', 22),
    (3, 'Chris', 23)
]

with con:
    con.executemany(sql, data)

The records are inserted without errors.

4. Query the Table

with con:
    data = con.execute("SELECT * FROM USER WHERE age <= 22")
    for row in data:
        print(row)

The query returns the expected rows.

5. Connect via SQL Client (DBeaver)

Download the my-test.db file and open it in DBeaver as an SQLite connection.

Seamless Connection with Pandas

SQLite can be used directly with Pandas data frames.

df_skill = pd.DataFrame({
    'user_id': [1,1,2,2,3,3,3],
    'skill': ['Network Security', 'Algorithm Development', 'Network Security', 'Java', 'Python', 'Data Science', 'Machine Learning']
})

df_skill.to_sql('SKILL', con)

Read a join of USER and SKILL back into a Pandas frame:

df = pd.read_sql('''
    SELECT s.user_id, u.name, u.age, s.skill
    FROM USER u LEFT JOIN SKILL s ON u.id = s.user_id
''', con)

df.to_sql('USER_SKILL', con)

The new table can also be inspected with any SQL client.

Conclusion

Python’s built‑in sqlite3 library lets you create, query, update, and delete tables with minimal setup, and it integrates smoothly with Pandas for powerful data analysis workflows.

Because SQLite is intentionally lightweight, it does not provide authentication, which aligns with its design goals.