CODEWITHSUNDEEP
pythonsqlitenumpysqlalchemyinteger
user3820991
user3820991

Reputation: 2610

Saving numpy integers in sqlite database with sqlalchemy

I want to save data from a pandas DataFrame to a sqlite database with sqlalchemy. (I know I can use pandas' .to_sql, but I found it easier to use sqlalchemy directly if something did not work properly.)

Some columns contain numpy integers. When I try to save those to the DB as sql.Integer they are actually saved as binaries. I think the answer of why this happens is given here and it also shows how to deal with this when using sqlite3: sqlite3.register_adapter(np.int64, lambda val: int(val)).

Is there something equivalent for sqlalchemy?

Here an example

import pandas as pd
import sqlalchemy as sql
import numpy as np

eng = sql.create_engine('sqlite:///C:/test.db')

df = pd.DataFrame({'name':['a','b'],'integer': np.random.randint(5,size=2)})

with eng.connect() as con:

    metadata = sql.MetaData()

    users = sql.Table('users', metadata,
         sql.Column('id', sql.Integer, primary_key=True),
         sql.Column('name', sql.String),
         sql.Column('integer', sql.Integer)
         )

    metadata.create_all(con)

    data = [ df.iloc[i].to_dict() for i in range(len(df)) ]
    con.execute(users.insert(),data)

Upvotes: 2

Views: 954

Answers (1)

Beethoven's 7th
Beethoven's 7th

Reputation: 116

You answered your own question. You're just missing an import. Add the following lines to your example code and it should work :)

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

So your example will now look like this:

import sqlite3
import pandas as pd
import sqlalchemy as sql
import numpy as np
sqlite3.register_adapter(np.int64, lambda val: int(val))

eng = sql.create_engine('sqlite:///C:/test.db')

df = pd.DataFrame({'name':['a','b'],'integer': np.random.randint(5,size=2)})

with eng.connect() as con:

    metadata = sql.MetaData()

    users = sql.Table('users', metadata,
         sql.Column('id', sql.Integer, primary_key=True),
         sql.Column('name', sql.String),
         sql.Column('integer', sql.Integer)
         )

    metadata.create_all(con)

    data = [ df.iloc[i].to_dict() for i in range(len(df)) ]
    con.execute(users.insert(),data)

Upvotes: 1

Related Questions