Integer in python/pandas becomes BLOB (binary) in sqlite

Question

Storing an integer in sqlite results in BLOBs (binary values) instead of INTEGER in sqlite. The problem is the INT in the "Baujahr" column. The table is created.

CREATE TABLE "Objekt" ( `No` INTEGER NOT NULL UNIQUE, `Objekt_id` INTEGER, 
`Strecke` TEXT, `Baujahr` INTEGER, `Eigentümer` TEXT, PRIMARY KEY(`No`) )

dataframe and dtypes:

id  Strecke  Baujahr Eigentümer Objekt_id
5     A23     1938    Fronz      0327

Objekt.dtypes
Strecke       object
Baujahr        int64
Eigentümer    object
Objekt_id     object
dtype: object

The DataFrame ist written to sqlite

stmt ="INSERT INTO Objekt (Objekt_id, Strecke, Baujahr, Eigentümer) VALUES (?, ?, ?, ?)"
c.execute(stmt, (Objekt.Objekt_id.values[0], Objekt.Strecke.values[0], 
Objekt.Baujahr.values[0], Objekt.Eigentümer.values[0] ))
conn.commit()

Sqlite works only up to INT 8 and works not with INT 32 or INT64 (The definition BIGINT in the CREATE TABLE .... does not help either). So I tried to convert using these conversions

Objekt.Baujahr.astype(int)
Objekt.Baujahr.astype(np.int8)
Objekt.Baujahr = int(Objekt.Baujahr)

The dtypes command shows that Baujahr remained int64!!

I cannot edit the values in the database and by querying these values i get a binary back. Any idea?

Python 3.6.4, sqlite3 2.6.0, pandas 0.22.0

Stivi B · Accepted Answer · 2018-03-27 08:13:16Z

22

For some reason Sqlite does not accept INT larger than 8 byte. Therefore it is necessary to add the following statements.

sqlite3.register_adapter(np.int64, lambda val: int(val))
sqlite3.register_adapter(np.int32, lambda val: int(val))

The docs in sqlite are at this point a little bit short. But it works perfectly.

answered Mar 27, 2018 at 8:13

Stivi B

4333 silver badges10 bronze badges

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

2 Comments

Seb Over a year ago

Very good solution, much better than manually casting the values before insert. Btw: instead of lambda val: int(val) you can just use int.

extreme4all Over a year ago

what to do if the data in the database is already a binary blob instead of an integer?

Collectives™ on Stack Overflow

Integer in python/pandas becomes BLOB (binary) in sqlite

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related