SQLite to JSON using Python and Pandas

You have data in a database that you want to export to a JSON file.



Solution

For this example, we are going to use a SQLite database. SQLite databases are used all over, especially for embedded use cases, such as mobile phones or IoT devices. These databases are file based and don't require running a database server.

Also, we will use the pandas data analysis package for easier syntax. You can use vanilla Python, but pandas abstracts the process a little bit.

Let's assume that our database file is located at "/path/to/file/my.db". If you are working with a database server, you just swap out the connection syntax (e.g. s3.connect("/path/to/file/my.db")) for your database appropriate syntax.

import pandas as pd
import sqlite3 as s3
import json

data = pd.read_sql(
    "select * from my_table", 
    s3.connect("/path/to/file/my.db"),
)

data = data.T.to_dict()
data = [data[x] for x in data]

dump = json.dumps(data)

with open("data.json", "w") as file:
    file.write(dump)