How to insert pandas Dataframe to SQLite table in Python

Pandas is a python library, that makes data representation and data manipulation much easier. If you are working with a python project and need to store a pandas dataframe in the SQLite database table, here is how you should do it.

In this article, I will guide you on how to insert a pandas dataframe in Python SQLite table. Follow this technique which I have checked personally and is working properly.

Insert Pandas Dataframe to Sqlite table in Python

To insert Pandas dataframe to SQLite table in python, you have to use df.to_sql() function. df.to_sql() function convert a dataframe into a SQLite database table. Passing the index parameter as False will avoid inserting the indices.

Below is the Python code that converts Pandas dataframe into SQLite table


import pandas as pd
import sqlite3
df = pd.read_csv('csvdata.csv')
print(df)
table_name = 'Student'

conn = sqlite3.connect('mydb.sqlite')
query = f'Create table if not Exists {table_name} (Name text, Course text, Age real)'
conn.execute(query)
df.to_sql(table_name,conn,if_exists='replace',index=False)
conn.commit()
conn.close()

Output of the Code

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

Retrieve dataframe from the SQLite table

We can convert SQLite database back to a dataframe. To get the dataframe back from the SQLite table by using the pd.read_sql() function.

Use the following python code to get back the dataframe from the SQLite table.


import pandas as pd
import sqlite3
df = pd.read_csv('csvdata.csv')
print(df)
table_name = 'Student'

conn = sqlite3.connect('mydb.sqlite')
query = f'Create table if not Exists {table_name} (Name text, Course text, Age real)'
conn.execute(query)
df.to_sql(table_name,conn,if_exists='replace',index=False)
conn.commit()
conn.close()

r_df = pd.read_sql("select * from Student",conn)
print(r_df)

Output of the Code

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

Summary and Conclusion

In this quick python tutorial, we have seen how we can insert pandas dataframe to SQLite table and do the vice versa. Please let me know if you have any questions.

Leave a Comment

Scroll to Top