Write data from sqllite data base in json format to text file in python

Question

I have a database called art.db which consists of 5 tables Tables : table1,table2,table3,table4 and table5

I want write all tables data in json format to text file. I am able to write one table data only. Could some one please help me

Below is the my code:

import json
import sqlite3
con = sqlite3.connect('/content/art.db')
cursor = con.cursor()

cursor.execute('''SELECT * FROM table1''')
rows = cursor.fetchall()

rowarray_list = []
for row in rows:
    t = (row[0], row[1])
    rowarray_list.append(t)

j = json.dumps(rowarray_list)

with open("table1.js", "w") as f:
    f.write(j)

import collections

objects_list = []
for row in rows:
    d = collections.OrderedDict()
    d["artwork_id"] = row[0]
    d["department_id"] = row[1]
    objects_list.append(d)

j = json.dumps(objects_list)

with open("final_data.txt", "w") as f:
    f.write(j)

The final_data.txt should contain all 5 tables data in json format.

Are you sure you need to loop over `rows`? Why not adjust the SQL to only return the fields of interest and use `json.dumps` on `rows`? — norie, Oct 29 '21 at 06:58

score 1 · Accepted Answer · edited Jul 15 '22 at 09:21

Your question lacks data to be reproducible but you can try something like this:

import sqlite3
import json

# https://stackoverflow.com/a/3300514/15239951
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

tables = ['table1', 'table2', 'table3', 'table4', 'table5']
data = {}

con = sqlite3.connect('/content/art.db')
con.row_factory = dict_factory
cursor = con.cursor()

for table in tables:
    cursor.execute(f'''SELECT * FROM {table}''')
    rows = cursor.fetchall()
    data[table] = rows

with open('final_data.json', 'w') as fp:
    json.dump(data, fp, indent=4)

Write data from sqllite data base in json format to text file in python

1 Answers1