Python and SQLite - Exporting Data

Sometimes it can be useful to export data from a database, so that it can be analysed, or, to import in to another computer system. CSV, or Comma Separated Value files, are one such file format that allows for both of these scenarios.

Below is an example of how Python can be used to export data to a CSV file called ‘personexport.csv’, from an SQLite database table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting and importing data.

Firstly, the CSV file path and name are set, along with the path to the database and a check is made to see if the database actually exists. A further check is then made to see if the path for the CSV file exists. If it does, a connection to the database is established and a query is executed to extract the data from the database. The CSV file is then opened and the table headers, along with the rows of data are added to it. Finally, confirmation of a successful export is provided. A ‘try-except-finally’ block is used to catch any errors that may occur, as well as close the database connection, regardless of whether the export is successful or not.

import csv
import os
import sqlite3

# File path and name.
filePath = 'c:\\demo\\'
fileName = 'personexport.csv'

# Database.
database = 'c:\\demo\\testDB.db'
connect = None

# Check if database file exists.
if not os.path.isfile(database):

    # Confirm incorrect database location and stop program execution.
    print("Error locating database.")
    quit()

# Check if the file path exists.
if os.path.exists(filePath):

    try:

        # Connect to database.
        connect = sqlite3.connect(database)

    except sqlite3.DatabaseError as e:

        # Confirm unsuccessful connection and quit.
        print("Database connection unsuccessful.")
        quit()

    # Cursor to execute query.
    cursor = connect.cursor()

    # SQL to select data from the person table.
    sqlSelect = \
        "SELECT id, firstname, lastname, title, dob \
         FROM person \
         ORDER BY id"

    try:

        # Execute query.
        cursor.execute(sqlSelect)

        # Fetch the data returned.
        results = cursor.fetchall()

        # Extract the table headers.
        headers = [i[0] for i in cursor.description]

        # Open CSV file for writing.
        csvFile = csv.writer(open(filePath + fileName, 'w', newline=''),
                             delimiter=',', lineterminator='\r\n',
                             quoting=csv.QUOTE_ALL, escapechar='\\')

        # Add the headers and data to the CSV file.
        csvFile.writerow(headers)
        csvFile.writerows(results)

        # Message stating export successful.
        print("Data export successful.")

    except sqlite3.DatabaseError as e:

        # Message stating export unsuccessful.
        print("Data export unsuccessful.")
        quit()

    finally:

        # Close database connection.
        connect.close()

else:

    # Message stating file path does not exist.
    print("File path does not exist.")

The CSV file produced contains the following data.

"id","firstname","lastname","title","dob"
"1","Bob","Smith","Mr","1980-01-20"
"3","Fred","Bloggs","Mr","1975-05-07"
"4","Alan","White","Mr","1989-03-20"
"5","Fiona","Bloggs","Mrs","1985-05-19"
"6","Zoe","Davis","Miss","1979-07-11"
"7","Tom","Ingram","Mr","1971-10-04"
"8","Karen","Thomas","Mrs","1969-03-08"
"9","Samantha","Yates","Miss","1995-08-27"