mail@pastecode.io avatar
a year ago
1.9 kB
import sqlite3
import json

# Connect to the database
conn = sqlite3.connect('mydatabase.db')

# Create a cursor object
cursor = conn.cursor()

# Execute the query and fetch the results
query = "SELECT * FROM mytable"
results = cursor.fetchall()

# Convert the results to a list of dictionaries
keys = [i[0] for i in cursor.description]
dict_results = [dict(zip(keys, row)) for row in results]

# Convert the list of dictionaries to a JSON string
json_results = json.dumps(dict_results, indent=4)

# Write the JSON string to a file
with open('results.json', 'w') as f:
#go to deeper    
In this code, you first connect to the SQLite database using the sqlite3 module and create a cursor object. You then execute your query using the execute() method of the cursor and fetch the results using the fetchall() method.

Next, you convert the results to a list of dictionaries using a list comprehension and the description attribute of the cursor object to get the column names. Each row in the result set is converted to a dictionary where the keys are the column names and the values are the corresponding values in the row.

After that, you convert the list of dictionaries to a JSON string using the json.dumps() function with the indent parameter set to 4 to make the output more readable.

Finally, you write the JSON string to a file using the open() function with the w mode to open the file for writing and the write() method to write the JSON string to the file.

Note that this example assumes that your query returns a result set that can be converted to a list of dictionaries. If your query returns a scalar value or other type of result, you'll need to adjust the code accordingly. Also, make sure to handle any exceptions that may occur during the execution of the code.