How to Open and Convert an SQLite Database to a Pandas DataFrame   

In this article, we’ll explore how to open an SQLite database and convert its tables into Pandas DataFrames with two practical examples.

TL;DR

To open and convert an SQLite database file like data.bg to a Pandas DataFrame we can use:

import sqlite3
import pandas as pd

# Connect to SQLite database (or create if it doesn’t exist)
conn = sqlite3.connect("example.db")

# Create a cursor object
cursor = conn.cursor()

Install pysqlite3 package

You may need to install the pysqlite3 python package by:

pip install pysqlite3

When working with SQLite databases in Python, it’s common to extract data and analyze it using Pandas.

Example 1: Reading an Entire Table into a DataFrame

Step 1: Connecting to the SQLite Database

First, we need to establish a connection using the sqlite3 module in Python.

import sqlite3
import pandas as pd

conn = sqlite3.connect("example.db")

cursor = conn.cursor() # Create a cursor object

where file "example.db" is exported from SQlite database.

Step 2: Creating a Sample Table (Optional)

To create a sample table and insert some records.

cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY,
                    name TEXT,
                    age INTEGER
                 )''')

cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", 
                   [("Alice", 25), ("Bob", 30), ("Charlie", 35)])

conn.commit()

Step 3: Loading the Table into a DataFrame

Now, we can load the entire users table into a Pandas DataFrame using pd.read_sql_query().

# Read entire table into a Pandas DataFrame
df = pd.read_sql_query("SELECT * FROM users", conn)

df

the result will be:

Close the connection

At the end we should close the sqlite3 connection afterwards with:

cnx.commit()
cnx.close()

Example 2: Running Custom Queries and Filtering Data

Instead of loading an entire table, we can use SQL queries to retrieve only specific records.

Step 1: Fetching Filtered Data

Let’s retrieve users who are older than 25 years.

df_filtered = pd.read_sql_query("SELECT * FROM users WHERE age > 25", conn)

df_filtered

result:

Step 2: List all SQlite tables

To list all SQlite tables with Python we can run:

query = """
SELECT name FROM sqlite_schema 
WHERE type IN ('table','view') 
AND name NOT LIKE 'sqlite_%'
ORDER BY 1;
"""

pd.read_sql_query(query, conn)

getting only the table name:

`users`

Alternatively we can get additional table info by:

query = """
SELECT * FROM sqlite_master WHERE type='table'
"""

pd.read_sql_query(query, conn)

Conclusion

Reading and converting an SQLite database to a Pandas DataFrame is a simple process using sqlite3 and pd.read_sql_query(). In this guide, we explored:

Example 1: Loading an entire table into Pandas
Example 2: Running custom queries to fetch filtered data