SQLAlchemy Insert executemany Func

I am trying to bulk insert a list of dicts into a MySQL database via SQLAlchemy. Inside the list each dict has time and temperature. The time values are as an ISO time string. I am trying to have MySQL use the STR_TO_DATE function on the way in to create datetime objects to store to the database.

I have sucessfully had this working via insert().values({'time':func.STR_TO_DATE(row.time, date_str), 'temp' = row.temp})

Example Code:

import sqlalchemy from sqlalchemy import create_engine, MetaData, Table, Column, func from sqlalchemy.dialects.mysql import DATETIME, INTEGER, insert temp_data = [{'time':'2019-02-07T14:01:00.000000000Z', 'temperature':68}, {'time':'2019-02-07T14:02:00.000000000Z', 'temperature':69}, {'time':'2019-02-07T14:03:00.000000000Z', 'temperature':70}, {'time':'2019-02-07T14:04:00.000000000Z', 'temperature':71}] date_string = '%Y-%m-%dT%H:%i:%S.%f000Z' mysql_metadata = MetaData() mysql_table = Table('temperature', mysql_metadata, Column('time', DATETIME(fsp = 6), primary_key = True), Column('temperature', INTEGER())) def single_insert(): engine = create_engine('mysql://test_user:test@localhost/temperature', echo = False) mysql_table.drop(engine, checkfirst = True) mysql_table.create(engine) conn = engine.connect() for row in temp_data: conn.execute(mysql_table.insert().values(time = func.STR_TO_DATE(row['time'], date_string), temperature = row['temperature'])) def insert_many(): engine = create_engine('mysql://test_user:test@localhost/temperature', echo = False) mysql_table.drop(engine, checkfirst = True) mysql_table.create(engine) conn = engine.connect() conn.execute(mysql_table.insert(), temp_data) 

Not sure how to go about getting this to work by binding a parameter to a single input value that can call the STR_TO_DATE so data can be inserted via a list of dicts.

Second Edit:

Here is my brute force code that works. Trying to find a way to use SQLALchemy Core to complete this statement.

def insert_many(): engine = create_engine('mysql://test_user:test@localhost/temperature', echo = True) mysql_table.drop(engine, checkfirst = True) mysql_table.create(engine) conn = engine.connect() stmt = text("INSERT INTO temperature VALUES( STR_TO_DATE(:time, '%Y-%m-%dT%H:%i:%S.%f000Z'), :temperature)") conn.execute(stmt, temp_data) 

Third Edit:

Eventually want to do a profile against Python Datetime strptime vs the MySQL method. Looking to see which is faster.

2 Answers

One way to do the bulk insertion for list of dictionaries is by using bindparam for each key and perform execute

from sqlalchemy.sql.expression import bindparam temp_data = [{'time':'2019-02-07T14:01:00.000000000Z', 'temperature':68}, {'time':'2019-02-07T14:02:00.000000000Z', 'temperature':69}, {'time':'2019-02-07T14:03:00.000000000Z', 'temperature':70}, {'time':'2019-02-07T14:04:00.000000000Z', 'temperature':71}] def insert_many(): engine = create_engine('mysql://test_user:test@localhost/temperature', echo = False) mysql_table.drop(engine, checkfirst = True) mysql_table.create(engine) conn = engine.connect() statement = Mysql_table.insert().values({ 'time' : bindparam('time'), 'temperature' : bindparam('temperature') }) conn.execute(statement, temp_data) conn.close() 

Regarding the STR_TO_DATE function , how about updating those values in the dictionary itself and then executing the insert_many() function

for item in temp_data: item.update((k, func.STR_TO_DATE(v)) for k,v in item.items() if k == 'time') 

You can always use the time module to measure time execution

import time from datetime import datetime start_time = time.time() func.STR_TO_DATE('date_value') # datetime.strptime(date_string, "%d %B, %Y") print('--%s seconds --' %(time.time() - start_time) 

SQLAlchemy (SA) doc has this example at the bottom of the page that illustrates the use of DBAPI's API executemany over the SA Core engine. I think the code sample is invalid (you just need the raw values in the args, no field names, if your engine's dialect uses positional inserts), but it shows the necessary calls. Keep in mind that executemany takes the SQL statement string, so the bindparams portion is only used to assist building the string - you may find it simpler to just write the statement as text and ax the bindparams part. However, you may try bindparams first to see how SA generates the statement for your data types in scope.

The advantage of this method is that true executemany is optimized in the DBAPI if you use a popular backend (as MySQL).

In the more modern syntax than the one used in the example, the SA connection should be opened by calling engine.raw_connection()