Importing bytea field into PostgreSQL database via psycopg2

Question

I have a list of values as such:

row = ['0x14', '0xb6', '0xa1', '0x0', '0xa1', '0x0']

I would like to insert these into a bytea field in my PostgreSQL database, using psycopg2, but I am unfamiliar with byte strings in python.

What is the best way to achieve this?

John · Accepted Answer · 2010-03-03 15:57:04Z

2

I am not sure if this is the correct way, but the following appears to work:

    row = ['0x14', '0xb6', '0xa1', '0x0', '0xa1', '0x0']
    as_hex = ''.join(byte[2:].zfill(2) for byte in row)
  # as_hex = '14b6a100a100'
    bytes = buffer(as_hex.decode('hex'))

    cur.execute("INSERT INTO mylog (binaryfield) VALUES (%(bytes)s)", 
                {'bytes': bytes})

Just a side note, when fetching it back out of the database psycopg2 provides it as a buffer, the first 4 bytes of which are the total length, so get the original data as:

    cur.execute("SELECT binaryfield FROM mylog")
    res = cur.fetchone()
    my_data = str(res[4:]).encode('hex')

The string can then be split into pairs and cast to integers.

edited Mar 3, 2010 at 15:57

answered Mar 3, 2010 at 14:51

John

2672 gold badges4 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

Importing bytea field into PostgreSQL database via psycopg2

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