使用psycopg2模块将值插入数据库时​​出错 [英] error inserting values to db with psycopg2 module

问题描述

我正在尝试使用与sqlalchemy一起使用的pscycopg2模块将数据帧插入到我的postgres数据库中。该过程是将excel文件加载到pandas数据框中，然后通过预定义的表模式将数据框插入数据库中。

I am attempting to insert a dataframe into my postgres database using the pscycopg2 module used with sqlalchemy. The process is loading an excel file into a pandas dataframe and then inserting the dataframe into the database via the predefined table schema.

我相信这些是相关的代码行：

I believe these are the relevant lines of code:

post_meta.reflect(schema="users")
df = pd.read_excel(path) 
table = sql.Table(table_name, post_meta, schema="users")
dict_items = df.to_dict(orient='records')
connection.execute(table.insert().values(dict_items))

我遇到以下错误：

<class 'sqlalchemy.exc.ProgrammingError'>, ProgrammingError("(psycopg2.ProgrammingError) can't adapt type 'numpy.int64'",)

数据框中的所有数据字段类型均为int64。

All data field types in the dataframe are int64.

关于此错误的原因及其含义，我似乎找不到类似的问题或信息。

I can't seem to find a similar question or information regarding why this error is and what it means.

任何方向都很好。
谢谢

Any direction would be great. Thanks

推荐答案

好像您要插入numpy整数，而psycopg2不知道如何处理这些对象。您需要先将它们转换为普通的python整数。也许尝试对每个值调用 int（）函数...如果失败，请提供更多带有代码的上下文。

Looks like you're trying to insert numpy integers, and psycopg2 doesn't know how to handle those objects. You need to convert them to normal python integers first. Maybe try calling the int() function on each value... Please provide more context with code if that fails.

这篇关于使用psycopg2模块将值插入数据库时​​出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！