escape single and double quotes

Marc 'BlackJack' Rintsch bj_666 at gmx.net
Thu Mar 24 19:25:20 CET 2005


In <GqB0e.3365$ai7.77869 at news2.e.nsc.no>, Leif B. Kristensen wrote:

> 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?

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



More information about the Python-list mailing list