insert pickle object to database using pyodbc

Question

I tried the following but it didn't work:

keypoints_database = pickle.load( open( "5958.p", "rb" ) )

sql = 'INSERT INTO tb_fpdata (Std_SymbolNo , FP_Descriptors) VALUES (5958 , ?)' , pyodbc.Binary(keypoints_database)

I got the following error:

How can I save the object to the database?

Answer 1

So you have a binary file containing a pickled object. A hex dump of the file looks like this:

00000000: 8004 9515 0000 0000 0000 007d 9428 8c03  ...........}.(..
00000010: 666f 6f94 4b01 8c03 6261 7294 4b02 752e  foo.K...bar.K.u.

You can save the object to a binary column, then read it back and unpickle it via pickle.loads like so:

import pyodbc
import pickle

conn_str = (
    r'DRIVER=ODBC Driver 17 for SQL Server;'
    r'SERVER=.\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("CREATE TABLE #test (id INT PRIMARY KEY, pkl VARBINARY(max))")

# read pre-pickled object from file and save to table
with open(r'C:\Users\Gord\Desktop\data.pickle', 'rb') as f:
    sql = "INSERT INTO #test (id, pkl) VALUES (?, ?)"
    params = (1, f.read())
    crsr.execute(sql, params)

# read it back from database and unpickle it
pickled_data = crsr.execute("SELECT pkl FROM #test WHERE id=1").fetchval()
unpickled_object = pickle.loads(pickled_data)

print(unpickled_object)
# {'foo': 1, 'bar': 2}