英文:
Python psycopg2 loading csv file with quote and comma delimiter
问题
以下是您要的翻译部分：
import csvimport psycopg2try:next(f)cursor.copy_from(f, table_name, sep=',', quotechar='"')conn.commit()except (Exception, psycopg2.DatabaseError) as error:print("Error: %s" % error)conn.rollback()cursor.close()print(1)print(f"Data successfully imported to {table_name}.")cursor.close()
您的目标输出表格如下：
id|Name |Remarks |status1 |Juan |Hello, World |active2 |Pepito |Howdy, how are you |inactive
英文:
I have a data like this
id, Name, Remarks, status1, Juan, "Hello, World", active2, Pepito, "Howdy, how are you", inactive
i am trying to load it to postgres using python, psycopg2
import csvimport psycopg2try:next(f)cursor.copy_from(f, table_name, sep=',', quotechar='"')conn.commit()except (Exception, psycopg2.DatabaseError) as error:# os.remove(path)print("Error: %s" % error)conn.rollback()cursor.close()print(1)print(f"Data successfully imported to {table_name}.")cursor.close()
i am getting error of
Error: 'quotechar' is an invalid keyword argument for this function
my target output to table is
id|Name |Remarks |status1 |Juan |Hello, World |active2 |Pepito |Howdy, how are you |inactive
答案1
得分: 0
错误信息显示，copy_from 方法的 quotechar 参数不存在，我为您提供 psycopg2.cursor 的文档链接：https://www.psycopg.org/docs/cursor.html
我处理这个问题的方式将是提取 CSV 信息到 Python 字典或列表，然后使用 psycopg2，这样您可以在将其提交到数据库之前使用 Python 内置方法修改字符串。
英文:
As the error shows you, the argument quotechar of the method copy_from does not exists, linking you the doc of psycopg2.cursor : https://www.psycopg.org/docs/cursor.html
The way i would approach this would be extract the csv informations to a python dictionary or to lists, and then use psycopg2, so you can modify the strings with python built-in methods before commiting it to your database.
答案2
得分: 0
使用copy_expert可以从这里Copy进行复制。
cat csv_test.csvid,Name,Remarks,status1,Juan,"Hello, World",active2,Pepito,"Howdy, how are you",inactivecreate table csv_test(id integer, name varchar, remarks varchar, status varchar);import psycopg2con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)cur = con.cursor()with open('csv_test.csv') as csv_file:cur.copy_expert("copy csv_test from stdin with csv header", csv_file)con.commit()select * from csv_test ;id | name | remarks | status----+--------+--------------------+----------1 | Juan | Hello, World | active2 | Pepito | Howdy, how are you | inactive
copy_to 和 copy_from 使用文本格式，如此处描述 COPY Text Format。这个格式与您文件中的CSV格式不同。copy_expert 允许您使用在 CSV Format 部分描述的 COPY CSV 格式，这样整个过程会更加简单。
英文:
Use copy_expert from here Copy.
cat csv_test.csvid,Name,Remarks,status1,Juan,"Hello, World",active2,Pepito,"Howdy, how are you",inactivecreate table csv_test(id integer, name varchar, remarks varchar, status varchar);import psycopg2con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)cur = con.cursor()with open('csv_test.csv') as csv_file:cur.copy_expert("copy csv_test from stdin with csv header", csv_file)con.commit()select * from csv_test ;id | name | remarks | status----+--------+--------------------+----------1 | Juan | Hello, World | active2 | Pepito | Howdy, how are you | inactive
copy_to and copy_from use the text format as described here COPY Text Format. This format is different from the CSV format you have in your file. copy_expert allows you to use the COPY csv format as described in section CSV Format. This makes the whole process a lot easier.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论