Convert SQLite 3 database to CSV

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.

+Upvote 755
Answer 1

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.

+Upvote 641
Answer 2

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

+Upvote 377
Answer 3

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=<yourpath>") 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: 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) & 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(); 
+Upvote 251

All Right-Reserved 2023 ©