Another MySQL Problem [Python]

Prev: Instance method decorator garbage collection problem
Next: Parsing email

From: Stephen Hansen on 23 Jun 2010 10:55

On 6/23/10 6:45 AM, Victor Subervi wrote:
> Hi;
> I have this line:
>
> cursor.execute('select clientEmail from clients where client=%s',
> (string.replace(client, '_', ' ')))
> clientEmail = cursor.fetchone()[0]
> cursor.execute('select * from %s' % (client))
>
> client = "Lincoln_Properties"
> With the replacement, the interpreter complains that mydatabase.Lincoln
> doesn't exist. Therefore, the first line of code isn't putting the %s
> replacement in quotes, as I was told by you all it would. So I add
> quotes to it and the interpreter complains on the second line of code
> that it's unsubscriptable (because it's None). What gives?

Its very early, so excuse me if I fall asleep in the middle of the response.

First: Don't do 'string.replace(your_string, x, y)' -- that's back in
the very, very old days before strings themselves had all the nice methods.

Do, 'client.replace("_", " ")' instead.

Second, you're forgetting a cardinal rule. Always, always, always,
include the actual tracebacks when reporting errors. Don't summarize them.

Third, I *think* the problem is-- though I may be wrong here, because
again, just woke up-- that you're not passing the options as a tuple.

Consider: ("hello") is not a tuple with one item. This is a slight
'wart' with Python syntax. Parens do not make tuples: *commas* do.
Therefore, every tuple *must* have a comma. To make a one-item tuple,
you must do ("hello", )

--

Stephen Hansen
... Also: Ixokai
... Mail: me+list/python (AT) ixokai (DOT) io
... Blog: http://meh.ixokai.io/

From: Stephen Hansen on 23 Jun 2010 11:35

