免责声明:我最近接管了其他人的代码库,所以您在这里看到的一些代码已经被预先编写好了。我还将某些变量名更改为可能实际上不起作用的更通用的代码名。你知道吗
我正在尝试使用SQLAlchemy在postgres数据库上批量插入,但遇到了问题。似乎当SQLAlchemy访问数据时,它需要更新表(排除的.columnName)找不到。你知道吗
首先是我得到的错误:
Missing FROM-clause entry for table "excluded"
LINE 1: ...tablename) VALUES (excluded.tablename...
我的密码是:
index_fields = list(billocity_db.get_indexes(self.table_name))
for row in self.data:
if 'id' in self.column_info.keys():
del row['id']
data = row.to_dict()
insert_stmt = insert(self.table).values(data)
for i in index_fields:
if i in data:
del data[i]
for key in data.keys():
data[key] = insert_stmt.excluded[key]
print(data[key])
print(data)
insert_stmt = insert_stmt.on_conflict_do_update(
#Tried both constraint and index_elements with same result
#constraint=self.table_name,
#None of my columns are 'id'. Could that be an issue?
index_elements=['id'],
set_=data
)
try:
print(insert_stmt)
self.session.execute(insert_stmt)
except Exception as e:
self.session.rollback()
self.log.exception(str(e))
finally:
self.session.commit()
调用后立即出现错误
self.session.execute(insert_stmt)
这就是insert stmt在执行之前打印的内容,显然列名称和表名称只是占位符。你知道吗
INSERT INTO
table_name (column_name, column_name, column_name,
column_name, column_name, column_name, column_name,
column_name, column_name, column_name, column_name,
column_name, column_name, column_name, column_name,
column_name)
VALUES
(
excluded.column_name, excluded.column_name, excluded.column_name,
excluded.column_name, excluded.column_name, excluded.column_name,
excluded.column_name, excluded.column_name, excluded.column_name,
excluded.column_name, excluded.column_name, excluded.column_name,
excluded.column_name, excluded.column_name, excluded.column_name,
excluded.column_name
)
ON CONFLICT (id) DO
UPDATE SET
column_name= excluded.column_name, column_name= excluded.column_name,
column_name= excluded.column_name, column_name= excluded.column_name,
column_name= excluded.column_name, column_name= excluded.column_name,
column_name= excluded.column_name, column_name= excluded.column_name,
column_name= excluded.column_name, column_name= excluded.column_name,
column_name= excluded.column_name, column_name= excluded.column_name,
column_name= excluded.column_name, column_name= excluded.column_name,
column_name= excluded.column_name, column_name= excluded.column_name
RETURNING
table_name.id
我在网上找不到很多关于这个问题的额外资源,我也不知道还有什么可以尝试的。任何帮助都将不胜感激。你知道吗
EXCLUDED
在UPDATE
部分中可用,但在VALUES
部分中不可用。你知道吗这是a good tutorial about UPSERT
而且总是有INSERT ON CONFLICT的官方文件
相关问题 更多 >
编程相关推荐