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'])
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]
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'}])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With