How to read column in postgres DB of data type BYTEA and encoding of compressed json using PYTHON

Question

I have a column in postgres DB of data type bytea and encoding is of compressed_json. How can I read the values of this column and get a proper json back in Python?

Answer 1

But the method gzip.compress and decompress is not showing. Maybe its a version issue with my gzip module. Anyways, I was able to decompress using below way :

1. Convert and store the column as byte array :-

      payload = bytearray(row[0]) 
   

2. For decompressing, used below code snippet :

      fileobj = cStringIO.StringIO(payload)
      gzf = gzip.GzipFile('dummy-name', 'rb', 9, fileobj)
      decomprJson = gzf.read()
   

Answer 2

I don't have time at the moment to give a complete working example. Instead I will provide an outline:

1. Read column from database

   data = cur.fetchone()[0]

2. Use Python gzip module.

json_data = gzip.decompress(data)

Where json_data is a bytes object.

3. Use Python json module to read json_data.

UPDATE. Example:

create table js_compressed(js_fld bytea);

js_str = b'[{"one": 1, "two": 2}, {"three": 3, "four": 4}]'
js_cmp = gzip.compress(js_str)
con = psycopg2.connect("dbname=test user=aklaver host=localhost") 
cur = con.cursor()
cur.execute("insert into js_compressed(js_fld) values(%s)", (psycopg2.Binary(js_cmp,),))
cur.execute("select js_fld from js_compressed limit 1")
js_fld = cur.fetchone()[0]
dc_fld = gzip.decompress(js_fld).decode()
json.loads(dc_fld)
[{'one': 1, 'two': 2}, {'three': 3, 'four': 4}]