Saving numpy integers in sqlite database with sqlalchemy

Question

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)

Given that SQLA uses sqlite3 underneath, the solution should work as is I think. — Ilja Everilä
– Ilja Everilä, Commented Aug 23, 2019 at 14:52
@IljaEverilä Can you be more specific? I cannot find a sql.register_adapter method. — user3820991
– user3820991, Commented Aug 23, 2019 at 15:01

Beethoven's 7th · Accepted Answer · 2019-08-23 15:32:49Z

1

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)

edited Aug 23, 2019 at 15:32

answered Aug 23, 2019 at 15:15

Beethoven's 7th

1165 bronze badges

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

Collectives™ on Stack Overflow

Saving numpy integers in sqlite database with sqlalchemy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related