How to pass a bytestring to PyGreSQL?
<p>I've got a PostgreSQL table with a column of type <code>bytea</code>. Porting that table from SQLite, I ran into an issue - I couldn't figure out how to pass raw binary data to an SQL query. The framework I use is PyGreSQL. I want to stick to the DB-API 2.0 interface to avoid a lot of conversion.<br>
That interface, unlike the classic one (dollar-sign parameters) and SQLite (question-mark parameters), requires to specify the type (%-formatting like the old Python's).<br>
The data I want to pass is a PNG file, binary-read using the <code>'rb'</code> flag in the <code>open()</code> method.<br>
The query code looks like this:</p>

<pre><code>db = pgdb.connect(args)
c = db.cursor()
c.execute('INSERT INTO tbl VALUES (%b)', (b'test_bytes',))
</code></pre>

<p>This gives an error <code>unsupported format character 'b' (0x62) at index 54</code> and doesn't allow the formatting to happen. What could be done to solve this issue?</p>

<p>Not really a solution, but a good-enough workaround. I decided to use <code>psycopg2</code> instead of <code>PyGreSQL</code> as it is more supported and common, so it's easier to find info about any common problems.<br>
There, the solution would be to use <code>%s</code> for every type, which I find much more Pythonic (no need to think about types in a dynamically-typed language)</p>

<p>So, my query would look like this:</p>

<pre><code>c.execute('INSERT INTO tbl VALUES (%s)', (b'test_bytes',))
</code></pre>

python postgresql sqlite formatting