If you have a SQLite database and you want to create it to a CSV file, here is the complete python tutorial for you.
Follow this method to successfully convert any SQLite table into a CSV file in python.
Convert SQLite Database into CVS file in Python
The best way to convert any SQLite database table into a CSV file is to use the sqlite3 module in python.
Python code to convert SQLite database table to CSV file with column names
import csv
import sqlite3
conn = sqlite3.connect('sqlite.db')
cursor = conn.cursor()
cursor.execute("select * from Student;")
with open("out.csv", 'w',newline='') as csv_file:
csv_writer = csv.writer(csv_file)
csv_writer.writerow([i[0] for i in cursor.description])
csv_writer.writerows(cursor)
conn.close()
Output of the code
filename : out.csv
Name,Course,Age
AlixaProDev,CS,19.0
Alixawebdev,BBa,21.0
AskALixa,Software,22.0
Follow these steps to convert any type of database table to a CSV file in python
Step No 1: get all data from the database table using the sqlite3 module
Step No 2: create a CSV file with the python CSV module
Step No 3: get each row from the database query and insert it into csv file
The Best way to convert any Sqlite database to CSV file in python
The best approach to convert any SQLite database to a CSV file is to use pandas. we can first convert an SQLite database data to a dataframe and then convert this dataframe into a CSV file using the pandas module.
Check out the following code to convert SQLite table to CSV
import sqlite3
import pandas as pd
from glob import glob; from os.path import expanduser
conn = sqlite3.connect('sqlite.db')
cursor = conn.cursor()
clients = pd.read_sql('SELECT * FROM Student' ,conn)
clients.to_csv('csvdata.csv', index=False)
Output of the python code
A csv file with the following data in it.
Name,Course,Age
AlixaProDev,CS,19.0
Alixawebdev,BBa,21.0
AskALixa,Software,22.0
Summary and conclusion
We have discussed two different ways to convert an SQLite database table into a CSV file in python. it comes in handy when you are working with data analysis or a machine learning project.
I am a software Engineer having 4+ Years of Experience in Building full-stack applications.