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.
I am a software Engineer having 4+ Years of Experience in Building full-stack applications.