32

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?

AP257
  • 89,519
  • 86
  • 202
  • 261

5 Answers5

42

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.

piro
  • 13,378
  • 5
  • 34
  • 38
  • 2
    In the same vein as `adapt()`, you could use a particular adaptation protocol object explicitly; e.g. `QuotedString(r"O'Reilly").getquoted()` see [the docs](http://initd.org/psycopg/docs/extensions.html#sql-adaptation-protocol-objects) – rakslice Apr 20 '11 at 21:37
  • Escaping is not automatic for my json column... Is there a way to do that? – Alex May 01 '18 at 20:21
  • @Alex googling for "psycopg json", which would bring you ti the [psycopg json adaptation](http://initd.org/psycopg/docs/extras.html#json-adaptation) page. – piro May 01 '18 at 21:50
  • Note: Don't put apostrophes around %s, i.e. it's not '%s' but just %s – dasWesen Jan 05 '22 at 13:11
  • in addition to query params look into the sql composition from psycopg2.sql module. e.g. you can properly pass the tables names with the help of `psycopg2.sql.Identifier` inside the call to `psycopg2.sql.SQL()` – dr_dronych Mar 13 '23 at 11:17
12

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])

Community
  • 1
  • 1
notbad.jpeg
  • 3,308
  • 1
  • 32
  • 37
4

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], )
David Wolever
  • 148,955
  • 89
  • 346
  • 502
0

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

Update 2019-03-03: changed the link to archive.org, because after nine years, the original is no longer available.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 5
    Notice that the guy there is wrong: what he wants to do can be obtained using the `mogrify()` method (http://initd.org/psycopg/docs/cursor.html#cursor.mogrify) – piro Oct 06 '10 at 10:15
  • Thanks @piro, that looks easier, though it is probably intended for using on full SQL expressions or queries, not just individual values to quote. – Bill Karwin Oct 06 '10 at 15:42
  • Notice also that mogrify, as also execute, does not handle escaping table names, only parameters. – a1an Jul 29 '15 at 08:16
  • @a1an, good point. I have never encountered a framework or API in any language that escaped table names. Have you? – Bill Karwin Jul 29 '15 at 13:20
  • Unfortunately not; the best solution I saw was to use a parameterized select query to the database catalog to get the "clean" table name. Unfortunately that does not work with CREATE statements. The latter should almost never be the case but with SOA a service might as well accept the name of a table to be created as a parameter, who knows? – a1an Jul 29 '15 at 14:14
  • getquoted() isn't working for Unicode Emoji strings. UnicodeEncodeError: 'latin-1' codec can't encode character '\U0001f4d8' in position 0: ordinal not in range(256) – sudo Jun 07 '16 at 23:28
  • the second link is broken – Alessandro Solbiati Mar 03 '19 at 14:44
  • @AlessandroSolbiati, you're downvoting me because a link I posted **nine years ago** became stale? That's pretty unforgiving. – Bill Karwin Mar 03 '19 at 19:42
  • sorry for being unforgiving, I just was trying to make the answer below from @piro to get on top since it is the one that was working for me (and I was almost giving up before scrolling down) You are a good man to update the link ;) – Alessandro Solbiati Mar 03 '19 at 21:20
  • @AlessandroSolbiati, It's appropriate to vote up an answer you feel is best, but downvoting other *correct* answers as a way of giving yourself a second virtual upvote for your preferred answer is not what the feature is intended for. Downvoting answers should be reserved for cases where the answer is actually incorrect, or shows sloppiness or no effort. It's also polite to leave a comment and give the person a chance to improve their answer. Read https://stackoverflow.com/help/privileges/vote-down – Bill Karwin Mar 03 '19 at 21:27
  • Besides, the answer from piro already had 25+ upvotes, so it wasn't nearly in danger of not being on top. – Bill Karwin Mar 03 '19 at 21:29
0

psycopg2 added a method in version 2.7 it seems: http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.quote_ident

from psycopg2.extensions import quote_ident

with psycopg2.connect(<db config>) as conn:
    with conn.cursor() as curs:
        ident = quote_ident('foo', curs)

If you get an error like: TypeError: argument 2 must be a connection or a cursor, try either:

ident = quote_ident('foo', curs.cursor)

# or

ident = quote_ident('food', curs.__wrapper__)
getup8
  • 6,949
  • 1
  • 27
  • 31