Python SQL查询结果转换为json示例

import sqlite3
import json
import collections
from sqlite3 import Error

try:

    conn = sqlite3.connect(':memory:')

except Error:

    print(Error)

cursor = conn.cursor()

cursor.execute("""
CREATE TABLE students_test (
    id integer,
    first_name text,
    last_name text,
    street text,
    city text,
    st text,
    zip text
); 
""")

conn.commit()

cursor.execute("""
INSERT INTO students_test VALUES
(1, 'Samantha', 'Baker', '9 Main St.', 'Hyde Park', 'NY', '12538'),
(2, 'Mark', 'Salomon', '3 Stavesacre Terrace', 'Dallas', 'TX', '75204');
""")

conn.commit()

cursor.execute('SELECT * FROM students_test')

rows = cursor.fetchall()

rowarray_list = []
for row in rows:
    t = (row[0], row[1], row[2], row[3], row[4], row[5], row[6])
    rowarray_list.append(t)
j = json.dumps(rowarray_list)
with open("student_rowarrays.js", "w") as f:
    f.write(j)
# Convert query to objects of key-value pairs
objects_list = []
for row in rows:
    d = collections.OrderedDict()
    d["id"] = row[0]
    d["firstName"] = row[1]
    d["lastName"] = row[2]
    d["Street"] = row[3]
    d["City"] = row[4]
    d["St"] = row[5]
    d["Zip"] = row[6]
    objects_list.append(d)
j = json.dumps(objects_list)
with open("student_objects.js", "w") as f:
    f.write(j)
conn.close()