Add way to export to a csv file using the Python library

[kevinlinxc]

According to the documentation, we can make a csv output using the CLI tool, but not the Python library. Could we have the latter?

[tobych]

Hi there. This looks like a useful feature. I had a go at coming up with something that could get us there.

Perhaps a feature could reasonably go in as sqlite_utils.utils.rows_to_file.

This is how I'd do it, without it being in the library. This code works for me using sqlite-utils v3.36.

This is based on code in sql_utils.cli, and sql_utils.utils.rows_from_file.

I'm only trying to write CSV.

import csv
import io

import sqlite_utils

def rows_to_csv_file(cursor, file, header=True, dialect="excel"):
    writer = csv.writer(file, dialect="excel")
    if header:
        writer.writerow([c[0] for c in cursor.description])
    writer.writerows(cursor)

db = sqlite_utils.Database(memory=True)
db["foo"].insert({"bar": 1, "baz": 2})

cursor = db.execute("SELECT * FROM foo")

with io.StringIO() as file:
    rows_to_csv_file(cursor, file)
    print(file.getvalue())

If you're writing to a file, you'd want to use something like this instead:

with open("foo.csv", 'w', newline='', encoding="utf-8-sig") as file:
    rows_to_csv_file(cursor, file)

Now it just needs:

• discussion: perhaps mostly around whether this is useful at all, and whether you'd want TSV, JSON and NL too.
• refactoring to make it easier to test
• unit tests
• type annotations
• documentation