[Tutor] mysql formatting

[Danny Yoo]

On Tue, 2 Nov 2004, Marilyn Davis wrote:

> Can you folks please help me some more?
>
> I'm having a trouble, now that I'm really trying to implement what I
> learned yesterday.
>

[some text cut]

> And from my log:
>
>    About to execute:update %s set status = "%s" where in_id = %s and
> out_address like "%%%s%%"<-->('doorman', 'MOVED', '60',
> 'wrgfgtfdpmrwe at hewpwbpsk.rashpie.com')




Hi Marilyn,


Unfortunately, I don't think the table name can be plugged into there
using the prepared statement syntax.  I think only column values can be
"plugged into".


For example, I have a database with a 'pipeline' table, but doing
something like:

###
>>> conn = MySQLdb.connect(db='testdb')
>>> cursor = conn.cursor()
>>> cursor.execute('select * from %s', 'pipeline')
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in
execute
    return self._execute(query, args)
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in
_execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33,
in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL
syntax.  Check the manual that corresponds to your MySQL server version
for the right syntax to use near ''pipeline'' at line 1")
###


That brings up an ugly error, even though this will work:

###
>>> cursor.execute('select * from pipeline')
1L
###


So I'm pretty sure that your code needs some adjustment so that the table
name itself is not a parameter.




> Why am I getting those extra \' thingies?

Those are there to make it clear that the quote isn't closing off the
string, but is a literal quote in the content of the string.



A simpler example might help:

###
>>> weird_string = "This is 'a string \"with literal quotes"
>>> weird_string
'This is \'a string "with literal quotes'
###

When we ask Python for the representation of 'weird_string', it
responds with:

    'This is \'a string "with literal quotes'

This tells us that it's a string, by the surrounding single quotes.  And
this string has the following character content:

    This is \'a string "with literal quotes

The leading backspace in there tells us that the inner single quote
doesn't close off the string, but is part of its content.  The double
quote here doesn't have to be escaped, since the string as a whole was
displayed with single quotes.



But imagine if Python had responded to our request without the backslash:

###
### Imaginary Python: Python does NOT respond this way.
###
>>> weird_string = "This is 'a string \"with literal quotes"
>>> weird_string
'This is 'a string "with literal quotes'
###

Then this would be harder to us to understand what's happening, since it
looks like the result is some kind of unbalanced value.  We have

    'This is'

as some string.  But then, there's a freestanding

    a string

thing there, followed by some unbalanced string value

    "with literal quotes'

which is wrong, since strings have to end with the same quote character
that they begin with.


So this is potentially very confusing.  So although the backslashes are
confusing at first, once you understand why they show up, it's not so bad.
The backslashes are there to save us from being completely confused.
*grin*


Hope this helps!