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

Answer 1

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.