Fixing a type-error in Python's Pg

Question

Thank you for bobince in solving the first bugs!

How can you use *pg.escape_bytea* or *pg.escape_string* in the following?

#1 With both pg.escape_string and pg.escape_bytea

    con1.query(
            "INSERT INTO files (file, file_name) VALUES ('%s', '%s')" %
            (pg.escape_bytea(pg.espace_string(f.read())), pg.espace_string(pg.escape_bytea(f.name)))

I get the error

AttributeError: 'module' object has no attribute 'espace_string'

I tested the two escapes in the reverse order unsuccessfully too.

#2 Without pg.escape_string()

 con1.query(
                "INSERT INTO files (file, file_name) VALUES ('%s', '%s')" %
                (pg.escape_bytea(f.read()), pg.escape_bytea(f.name))
        )

I get

WARNING:  nonstandard use of \\ in a string literal
LINE 1: INSERT INTO files (file, file_name) VALUES ('%PDF-1.4\\012%\...
                                                    ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
------------------------
-- Putting pdf files in

I get the following error

# 3 With only pg.escape_string

------------------------
-- Putting pdf files in
------------------------
Traceback (most recent call last):
  File "<stdin>", line 30, in <module>
  File "<stdin>", line 27, in put_pdf_files_in
  File "/usr/lib/python2.6/dist-packages/pg.py", line 313, in query
    return self.db.query(qstr)
pg.ProgrammingError: ERROR:  invalid byte sequence for encoding "UTF8": 0xc7ec
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

Answer 1

INSERT INTO files('binf','file_name') VALUES(file,file_name)

You've got the (...) sections the wrong way round, you're trying to insert the columns (file, filename) into the string literals ('binf', 'file_name'). You're also not actually inserting the contents of the variables binf and file_name into the query.

The pg module's query call does not support parameterisation. You would have to make the string yourself:

con1.query(
    "INSERT INTO files (file, file_name) VALUES ('%s', '%s')" %
    (pg.escape_string(f.read()), pg.escape_string(f.name))
)

This is assuming f is a file object; I'm not sure where file is coming from in the code above or what .read(binf) is supposed to mean. If you are using a bytea column to hold your file data you must use escape_bytea instead of escape_string.

Better than creating your own queries is letting pg do it for you with the insert method:

con1.insert('files', file= f.read(), file_name= f.name)

Alternatively, consider using the pgdb interface or one of the other DB-API-compliant interfaces that is not PostgreSQL-specific, if you ever want to consider running your app on a different database. DB-API gives you parameterisation in the execute method:

cursor.execute(
    'INSERT INTO files (file, file_name) VALUES (%(content)s, %(name)s)', 
    {'content': f.read(), 'name': f.name }
)