How to convert sqlilte3 database to tsv from python code?

0
votes

I am trying to get the tables inside sqlite3 database and save them into a tsv file.

does pandas have a tool to do that?

I know how to do it from sqlite:

sqlite> .mode tabs
sqlite> .output test1.tsv
sqlite> Select * from <table_name>;

but how to do the samething in python environment?

2
pythonsqlitecsv
The key to resolving this is iteration. Iterate over the table, with each iteration write to the tsv. There are lots of examples of both. Tutorials Point has a good one on sqlite + Python. - diek

2 Answers

4
votes

TSV == tab separated values, so the built-in csv module is more than enough to export your data. Something as simple as:

import csv
import sqlite3

connection = sqlite3.connect("your_database.db")  # open connection to your database
cursor = connection.cursor()  # get a cursor for it
cursor.execute("SELECT * FROM your_table_name")  # execute the query
rows = cursor.fetchall()  # collect the data
with open("test1.tsv", "wb") as f:  # On Python 3.x use "w" mode and newline=""
    writer = csv.writer(f, delimiter="\t")  # create a CSV writer, tab delimited
    writer.writerows(rows)  # write your SQLite data

should do the trick.

0
votes

Above answer did not really work for me. I couldn't find a good answer without many lines of code or without having format issue in the output file. So I just came up with my own version that works without issues in a couple of lines.

    import pandas as pd

    conn = sqlite3.connect(self.err_db_file, isolation_level=None,
                           detect_types=sqlite3.PARSE_COLNAMES)
    db_df = pd.read_sql_query("SELECT * FROM error_log", conn)
    db_df.to_csv('database.tsv', index=False, sep='\t')