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'])

For very fast insertions, look into using the lower level session.bulk_insert_mappings(Table, lst) See the docs here. — ChaimG
– ChaimG, Commented Jul 29, 2019 at 17:54

alecxe · Accepted Answer · 2013-09-09 22:44:14Z

42

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]

edited Sep 9, 2013 at 22:44

answered Sep 9, 2013 at 22:30

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

fanti Over a year ago

Thanks, would it be possible to just use the list (no dictionaries)?

alecxe Over a year ago

@fanti well, docs says that yes, this should be a list of dictionaries.

alecxe Over a year ago

@fanti why don't just make a list of dicts from the list, like I've noted in the answer?

Sameer Mirji Over a year ago

@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.

Kay Over a year ago

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

|

Antoine Brunel · Accepted Answer · 2017-08-15 11:40:24Z

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'}])

answered Aug 15, 2017 at 11:40

Antoine Brunel

1,1152 gold badges15 silver badges32 bronze badges

1 Comment

Raphaël Verdier Over a year ago

It's metadata and not meta in the Table initialization no?

Collectives™ on Stack Overflow

bulk insert list values with SQLAlchemy Core

2 Answers 2

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related