escape single and double quotes

[Leif B. Kristensen]

I'm working with a Python program to insert / update textual data into a
PostgreSQL database. The text has single and double quotes in it, and I
wonder: What is the easiest way to escape quotes in Python, similar to
the Perlism "$str =~ s/(['"])/\\$1/g;"?

I tried the re.escape() method, but it escapes far too much, including
spaces and accented characters. I only want to escape single and double
quotes, everything else should be acceptable to the database.

 

[Jiri Barton]

Hey there,

str.replace('"', '\\"').replace("'", "\\'")

HTH, jbar

 

[Damjan]

I'm working with a Python program to insert / update textual data into a

PostgreSQL database. The text has single and double quotes in it, and I
wonder: What is the easiest way to escape quotes in Python, similar to
the Perlism "$str =~ s/(['"])/\\$1/g;"?

I tried the re.escape() method, but it escapes far too much, including
spaces and accented characters. I only want to escape single and double
quotes, everything else should be acceptable to the database.

You don't need to escape text when using the Python DB-API.
DB-API will do everything for you.
For example:
SQL = 'INSERT into TEMP data = %s'
c.execute(SQL, """ text containing ' and ` and all other stuff we might
read from the network""")

You see, the SQL string contains a %s placeholder, but insetad of executing
the simple string expansion SQL % """....""", I call the execute method
with the text as a second *parametar*. Everything else is magic .

 

[Leif B. Kristensen]

Damjan skrev:

You don't need to escape text when using the Python DB-API.
DB-API will do everything for you.
For example:
SQL = 'INSERT into TEMP data = %s'
c.execute(SQL, """ text containing ' and ` and all other stuff we
might
read from the network""")

You see, the SQL string contains a %s placeholder, but insetad of
executing the simple string expansion SQL % """....""", I call the
execute method with the text as a second *parametar*. Everything else
is magic .

Sure, but does this work if you need more than one placeholder? FWIW,
here's the whole script. It will fetch data from the table name_parts
and pump them into the "denormalized" table names ( a real SQL guru
would probably do the same thing with one single monster query):

import psycopg
from re import escape

connection = psycopg.connect("dbname=slekta", serialize=0)
sql = connection.cursor()

sql.execute("select * from name_parts")
result = sql.fetchall()
for row in result:
if row[2] == 1: # name part = 'prefix'
query = ("update names set prefix='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 2: # name part = 'given'
query = ("update names set given='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 3: # name part = 'surname'
query = ("update names set surname='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 4: # name part = 'suffix'
query = ("update names set suffix='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 5: # name part = 'patronym'
query = ("update names set patronym='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 6: # name part = 'toponym'
query = ("update names set toponym='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
sql.execute(query)
sql.commit()
connection.close()

 

[Scott David Daniels]

Damjan skrev:

Sure, but does this work if you need more than one placeholder?
Yup.


FWIW,
here's the whole script. It will fetch data from the table name_parts
and pump them into the "denormalized" table names ( a real SQL guru
would probably do the same thing with one single monster query):

import psycopg
from re import escape

connection = psycopg.connect("dbname=slekta", serialize=0)
cursor = connection.cursor()

cursor.execute("select * from name_parts")
result = cursor.fetchall()

kind = 'prefix', 'given', 'surname', 'suffix', 'patronym', 'toponym'

for row in result:
if 0 < row[2] <= 6:
cursor.execute("update names set " + kind[row[2] - 1] +
" = %s where name_id = %s",
(row[4], row[1]))
cursor.commit()
connection.close()


1) I would prefer "SELECT name_id, part, name FROM name_parts", rather
than relying on * to return the field names in an expected order
and size as your database evolves. I generally do SQL keywords in
all-caps as documentation for those reading the code later.

2) I suspect that last line of the second execute might need to be:
[(row[4], row[1])])
I don't really remember; I'd just try both and see which works.

3) It is not really clear to when you want to do the commits.
I might be tempted to do the first query with "ORDER BY name_id"
and do a commit after each distinct name_id is finished. This
strategy would keep data for individuals coherent.

4) In fact, I'd leave the data in the database. Perhaps more like a
set of queries like:

UPDATE names
SET names.prefix = name_parts.name
FROM name_parts
WHERE names.name_id = name_parts.name_id
AND name_parts.name_kind = 1

You really need to think about commits when you adopt this strategy.

--Scott David Daniels
(e-mail address removed)

 

[Marc 'BlackJack' Rintsch]

Damjan skrev:


Sure, but does this work if you need more than one placeholder?

Yes it works with more than one placeholder.

FWIW,
here's the whole script. It will fetch data from the table name_parts
and pump them into the "denormalized" table names ( a real SQL guru
would probably do the same thing with one single monster query):

import psycopg
from re import escape

connection = psycopg.connect("dbname=slekta", serialize=0)
sql = connection.cursor()

sql.execute("select * from name_parts")
result = sql.fetchall()
for row in result:
if row[2] == 1: # name part = 'prefix'
query = ("update names set prefix='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 2: # name part = 'given'
query = ("update names set given='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 3: # name part = 'surname'
query = ("update names set surname='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 4: # name part = 'suffix'
query = ("update names set suffix='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 5: # name part = 'patronym'
query = ("update names set patronym='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 6: # name part = 'toponym'
query = ("update names set toponym='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
sql.execute(query)
sql.commit()
connection.close()

A lot of redundant code. Try something like the following instead of the
``elif`` sequence::

name_part = ['prefix', 'given', 'surname', 'suffix', 'patronym', 'toponym']
for row in result:
query = 'update names set %s=%%s where name_id=%%s' % name_part[row[2]-1]
sql.execute(query, (row[4], row[1]))
sql.commit()

Ciao,
Marc 'BlackJack' Rintsch

 

[Leif B. Kristensen]

First, thanks to all who have replied. I learned a lot more than I had
expected

This is a small part of a major project; converting my genealogy
database from a commercial FoxPro application to my homegrown Python /
PostgreSQL app. I'm still in a phase where I'm experimenting with
different models, hence the need for shuffling data between two tables.

Now, the script in its refined form looks like this:

#! /usr/bin/env python
# name_convert.py - populate "names" with values from "name_parts"

import psycopg

name_part = ('prefix','given','surname','suffix','patronym','toponym')
connection = psycopg.connect("dbname=slekta", serialize=0)
sql = connection.cursor()
sql.execute("select name_id, name_part_type, name_part from name_parts")
result = sql.fetchall()
for row in result:
query = "update names set %s=%%s where name_id=%%s" % \
name_part[row[1]-1]
sql.execute(query, (row[2], row[0]))
sql.commit()
connection.close()

 

[Kent Johnson]

I'm working with a Python program to insert / update textual data into a
PostgreSQL database. The text has single and double quotes in it, and I
wonder: What is the easiest way to escape quotes in Python, similar to
the Perlism "$str =~ s/(['"])/\\$1/g;"?

Just for the record (even though it's not the right solution to your problem), the Python equivalent is
re.sub('''(['"])''', r'\\\1', s)

Kent