CODEWITHSUNDEEP
jsonpython-3.xpostgresql
Raqib
Raqib

Reputation: 1442

Writing postgres bytea to json

Given:

I have a column in postgres of type bytea. This data was originally a string that was input by a user, and after encryption (the output being bytea) was saved in the db.

Problem:

I want to read the stuff column (bytea) and save it in json form in a file so that I can retrieve it later. I want the stuff data to be saved in bytea format in json as I do not have the decryption key to decrypt the data to a string.

Are there better ways of doing this? Is my approach too naive?

What I have tried:

import json

import psycopg2.extras


if __name__ == '__main__':
    conn = None
    try:
        conn_string = "host='localhost' dbname='user' user='postgres'"
        conn = psycopg2.connect(conn_string)

        custom_cursor = conn.cursor('data_cursor', cursor_factory=psycopg2.extras.DictCursor)
        custom_cursor.itersize = 10000

        custom_cursor.execute("SELECT * FROM user")

        page = []
        page_number = 1

        for record in custom_cursor:
            data = {
                "user": record[record._index['user']],
                "stuff": (record[record._index['stuff']].tobytes())
            })
            page.append(data)
            if len(page) == 10000:
                file_name = "./data/page-{pn}.json".format(pn=page_number)
                with open(file_name, "w") as jfile:
                    json.dump(page, jfile)
                page.clear()
                page_number += 1
    except (Exception, psycopg2.DatabaseError) as e:
        print(e)
    finally:
        if conn is not None:
            conn.commit()
            conn.close()

Error:

Object of type bytes is not JSON serializable

Upvotes: 0

Views: 2779

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248195

If you really need to store binary data in a JSON, you will have to encode it as string, for example with

encode(binary_column, 'base64')

Upvotes: 2

Related Questions