sqlalchemy 中的 Postgresql ON CONFLICT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33307250/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql ON CONFLICT in sqlalchemy
提问by puredevotion
I've read quite a few resources (ao. 1, 2) but I'm unable to get Postgresql's ON CONFLICT IGNORE behaviour working in sqlalchemy.
我已经阅读了很多资源（ao. 1, 2），但我无法在 sqlalchemy 中获得 Postgresql 的 ON CONFLICT IGNORE 行为。
I've used this accepted answeras a basis, but it gives
我已经使用这个接受的答案作为基础，但它给出了
SAWarning: Can't validate argument 'append_string'; can't locate any SQLAlchemy dialect named 'append'
I've tried adding the postgresql dialect to the @compile clause, renaming my object, but it doesn't work.
I also tried to use the str(insert())+ " ON CONFILCT IGNORE"without results. (not surprising btw)
我尝试将 postgresql 方言添加到 @compile 子句，重命名我的对象，但它不起作用。我也尝试使用str(insert())+ " ON CONFILCT IGNORE"没有结果的。（顺便说一句，这并不奇怪）
How can I get the On CONFLICT IGNOREto get added to my inserts? I like the proposed solution, as I can see myself not wanting the IGNOREbehaviour on each INSERT
我怎样才能将On CONFLICT IGNORE要添加到我的插件中？我喜欢提议的解决方案，因为我可以看到自己不希望IGNORE在每个INSERT
ps. using python 2.7 (don't mind upgrading to 3.4/3.5), latest sqlalchemy (1.x)
附：使用 python 2.7（不介意升级到 3.4/3.5），最新的 sqlalchemy (1.x)
采纳答案by Adam Silenko
You don't need this, use exists condition to prevent inserting duplicates.
你不需要这个，使用存在条件来防止插入重复项。
for example:
例如：
INSERT INTO table (unique_name)
SELECT 'some_string'
WHERE NOT EXISTS(SELECT 1 FROM table WHERE unique_name = 'some_string')
you can also
你也可以
INSERT INTO table (unique_name)
VALUES('some_string')
ON CONFLICT (unique_name) DO NOTHING
but if you need insertor update in single query, then this is example for you:
但是如果您需要在单个查询中插入或更新，那么这是您的示例：
INSERT INTO distributors (did, dname)
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
This is an example from PostgreSQL Documentation.
这是 PostgreSQL 文档中的一个示例。
回答by Itay Livni
Using Postgres 9.6.1, sqlachemy 1.1.4, and psycopg2 2.6.2:
使用 Postgres 9.6.1、sqlachemy 1.1.4 和 psycopg2 2.6.2：
Convert your data structure to a dictionary. From Pandas it is
import pandas from sqlalchemy import MetaData from sqlalchemy.dialects.postgresql import insert import psycopg2 # The dictionary should include all the values including index values insrt_vals = df.to_dict(orient='records')Connect to database through sqlalchemy . Instead try psycog2 driver underneath and the native COPY function, which bypasses all the postgres indexing.
csv_data = os.path.realpath('test.csv')
con = psycopg2.connect(database = 'db01', user = 'postgres') cur = con.cursor()
cur.execute("\copy stamm_data from '%s' DELIMITER ';' csv header" % csv_data)
con.commit()
Execute
results = engine.execute(do_nothing_stmt) # Get number of rows inserted rowcount = results.rowcount
将您的数据结构转换为字典。从Pandas是
import pandas from sqlalchemy import MetaData from sqlalchemy.dialects.postgresql import insert import psycopg2 # The dictionary should include all the values including index values insrt_vals = df.to_dict(orient='records')通过 sqlalchemy 连接到数据库。而是尝试下面的 psycog2 驱动程序和本机 COPY 功能，它绕过所有 postgres 索引。
csv_data = os.path.realpath('test.csv')
con = psycopg2.connect(database = 'db01', user = 'postgres') cur = con.cursor()
cur.execute("\copy stamm_data from '%s' DELIMITER';' csv header" % csv_data)
提交（）
执行
results = engine.execute(do_nothing_stmt) # Get number of rows inserted rowcount = results.rowcount
Warning:
警告：
This method does not work with NaTs out of the box.
此方法不适NaT用于开箱即用的s。
Everything together
一切都在一起
tst_df = pd.DataFrame({'colA':['a','b','c','a','z', 'q'],
'colB': pd.date_range(end=datetime.datetime.now() , periods=6),
'colC' : ['a1','b2','c3','a4','z5', 'q6']})
insrt_vals = tst_df.to_dict(orient='records')
engine = sqlalchemy.create_engine("postgresql://user:password@localhost/postgres")
connect = engine.connect()
meta = MetaData(bind=engine)
meta.reflect(bind=engine)
table = meta.tables['tstbl']
insrt_stmnt = insert(table).values(insrt_vals)
do_nothing_stmt = insrt_stmnt.on_conflict_do_nothing(index_elements=['colA','colB'])
results = engine.execute(do_nothing_stmt)
Instead of step 2 and 3 , Using psycog2 driver with the copy command in postgres is faster for larger files (approaching a gig ) because it sets all the table indexing off.
代替第 2 步和第 3 步，在 postgres 中使用带有 copy 命令的 psycog2 驱动程序对于较大的文件（接近 gig）更快，因为它设置了所有表索引。
csv_data = os.path.realpath('test.csv')
csv_data = os.path.realpath('test.csv')
回答by Niklas B
This works with Postgresql 9.5:
这适用于 Postgresql 9.5：
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Insert
@compiles(Insert)
def prefix_inserts(insert, compiler, **kw):
return compiler.visit_insert(insert, **kw) + " ON CONFLICT DO NOTHING"
I use it for bulk_insert_mappings. It does however not make ON CONFLICT DO NOTHINGoptional
我用它来bulk_insert_mappings。然而，它并没有使ON CONFLICT DO NOTHING可选
回答by Eds_k
This works with Postgresql 10.5 and Sqlalchemy 1.3.6:
这适用于 Postgresql 10.5 和 Sqlalchemy 1.3.6：
from sqlalchemy.dialects.postgresql import insert
table_info = {
'tableTime': '',
'deploymentID': '',
'tableData': ''
}
insert_table = insert(Table).values(table_info)
insert_table_sql = insert_table.on_conflict_do_nothing(
index_elements=['tableTime', 'deploymentID']
)
db.session.execute(insert_table_sql)
db.session.commit()
