Ben Chuanlong Du's Blog

It is never too late to learn.

Use the Blob Data Type in Sqlite3

In [6]:
import io
import datetime
import sqlite3
import pandas as pd
In [2]:
df = pd.DataFrame({"x": [1, 2, 3, 4, 5], "y": [5, 4, 3, 2, 1], "z": [1, 1, 1, 1, 1]})

df.head()
Out[2]:
x y z
0 1 5 1
1 2 4 1
2 3 3 1
3 4 2 1
4 5 1 1
In [4]:
bio = io.BytesIO()
df.to_parquet(bio)
bio.seek(0)
Out[4]:
0
In [7]:
conn = sqlite3.connect("blob.sqlite3")
conn.execute(
    """
    CREATE TABLE IF NOT EXISTS queries (
        query Text NOT NULL PRIMARY KEY,
        timestamp Real NOT NULL,
        data Blob NOT NULL
    )
    """
)
conn.execute(
    """
    insert into queries (
        query, timestamp, data
    ) values (
        ?, ?, ?
    )
    """,
    ["select * from queries", datetime.datetime.now().timestamp(), bio.read()],
)
Out[7]:
<sqlite3.Cursor at 0x7f8415d66c70>
In [8]:
rows = conn.execute("select * from queries").fetchall()
In [10]:
bio2 = io.BytesIO(rows[0][2])
In [11]:
bio2.seek(0)
Out[11]:
0
In [12]:
pd.read_parquet(bio2)
Out[12]:
x y z
0 1 5 1
1 2 4 1
2 3 3 1
3 4 2 1
4 5 1 1
In [ ]:

Comments