Python-Use binary copy table from with psycopg2

Question

i'm trying to adapt this Use binary COPY table FROM with psycopg2 example from @Mike T to my data but with i'm having some problems.

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))

I'm getting this error:

curs.copy_expert('COPY ' + table + ' FROM STDIN WITH BINARY', cpy) psycopg2.DataError: incorrect binary data format CONTEXT: COPY test_test, line 1, column id_from_database

What am i doing wrong?

Thank you :)

(I can not comment on the original question because i don't have enough reputation)

Solution to datetime and string columns: stackoverflow.com/questions/54678121/… github.com/enomado/python-postgresql-load/blob/master/… hh = pack('!i{}s'.format(size), size, value) for string size and value binary encoded with encode('utf8') and hh = pack('!iQ'.format(size), size, from_epoch) for epoch difference between 2020-01-01 and the given date after it. — 42n4
– 42n4, Commented May 13, 2021 at 10:56
Some solutions: github.com/pbrumm/pg_data_encoder/blob/master/lib/… — 42n4
– 42n4, Commented May 13, 2021 at 12:14

HoverHell · Accepted Answer · 2018-06-18 17:53:19Z

0

cpgcopy might be relevant here.

answered Jun 18, 2018 at 17:53

HoverHell

4,8993 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python-Use binary copy table from with psycopg2

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related