Damjan skrev:For example: SQL = 'INSERT into TEMP data = %s' c.execute(SQL, """ text containing ' and ` and all other stuff we might read from the network""")
Sure, but does this work if you need more than one placeholder?
Yup.
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)
cursor = connection.cursor() cursor.execute("select * from name_parts")
result = cursor.fetchall()kind = 'prefix', 'given', 'surname', 'suffix', 'patronym', 'toponym'
for row in result:
if 0 < row[2] <= 6:
cursor.execute("update names set " + kind[row[2] - 1] +
" = %s where name_id = %s",
(row[4], row[1]))
cursor.commit()
connection.close()
1) I would prefer "SELECT name_id, part, name FROM name_parts", rather than relying on * to return the field names in an expected order and size as your database evolves. I generally do SQL keywords in all-caps as documentation for those reading the code later.
2) I suspect that last line of the second execute might need to be:
[(row[4], row[1])])
I don't really remember; I'd just try both and see which works.3) It is not really clear to when you want to do the commits. I might be tempted to do the first query with "ORDER BY name_id" and do a commit after each distinct name_id is finished. This strategy would keep data for individuals coherent.
4) In fact, I'd leave the data in the database. Perhaps more like a set of queries like:
UPDATE names
SET names.prefix = name_parts.name
FROM name_parts
WHERE names.name_id = name_parts.name_id
AND name_parts.name_kind = 1You really need to think about commits when you adopt this strategy.
--Scott David Daniels [EMAIL PROTECTED] -- http://mail.python.org/mailman/listinfo/python-list
