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:

error message

How can I save the object to the database?

If you already have a file containing the serialized (pickled) object then why not just read that file as a stream of bytes and then write that byte stream to a binary column (e.g., VARBINARY(max))? — Gord Thompson
– Gord Thompson, Commented Sep 22, 2018 at 20:35
I was able to write the binary stream to the database. Now I want to retrieve the binary stream and convert it back to pickle object. Is there any way?? — Kshitiz Tiwari
– Kshitiz Tiwari, Commented Sep 23, 2018 at 6:44

Gord Thompson · Accepted Answer · 2018-09-23 13:42:10Z

5

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}

edited Sep 23, 2018 at 13:42

answered Sep 23, 2018 at 13:22

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bluenote10 Over a year ago

Is this example for Python 2 or 3? With Python3 and MySQL, this approach results in

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xxÂ\x81L\x0f' at line 5 (1064) (SQLParamData)

. I assume that is due to the difference of handling binary strings?

Gord Thompson Over a year ago

@bluenote10 - I'm fairly certain that it would have been for Python 3; I started avoiding Python 2 quite a while ago. I just tried this code in Python 3.8 against a MySQL 5.6.13 server using pyodbc version 4.0.30 and myodbc8a.dll version 08.00.0018 and it worked fine for me.

bluenote10 Over a year ago

Thanks for the clarification. It looks like the problem has to do with the actual content and/or length of the binary and/or other values present in the insert. I still need to narrow down the problem, but it could be an issue in pyodbc or the MySQL ODBC driver. In any case I can confirm that the example works in theory for Python 3.

Collectives™ on Stack Overflow

insert pickle object to database using pyodbc

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related