Psycopg2：将CSV数据复制到具有额外列值的表中

Question

我正在使用psycopg2中的copy_expert方法将数据从CSV文件复制到postgresql表。我有这样的postgres表格：

create table my_table (
  cola          text,
  colb          text,
  colc          text,
  cold          text,
  cole          text,
  colf          text,
  colg          text
)

以及一个包含前五列数据的CSV，如下所示：

^{pr2}$

我想复制前五列的CSV数据，同时还要为colf和colg指定值（每行的colf和colg的值相同）。在

我可以像这样将前五列复制到我的表中：

conn = psycopg2.connect('dbname=name user=username')
cur = conn.cursor()
copy_sql = """
  copy my_table (cola, colb, colc, cold, cole)
  from stdin with
    csv
    header
    delimiter as ','
"""
from_csv = '/path/to/data.csv'
with open(from_csv, 'r') as f:
  cur.copy_expert(sql=copy_sql, file=f)
  conn.commit()
  cur.close()

如何使用python为最后两列指定值？我知道我可以在表DDL中指定默认值，如下所示：

create table my_table (
  cola          text,
  colb          text,
  colc          text,
  cold          text,
  cole          text,
  colf          text default 'foo',
  colg          text default 'bar'
)

但是我想使用python添加这些值，因为每个CSV上传都有自己的colf和{}的值，这些值是由python代码中的逻辑决定的。在

Answer 1

看起来有几种方法可以做到这一点，首先将我需要的列添加到数据中，然后上载更新的数据。在

使用petl包：

import psycopg2
from petl import fromcsv, addfield, todb

csv_file = '/path/to/data.csv'
table = fromcsv(csv_file)
table = addfield(table, 'colf', 'Some value')
table = addfield(table, 'colg', 'Another value')

conn = psycopg2.connect('dbname=test user=user')
todb(table, conn, 'my_table')

这在小数据上可以正常工作，但在大数据上却慢得令人难以置信。psycopg2copy_from和{}命令似乎执行得更快，因为它们使用了postgresql大容量复制。我可以使用copy_from复制我的数据，方法是先将csv文件转换成pandasdataframe：

^{pr2}$

为了使用psycopg2copy_命令，我需要使用read()和readline()方法将dataframe转换为类似文件的对象，我可以使用StringIO：

buf = StringIO()
df.to_csv(buf, header=False, index=False)
buf.pos = 0

请注意，您需要将缓冲区的pos设置为0，因为pandas.to_csv似乎在默认情况下将pos设置为结束。有关说明，请参见this SO answer。在

然后我可以复制缓冲区对象：

conn = psycopg2.connect('dbname=test user=user')
cur = conn.cursor()
cur.copy_from(buf, 'my_table', sep=',')
conn.commit()
cur.close()