SQLite How do I save the result of a query as a CSV file

Question

I m new to SQLite   Is there a way I can export the results of a query into a CSV file

User · Answer

All the existing answers only work from the sqlite command line, which isn't ideal if you'd like to build a reusable script. Python makes it easy to build a script that can be executed programatically.

import pandas as pd
import sqlite3

conn = sqlite3.connect('your_cool_database.sqlite')

df = pd.read_sql('SELECT * from orders', conn)
df.to_csv('orders.csv', index = False)

You can customize the query to only export part of the sqlite table to the CSV file.

You can also run a single command to export all sqlite tables to CSV files:

for table in c.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall():
    t = table[0]
    df = pd.read_sql('SELECT * from ' + t, conn)
    df.to_csv(t + '_one_command.csv', index = False)

See here for more info.

User · Answer

To include column names to your csv file you can do the following   sqlite gt   headers on sqlite gt   mode csv sqlite gt   output test csv sqlite gt  select   from tbl1  sqlite gt   output stdout   To verify the changes that you have made you can run this command   sqlite gt   show   Output   echo  off    explain  off    headers  on    mode  csv    nullvalue       output  stdout   separator         stats  off    width  22 18

User · Answer

From here and d5e5 s comment   You ll have to switch the output to csv-mode and switch to file output   sqlite gt   mode csv sqlite gt   output test csv sqlite gt  select   from tbl1  sqlite gt   output stdout

User · Answer

Alternatively you can do it in one line  tested in win10   sqlite3 -help sqlite3 -header -csv db sqlite  select   from tbl1    gt  test csv   Bonus  Using powershell with cmdlet and pipe       get-content query sql   sqlite3 -header -csv db sqlite  gt  test csv   where query sql is a file containing your SQL query

User · Answer

In addition to the above answers you can also use  once in a similar way to  output  This outputs only the next query to the specified file  so that you don t have to follow with  output stdout   So in the above example   mode csv  headers on  once test csv select   from tbl1

User · Answer

Good answers from gdw2 and d5e5   To make it a little simpler here are the recommendations pulled together in a single series of commands   sqlite gt   mode csv sqlite gt   output test csv sqlite gt  select   from tbl1  sqlite gt   output stdout

[sqlite] SQLite: How do I save the result of a query as a CSV file?

Examples related to sqlite

Examples related to csv