On 6/23/10 8:16 AM, Victor Subervi wrote:
> On Wed, Jun 23, 2010 at 10:25 AM, Stephen Hansen
> <me+list/python(a)ixokai.io <mailto:python(a)ixokai.io>> wrote:
>
> On 6/23/10 6:45 AM, Victor Subervi wrote:
> > Hi;
> > I have this line:
> >
> > cursor.execute('select clientEmail from clients where client=%s',
> > (string.replace(client, '_', ' ')))
> > clientEmail = cursor.fetchone()[0]
> > cursor.execute('select * from %s' % (client))
> >
> > client = "Lincoln_Properties"
> > With the replacement, the interpreter complains that
> mydatabase.Lincoln
> > doesn't exist. Therefore, the first line of code isn't putting the %s
> > replacement in quotes, as I was told by you all it would. So I add
> > quotes to it and the interpreter complains on the second line of code
> > that it's unsubscriptable (because it's None). What gives?
>
> Its very early, so excuse me if I fall asleep in the middle of the
> response.
>
> First: Don't do 'string.replace(your_string, x, y)' -- that's back in
> the very, very old days before strings themselves had all the nice
> methods.
>
> Do, 'client.replace("_", " ")' instead.
>
> Second, you're forgetting a cardinal rule. Always, always, always,
> include the actual tracebacks when reporting errors. Don't summarize
> them.
>
> Third, I *think* the problem is-- though I may be wrong here, because
> again, just woke up-- that you're not passing the options as a tuple.
>
> Consider: ("hello") is not a tuple with one item. This is a slight
> 'wart' with Python syntax. Parens do not make tuples: *commas* do.
> Therefore, every tuple *must* have a comma. To make a one-item tuple,
> you must do ("hello", )
>
>
> Well making the changes you suggest throws this error:
>
> A problem occurred in a Python script. Here is the sequence of function
> calls leading up to the error, in the order they occurred.
> /var/www/html/globalsolutionsgroup.vi/mailSpreadsheet.py
> <http://globalsolutionsgroup.vi/mailSpreadsheet.py>
> 67 </body>
> 68 </html>'''
> 69
> 70 mailSpreadsheet()
> 71
> mailSpreadsheet = <function mailSpreadsheet>
> /var/www/html/globalsolutionsgroup.vi/mailSpreadsheet.py
> <http://globalsolutionsgroup.vi/mailSpreadsheet.py> in mailSpreadsheet()
> 34 subject = 'Order From Client'
> 35 cursor.execute('select clientEmail from clients where
> client="%s"', (client.replace('_', ' '),))
> 36 clientEmail = cursor.fetchone()[0]
> 37 cursor.execute('select * from %s', (client,))
> 38 data = cursor.fetchall()
> clientEmail = 'jpage(a)lpc.com <mailto:jpage(a)lpc.com>', cursor =
> <MySQLdb.cursors.Cursor object>, cursor.fetchone = <bound method
> Cursor.fetchone of <MySQLdb.cursors.Cursor object>>
>
> TypeError: unsubscriptable object
> args = ('unsubscriptable object',)

Generally speaking, though this may not be true for MySQL (I don't use
it as a rule), double quotes in SQL often have a special meaning that is
distinct from what you're used to in programming languages.

I.e., cur.execute('SELECT blah FROM blah WHERE blah = "hello"') is not
necessecarily the same as if you had spelled that last bit 'hello'. This
isn't universal across all SQL engines, but its something to keep in
mind. Basically: always use single quotes.

That said: you don't quote things when using parameterized statements.
You write, cur.execute("SELECT ... client = %s", ("my string",)) and not
"%s" in there. You did mention you switched this around at some point
earlier, but you had other problems then so there's no telling if those
other problems are what caused it not to work.

I'm assuming its line 36 which threw the error: cgitb includes some
lines after, so its non obvious when you're looking at it in plain text.
You should note the line number in such cases in case there's more then
one line nearby which looks possibly suspect.

--

Stephen Hansen
... Also: Ixokai
... Mail: me+list/python (AT) ixokai (DOT) io
... Blog: http://meh.ixokai.io/

From: Paul Rubin on 23 Jun 2010 12:12

Stephen Hansen <me+list/python(a)ixokai.io> writes:
>On 6/23/10 6:45 AM, Victor Subervi wrote:
>> cursor.execute('select clientEmail from clients where client=%s', ...
> Do, 'client.replace("_", " ")' instead.

Er, look what happened to Little Bobby Tables (a quick web search on his
name should find his story) because someone wrote code like that.
Really, write the code a different way, with a prepared query.

From: Simon Malinge on 23 Jun 2010 12:06

On Wed, 23 Jun 2010 10:46:37 -0430, Victor Subervi
<victorsubervi(a)gmail.com> wrote:
> On Wed, Jun 23, 2010 at 10:25 AM, Stephen Hansen
> <me+list/python(a)ixokai.io>wrote:
>
>> On 6/23/10 6:45 AM, Victor Subervi wrote:
>> > Hi;
>> > I have this line:
>> >
>> > cursor.execute('select clientEmail from clients where client=%s',
>> > (string.replace(client, '_', ' ')))
>> > clientEmail = cursor.fetchone()[0]
>> > cursor.execute('select * from %s' % (client))
>> >
>> > client = "Lincoln_Properties"
>> > With the replacement, the interpreter complains that
mydatabase.Lincoln
>> > doesn't exist. Therefore, the first line of code isn't putting the %s
>> > replacement in quotes, as I was told by you all it would. So I add
>> > quotes to it and the interpreter complains on the second line of code
>> > that it's unsubscriptable (because it's None). What gives?
>>
>> Its very early, so excuse me if I fall asleep in the middle of the
>> response.
>>
>> First: Don't do 'string.replace(your_string, x, y)' -- that's back in
>> the very, very old days before strings themselves had all the nice
>> methods.
>>
>> Do, 'client.replace("_", " ")' instead.
>>
>> Second, you're forgetting a cardinal rule. Always, always, always,
>> include the actual tracebacks when reporting errors. Don't summarize
>> them.
>>
>> Third, I *think* the problem is-- though I may be wrong here, because
>> again, just woke up-- that you're not passing the options as a tuple.
>>
>> Consider: ("hello") is not a tuple with one item. This is a slight
>> 'wart' with Python syntax. Parens do not make tuples: *commas* do.
>> Therefore, every tuple *must* have a comma. To make a one-item tuple,
>> you must do ("hello", )
>>
>
> Well making the changes you suggest throws this error:
>
> A problem occurred in a Python script. Here is the sequence of function
> calls leading up to the error, in the order they occurred.
> /var/www/html/globalsolutionsgroup.vi/mailSpreadsheet.py
> 67 </body>
> 68 </html>'''
> 69
> 70 mailSpreadsheet()
> 71
> mailSpreadsheet = <function mailSpreadsheet>
> /var/www/html/globalsolutionsgroup.vi/mailSpreadsheet.py in
> mailSpreadsheet()
> 34 subject = 'Order From Client'
> 35 cursor.execute('select clientEmail from clients where
client="%s"',
> (client.replace('_', ' '),))
> 36 clientEmail = cursor.fetchone()[0]
> 37 cursor.execute('select * from %s', (client,))
> 38 data = cursor.fetchall()
> clientEmail = 'jpage(a)lpc.com', cursor = <MySQLdb.cursors.Cursor object>,
> cursor.fetchone = <bound method Cursor.fetchone of
<MySQLdb.cursors.Cursor
> object>>
>
> TypeError: unsubscriptable object
> args = ('unsubscriptable object',)
>
> Please advise.
> TIA,
> beno

You should strongly consider reading the DB API 2.0 documentation :
http://www.python.org/dev/peps/pep-0249/

..fetchone()

Fetch the next row of a query result set, returning a
single sequence, or None when no more data is
available. [6]

You script fails because the request returns no results, and fetchone()
returns None.
Evaluating None[0] is invalid, so you get an exception.

Test fetchone() return value before accessing it as a sequence, and
everything should be OK :

row = cursor.fetchone()
if row is None:
# error handling, i.e.:
raise CustomException("Client not found")
clientEmail = row[0]
....

From: Tapi on 23 Jun 2010 12:12

| Next | Last
Pages: 1 2
Prev: Instance method decorator garbage collection problem
Next: Parsing email