How to convert sqlite database to CSV file in Python

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.