Python SQLAlchemy 在元组数据结构中插入多行
我一直在研究如何将大约500个元组（行），每个元组有7个元素（列），插入到数据库中。我在StackOverflow和其他论坛上阅读了很多帖子。我发现有一个建议是使用'executemany()'方法，但我不太明白具体怎么做。我需要把我的对象从元组转换成字典吗？问题是我没有那种名称:值的数据结构。
如何使用SQLAlchemy将查询表达式转储为SQL文件并批量插入到数据库管理系统中？
Here is an example:
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = MetaData()
hockey= Table('hockey', metadata,
Column('team', String(16), primary_key=True),
Column('jersey_colour', String(16)),
Column('stadium', String(32)),
Column('goals', Integer),
Column('date', Date, primary_key=True),
Column('assists', Integer))
>>>data[0]
[(u'Maple Leafs', u'Blue', u'Air Canada Center', 151, '2013-03-25', 301)]
编辑：
我尝试了描述的解决方案（Sqlalchemy核心，从元组插入多行而不是字典），如下所示：
markers = ','.join('?' * len(data[0]))
ins = 'INSERT INTO {tablename} VALUES ({markers})'
ins = ins.format(tablename=hockey.name, markers=markers)
>>str(ins)
'INSERT INTO hockey VALUES (?,?,?,?,?,?)'
conn = engine.connect()
result = conn.execute(ins, data)
In [59]: result = conn.execute(ins, data)
2013-03-26 07:29:28,371 INFO sqlalchemy.engine.base.Engine INSERT INTO hockey VALUES (?,?,?,?,?,?)
2013-03-26 07:29:28,371 INFO sqlalchemy.engine.base.Engine (u'Maple Leafs', u'Blue', u'Air Canada Center', 151, '2013-03-25', 301)
2013-03-26 07:29:28,371 INFO sqlalchemy.engine.base.Engine ROLLBACK
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
<ipython-input-59-dafe2aef2c66> in <module>()
----> 1 result = conn.execute(ins, data)
/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
662 object,
663 multiparams,
--> 664 params)
665 else:
666 raise exc.InvalidRequestError(
/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_text(self, statement, multiparams, params)
806 statement,
807 parameters,
--> 808 statement, parameters
809 )
810 if self._has_events:
/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
876 parameters,
877 cursor,
--> 878 context)
879 raise
880
/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
869 statement,
870 parameters,
--> 871 context)
872 except Exception, e:
873 self._handle_dbapi_exception(
/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
318
319 def do_execute(self, cursor, statement, parameters, context=None):
--> 320 cursor.execute(statement, parameters)
321
322 def do_execute_no_params(self, cursor, statement, context=None):
OperationalError: (OperationalError) near "hockey": syntax error 'INSERT INTO hockey VALUES (?,?,?,?,?,?)' (u'Maple Leafs', u'Blue', u'Air Canada Center', 151, '2013-03-25', 301)
出现的错误：
OperationalError: (OperationalError) near "hockey": syntax error 'INSERT INTO hockey VALUES (?,?,?,?,?,?)' (u'Maple Leafs', u'Blue', u'Air Canada Center', 151, '2013-03-25', 301)
2 个回答
1
来自未来的问候！
我发现自己也想根据现有的SQLAlchemy表（或者说模型，我的情况是继承自declarative_base()）进行批量数据插入。我也不喜欢为了插入数据而临时创建一个字典，所以我去寻找更简单的方法。
我相信自从你在2013年问这个问题以来，很多事情都发生了变化，但在2021年，使用SQLAlchemy 1.4和Python 3.7.x时，这里是我找到的有效方法：
from sqlalchemy import create_engine, MetaData, Table, Column, String, Integer
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = MetaData(bind=engine)
hockey = Table('hockey', metadata,
Column('team', String(16), primary_key=True),
Column('jersey_colour', String(16)),
Column('stadium', String(32)),
Column('goals', Integer),
Column('date', String(10), primary_key=True),
Column('assists', Integer))
data = [
['Blue', 'Toronto Maple Leafs', 'Air Canada Center', '2013-03-25', 301, 151],
['Red', 'Calgary Flames', 'PenWest', '2013-03-25', 254, 147]
]
metadata.create_all()
engine.execute(hockey.insert().values(data))
你可能会注意到，我把date字段改成了字符串，这样更简单，因为SQLite的数据库API库需要一个Python的datetime.date类型，具体可以参考这里。（如果有人对这个小技巧感到困惑，可以看看这个回答来澄清。）
总之，现在使用.insert().values将一个二维数据结构插入SQLite数据库是完全可以的，不再需要dict(zip(…))这种繁琐的操作了。
>>> from sqlalchemy import select
>>> engine.execute(select(hockey)).all()
[('Blue', 'Toronto Maple Leafs', 'Air Canada Center', '2013-03-25', '301', 151),
('Red', 'Calgary Flames', 'PenWest', '2013-03-25', '254', 147)]
2
我做了以下操作：
column_names = tuple(c.name for c in hockey.c)
>>>column_names
('team', 'jersey_colour', 'stadium', 'goals', 'date', 'assists')
final = [dict(zip(column_names,x)) for x in data]
上面的代码为每一行创建了一个字典的列表。理论上这个应该没问题，但当我运行的时候却出现了以下错误：
>>>conn.execute(ins, final)
SQLite Date type only accepts Python date objects as input.
无论如何，这又是一个我需要进一步研究的问题。不过，我还是在这里回答并接受这个问题，因为上面的字典应该是可以正常工作的。