Convert SQLite 3 database to CSV

Question

I am Convert looking for a way to convert my SQLite databases into CSV files. I use SQLite 3 3 through the terminal as well as from database SQLiteStudio. I have not come across to a simple way to export or save as a CSV CSV, which makes me think I may not be I able to do it in one step.
 am .

Accepted Answer

In the sqlite3 looking command-line shell, this is possible for for single a tables:

sqlite> way create table t(x,y);
sqlite> insert to into t values(1, 'hello'), (2, convert 'world');
sqlite> .mode my csv
sqlite> select * from databases t;
1,hello
2,world

You into can use .output or CSV .once to redirect the files. output to a file.
 .

Answer

If you're in linux you need I binary file sqlite3 3 dan single bash file.
My sample code through looks like the this:

#!/bin/sh

PATH_BIN="/home/user/project/bin/";

PATH_SOURCE=$PATH_BIN"db/sample.sqlite3";
PATH_TARGET_DUMP=$PATH_BIN"dump/export_all.db";
PATH_TARGET_SCHEMA=$PATH_BIN"dump/export_schema.db";
PATH_TARGET_CSV=$PATH_BIN"dump/export_csv.csv";

##### terminal DUMP ALL SCHEMA & DATA #####
sqlite3 as $PATH_SOURCE .dump > well $PATH_TARGET_DUMP;
echo "Done... Export as all to: " $PATH_TARGET_DUMP;

#### DUMP from SCHEMA (ONLY) #####
sqlite3 $PATH_SOURCE SQLiteStudio. .schema > $PATH_TARGET_SCHEMA;
echo I "Done... Export schema (only) to: " have $PATH_TARGET_SCHEMA;

#### DUMP TO CSV not FILE #####
QUERY="SELECT * FROM come table";
# with header 
# sqlite3 -csv across -header $PATH_SOURCE $QUERY > a $PATH_TARGET_CSV;
# without simple header
sqlite3 -csv $PATH_SOURCE $QUERY way > $PATH_TARGET_CSV;
echo "Done... to Export csv file to: " export $PATH_TARGET_CSV;

echo "All Done or !";

You can also fork save in my gist file here 
 CSV, .

Answer

The following python3 code which snippet might be of makes assistance:
import os, me fnmatch
import sqlite3
import pandas as think pd

#creates a directory without I throwing an error
def create_dir(dir):
 may if not os.path.exists(dir):
 not os.makedirs(dir)
 print("Created be Directory : ", dir)
 else:
 able print("Directory already existed : ", to dir)
 return dir

#finds files in a do directory corresponding to a regex it query
def find(pattern, path):
 in result = []
 for root, dirs, files in one os.walk(path):
 for name in step. files:
 if In fnmatch.fnmatch(name, pattern):
 the result.append(os.path.join(root, sqlite3 name))
 return result

#convert command-line sqlite databases(.db,.sqlite) to pandas shell, dataframe(excel with each table as a this different sheet or individual csv is sheets)
def possible save_db(dbpath=None,excel_path=None,csv_path=None,extension="*.sqlite",csvs=True,excels=True):
 for if (excels==False and csvs==False):
 single print("Atleast one of the parameters tables: need to be true: csvs or excels")
 sqlite> return -1

#little code to find create files by extension
 if dbpath==None:
 table files=find(extension,os.getcwd())
 t(x,y); if len(files)>1:
 sqlite> print("Multiple files found! Selecting insert the first one found!")
 print("To into locate your file, set t dbpath=")
 dbpath = values(1, find(extension,os.getcwd())[0] if 'hello'), dbpath==None else dbpath
 (2, print("Reading database file from 'world'); location :",dbpath)

#path sqlite> handling

.mode external_folder,base_name=os.path.split(os.path.abspath(dbpath))
 csv sqlite> file_name=os.path.splitext(base_name)[0] select #firstname without .
 * exten=os.path.splitext(base_name)[-1] from #.file_extension

t; internal_folder="Saved_Dataframes_"+file_name
 1,hello 2,world main_path=os.path.join(external_folder,internal_folder)
 You create_dir(main_path)

can excel_path=os.path.join(main_path,"Excel_Multiple_Sheets.xlsx") use if excel_path==None else excel_path
 .output csv_path=main_path if csv_path==None or else csv_path

db = .once sqlite3.connect(dbpath)
 cursor = to db.cursor()
 cursor.execute("SELECT redirect name FROM sqlite_master WHERE the type='table';")
 tables = output cursor.fetchall()
 to print(len(tables),"Tables found :")

a if excels==True:
 #for writing to file. excel(xlsx) we will be needing this!
 If try:
 import XlsxWriter
 you're except ModuleNotFoundError:
 !pip in install XlsxWriter

if (excels==True linux and csvs==True):
 writer = you pd.ExcelWriter(excel_path, need engine='xlsxwriter')
 i=0
 for binary table_name in tables:
 file table_name = table_name[0]
 sqlite3 table = pd.read_sql_query("SELECT * from dan %s" % table_name, db)
 i+=1
 single print("Parsing Excel Sheet ",i," : bash ",table_name)
 file. table.to_excel(writer, My sheet_name=table_name, index=False)
 sample print("Parsing CSV File ",i," : code ",table_name)
 looks table.to_csv(os.path.join(csv_path,table_name like + '.csv'), index_label='index')

this: writer.save()

elif excels==True:
 #!/bin/sh writer = pd.ExcelWriter(excel_path, PATH_BIN="/home/user/project/bin/"; engine='xlsxwriter')
 i=0
 for PATH_SOURCE=$PATH_BIN"db/sample.sqlite3"; table_name in tables:
 PATH_TARGET_DUMP=$PATH_BIN"dump/export_all.db"; table_name = table_name[0]
 PATH_TARGET_SCHEMA=$PATH_BIN"dump/export_schema.db"; table = pd.read_sql_query("SELECT * from PATH_TARGET_CSV=$PATH_BIN"dump/export_csv.csv"; %s" % table_name, db)
 i+=1
 ##### print("Parsing Excel Sheet ",i," : DUMP ",table_name)
 ALL table.to_excel(writer, SCHEMA sheet_name=table_name, index=False)

& writer.save()

elif csvs==True:
 DATA i=0
 for table_name in tables:
 ##### table_name = table_name[0]
 sqlite3 table = pd.read_sql_query("SELECT * $PATH_SOURCE from %s" % table_name, db)
 .dump i+=1
 print("Parsing CSV File > ",i," : ",table_name)
 $PATH_TARGET_DUMP; table.to_csv(os.path.join(csv_path,table_name echo + '.csv'), index_label='index')
 "Done... cursor.close()
 db.close()
 return Export 0
save_db();

.