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/