Python使用psycopg2加载带引号和逗号分隔符的CSV文件

huangapple go评论62阅读模式
英文:

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_tocopy_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.

huangapple
  • 本文由 发表于 2023年2月10日 16:53:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/75408821.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定