escape single and double quotes

[Scott David Daniels]

Leif B. Kristensen wrote:
> 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 = 1

     You really need to think about commits when you adopt this strategy.

--Scott David Daniels
Scott.Daniels at Acm.Org