Serving the Quantitative Finance Community

 
User avatar
Piyushbhatt
Topic Author
Posts: 5
Joined: July 8th, 2022, 5:14 am

insert psycopg2 python dictionary as json

December 1st, 2022, 8:39 am

I want to insert a python dictionary into my postgresql database as a json file (via python and psycopg2).
I have this:
thedictionary = {'price money': '$1', 'name': 'Google', 'color': '', 'imgurl': 'http://www.google.com/images/nav_logo225.png', 'charateristics': 'No Description', 'store': 'google'}
cur.execute("INSERT INTO product(store_id, url, price, charecteristics, color, dimensions) VALUES (%d, %s, %s, %d, %s, %s)", (1,  'http://www.google.com', '$20', thedictionary, 'red', '8.5x11'))
This gives an error:
cur.execute("INSERT INTO product(store_id, url, price, charecteristics, color, dimensions) VALUES (%d, %s, %s, %d, %s, %s)", (1, 'http://www.google.com', '$20', thedictionary, 'red', '8.5x11')) psycopg2.ProgrammingError: can't adapt type 'dict'
I'm not sure where to go from here. I couldn't locate anything related on the internet, but I did come across this page, which stated that "you may use register adapter() to adapt any Python dictionary to JSON, either registering Json or any subclass or factory building a suitable adapter." Is that right?
I'm not sure how to perform this precise thing, as I'm new to psycopg2.
Hi, I’m Piyush. I’m a Computer Science and Engineering graduate who is passionate about programming and technology. I found this forum in hopes of learning something valuable in programming.
 
User avatar
Cuchulainn
Posts: 18225
Joined: July 16th, 2004, 7:38 am
Location: Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch

Re: insert psycopg2 python dictionary as json

December 2nd, 2022, 7:09 pm

Sounds _really_ messy mixing JSON and Postgres. I would have Postgres take a dictionary + SQL insert.

Here's sqlite3
# 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

They sought it with thimbles, they sought it with care; They pursued it with forks and hope; They threatened its life with a railway-share; They charmed it with smiles and soap.
 
User avatar
Cuchulainn
Posts: 18225
Joined: July 16th, 2004, 7:38 am
Location: Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch

Re: insert psycopg2 python dictionary as json

December 2nd, 2022, 7:11 pm

And JSON on its own is also easy
# Streams and files
data = [{'K': 65, 'T': 0.25, 'r': 0.8, 'd': 0.0, 'sig':0.3}]
f = io.StringIO()
json.dump(data, f)
print(f.getvalue())
Last edited by Cuchulainn on December 2nd, 2022, 7:16 pm, edited 1 time in total.
They sought it with thimbles, they sought it with care; They pursued it with forks and hope; They threatened its life with a railway-share; They charmed it with smiles and soap.
 
User avatar
Cuchulainn
Posts: 18225
Joined: July 16th, 2004, 7:38 am
Location: Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch

Re: insert psycopg2 python dictionary as json

December 2nd, 2022, 7:12 pm

 I'm new to psycopg2.
Meaning? white belt, yellow, orange,...?
They sought it with thimbles, they sought it with care; They pursued it with forks and hope; They threatened its life with a railway-share; They charmed it with smiles and soap.
 
User avatar
tagoma
Posts: 2510
Joined: February 21st, 2010, 12:58 pm

Re: insert psycopg2 python dictionary as json

December 3rd, 2022, 8:34 pm

 I'm new to psycopg2.
Meaning? white belt, yellow, orange,...?
OP is bot belt me thinkin.
Exact same question asked years ago on SO