Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
590 views
in Technique[技术] by (71.8m points)

python - bulk insert list values with SQLAlchemy Core

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'])
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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]

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...