Convert blob to bytes SQLite3 Python

Question

Have a requirement of storing/retrieving bytes as a blob in SQLite3; The column definition in as follows:"bytes_as_blob " BLOB, Byte data is stored in the table using the following construct sqlite3.Binary(some_data) and the data when visualized via DB Browser is as below:

<memory at 0x000002157DA24F40>

However, the issue is me being unable to convert the blob stored in SQLite back to bytes. The select statement to retrieve data is SELECT uid, bytes_as_blob from a_table LIMIT 10 and results from SQLite3 are retuned as a DataFrame. The dtypes for the dataframe columns are dobject ;

df = pd.read_sql_query(sql_statement, conn)

print(f"type(df.loc[1].iat[0]) = {type(df.loc[1].iat[0]))}") # uid

print(f"type(df.loc[1].iat[1]) = {type(df.loc[1].iat[1]))}") # bytes_as_blob

The type of the Python objects in the DF are of type <class 'str'>

Is there something that that is needed when converting a blob back to bytes - could not find anything in here https://pandas.pydata.org/docs/user_guide/io.html#io-sql

Tried BytesIO(df_cell_value).read() which which did not work as expected.

what, exactly is the object you are working with? is it a memorview object? — juanpa.arrivillaga
– juanpa.arrivillaga, Commented Nov 12, 2024 at 20:49
That doesn't answer my question at all. What, exactly is df_cell_value? What is the type of object? There is no type "blob" in Python. — juanpa.arrivillaga
– juanpa.arrivillaga, Commented Nov 12, 2024 at 21:17
No, I am not asking for the dtype of the column, object just means it can hold any type of python object. I am asking you what type of python object are you actually working with. — juanpa.arrivillaga
– juanpa.arrivillaga, Commented Nov 12, 2024 at 21:29

tbrugere · Accepted Answer · 2024-11-12 22:30:30Z

0

It looks like pandas is wrongly reading the bytes_as_blob column as a str.

You can tell it not to do that by using the dtype column, eg:

df = pd.read_sql_query(sql_statement, conn, dtype={"bytes_as_blob":bytes})

answered Nov 12, 2024 at 22:30

tbrugere

1,84418 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

Convert blob to bytes SQLite3 Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related