> ## Content Index
> Fetch the complete content index at: https://datascientyst.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How to Open and Convert an SQLite Database to a Pandas DataFrame
- URL: https://datascientyst.com/how-to-open-and-convert-an-sqlite-database-to-a-pandas-dataframe/
- Published: 2025-01-31T22:49:27.000Z
- Updated: 2025-01-31T22:49:27.000Z
- Author: John D K
- Tags: Create

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:

```python
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](https://pypi.org/project/pysqlite3/?ref=datascientyst.com) python package by:

```bash
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.

```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.

```python
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()`.

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

df

```

the result will be:

|   | id | name    | age |
| - | -- | ------- | --- |
| 0 | 1  | Alice   | 25  |
| 1 | 2  | Bob     | 30  |
| 2 | 3  | Charlie | 35  |

### Close the connection

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

```python
cnx.commit()
cnx.close()

```

![](https://datascientyst.com/content/images/2025/01/how-to-open-and-convert-an-sqlite-database-to-a-pandas-dataframe.png)

## 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**.

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

df_filtered

```

result:

|   | id | name    | age |
| - | -- | ------- | --- |
| 0 | 2  | Bob     | 30  |
| 1 | 3  | Charlie | 35  |

### Step 2: List all SQlite tables

To list all SQlite tables with Python we can run:

```python
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:

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

pd.read_sql_query(query, conn)

```

|   | type  | name  | tbl\_name | rootpage | sql                     |
| - | ----- | ----- | --------- | -------- | ----------------------- |
| 0 | table | users | users     | 2        | CREATE TABLE users (    |
|   |       |       |           |          | id INTEGER PRIMARY KEY, |
|   |       |       |           |          | name TEXT,              |
|   |       |       |           |          | age INTEGER             |
|   |       |       |           |          | )                       |

## 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