How to Save the Query Result Into a CSV File in SQLite

1. Save a Single SQLite Query Into the CSV File
2. Save All SQLite Queries Into the CSV File
3. Automatic Opening of the CSV File
4. Copy Single or All SQL Queries Into the CSV File
5. Use JOIN Queries to Copy Output Into the CSV File

The purpose of SQLite is to store, read and manage data in the local database. We can use the query language to get the desired data from the SQLite database.

Sometimes, there is a need to save the retrieved results to a save. For this purpose, we save SQLite database query results to the CSV file.

To do so, we use the SQLite command line shell by the .mod command joined with the .once or .output commands. Later, we use the .system command to open that file.

Save a Single SQLite Query Into the CSV File

We use the .once command to export only a single or one query to the CSV file. When we use the .once command, the next SQL statement result exports to the CSV file and returns to the console.

In the below example, we enabled the column headers as well. In the next line, we turned the CSV mode and used the .once command to copy the next SQL command to the result_of_single_query.csv file.

# for showing headers on the screen
.headers on
# turning on the csv mode
.mode csv
# copying a single query only
.once result_of_single_query.csv
SELECT * FROM Customers;

The result of the above statement exports to the CSV file, and here is the data that the CSV file contains. If you want to ignore the headers in exporting the file, use .headers off in the above SQL query.

CustomerId, CustomerName, Age
1, "Holder", 39
2, "Alex", 42
3, "Bob", 27
4, "John", 74

Save All SQLite Queries Into the CSV File

Using the .once command, we can export the next SQL query only.

We also have a facility to store all the SQL queries in the CSV file. With the .output command, we save or append all the SQL queries to the CSV file.

# for copying all the queries to the CSV file
.output save_all_queries.csv
# both these SQL statements get copied to the CSV file
SELECT * FROM Customers;
SELECT * FROM Customers LIMIT 2;

First, we set the output CSV file in the above SQL queries, exactly like in the first example. Then, later run the two SQL queries to save or append the SQL queries data into the CSV file.

Once you open the saved file, the file will show the following output on the screen.

CustomerId, CustomerName, Age
1, "Holder", 39
2, "Alex", 42
3, "Bob", 27
4, "John", 74
CustomerId, CustomerName, Age
1, "Holder", 39
2, "Alex", 42

Again, we didn’t use the .headers off command in SQL statements; hence, headers get a copy in a file once each SQL statement executes. To skip the headers a second time, we have to use the .headers on command the first time and then .headers off before executing the last statement.

.headers on
.output save_all_queries.csv
SELECT * FROM Customers;
.headers off
SELECT * FROM Customers LIMIT 2;

The result of the above SQL statement would be like this:

CustomerId, CustomerName, Age
1, "Holder", 39
2, "Alex", 42
3, "Bob", 27
4, "John", 74
1, "Holder", 39
2, "Alex", 42

Automatic Opening of the CSV File

The .system command is used to open the CSV file. The syntax of opening the file depends on the type of system.

We use the following commands to open the CSV file on the different operating systems.

On a Mac operating system, we use the below command to open a file:

.system open query_to_open_file.csv

On a Windows operating system, we use the below command to open a file:

.system c:/data/query_to_open_file.csv

On a Unix/Linux operating system, we use the below command to open a file:

.system xdg-query_to_open_file.csv

Copy Single or All SQL Queries Into the CSV File

We have discussed that the .once and .output commands are used to store or append SQL queries into the CSV file. This solution is possible when we are using the SQL command prompt.

But if we try a reusable script to save the data programmatically into the CSV file, we use the following source code.

# use pandas for data manipulation and analysis
import pandas as pd

# sqlite3 represents the version of the sqlite, which is 3
import sqlite3

connection = sqlite3.connect("present_database.sqlite")
dataframe = pd.read_sql("SELECT * from customers", connection)
dataframe.to_csv("customers.csv", index=False)

Inside the query part of the read_sql function, we can customize it to get another table part from the SQLite database. Similarly, we can run a single command to save all the queries into the CSV file.

# gets all the table
for table in c.execute("SELECT customerName FROM sqlite_master WHERE type='table';").fetchall():
    t = table[0]
    dataframe = pd.read_sql('SELECT * from ' + t, conn)
    dataframe.to_csv(t + '_single_command.csv', index = False)

Here, we use the sqlite_master command to get all the tables in the SQLite database.

Use JOIN Queries to Copy Output Into the CSV File

We are not limited to a single table query copying; instead, we can copy multiple any query with left or right JOINs. The below shows that the query joins two tables and outputs the query to the CSV file.

.header on
.mode csv
.once /Users/sqlite//customers.csv
SELECT CustomerId, CustomerName, ProductName
FROM Customers AS a
  INNER JOIN Products AS b
  ON a.ProductId = b.ProductId
ORDER BY ProductName;

The above SQL statements output the following into the CSV file.

CustomerId, CustomerName, Age
1, "Holder Boxer", 39
2, "Alex Ferguson", 42
3, "Bob Marlet", 27
4, "John Wack", 74