使用Python将CSV数据复制到PostgreSQL

Question

我正在使用Windows 7 64位操作系统。 我有一个名为"data.csv"的CSV文件。 我希望通过Python脚本将数据导入到PostgreSQL表“temp_unicommerce_status”中。

我的脚本如下：

import psycopg2
conn = psycopg2.connect("host='localhost' port='5432' dbname='Ekodev' user='bn_openerp' password='fa05844d'")
cur = conn.cursor()
cur.execute("""truncate table "meta".temp_unicommerce_status;""")
cur.execute("""Copy temp_unicommerce_status from 'C:\Users\n\Desktop\data.csv';""")
conn.commit()
conn.close()

我遇到了这个错误

Traceback (most recent call last):
  File "C:\Users\n\Documents\NetBeansProjects\Unicommerce_Status_Update\src\unicommerce_status_update.py", line 5, in <module>
cur.execute("""Copy temp_unicommerce_status from     'C:\\Users\\n\\Desktop\\data.csv';""")
psycopg2.ProgrammingError: must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

- Manish Gupta

Answer 1

使用 copy_from 游标方法。

f = open(r'C:\Users\n\Desktop\data.csv', 'r')
cur.copy_from(f, temp_unicommerce_status, sep=',')
f.close()

文件必须作为对象传递。

由于您将从CSV文件中复制，因此需要指定分隔符，因为默认分隔符是制表符。

- Clodoaldo Neto

---

这个（以及copy_expert）需要超级用户角色吗？它不是从STDIN管道传输，因此避免了超级用户角色。从文件复制需要用户，似乎这个也是。我的问题是它运行了，但什么也没做，我认为有一个超级用户错误在默默失败。 - yekta

我可以确认，copy_from 可以在 AWS RDS-PostgreSQL 实例上工作，实际上你只被授予 rds_superuser，但不是 superuser。 - mork

我能够使用copy_expert和STDIN通过csv文件传播数据库。这消除了在postgres中给予postgres用户角色SUPERUSER特权的需要。请参见此处的答案：https://stackoverflow.com/questions/51850512/postgressqlfile-permissions-error-using-copy/51866826#51866826 - raw-bin hood

只有当代码运行在与数据库相同的服务器上时，这才是可行的解决方案。否则，您将在内存中加载文件并通过网络传输。这会产生大量不必要的IO。 - Sonic Soul

需要小心处理，因为psycopg2的copy_from函数使用文本格式而不是CSV格式。 - Gohn67

使用上下文管理器会使这个过程更加高效，特别是在导入大量csv文件时。不要使用一个级别的代码块，而是在该代码块中使用“with open（...”和COPY。 - Entree

Answer 2

