[Tutor] escape-quoting strings

[Marilyn Davis]

On Sun, 31 Oct 2004, Lloyd Kvam wrote:

> On Sun, 2004-10-31 at 18:06, Marilyn Davis wrote:
> > I got it!!
> > 
> > This works:
> > 
> > ad0 = address.replace('\\', '\\\\')
> > ad0 = ad0.replace('\"','\\"')
> > db_style0 = '''%%%s%%''' % ad0
> > print "0:", db_style0
> > 
> > On Sun, 31 Oct 2004, Rick Pasotto wrote:
> > 
> > > On Sun, Oct 31, 2004 at 01:53:42PM -0800, Marilyn Davis wrote:
> > > > Hi Python Tutors,
> > > > 
> > > > I'm having a devil of a time quoting strings properly for MySql.
> > > 
> > > You example did not include any MySql statements. How are you sending
> > > the string to MySql?
> > 
> > I didn't want to complicate my question with MySql stuff.
> > 
> > Here is a successful call that depended on the quoting scheme above:
> > 
> > insert ignore into doorman (in_id,  msg_id, out_address, status, start) values         (60, "1COOZD-0003KN-2z", "\"Toys \\\"R\\\" Us Gift Card Giveaway\" <fqgdmvfjfyvjd at dlawczhyh.montebic.com>", "NO_MESSAGE", "0")
> 
> Your real MySQL code was omitted.  I'll assume a cursor named curs.
> 
> curs.execute(sql_cmd, parameters) 

Oh, I have a method wrapped around it to throw meaningful exceptions,
and then that's in a class that maintains the connection.

> 
> is the general format for executing an SQL statement.  With MySQL the
> parameter positions are marked with %s - so it looks like string
> interpolation.  Parameters is a tuple of variables that need to be
> inserted into the sql_cmd at the positions marked by %s.  DO NOT USE
> PYTHON string interpolation.  Let the MySQLdb module do it.

Wow.  I had no idea that this was available.  I did it all wrong.  I
just did a little test and it is totally cool.

> 
> For your situation:
> sql_cmd = '''insert ignore into doorman (in_id,  msg_id, out_address,
> status, start) values (60, "1COOZD-0003KN-2z", %s, "NO_MESSAGE", "0")'''
> 
> parameters = (address, )
> 
> There is no need to change the incoming string value to be able to feed
> it into the database.
> 
> The Python DBI documentation does not discuss this clearly enough.  I
> made the same mistake in some of my first DBI programs.

I don't see it in the documentation for MySQLdb at all!  Should I be
looking somewhere else?

How did you learn this?

I wish I knew this a few months ago.  Where have you been?? ;^)

Marilyn

> 
> > 
> > 
> > 
> > Thank you.
> > 
> > Marilyn
> > 
> > 
> > _______________________________________________
> > Tutor maillist  -  Tutor at python.org
> > http://mail.python.org/mailman/listinfo/tutor
> 

--