每當我想將 pandas 數據框中的數據插入到 postgresql 數據庫中時,我都會收到此錯誤 error: extra data after last expected column CONTEXT: COPY recommendations, line 1: "0,4070,"[5963, 8257, 9974, 7546, 11251, 5203, 102888, 8098, 101198, 10950]""數據幀由三列組成,第一列和第二列是整數類型,第三列是整數列表。我使用下面的函數在 PostgreSQL 中創(chuàng)建了一個表def create_table(query: str) -> None:""":param query: A string of the query to create table in the database:return: None"""try: logger.info("Creating the table in the database") conn = psycopg2.connect(host=HOST, dbname=DATABASE_NAME, user=USER, password=PASSWORD, port=PORT) cur = conn.cursor() cur.execute(query) conn.commit() logger.info("Successfully created a table in the database using this query {}".format(query)) returnexcept (Exception, psycopg2.Error) as e: logger.error("An error occurred while creating a table using the query {} with exception {}".format(query, e))finally: if conn is not None: conn.close() logger.info("Connection closed!")傳遞到該函數的查詢是這樣的:create_table_query = '''CREATE TABLE Recommendations(id INT NOT NULL,applicantId INT NOT NULL,recommendation INTEGER[], PRIMARY KEY(id), CONSTRAINT applicantIdFOREIGN KEY(applicantId)REFERENCES public."Applicant"(id)ON DELETE CASCADEON UPDATE CASCADE ); '''然后,我使用下面的函數將數據框復制到 postgres 中創(chuàng)建的表中。def copy_from_file(df: pd.DataFrame, table: str = "recommendations") -> None: """ Here we are going save the dataframe on disk as a csv file, load the csv file and use copy_from() to copy it to the table """然后我仍然得到這個問題,error: extra data after last expected column CONTEXT: COPY recommendations, line 1: "0,4070,"[5963, 8257, 9974, 7546, 11251, 5203, 102888, 8098, 101198, 10950]""請問您對如何解決這個問題有什么建議嗎?謝謝
1 回答

四季花海
TA貢獻1811條經驗 獲得超5個贊
copy_from
使用文本格式,而不是 csv 格式。您告訴它用作,
分隔符,但這不會改變它嘗試使用的保護方法。因此,引號內的逗號不被視為受保護,它們被視為字段分隔符,所以當然它們太多了。
我認為你需要使用copy_expert
并告訴它使用該csv
格式。
添加回答
舉報
0/150
提交
取消