Error when loading pickle object from SQLite

Question

I have stored a class object as a pickle in an SQLite DB.

Below is code for the file pickle.py

sqlite3.register_converter("pickle", pickle.loads)
sqlite3.register_adapter(list, pickle.dumps)
sqlite3.register_adapter(set, pickle.dumps)

class F:

    a = None
    b = None

    def __init__(self) -> None:
        pass
df = pd.DataFrame({'col1': [1, 2], 'col2': [3, 4]})
f = F()
f.a = df
f.b = df.columns

data = pickle.dumps(f, protocol=pickle.HIGHEST_PROTOCOL)

sqliteConnection = sqlite3.connect('SQLite_Python.db')
cursor = sqliteConnection.cursor()
print("Successfully Connected to SQLite")
DATA = sqlite3.Binary(data)
sqlite_insert_query = f"""INSERT INTO PICKLES1 (INTEGRATION_NAME, DATA) VALUES ('James',?)"""

resp = cursor.execute(sqlite_insert_query,(DATA,))
sqliteConnection.commit()

After that, I am trying to fetch the pickle from the DB. The pickle is stored in a pickle datatype column which I had registered earlier on SQLite in file retrieve_pickle.py.

cur = conn.cursor()
    cur.execute("SELECT DATA FROM PICKLES1 where INTEGRATION_NAME='James'")
    df = None
    rows = cur.fetchall()
    for r in rows[0]:
        print(type(r)) #prints <class 'bytes'>
        df = pickle.loads(r)

But it gives me an error

  File "/Users/ETC/Work/pickle_work/picklertry.py", line 34, in select_all_tasks
    df = pickle.loads(r)
AttributeError: Can't get attribute 'F' on <module '__main__' from '/Users/rusab1/Work/pickle_work/picklertry.py'>

I was trying to store a class object in a pickle column in sqlite after registering pickle.loads as a pickle datatype. I kept the object successfully and was able to retrieve it from DB but when I try to load it back so that I can access the thing and attributes it gives me an error.

digital_monk · Accepted Answer · 2023-02-01 13:49:03Z

2

Pickling requires you to import the actual module which you have in the pickle. I had to import F into the 2nd file where I was loading the pickle.

answered Feb 1, 2023 at 13:49

digital_monk

876 bronze badges

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

1 Comment

Booboo Over a year ago

Because you are not posting complete minimal, reproducible examples, one cannot explicitly see that you are missing a declaration for F. Yes, I suppose all of this could be deduced from the actual error you are getting, but still it's always best to follow the guidelines on how to post to make it easier for people to help you. My answer below is not particularly relevant to your problem, but it may be worth a look.

Booboo · Accepted Answer · 2023-02-01 15:38:36Z

You didn't specify how the database was actually created.

First, I would use on the connect statement the additional arguments: detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES.

Then I would declare column "DATA" to be of type pickle. Then when you select the "DATA" column the binary will automatically be unpickled:

#!/usr/bin/env python3

from sqlite_common import *
import pandas as pd
import pickle

sqlite3.register_converter("pickle", pickle.loads)
sqlite3.register_adapter(list, pickle.dumps)
sqlite3.register_adapter(set, pickle.dumps)

class F:

    a = None
    b = None

    def __init__(self) -> None:
        pass
df = pd.DataFrame({'col1': [1, 2], 'col2': [3, 4]})
f = F()
f.a = df
f.b = df.columns

data = pickle.dumps(f, protocol=pickle.HIGHEST_PROTOCOL)

sqliteConnection = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
print("Successfully Connected to SQLite")
sqliteConnection.execute("""
    create table PICKLES1 (
        "INTEGRATION_NAME" text unique not null,
        "DATA" pickle not null
    )
""")

cursor = sqliteConnection.cursor()
# This is unnecessary:
#DATA = sqlite3.Binary(data)
sqlite_insert_query = f"""INSERT INTO PICKLES1 (INTEGRATION_NAME, DATA) VALUES ('James',?)"""

resp = cursor.execute(sqlite_insert_query,(data,))
sqliteConnection.commit()

cur = sqliteConnection.cursor()
cur.execute("SELECT DATA FROM PICKLES1 where INTEGRATION_NAME='James'")
rows = cur.fetchall()
for r in rows: # Note this change
    f = r[0]
    print(f.a)
    print(f.b)

Prints:

Successfully Connected to SQLite
   col1  col2
0     1     3
1     2     4
Index(['col1', 'col2'], dtype='object')

Even with the way you currently have the "DATA" column defined, you can force the data to be interpreted as pickle-serialized data and have it automatically de-serialized with the following statement if you connect the way I suggest:

cur.execute("SELECT DATA [pickle] FROM PICKLES1 where INTEGRATION_NAME='James'")

You can even define an adapter for class F and void explcit calls to pickle.dumps:

#!/usr/bin/env python3

from sqlite_common import *
import pandas as pd
import pickle

class F:

    a = None
    b = None

    def __init__(self) -> None:
        pass

sqlite3.register_converter("pickle", pickle.loads)
sqlite3.register_adapter(list, pickle.dumps)
sqlite3.register_adapter(set, pickle.dumps)
sqlite3.register_adapter(F, pickle.dumps)

df = pd.DataFrame({'col1': [1, 2], 'col2': [3, 4]})
f = F()
f.a = df
f.b = df.columns

sqliteConnection = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
print("Successfully Connected to SQLite")
sqliteConnection.execute("""
    create table PICKLES1 (
        "INTEGRATION_NAME" text unique not null,
        "DATA" pickle not null
    )
""")

cursor = sqliteConnection.cursor()
sqlite_insert_query = f"""INSERT INTO PICKLES1 (INTEGRATION_NAME, DATA) VALUES ('James',?)"""

resp = cursor.execute(sqlite_insert_query,(f,))
sqliteConnection.commit()

cur = sqliteConnection.cursor()
cur.execute("SELECT DATA FROM PICKLES1 where INTEGRATION_NAME='James'")
rows = cur.fetchall()
for r in rows:
    f = r[0]
    print(f.a)
    print(f.b)

Collectives™ on Stack Overflow

Error when loading pickle object from SQLite

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related