Re: escape single and double quotes
Thanks Gabriel. You are correct - this is even documented in the MySQLdb User's Guide (http://mysql-python.sourceforge.net/MySQLdb.html), but it's certainly not intuitive, given how python string interpolation normally works. Gabriel Genellina [EMAIL PROTECTED] wrote: En Tue, 23 Oct 2007 20:50:55 -0300, Michael Pelz Sherman escribió: Leif B. Kristensen wrote: 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? Yes it works with more than one placeholder. Yes, BUT: I have found that all of the placeholders must be STRINGS! If I try to use other data types (%d, %f, etc.), I get an error: File /usr/lib/python2.5/site-packages/MySQLdb/cursors.py, line 149, in execute query = query % db.literal(args) TypeError: float argument required It's not a huge problem to convert my non-string args, but it seems like this should be fixed if it's a bug, no? No. The *MARK* is always %s - but the data may be any type (suitable for the database column, of course). The only purpose of %s is to say insert parameter here. Other adapters use a question mark ? as a parameter placeholder, a lot less confusing, as it does not look like string interpolation. -- Gabriel Genellina -- http://mail.python.org/mailman/listinfo/python-list -- http://mail.python.org/mailman/listinfo/python-list
Re: escape single and double quotes
Leif B. Kristensen wrote: 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. Yes, BUT: I have found that all of the placeholders must be STRINGS! If I try to use other data types (%d, %f, etc.), I get an error: File /usr/lib/python2.5/site-packages/MySQLdb/cursors.py, line 149, in execute query = query % db.literal(args) TypeError: float argument required It's not a huge problem to convert my non-string args, but it seems like this should be fixed, no? - Michael -- http://mail.python.org/mailman/listinfo/python-list
Re: escape single and double quotes
Leif B. Kristensen wrote: 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. Yes, BUT: I have found that all of the placeholders must be STRINGS! If I try to use other data types (%d, %f, etc.), I get an error: File /usr/lib/python2.5/site-packages/MySQLdb/cursors.py, line 149, in execute query = query % db.literal(args) TypeError: float argument required It's not a huge problem to convert my non-string args, but it seems like this should be fixed if it's a bug, no? - Michael -- http://mail.python.org/mailman/listinfo/python-list
Re: escape single and double quotes
En Tue, 23 Oct 2007 20:50:55 -0300, Michael Pelz Sherman [EMAIL PROTECTED] escribió: Leif B. Kristensen wrote: 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? Yes it works with more than one placeholder. Yes, BUT: I have found that all of the placeholders must be STRINGS! If I try to use other data types (%d, %f, etc.), I get an error: File /usr/lib/python2.5/site-packages/MySQLdb/cursors.py, line 149, in execute query = query % db.literal(args) TypeError: float argument required It's not a huge problem to convert my non-string args, but it seems like this should be fixed if it's a bug, no? No. The *MARK* is always %s - but the data may be any type (suitable for the database column, of course). The only purpose of %s is to say insert parameter here. Other adapters use a question mark ? as a parameter placeholder, a lot less confusing, as it does not look like string interpolation. -- Gabriel Genellina -- http://mail.python.org/mailman/listinfo/python-list
escape single and double quotes
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 Biberg Kristensen http://solumslekt.org/ -- http://mail.python.org/mailman/listinfo/python-list
Re: escape single and double quotes
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. 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: escape single and double quotes
Hey there,
str.replace('', '\\').replace(', \\')
HTH, jbar
--
http://mail.python.org/mailman/listinfo/python-list
Re: escape single and double quotes
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/ -- http://mail.python.org/mailman/listinfo/python-list
Re: escape single and double quotes
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 [EMAIL PROTECTED] -- http://mail.python.org/mailman/listinfo/python-list
Re: escape single and double quotes
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
Re: escape single and double quotes
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 Biberg Kristensen
http://solumslekt.org/
--
http://mail.python.org/mailman/listinfo/python-list
