CODEWITHSUNDEEP
pythonsql
Houman
Houman

Reputation: 66320

How to escape a ' within a string?

I have a little script that creates a certain INSERT SQL statement for me.

For postgresql I need to wrap the values to be inserted within two single quotes.

Unfortunately some of the value strings to be inserted also contain a single quote, and I need to escape them automatically.

for line in f:
    out.write('(\'' + line[:2] + '\', \'' + line[3:-1] + '\'),\n')

How can I make sure that any single quote (e.g. ' ) inside line[3:-1] is automatically escaped?

Thanks,

UPDATE:

e.g. the line 

CI|Cote D'ivoire

fails due '

Update 2:

I can't use double quotes in values, e.g.

INSERT INTO "App_country" (country_code, country_name) VALUES ("AF", "Afghanistan")

I get the error message: ERROR: column "AF" does not exist

This however works fine:

INSERT INTO "App_country" (country_code, country_name) VALUES ('AF', 'Afghanistan')

Upvotes: 3

Views: 9359

Answers (5)

Jon Clements
Jon Clements

Reputation: 142106

Never use a generated, rolled-your-own escaping for DML. Use the appropriate DBAPI as Keith has mentioned. Work would have gone into that to make sure escapes from various sources and type conversion can occur almost transparently. If you're using DDL such as a CREATE TABLE whatever (...) - you can be more slight slack-handed if you trust your own datasource.

using data shown in example:

import sqlite3

text = "CI|Cote D'ivoire" # had to been escaped as it's a string literal, but from another data source - possibly not...

code, name = text.split('|', 1)

db = sqlite3.connect(':memory:')
db.execute('create table something(code, name)')
db.execute('insert into something(code, name) values(?, ?)', (code, name))

for row in db.execute('select * from something'):
    print row
# (u'CI', u"Cote D'ivoire")

Upvotes: 2

Ned Batchelder
Ned Batchelder

Reputation: 375484

The SQL standard way to escape a quote is to double it:

'This won''t be a problem.'

So replace every quote with two quotes (and use double quotes in Python to stay sane):

out.write("('" + line[:2] + "', '" + line[3:-1].replace("'", "''") + "'),\n")

Upvotes: 2

Keith
Keith

Reputation: 43024

As described in the PEP-249, the DBPI is a generic interface to various databases. Different implementations exist for different databases. For postgres there is psycopg. from the docs:

cur.execute(
...     """INSERT INTO some_table (an_int, a_date, a_string)
...         VALUES (%s, %s, %s);""",
...     (10, datetime.date(2005, 11, 18), "O'Reilly"))

You simple pass your parameters in a tuple. The underlying library escapes it for you. This is much safer and easier than trying to roll your own.

Upvotes: 3

Levon
Levon

Reputation: 143037

Not sure if there are some SQL related limitations, but you could always use double quotes to surround your string that contains the single quote.

Eg. 

print "That's all Folks!"

or single quotes to surround double quotes:

print 'The name of the file is "rosebud".'

Upvotes: 0

applepie
applepie

Reputation: 504

For a complete solution toadd escape characters to a string, use:

re.escape(string)
>>> re.escape('\ a.*$')
'\\\\\\ a\\.\\*\\$'

for more, see: http://docs.python.org/library/re.html

Upvotes: 1

Related Questions