Python-使用来自 psycopg2 的二进制复制表

Question

我正在尝试调整这个 Use binary COPY table FROM with psycopg2来自@Mike T 的示例到我的数据，但我遇到了一些问题。

import psycopg2
import numpy as np
from struct import pack
from io import BytesIO
from datetime import datetime


conn = psycopg2.connect(host = 'x', database = 'x', user = 'x')
curs = conn.cursor()

DROP TABLE IF EXISTS test_test;
CREATE TABLE test_test(
    id_from_database INT PRIMARY KEY, 
    version VARCHAR, 
    information TEXT
    );

data = [(3,1,'hello hello!!'), (2,'123','test test???!'),(3,9, 'bye bye :)')]
dtype = [('id_from_database', 'object'),('version', 'object'),('information', 'object')]
data = np.array(data,dtype=dtype)

def prepare_text(dat):
   cpy = BytesIO()
   for row in dat:
       cpy.write('\t'.join([repr(x) for x in row]) + '\n')
   return(cpy)

def prepare_binary(dat):
    pgcopy_dtype = [('num_fields','>i2')]
    for field, dtype in dat.dtype.descr:
        pgcopy_dtype += [(field + '_length', '>i4'),
                         (field, dtype.replace('<', '>'))]
    pgcopy = np.empty(dat.shape, pgcopy_dtype)
    pgcopy['num_fields'] = len(dat.dtype)
    for i in range(len(dat.dtype)):
        field = dat.dtype.names[i]
        pgcopy[field + '_length'] = dat.dtype[i].alignment
        pgcopy[field] = dat[field]
    cpy = BytesIO()
    cpy.write(pack('!11sii', b'PGCOPY\n\377\r\n\0', 0, 0))
    cpy.write(pgcopy.tostring())  # all rows
    cpy.write(pack('!h', -1))  # file trailer
    #print("cpy")
    #print(cpy)
    return(cpy)
###
def time_pgcopy(dat, table, binary):
    print('Processing copy object for ' + table)
    tstart = datetime.now()
    cpy = prepare_binary(dat)
    tendw = datetime.now()
    print('Copy object prepared in ' + str(tendw - tstart) + '; ' +
        str(cpy.tell()) + ' bytes; transfering to database')
    cpy.seek(0)
    curs.copy_expert('COPY ' + table + ' FROM STDIN WITH BINARY', cpy)
    conn.commit()
    tend = datetime.now()
    print('Database copy time: ' + str(tend - tendw))
    print('        Total time: ' + str(tend - tstart))
    return
print(time_pgcopy(data, 'test_test', binary=True))

我收到这个错误:

curs.copy_expert('COPY' + table + 'FROM STDIN WITH BINARY', cpy)psycopg2.DataError:不正确的二进制数据格式上下文:复制 test_test，第 1 行，列 id_from_database

我做错了什么？

谢谢你:)

(我无法对原始问题发表评论，因为我没有足够的声誉)

Answer 1

cpgcopy可能与此相关。