Sqlalchemy 核心,从元组而不是 dict 插入多行 [英] Sqlalchemy core, insert multiple rows from a tuple instead of dict
问题描述
我在 2D 元组中有数据(或者说来自 Numpy 表),并且需要将其插入到 SQL 表中.将 Sqlalchemy Core 与 SQLite 一起使用,如何有效且简单地将这些数据插入到我的表中?
I have data in a 2D tuple (or say coming from a Numpy table), and need to insert it into an SQL table. Using Sqlalchemy Core with SQLite, how can I efficiently and simply insert this data into my table?
例如来自@eclaird;
Take i.e. from @eclaird;
engine = sa.create_engine('sqlite://', echo=True)
metadata = sa.MetaData()
widgets_table = sa.Table('widgets', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('bar', sa.String(50)),
sa.Column('biz', sa.Boolean),
sa.Column('baz', sa.Integer),
)
metadata.create_all(engine)
# Assuming this is the data where None holds place for primary key
my_data = [
(None, "Test", True, 3),
(None, "Test", True, 3),
]
到目前为止,我在 文档中的这一点;所以我有;
So far I'm at this point in the docs; so I have;
engine.execute(widgets_table.insert().values((None, "Test", True, 3)))
哪个有效.但我想一次插入多行,例如
Which works. But I want to insert many rows at once such as
engine.execute(widgets_table.insert().values(((None, "Test", True, 3), (None, "Test", True, 3))))
然后报错;
具有当前数据库版本设置的sqlite"方言不支持就地多行插入.
The 'sqlite' dialect with current database version settings does not support in-place multirow inserts.
也试过了;
insert = widgets_table.insert()
engine.execute(insert, [
(None, "Test", True, 3),
(None, "Test", True, 3)
])
有错误;
AttributeError: 'tuple' 对象没有属性 'keys'
AttributeError: 'tuple' object has no attribute 'keys'
作为最近转换到 SQLalch 的人,我有点迷失在这里.
As a recent convert to SQLalch, I'm a bit lost here.
推荐答案
你遗漏了一些关于你的设置的细节,所以我做了一些事情.插入元组很困难,除非您也插入表的主键,那么为什么不在插入之前根据您的数据创建一个字典?
You're missing some details about your setup so I made something up. Inserting tuples is difficult unless you're inserting the table's primary key too, so why not create a dictionary from your data before insert?
这应该适用于 SQLAlchemy 0.7.6 及更高版本:
This should work with SQLAlchemy 0.7.6 and later:
import sqlalchemy as sa
engine = sa.create_engine('sqlite://', echo=True)
metadata = sa.MetaData()
widgets_table = sa.Table('widgets', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('foo', sa.String(50)),
sa.Column('bar', sa.String(50)),
sa.Column('biz', sa.Boolean),
sa.Column('baz', sa.Integer),
)
metadata.create_all(engine)
# Assuming this is your data
values = [
(None, "Test", True, 3),
(None, "Test", True, 3),
]
with engine.connect() as connection:
with connection.begin() as transaction:
try:
markers = ','.join('?' * len(values[0]))
ins = 'INSERT INTO {tablename} VALUES ({markers})'
ins = ins.format(tablename=widgets_table.name, markers=markers)
connection.execute(ins, values)
except:
transaction.rollback()
raise
else:
transaction.commit()
这篇关于Sqlalchemy 核心,从元组而不是 dict 插入多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!