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

Answer 1

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)