How to export SQLite database to CSV

SQLite is a popular database engine used in many applications for handling structured data. Often, there arises a need to export data from an SQLite database to a CSV file for reporting, analysis, or importing into other systems. CSV, or Comma Separated Values, is a widely-used format for representing tabular data. In this article, we will walk through the steps to export an SQLite database to a CSV file using different techniques.

Using SQLite Command-Line Interface

The SQLite command-line tool (sqlite3) offers a convenient way to convert a database table to a CSV using a simple set of commands. Follow the steps below to achieve this:

1. Open the terminal and navigate to the directory where your database file is located.
2. Invoke the SQLite command-line utility.

sqlite3 mydatabase.db

Within the SQLite prompt, you can set CSV mode and export data with the following commands:

.headers on
.mode csv
.output data.csv
SELECT * FROM my_table;
.output stdout
.quit

In this code:

• .headers on tells SQLite to include the column names as the first row in the CSV.
• .mode csv switches the output mode of the SQLite to CSV.
• .output data.csv directs the output to a file named data.csv.
• SELECT * FROM my_table; is the command that selects all data from my_table for export.
• .output stdout reverts the output back to the screen after the export is complete.

Using Python to Export SQLite to CSV

Another powerful method is using Python with its built-in SQLite3 and CSV libraries for programmatic export.

Here is a Python script to export data:

import sqlite3
import csv

def export_sqlite_to_csv(db_file, csv_file):
    try:
        # Connect to the database
        conn = sqlite3.connect(db_file)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM my_table")

        # Fetch all data from the executed SQL
        rows = cursor.fetchall()
        column_names = [description[0] for description in cursor.description]

        # Write to CSV file
        with open(csv_file, 'w', newline='') as file:
            writer = csv.writer(file)
            writer.writerow(column_names)  # write header
            writer.writerows(rows)

        print("Data exported successfully to ", csv_file)
    except sqlite3.Error as error:
        print("Error while connecting to sqlite", error)
    finally:
        # Close the connection
        if conn:
            conn.close()

# Usage
export_sqlite_to_csv('mydatabase.db', 'data.csv')

In this example:

• We import the necessary modules for SQLite connection and CSV handling.
• The function export_sqlite_to_csv takes two parameters: the database file name and desired CSV file name.
• We obtain all the data from my_table and store it in memory.
• We open the target CSV file and write both the column headers and row data into it.
• Finally, close the database connection.

Conclusion

Exporting data from SQLite to CSV is a straightforward task that can be accomplished with just a few commands or with the help of a script. Using the command-line interface is quick and efficient for smaller tasks or singular exports, while scripting using Python provides greater flexibility and automation potential for more complex scenarios. Depending on your needs, either approach can be used to enable seamless data exportations.