psycopg2 equivalent of mysqldb.escape_string?

Question

I'm passing some values into a postgres character field using psycopg2 in Python. Some of the string values contain periods, slashes, quotes etc.

With MySQL I'd just escape the string with

MySQLdb.escape_string(my_string)

Is there an equivalent for psycopg2?

Answer 1

In the unlikely event that query parameters aren't sufficient and you need to escape strings yourself, you can use Postgres escaped string constants along with Python's repr (because Python's rules for escaping non-ascii and unicode characters are the same as Postgres's):

def postgres_escape_string(s):
   if not isinstance(s, basestring):
       raise TypeError("%r must be a str or unicode" %(s, ))
   escaped = repr(s)
   if isinstance(s, unicode):
       assert escaped[:1] == 'u'
       escaped = escaped[1:]
   if escaped[:1] == '"':
       escaped = escaped.replace("'", "\\'")
   elif escaped[:1] != "'":
       raise AssertionError("unexpected repr: %s", escaped)
   return "E'%s'" %(escaped[1:-1], )

Answer 2

Psycopg2 doesn't have such a method. It has an extension for adapting Python values to ISQLQuote objects, and these objects have a getquoted() method to return PostgreSQL-compatible values.

See this blog for an example of how to use it: Quoting bound values in SQL statements using psycopg2

Answer 3

Escaping is automatic, you just have to call:

cursor.execute("query with params %s %s", ("param1", "pa'ram2"))

(notice that the python % operator is not used) and the values will be correctly escaped.

You can escape manually a variable using extensions.adapt(var), but this would be error prone and not keep into account the connection encoding: it is not supposed to be used in regular client code.

Answer 4

Like piro said, escaping is automatic. But there's a method to also return the full sql escaped by psycopg2 using cursor.mogrify(sql, [params])