英文:
Python psycopg2 loading csv file with quote and comma delimiter
问题
以下是您要的翻译部分:
import csv
import psycopg2
try:
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 |status
1 |Juan |Hello, World |active
2 |Pepito |Howdy, how are you |inactive
英文:
I have a data like this
id, Name, Remarks, status
1, Juan, "Hello, World", active
2, Pepito, "Howdy, how are you", inactive
i am trying to load it to postgres using python, psycopg2
import csv
import psycopg2
try:
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 |status
1 |Juan |Hello, World |active
2 |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.csv
id,Name,Remarks,status
1,Juan,"Hello, World",active
2,Pepito,"Howdy, how are you",inactive
create table csv_test(id integer, name varchar, remarks varchar, status varchar);
import psycopg2
con = 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 | active
2 | 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.csv
id,Name,Remarks,status
1,Juan,"Hello, World",active
2,Pepito,"Howdy, how are you",inactive
create table csv_test(id integer, name varchar, remarks varchar, status varchar);
import psycopg2
con = 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 | active
2 | 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论