bulk insert list values with SQLAlchemy Core

Question

I'd like to bulk insert a list of strings into a MySQL Database with SQLAlchemy Core.

engine = create_engine("mysql+mysqlconnector://...") meta = MetaData() meta.bind = engine 

My table layout looks like this - together with two currently unused columns (irrelevant1/2):

MyTabe = Table('MyTable', meta, Column('id', Integer, primary_key=True),  Column('color', Text), Column('irrelevant1', Text) Column('irrelevant2', Text)) 

Unfortunately the following does not work - it inserts an empty row. What's the right way to do this?

MyTable.insert().execute(['blue', 'red', 'green']) 

Answer 1

Here's one way to do it:

MyTable.__table__.insert().execute([{'color': 'blue'},                                      {'color': 'red'},                                      {'color': 'green'}]) 

Or, using connection.execute():

conn.execute(MyTable.insert(), [{'color': 'blue'},                                  {'color': 'red'},                                  {'color': 'green'}]) 

You can easily make a list of dicts from the list you have:

[{'color': value} for value in colors] 

Answer 2

Another way to do it:

from sqlalchemy import MetaData, Table, create_engine  engine = create_engine("mysql+mysqlconnector://....") metadata = MetaData() metadata.reflect(engine, only=['MyTable']) table = Table('MyTable', meta, autoload=True, autoload_with=engine)  engine.execute(table.insert(), [{'color': 'blue'},                              {'color': 'red'},                              {'color': 'green'}])