SQLAlchemy: How to cast string value to date in multiple insert statement?
<p>I have a list of dict objects</p>

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

<p>and a table in sqlite created using sqlalchemy as follows</p>

<pre><code>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)
</code></pre>

<p>This gives me the following error:</p>

<blockquote>
  <p>sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite Date type
  only accepts Python date objects as input.</p>
</blockquote>

<p>What do I assign to the "type_" parameter to make this binding work ?</p>

<p>You need to convert your <code>dt</code> strings to date objects, for instance:</p>

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

<p>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:</p>

<pre><code>tbl1 = Table('t1', metadata,
    Column('the_id', Integer, primary_key=True),
    Column('the_dt', String))
</code></pre>

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

<p>But the best practices are:</p>

<ol>
<li>read records from CSV</li>
<li>convert data to Python objects (<code>int</code>, <code>date</code>, etc.)</li>
<li>insert data in database.</li>
</ol>

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

<p><strong>EDIT</strong> A kind of "solution"</p>

<pre><code>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()
</code></pre>

<p>I've created a "fake" table with a <code>String</code> type instead of a <code>Date</code>:</p>

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

metadata.create_all(bind=engine)
</code></pre>

<p>And insert the data as-is:</p>

<pre><code>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)
</code></pre>

<p>Then I redefine the same table with the required <code>Date</code> field:</p>

<pre><code>tbl2 = Table('t1', metadata,
             Column('id', Integer, primary_key=True),
             Column('dt', Date),
             extend_existing=True)
</code></pre>

<p>Here is a rows selection:</p>

<pre><code>stmt_select = select([tbl2])

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

for row in result:
    print(row)
</code></pre>

<p>You'll get:</p>

<pre><code>(1, datetime.date(2002, 1, 2))
(2, datetime.date(2014, 1, 15))
(3, datetime.date(2005, 10, 20))
</code></pre>

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

python sqlite sqlalchemy