CODEWITHSUNDEEP
pythonsqlitesqlalchemy
Storm
Storm

Reputation: 4445

SQLAlchemy: How to cast string value to date in multiple insert statement?

I have a list of dict objects

data = [
{'id': 1, 'dt':'2002-01-02' },
{'id': 2, 'dt':'2014-01-15' },
{'id': 3, 'dt':'2005-10-20' }
]

and a table in sqlite created using sqlalchemy as follows

engine = create_engine(config.SQLALCHEMY_DATABASE_URI)
metadata = MetaData()

tbl1 = Table('t1', metadata,
                     Column('the_id', Integer, primary_key=True),
                     Column('the_dt', Date))

metadata.create_all(bind=engine)

stmt_insert = tbl1.insert().values(the_id=bindparam('id'), the_dt=bindparam('dt', type_=Date)

with engine.connect() as conn:
    conn.execute(stmt_insert, data)

This gives me the following error:

sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite Date type only accepts Python date objects as input.

What do I assign to the "type_" parameter to make this binding work ?

Upvotes: 1

Views: 6943

Answers (1)

Laurent LAPORTE
Laurent LAPORTE

Reputation: 22942

You need to convert your dt strings to date objects, for instance:

import datetime
for item in data:
    item['dt'] = datetime.datetime.strptime(item['dt'], "%Y-%m-%d").date()

If you don't need the ORM part of SQLAlchemy (no class/table mapping). The easiest way is to tell SQLAlchemy that you use String instead of Date, like this:

tbl1 = Table('t1', metadata,
    Column('the_id', Integer, primary_key=True),
    Column('the_dt', String))

It will work because your date string use ISO 8601 format ("%Y-%m-%d").

But the best practices are:

  1. read records from CSV
  2. convert data to Python objects (int, date, etc.)
  3. insert data in database.

The conversion can be done in the constructor of the class which is mapped to the table.

EDIT A kind of "solution"

from sqlalchemy import Table, Column, Integer, MetaData, Date, String
from sqlalchemy import create_engine
from sqlalchemy.sql import select

engine = create_engine('sqlite:///:memory:', echo=True)

metadata = MetaData()

I've created a "fake" table with a String type instead of a Date:

fake_tbl1 = Table('t1', metadata,
                  Column('id', Integer, primary_key=True),
                  Column('dt', String))

metadata.create_all(bind=engine)

And insert the data as-is:

data = [
    {'id': 1, 'dt': '2002-01-02'},
    {'id': 2, 'dt': '2014-01-15'},
    {'id': 3, 'dt': '2005-10-20'}
]

stmt_insert = fake_tbl1.insert()

with engine.connect() as conn:
    conn.execute(stmt_insert, data)

Then I redefine the same table with the required Date field:

tbl2 = Table('t1', metadata,
             Column('id', Integer, primary_key=True),
             Column('dt', Date),
             extend_existing=True)

Here is a rows selection:

stmt_select = select([tbl2])

with engine.connect() as conn:
    result = conn.execute(stmt_select)

for row in result:
    print(row)

You'll get:

(1, datetime.date(2002, 1, 2))
(2, datetime.date(2014, 1, 15))
(3, datetime.date(2005, 10, 20))

This works for your simple case, but I won't recommend this for a generic solution.

Upvotes: 2

Related Questions