psycopg2:无法适应类型'numpy.int64' [英] psycopg2: can't adapt type 'numpy.int64'
问题描述
我有一个具有如下所示dtypes的数据框,我想将该数据框插入到postgres DB中,但是由于错误 不能适应类型为'numpy.int64'
I have a dataframe with the dtypes shown below and I want to insert the dataframe into a postgres DB but it fails due to error can't adapt type 'numpy.int64'
id_code int64
sector object
created_date float64
updated_date float64
如何将这些类型转换为本地python类型,例如从int64(本质上是"numpy.int64")转换为经典int,然后可以通过psycopg2客户端对postgres进行接受.
How can I convert these types to native python types such as from int64 (which is essentially 'numpy.int64') to a classic int that would then be acceptable to postgres via the psycopg2 client.
data['id_code'].astype(np.int) defaults to int64
仍然可以从一种numpy类型转换为另一种(例如,从int转换为float)
It is nonetheless possible to convert from one numpy type to another (e.g from int to float)
data['id_code'].astype(float)
更改为
dtype: float64
最重要的是,如果有人知道如何将psycopg2转换为经典类型,psycopg2似乎不理解numpy数据类型.
The bottomline is that psycopg2 doesn't seem to understand numpy datatypes if any one has ideas how to convert them to classic types that would be helpful.
已更新:插入数据库
def insert_many():
"""Add data to the table."""
sql_query = """INSERT INTO classification(
id_code, sector, created_date, updated_date)
VALUES (%s, %s, %s, %s);"""
data = pd.read_excel(fh, sheet_name=sheetname)
data_list = list(data.to_records())
conn = None
try:
conn = psycopg2.connect(db)
cur = conn.cursor()
cur.executemany(sql_query, data_list)
conn.commit()
cur.close()
except(Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
推荐答案
我不确定为什么您的data_list包含NumPy数据类型,但是当我运行您的代码时,我也会遇到同样的事情.这是构造data_list的另一种方法,该方法使整数和浮点数最终成为其本机python类型:
I'm not sure why your data_list contains NumPy data types, but the same thing happens to me when I run your code. Here is an alternative way to construct data_list that so that integers and floats end up as their native python types:
data_list = [list(row) for row in data.itertuples(index=False)]
替代方法
I think you could accomplish the same thing in fewer lines of code by using pandas to_sql:
import sqlalchemy
import pandas as pd
data = pd.read_excel(fh, sheet_name=sheetname)
engine = sqlalchemy.create_engine("postgresql://username@hostname/dbname")
data.to_sql(engine, 'classification', if_exists='append', index=False)
这篇关于psycopg2:无法适应类型'numpy.int64'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!