psycopg - INSERT gzipped data into bytea column

Question

I'm trying to gzip a string and then write it into a bytea column using psycopg2.

table:

CREATE TABLE test
(
  data bytea
)

insert:

import psycopg2

data = "some string".encode("zlib") # 'x\x9c+\xce\xcfMU(.)\xca\xccK\x07\x00\x1ak\x04l'

conn = psycopg2.connect("my parameters")
cur = conn.cursor()
cur.execute("INSERT INTO public.test VALUES (%s)", (data,))

    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
    psycopg2.DataError: invalid byte sequence for encoding "UTF8": 0x9c

is this an issue with the library? Do I need to change the encoding somehow? any help is appreciated.

Answer 1

If you want to insert binary data into the database, you will need to use the psycopg2.Binary() wrapper. Using a string like you've done will cause the data to be treated as text, which will either be rejected due to encoding issues, or accepted but mangled when you try to read it again.

Try replacing the last execute call with:

cur.execute("INSERT INTO public.test VALUES (%s)", (psycopg2.Binary(data),))