Sounds _really_ messy mixing JSON and Postgres. I would have Postgres take a dictionary + SQL insert.
# Create table
cur.execute("DROP TABLE IF EXISTS OptionData")
cur.execute('''CREATE TABLE IF NOT EXISTS OptionData
(K real, T real, r real, d real, sig real)''')
# Insert a row of data
cur.execute("INSERT INTO OptionData VALUES (65, 0.25, 0.08, 0.0, 0.3)")
# Save (commit) the changes
conn.commit()
res = cur.execute('SELECT count(rowid) FROM OptionData')
print(res.fetchone()) # next row of a query result set
data = [
(60, 0.25, 0.08, 0.0, 0.3),
(63, 1.0, 0.08, 0.02, 0.2),
(65, 0.25, 0.06, 0.0, 0.3),
(70, 0.5, 0.08, 0.0, 0.4),
]
cur.executemany('INSERT INTO OptionData VALUES(?, ?, ?, ?, ?)',data)
# Inserting a single record
data2 = [
(100, 10.25, 0.1, 0.0, 0.1),
]
cur.executemany('INSERT INTO OptionData VALUES(?,?,?,?,?)',data2)
data3 = (120, 10.25, 0.1, 0.0, 0.1)
cur.execute('INSERT INTO OptionData VALUES(?,?,?,?,?)',data3)
conn.commit()
data4 = (420, 1220.25, 0.1, 0.0, 0.1)
cur.execute('INSERT INTO OptionData VALUES(?,?,?,?,?)',data4)
# rollback any changes to the database since the last commit
conn.rollback() # data4 will not be in database
cur.execute("select * from OptionData")
# fetch all remaining rows of a query results
print(cur.fetchall()) # as a list
cur = conn.execute("SELECT K,T,r,d,sig from OptionData")
for row in cur:
print ("K = ", row[0])
print ("T = ", row[1])
print ("r = ", row[2])
print ("d = ", row[3])
print ("sig = ", row[4],'\n')
# Update a record by changing an attribute
cur = conn.execute("UPDATE OptionData set r = 0.10 where K = 70")
conn.commit()
# delete
cur = conn.execute("DELETE from OptionData where K = 63")
conn.commit()
print("*** final print of records")
cur = conn.execute("SELECT K,T,r,d,sig from OptionData")
for row in cur:
print ("K = ", row[0])
print ("T = ", row[1])
print ("r = ", row[2])
print ("d = ", row[3])
print ("sig = ", row[4],'\n')
# filtering
cur.execute("select * from OptionData where K > 64")
# fetch all remaining rows of a query results
print("WHERE ", cur.fetchall()) # as a list