escape single and double quotes

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