Inserting Bytes to Postgres with python gives error, but with PG SQL console works

Question

I'm encrypting some data and trying to insert it into postgres as bytes. I'm using psycopg2 and python. When I try from python it gives me an error about varying character.

Error value too long for type character varying(30)

Entry Exist Passing!

When I insert it into the Postgresql using the exact output of the SQL statement im using in python it will work. I got the statement using the eclipse console window by displaying the variable the statement is stored in. This is the PG Console output:

metering=# insert into customers(customer_id,customer_name, inactive, datetime) values (101,'\021;\213D\351O\0339"(($v_\033\262'::bytea,'Y', CURRENT_TIMESTAMP);
INSERT 0 1
                      ^
metering=# select * from customers;
 customer_id |           customer_name            | inactive |  datetime  
-------------+------------------------------------+----------+------------
         101 | \x113b8b44e94f1b3922282824765f1bb2 | Y        | 2014-10-06
(1 row)

Database table definition and database format

ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;

customer_id bigint NOT NULL,
customer_name bytea NOT NULL,
inactive character varying(1) NOT NULL,
datetime date NOT NULL,
CONSTRAINT customers_pkey PRIMARY KEY (customer_id)

Errors returned by python

2014-10-16 12:19:23,077 ERROR Error invalid byte sequence for encoding "UTF8": 0xca 0x3c
2014-10-16 12:20:47,796 ERROR Error invalid byte sequence for encoding "UTF8": 0xca 0x3c

Code to insert encrypted customer name.

password = hashlib.sha256(secretmofokey).digest()  
IV = 16 * '\x00'
mode = AES.MODE_CBC
encryptor = AES.new(password, mode, IV=IV)

      for customer_id, customer_name, inactive in oracle_cursor:
         try:
               encrypted_customer_name = encryptor.encrypt(pad(customer_name))

               pg_delete.execute("""delete from customers where customer_id = %s """ % customer_id)

               customers_sql = ("""insert into customers(customer_id, 
                           customer_name , inactive, datetime) 
                           values (%s, '%s', '%s' , CURRENT_TIMESTAMP) """ % (customer_id, encrypted_customer_name, inactive))

               pg_insert.execute(customers_sql)
               postgres.commit()

         except psycopg2.DatabaseError, e:
            logging.error('Error %s' % e)    
            postgres.rollback()
            continue

... and what's the table definition? Please show \d customers. Also show the exact text of the error you get from the Python code. — Craig Ringer
– Craig Ringer, Commented Oct 7, 2014 at 3:16
@CraigRinger There I added the database format and table definition. I've tried with '%s' and without '%s' and its the same error. — Grant Zukel
– Grant Zukel, Commented Oct 16, 2014 at 18:27

Craig Ringer · Accepted Answer · 2014-10-16 23:33:36Z

4

Per the psycopg2 manual, you have to explicitly identify data you want inserted as bytea:

You're also incorrectly trying to quote your format specifiers. You do not need and should not include the single quotes around %s; psycopg2 will do the appropriate quoting.

So you want:

"""... values (%s, %s, %s , CURRENT_TIMESTAMP) """
 % (customer_id, psycopg2.Binary(encrypted_customer_name), inactive)

answered Oct 16, 2014 at 23:33

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

Collectives™ on Stack Overflow

Inserting Bytes to Postgres with python gives error, but with PG SQL console works

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related