sqliteのデモ

sqlite.py

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

c = conn.cursor()
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real, json text, id integer primary key AUTOINCREMENT)''')
c.execute(
    "INSERT INTO stocks(date, trans, symbol, qty, price, json) VALUES ('2006-01-05','BUY','RHAT',100,35.14,'{\"name\":\"太郎1\"}')")
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00, '{"name":"太郎2"}'),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00, '{"name":"太郎3"}'),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00, '{"name":"太郎4"}'),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00, '{"name":"太郎4"}'),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00, '{"name":"太郎4"}'),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00, '{"name":"太郎4"}'),
             ]
c.executemany('INSERT INTO stocks(date, trans, symbol, qty, price, json) VALUES (?,?,?,?,?,?)', purchases)
conn.commit()
t = ('IBM',)
c.execute('SELECT id,date,trans,json FROM stocks WHERE symbol=? order by id desc', t)
a = c.fetchall()
conn.close()
b = [(z[0], json.loads(z[3]).get("name")) for z in a]
print(b)

https://docs.python.jp/3/library/sqlite3.html