我解决这个问题的方法是使用 psychopg2 的 cursor 类函数 copy_expert (文档: http://initd.org/psycopg/docs/cursor.html)。copy_expert 允许您使用 STDIN，因此无需为 postgres 用户发出超级用户特权。然后，您对文件的访问取决于客户端（linux/windows/mac）用户对文件的访问权限。

从 Postgres COPY 文档中（https://www.postgresql.org/docs/current/static/sql-copy.html）：

不要将 COPY 与 psql 指令 \copy 混淆。 \copy 调用 COPY FROM STDIN 或 COPY TO STDOUT，然后在可访问 psql 客户端的文件中获取/存储数据。因此，在使用 \copy 时，文件可访问性和访问权限取决于客户端而不是服务器。

您还可以保留权限，以严格访问 development_user 主文件夹和 App 文件夹。

csv_file_name = '/home/user/some_file.csv'
sql = "COPY table_name FROM STDIN DELIMITER '|' CSV HEADER"
cursor.copy_expert(sql, open(csv_file_name, "r"))

- raw-bin hood

---

是的，我认为psycopg2应该更清楚地说明它期望的是文本格式而不是CSV格式。使用copy_expert似乎是正确传递CSV文件的唯一方法。 - Gohn67

Answer 3

#sample of code that worked for me

import psycopg2 #import the postgres library

#connect to the database
conn = psycopg2.connect(host='localhost',
                       dbname='database1',
                       user='postgres',
                       password='****',
                       port='****')  
#create a cursor object 
#cursor object is used to interact with the database
cur = conn.cursor()

#create table with same headers as csv file
cur.execute("CREATE TABLE IF NOT EXISTS test(**** text, **** float, **** float, **** 
text)")

#open the csv file using python standard file I/O
#copy file into the table just created 
with open('******.csv', 'r') as f:
next(f) # Skip the header row.
    #f , <database name>, Comma-Seperated
    cur.copy_from(f, '****', sep=',')
    #Commit Changes
    conn.commit()
    #Close connection
    conn.close()


f.close()

- Data Analyst

Answer 4

这里是相关PostgreSQL文档的摘录：COPY指令与文件名一起使用，指示PostgreSQL服务器直接从文件中读取或写入。文件必须对服务器可访问，并且名称必须从服务器的视角指定。当指定STDIN或STDOUT时，数据通过客户端和服务器之间的连接传输。

这就是为什么copy命令只限于PostgreSQL超级用户从文件复制或复制到文件的原因：文件必须存在于服务器上，并由服务器进程直接加载。

相反，您应该使用：

cur.copy_from(r'C:\Users\n\Desktop\data.csv', temp_unicommerce_status)

正如另一个答案所建议的那样，因为它在内部使用了从标准输入进行COPY操作。

- Serge Ballesta

Answer 5

我知道这个问题已经有答案了，但是我想补充一些描述:

你可以使用 cursor.copy_from 方法：

首先，你需要创建一个与你的 csv 文件具有相同列数的表。

例如：

我的 csv 文件看起来像这样：

Name,       age , college , id_no , country , state   , phone_no

demo_name   22  , bdsu    , 1456  , demo_co , demo_da , 9894321_

首先创建一个表格：

import psycopg2
from psycopg2 import Error

connection = psycopg2.connect(user = "demo_user",
                                  password = "demo_pass",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "postgres")
cursor = connection.cursor()


create_table_query = '''CREATE TABLE data_set
(Name  TEXT NOT NULL ,
age  TEXT NOT NULL ,
college  TEXT NOT NULL ,
id_no TEXT NOT NULL ,
country TEXT NOT NULL ,
state TEXT NOT NULL ,
phone_no TEXT NOT NULL);'''

cursor.execute(create_table_query)
connection.commit()

现在，您可以在需要三个参数的地方使用cursor.copy_from。

first file object , second table_name , third sep type

you can copy now :

f = open(r'final_data.csv', 'r')
cursor.copy_from(f, 'data_set', sep=',')
f.close()

完成

- Aaditya Ura

Answer 6

你可以使用 d6tstack 来使这个过程变得简单。

import d6tstack
import glob

c = d6tstack.combine_csv.CombinerCSV([r'C:\Users\n\Desktop\data.csv']) # single-file
c = d6tstack.combine_csv.CombinerCSV(glob.glob('*.csv')) # multi-file
c.to_psql_combine('postgresql+psycopg2://psqlusr:psqlpwdpsqlpwd@localhost/psqltest', 'tablename')

它还涉及数据架构更改，创建/附加/替换表格，并允许您使用pandas预处理数据。

- citynorman

---

这对我来说非常有效，没有任何问题。谢谢Citynorman。 - Utkarsh Saraf

Answer 7

我将发布一些在尝试将csv文件复制到基于Linux的系统上的数据库时遇到的错误...

这是一个示例csv文件：

Name Age Height
bob  23   59
tom  56   67

1. 您必须安装 psycopg2 库（即 pip install psycopg2 或 sudo apt install python3-psycopg2）

2. 在使用 psycopg2 之前，您必须在系统上安装 postgres（sudo apt install postgresql-server postgresql-contrib）

3. 现在，您必须创建一个数据库来存储 csv，除非您已经设置了带有预先存在的数据库的 postgres

使用 POSTGRES 命令复制 CSV

• 安装 postgres 后，它会创建一个默认用户帐户，该帐户使您可以访问 postgres 命令

• 要切换到 postgres 帐户，请发出：sudo -u postgres psql

• 通过发出以下命令访问提示符：psql

  #创建数据库的命令 create database mytestdb; #连接到数据库以创建表 \connect mytestdb; #使用与 csv 列名称相同的列创建表 create table test(name char(50), age char(50), height char(50)); #将 csv 文件复制到表中 copy mytestdb 'path/to/csv' with csv header;

使用Python复制CSV文件 我在将CSV文件复制到数据库时遇到的主要问题是我还没有创建数据库，但这仍然可以使用Python完成。

import psycopg2 #import the Postgres library

#connect to the database
conn = psycopg2.connect(host='localhost',
                       dbname='mytestdb',
                       user='postgres',
                       password='')  
#create a cursor object 
#cursor object is used to interact with the database
cur = conn.cursor()

#create table with same headers as csv file
cur.execute('''create table test(name char(50), age char(50), height char(50));''')

#open the csv file using python standard file I/O
#copy file into the table just created 
f = open('file.csv','r')
cursor.copy_from(f, 'test', sep=',')
f.close()

- Blaine McMahon

Answer 8

尝试以root用户postgres的身份执行相同操作。如果这是Linux系统，您可以更改文件权限或将文件移动到/tmp目录中。问题的原因是缺少从文件系统读取的凭据。

- percy