escape single and double quotes

Discussion in 'Python' started by Leif B. Kristensen, Mar 24, 2005.

  1. I'm working with a Python program to insert / update textual data into a
    PostgreSQL database. The text has single and double quotes in it, and I
    wonder: What is the easiest way to escape quotes in Python, similar to
    the Perlism "$str =~ s/(['"])/\\$1/g;"?

    I tried the re.escape() method, but it escapes far too much, including
    spaces and accented characters. I only want to escape single and double
    quotes, everything else should be acceptable to the database.
     
    Leif B. Kristensen, Mar 24, 2005
    #1
    1. Advertisements

  2. Leif B. Kristensen

    Jiri Barton Guest

    Hey there,

    str.replace('"', '\\"').replace("'", "\\'")

    HTH, jbar
     
    Jiri Barton, Mar 24, 2005
    #2
    1. Advertisements

  3. Leif B. Kristensen

    Damjan Guest

    I'm working with a Python program to insert / update textual data into a
    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 :).
     
    Damjan, Mar 24, 2005
    #3
  4. Damjan skrev:
    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 B. Kristensen, Mar 24, 2005
    #4
  5. 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 David Daniels, Mar 24, 2005
    #5
  6. Yes it works with more than one placeholder.
    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
     
    Marc 'BlackJack' Rintsch, Mar 24, 2005
    #6
  7. First, thanks to all who have replied. I learned a lot more than I had
    expected :)

    This is a small part of a major project; converting my genealogy
    database from a commercial FoxPro application to my homegrown Python /
    PostgreSQL app. I'm still in a phase where I'm experimenting with
    different models, hence the need for shuffling data between two tables.

    Now, the script in its refined form looks like this:

    #! /usr/bin/env python
    # name_convert.py - populate "names" with values from "name_parts"

    import psycopg

    name_part = ('prefix','given','surname','suffix','patronym','toponym')
    connection = psycopg.connect("dbname=slekta", serialize=0)
    sql = connection.cursor()
    sql.execute("select name_id, name_part_type, name_part from name_parts")
    result = sql.fetchall()
    for row in result:
    query = "update names set %s=%%s where name_id=%%s" % \
    name_part[row[1]-1]
    sql.execute(query, (row[2], row[0]))
    sql.commit()
    connection.close()
     
    Leif B. Kristensen, Mar 24, 2005
    #7
  8. Leif B. Kristensen

    Kent Johnson Guest

    Just for the record (even though it's not the right solution to your problem), the Python equivalent is
    re.sub('''(['"])''', r'\\\1', s)

    Kent
     
    Kent Johnson, Mar 24, 2005
    #8
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.