escape single and double quotes

[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()
-- 
Leif Biberg Kristensen
http://solumslekt.org/