24

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'])
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
fanti
  • 1,859
  • 3
  • 16
  • 31
  • 3
    Take a look at the docs for [Executing Multiple Statements](http://docs.sqlalchemy.org/ru/latest/core/tutorial.html#executing-multiple-statements) – miku Sep 09 '13 at 22:29
  • For very fast insertions, look into using the lower level `session.bulk_insert_mappings(Table, lst)` See the docs [here](https://docs.sqlalchemy.org/en/13/orm/session_api.html#session-and-sessionmaker). – ChaimG Jul 29 '19 at 17:54

2 Answers2

40

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]
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • Thanks, would it be possible to just use the list (no dictionaries)? – fanti Sep 09 '13 at 22:35
  • @fanti well, [docs](http://docs.sqlalchemy.org/ru/latest/core/tutorial.html#executing-multiple-statements) says that yes, this should be a list of dictionaries. – alecxe Sep 09 '13 at 22:41
  • 3
    @fanti why don't just make a list of dicts from the list, like I've noted in the answer? – alecxe Sep 09 '13 at 22:44
  • @alecxe: Is there a way to pass _hints_ to the bulk insert statements in SQLAlchemy? so that I can make it even faster? For Eg.: `/*+append*/` in Oracle helps bulk inserts speed up much faster. – Sameer Mirji Mar 18 '16 at 10:57
  • This [results in an `executemany` DBAPI call](https://docs.sqlalchemy.org/en/13/core/tutorial.html#executing-multiple-statements), which is usually converted to multi-value `INSERT .. VALUES (..), (..), (..)` statement (e.g. see [MySQL docs](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html)). According to the [sqlalchemy's FAQ](https://docs.sqlalchemy.org/en/13/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow), this is the fastest you can get without using DB-specific bulk loading methods. – Nickolay Aug 16 '19 at 18:12
  • ...I believe sqlalchemy doesn't provide an API to use bulk-loads, you [have to use dialect-specific APIs for that](https://stackoverflow.com/a/34523707/1026). – Nickolay Aug 16 '19 at 18:13
  • Which variant would be faster? – Jay Feb 19 '20 at 14:26
  • 1
    maybe this helps someone later, we need to use __table__ to access the Table object that is underlying in the class. i.e. MyTable.__table__ = table is equivalent to the Table('MyTable'), meta,....) construct of the classical sqlalchemy method. And insert is a method of the classical Table – Kay Mar 25 '20 at 16:55
9

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'}])
Antoine Brunel
  • 1,065
  • 2
  • 14
  • 30