1

I'm trying to write binary data into bytea column in PostgreSQL table. My data contains null characters and I get following error.

ValueError: A string literal cannot contain NUL (0x00) characters.

This is my code.

import numpy as np
fft = [0.0, 0.2, 0.0215]
[float(i) for i in fft]
blob = struct.pack('%sd' % np.size(fft), *fft)
cur.execute("""INSERT INTO fft (id, v) VALUES(%s, %s)""", ("widget_fft", blob))

id is of type text and v is of type bytea.

I have also tried using psycopg.Binary(blob), but it inserts backslashes which I don't want.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Salman Azmat
  • 298
  • 6
  • 18

1 Answers1

1

In Python 2.7, you have to use the psycopg2.Binary wrapper:

cur.execute("""INSERT INTO fft (id, v) VALUES(%s, %s)""",
            ("widget_fft", psycopg2.Binary(blob)))

See the documentation for details.

This is not required in Python 3.

I tried it and didn't see any backslashes. Can you show what exactly is in blob?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    well, it turns out there's no issue with the psycopg2. I was building the blob using struct.pack, which gives output as a string. When I changed that string to a bytearray, the problem went away. – Salman Azmat Feb 26 '19 at 07:21