I'm trying to insert a file compressed with lzma into a postgresql table with a bytea field. The problem is that it's unable to format the string and this error occurs:
TypeError: not all arguments converted during string formatting
To insert the data in the database, i'm using psycopg2:
CUR.execute(f"""INSERT INTO table (id, date, bytes) """ + """VALUES ("{file_name}", CURRENT_DATE, %s""", (str(compress(file.read()))[2:-1]))
Any ideas?
Hard to tell, without a minimal reproducible sample, but maybe this way:
CUR.execute(f"""INSERT INTO table (id, date, bytes) VALUES (%s, CURRENT_DATE, %s""", (file_name, compress(file.read()))[2:-1])
Note that all variables are in %s, that what psycopg2 needs. The {file_name} notation is for the format function, that is not used here. You do not need quotes, psycopg2 handles that for you. bytea Postgres type expects a buffer python type, so I removed the str.
Your parameter needs to be a tuple. See here: https://www.psycopg.org/docs/usage.html#query-parameters
The second argument must always be a sequence, even if it contains a single variable:
>>> cur.execute("INSERT INTO foo VALUES (%s)", "bar") # WRONG
>>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar")) # WRONG
>>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # correct
>>> cur.execute("INSERT INTO foo VALUES (%s)", ["bar"]) # correct
BTW, why are trying to convert binary data into a string in Python? I'm referring to "str(...)[2:-1]". I would think you need to pass a bytes object there, e.g.:
binarystr = b"bytes here"
cur.execute("INSERT INTO foo VALUES (%s)", [binarystr])
