How to convert SQLite database to pandas dataframe in python

In Python, Pandas library is the most popular library for data processing and data representation. Most of the datasets are available in CSV form but there are exceptions, where the dataset is given in the SQLite file.

Be ready for this situation, where you want to convert SQLite database into a pandas dataframe. This is a quick python tutorial that will guide you to converting SQLite database into pandas dataframe.

There are 3 different ways to convert SQLite database into pandas dataframe in python

Method No 1: Convert Sqlite Database into pandas Dataframe in Python

To convert an SQLite database into a pandas dataframe, you have to use the read_sqli_table() function in python. Another way to read a SQLite database is to use the read_sql_query() function.

These two functions are available in the pandas library in python. Check out the example to see how to convert a SQLite database into dataframe in python.

Python code to convert SQLite database to Pandas Dataframe

import sqlite3
import pandas as pd
# Create your connection.
cnx = sqlite3.connect('sqlite.db')
df = pd.read_sql_query("SELECT * FROM Student", cnx)
print(df)
cnx.commit()
cnx.close()

Output of the code

          Name    Course   Age
0  AlixaProDev        CS  19.0
1  Alixawebdev       BBa  21.0
2     AskALixa  Software  22.0

Method No 2: Convert database table to Pandas dataframe in Python

There are sometimes that you do not know about the table name in the database. In this case, you can use the following steps to convert SQLite table into pandas dataframe in python.

Step No 1 : first, find all the table names

Step No 2: Convert Each table into Dataframe

Check the following code to convert SQLite database into dataframe in python

import sqlite3
from pandas import read_sql_query, read_sql_table

with sqlite3.connect('sqlite.db') as dbcon:
    tables = list(read_sql_query("SELECT name FROM sqlite_master WHERE type='table';", dbcon)['name'])
    dataframe = {tbl : read_sql_query(f"SELECT * from {tbl}", dbcon) for tbl in tables}
    print(dataframe)
    

Output of the Code

          Name    Course   Age
0  AlixaProDev        CS  19.0
1  Alixawebdev       BBa  21.0
2     AskALixa  Software  22.0

Method No 3: Convert Sqlite Database to Pandas dataframe Using sqlAlchemy Library in Python

The best way to convert a SQLite database to pandas dataframe is to use the sqlalchemy library in python. use the read_sql_table() function of the sqlalchemy, it will change SQLite database into pandas dataframe.

import pandas as pd
import sqlalchemy

db_name = "sqlite.db"
table_name = "Student"

engine = sqlalchemy.create_engine("sqlite:///%s" % db_name, execution_options={"sqlite_raw_colnames": True})
df = pd.read_sql_table(table_name, engine)
print(df)

Output of the Code

          Name    Course   Age
0  AlixaProDev        CS  19.0
1  Alixawebdev       BBa  21.0
2     AskALixa  Software  22.0

Summary and Conclusion

We have learned about three different ways to convert any SQLite database into pandas dataframe in python.

• 1. Convert SQLite to dataframe using Sqlite Query
• 2. Convert if you do not know the table name
• 3. Convert SQLite database into pandas dataframe using sqlalchemy

That is it for this python tutorial. let me know if you have any paid work or any questions using any social handles.