Using Python’s Built‑in sqlite3 Module: Creating, Querying, and Integrating SQLite with Pandas

Import and Use

SQLite is a lightweight, file‑based relational database that comes bundled with Python as the sqlite3 module, so no separate installation is required; simply import it with import sqlite3 as sl.

1. Create a Connection

You can create or open a database file in one line: con = sl.connect('my-test.db') If the file does not exist, SQLite automatically creates an empty database; otherwise it opens the existing file.

2. Create a Table

Define a table using standard SQL syntax:

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

The table supports typical RDBMS features such as data types, NULLability, primary keys, and auto‑increment.

3. Insert Records

Insert multiple rows efficiently with parameter placeholders:

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

Running this code adds the sample rows without errors.

4. Query the Table

Retrieve rows that satisfy a condition:

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

The result is printed directly, demonstrating SQLite’s simplicity.

5. Connect via SQL Client (DBeaver)

After downloading the my-test.db file, you can create a new SQLite connection in DBeaver, browse the file, and run any SQL query just like with a traditional RDBMS.

Seamless Integration with Pandas

SQLite can be used together with Pandas without extra drivers. Create a DataFrame and store it in the database:

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)

You can then join tables and read the result back into a DataFrame:

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)

Finally, write the joined data back to SQLite: df.to_sql('USER_SKILL', con) This round‑trip demonstrates how easily data can move between SQLite and Pandas, enabling powerful analysis workflows.

Conclusion

Python’s built‑in sqlite3 module provides a fully functional relational database that requires no separate server, making it ideal for embedded devices, quick prototyping, testing, and data analysis. Combined with Pandas, it offers a smooth path for data manipulation and exploration.