In <[EMAIL PROTECTED]>, 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
--
http://mail.python.org/mailman/listinfo/python-list
