Re: Some More MySQL

[MRAB]

Victor Subervi wrote:

I still have this code:

  sql = 'select * from options%s where ID=%%s', (opTable[0].upper() 
+ opTable[1:])

  cursor.execute(sql, (id,))

which throws this error:

 /var/www/html/angrynates.com/cart/enterOptionsPrices2.py 


   70 print 'All options prices have been successfully updated.'
   71 print '\n'
   72
   73 enterOptionsPrices2()
   74
enterOptionsPrices2 = 
 /var/www/html/angrynates.com/cart/enterOptionsPrices2.py 
 in enterOptionsPrices2()

   58   id = form.getfirst('%sID' % option)
   59   sql = 'select * from options%s where ID=%%s', 
(opTable[0].upper() + opTable[1:])

   60   cursor.execute(sql, (id,))
   61 #  cursor.execute('select * from options%s where ID=%s' % 
(opTable[0].upper() + opTable[1:], id))

   62   if cursor.fetchone() is not None:
cursor = , cursor.execute = Cursor.execute of >, sql = ('select * 
from options%s where ID=%%s', 'Sizes'), id = '0.00'
 /usr/lib64/python2.4/site-packages/MySQLdb/cursors.py in 
execute(self=, query=('select * from 
options%s where ID=%%s', 'Sizes'), args=('0.00',))

  144 db = self._get_db()
  145 charset = db.character_set_name()
  146 query = query.encode(charset)
  147 if args is not None:
  148 query = query % db.literal(args)
query = ('select * from options%s where ID=%%s', 'Sizes'), query.encode 
undefined, charset = 'latin1'


AttributeError: 'tuple' object has no attribute 'encode'
  args = ("'tuple' object has no attribute 'encode'",)


Please advise.


On line 59 you're making a tuple.
--
http://mail.python.org/mailman/listinfo/python-list

Re: Some More MySQL

[Victor Subervi]

I still have this code:

  sql = 'select * from options%s where ID=%%s', (opTable[0].upper() +
opTable[1:])
  cursor.execute(sql, (id,))

which throws this error:

 /var/www/html/angrynates.com/cart/enterOptionsPrices2.py
   70 print 'All options prices have been successfully updated.'
   71 print '\n'
   72
   73 enterOptionsPrices2()
   74
enterOptionsPrices2 = 
 /var/www/html/angrynates.com/cart/enterOptionsPrices2.py in
enterOptionsPrices2()
   58   id = form.getfirst('%sID' % option)
   59   sql = 'select * from options%s where ID=%%s',
(opTable[0].upper() + opTable[1:])
   60   cursor.execute(sql, (id,))
   61 #  cursor.execute('select * from options%s where ID=%s' %
(opTable[0].upper() + opTable[1:], id))
   62   if cursor.fetchone() is not None:
cursor = , cursor.execute = >, sql = ('select * from
options%s where ID=%%s', 'Sizes'), id = '0.00'
 /usr/lib64/python2.4/site-packages/MySQLdb/cursors.py in
execute(self=, query=('select * from
options%s where ID=%%s', 'Sizes'), args=('0.00',))
  144 db = self._get_db()
  145 charset = db.character_set_name()
  146 query = query.encode(charset)
  147 if args is not None:
  148 query = query % db.literal(args)
query = ('select * from options%s where ID=%%s', 'Sizes'), query.encode
undefined, charset = 'latin1'

AttributeError: 'tuple' object has no attribute 'encode'
  args = ("'tuple' object has no attribute 'encode'",)


Please advise.
TIA
beno
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: Some More MySQL

[Kushal Kumaran]

On Fri, May 28, 2010 at 5:46 PM, Victor Subervi  wrote:
> On Fri, May 28, 2010 at 2:17 AM, Dennis Lee Bieber 
> wrote:
>>
>> On Thu, 27 May 2010 23:22:24 +0100, MRAB 
>> declaimed the following in gmane.comp.python.general:
>>
>> >
>> > Placeholders which are handled by .execute shouldn't be wrapped in
>> > quotes, even is the value is a string, because .execute will handle that
>> > (and any other details) itself.
>>
>>        Even more internal details -- the MySQLdb placeholder is %s because
>> the adapter, internally, converts ALL parameters to strings, applies
>> escapes to them, and THEN wraps them with quotes before using Python
>> string interpolation to make the query that gets submitted to the
>> server.
>>
>>        This is why you can not use, say %d as a placeholder for a numeric
>> parameter... MySQLdb will convert that numeric to a string, and then
>> Python will choke when it tries to use a %d formatter and is given a
>> string value.
>>
>>
> All of your and MRAB's comments were very helpful. However, I don't see how
> these two problems are addressed:
>
>   sql = 'select * from options%s where ID=%%s', (opTable[0].upper() +
> opTable[1:])
> #  cursor.execute(sql, id)
>   cursor.execute('select * from options%s where ID=%s' %
> (opTable[0].upper() + opTable[1:], id))
>

The second argument to cursor.execute needs to be a tuple.  Change the
call to this:

cursor.execute(sql, (id,))

> The last one works, but if I comment it out and uncomment the middle line,
> it doesn't. Same here:
>
>     sql = "update options%s set PriceDiff='%%s' where Field='%%s' and
> ID=%%s and Store='%%s'" % (opTable[0].upper() + opTable[1:])
> #    cursor.execute(sql, (value, opName, id, store))
>     cursor.execute('update options%s set PriceDiff="%s" where Field="%s"
> and ID=%s and Store="%s"' % (opTable[0].upper() + opTable[1:], value,
> opName, id, store))
>

Lose the quotes around the %s.  You are replying to a post that
describes why this is important.

-- 
regards,
kushal
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: Some More MySQL

[Victor Subervi]

On Fri, May 28, 2010 at 2:17 AM, Dennis Lee Bieber wrote:

> On Thu, 27 May 2010 23:22:24 +0100, MRAB 
> declaimed the following in gmane.comp.python.general:
>
> >
> > Placeholders which are handled by .execute shouldn't be wrapped in
> > quotes, even is the value is a string, because .execute will handle that
> > (and any other details) itself.
>
> Even more internal details -- the MySQLdb placeholder is %s because
> the adapter, internally, converts ALL parameters to strings, applies
> escapes to them, and THEN wraps them with quotes before using Python
> string interpolation to make the query that gets submitted to the
> server.
>
>This is why you can not use, say %d as a placeholder for a numeric
> parameter... MySQLdb will convert that numeric to a string, and then
> Python will choke when it tries to use a %d formatter and is given a
> string value.
>
>
> All of your and MRAB's comments were very helpful. However, I don't see how
these two problems are addressed:

  sql = 'select * from options%s where ID=%%s', (opTable[0].upper() +
opTable[1:])
#  cursor.execute(sql, id)
  cursor.execute('select * from options%s where ID=%s' %
(opTable[0].upper() + opTable[1:], id))

The last one works, but if I comment it out and uncomment the middle line,
it doesn't. Same here:

sql = "update options%s set PriceDiff='%%s' where Field='%%s' and
ID=%%s and Store='%%s'" % (opTable[0].upper() + opTable[1:])
#cursor.execute(sql, (value, opName, id, store))
cursor.execute('update options%s set PriceDiff="%s" where Field="%s"
and ID=%s and Store="%s"' % (opTable[0].upper() + opTable[1:], value,
opName, id, store))

TIA,
beno
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: Some More MySQL

[MRAB]

Victor Subervi wrote:
On Thu, May 27, 2010 at 2:54 PM, MRAB > wrote:


Victor Subervi wrote:

Hi;
But what about this?

 sql = "select pic%d from %s where ID='%%s';" % (pic, store)
 cursor.execute(sql % id)

If I try and rewrite the last line like this:

 cursor.execute(sql, id)

it doesn't work. What do?

How about this one:

   cursor.execute("insert into categories (Store, Category,
Parent) values('%s', '%s', Null)", (store, cat))

For some reason it puts single quotes around my variables! This
doesn't happen if I change that comma for a percent sign! What do?

How about this one:

 sql = 'select * from options%s where ID=%%s',
(opTable[0].upper() + opTable[1:])
#  cursor.execute(sql, id)
 cursor.execute('select * from options%s where ID=%s' %
(opTable[0].upper() + opTable[1:], id))

The last one works, but if I comment it out and uncomment the
middle line, it doesn't. Same here:

   sql = "update options%s set PriceDiff='%%s' where
Field='%%s' and ID=%%s and Store='%%s'" % (opTable[0].upper() +
opTable[1:])
#cursor.execute(sql, (value, opName, id, store))
   cursor.execute('update options%s set PriceDiff="%s" where
Field="%s" and ID=%s and Store="%s"' % (opTable[0].upper() +
opTable[1:], value, opName, id, store))

As has already been explained, when working with SQL in Python there are
2 forms of placeholder:

1. Python's %s placeholder, replaced by Python's % operator.

2. SQL's %s placeholder, replaced by the .execute method.

SQL might not let you use its %s placeholder for table or column names,
but they are normally hidden from the user and fixed by the application.

For user-supplied values there's the risk of SQL-injection attacks.
There are 2 ways of approaching that:

1. The hard way: check the values and add any necessary quoting or
escaping before using Python's % operator, then pass the fully-formed
SQL statement to result to .execute.

2. The easy way: pass the SQL statement to .execute with a %s for each
value and let the method substitute the values itself (it'll add
whatever quoting or escaping is necessary).


Ok, so you're telling me I'm trying to do it the hard way. That's 
because I still don't have my head wrapped around the easy way. I was 
able to follow what Kushal Kumaran supplied; however I must still be 
lost on how that applies to the above examples. Could you illustrate 
with the first and let me try and figure out the rest?



First build the SQL statement with placeholder(s) for the values:

sql = "select pic%d from %s where ID=%%s;" % (pic, store)

Then execute the SQL statement, passing the value(s) so that .execute
performs the substitution itself:

cursor.execute(sql, id)

Placeholders which are handled by .execute shouldn't be wrapped in
quotes, even is the value is a string, because .execute will handle that
(and any other details) itself.
--
http://mail.python.org/mailman/listinfo/python-list

Re: Some More MySQL

[Victor Subervi]

On Thu, May 27, 2010 at 2:54 PM, MRAB  wrote:

> Victor Subervi wrote:
>
>> Hi;
>> But what about this?
>>
>>  sql = "select pic%d from %s where ID='%%s';" % (pic, store)
>>  cursor.execute(sql % id)
>>
>> If I try and rewrite the last line like this:
>>
>>  cursor.execute(sql, id)
>>
>> it doesn't work. What do?
>>
>> How about this one:
>>
>>cursor.execute("insert into categories (Store, Category, Parent)
>> values('%s', '%s', Null)", (store, cat))
>>
>> For some reason it puts single quotes around my variables! This doesn't
>> happen if I change that comma for a percent sign! What do?
>>
>> How about this one:
>>
>>  sql = 'select * from options%s where ID=%%s', (opTable[0].upper() +
>> opTable[1:])
>> #  cursor.execute(sql, id)
>>  cursor.execute('select * from options%s where ID=%s' %
>> (opTable[0].upper() + opTable[1:], id))
>>
>> The last one works, but if I comment it out and uncomment the middle line,
>> it doesn't. Same here:
>>
>>sql = "update options%s set PriceDiff='%%s' where Field='%%s' and
>> ID=%%s and Store='%%s'" % (opTable[0].upper() + opTable[1:])
>> #cursor.execute(sql, (value, opName, id, store))
>>cursor.execute('update options%s set PriceDiff="%s" where
>> Field="%s" and ID=%s and Store="%s"' % (opTable[0].upper() + opTable[1:],
>> value, opName, id, store))
>>
>>  As has already been explained, when working with SQL in Python there are
> 2 forms of placeholder:
>
> 1. Python's %s placeholder, replaced by Python's % operator.
>
> 2. SQL's %s placeholder, replaced by the .execute method.
>
> SQL might not let you use its %s placeholder for table or column names,
> but they are normally hidden from the user and fixed by the application.
>
> For user-supplied values there's the risk of SQL-injection attacks.
> There are 2 ways of approaching that:
>
> 1. The hard way: check the values and add any necessary quoting or
> escaping before using Python's % operator, then pass the fully-formed
> SQL statement to result to .execute.
>
> 2. The easy way: pass the SQL statement to .execute with a %s for each
> value and let the method substitute the values itself (it'll add
> whatever quoting or escaping is necessary).
>
>
> Ok, so you're telling me I'm trying to do it the hard way. That's because I
still don't have my head wrapped around the easy way. I was able to follow
what Kushal Kumaran supplied; however I must still be lost on how that
applies to the above examples. Could you illustrate with the first and let
me try and figure out the rest?
TIA,
beno
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: Some More MySQL

[MRAB]

Victor Subervi wrote:

Hi;
But what about this?

  sql = "select pic%d from %s where ID='%%s';" % (pic, store)
  cursor.execute(sql % id)

If I try and rewrite the last line like this:

  cursor.execute(sql, id)

it doesn't work. What do?

How about this one:

cursor.execute("insert into categories (Store, Category, Parent) 
values('%s', '%s', Null)", (store, cat))


For some reason it puts single quotes around my variables! This doesn't 
happen if I change that comma for a percent sign! What do?


How about this one:

  sql = 'select * from options%s where ID=%%s', (opTable[0].upper() 
+ opTable[1:])

#  cursor.execute(sql, id)
  cursor.execute('select * from options%s where ID=%s' % 
(opTable[0].upper() + opTable[1:], id))


The last one works, but if I comment it out and uncomment the middle 
line, it doesn't. Same here:


sql = "update options%s set PriceDiff='%%s' where Field='%%s' 
and ID=%%s and Store='%%s'" % (opTable[0].upper() + opTable[1:])

#cursor.execute(sql, (value, opName, id, store))
cursor.execute('update options%s set PriceDiff="%s" where 
Field="%s" and ID=%s and Store="%s"' % (opTable[0].upper() + 
opTable[1:], value, opName, id, store))



As has already been explained, when working with SQL in Python there are
2 forms of placeholder:

1. Python's %s placeholder, replaced by Python's % operator.

2. SQL's %s placeholder, replaced by the .execute method.

SQL might not let you use its %s placeholder for table or column names,
but they are normally hidden from the user and fixed by the application.

For user-supplied values there's the risk of SQL-injection attacks.
There are 2 ways of approaching that:

1. The hard way: check the values and add any necessary quoting or
escaping before using Python's % operator, then pass the fully-formed
SQL statement to result to .execute.

2. The easy way: pass the SQL statement to .execute with a %s for each
value and let the method substitute the values itself (it'll add
whatever quoting or escaping is necessary).
--
http://mail.python.org/mailman/listinfo/python-list

Some More MySQL

[Victor Subervi]

Hi;
But what about this?

  sql = "select pic%d from %s where ID='%%s';" % (pic, store)
  cursor.execute(sql % id)

If I try and rewrite the last line like this:

  cursor.execute(sql, id)

it doesn't work. What do?

How about this one:

cursor.execute("insert into categories (Store, Category, Parent)
values('%s', '%s', Null)", (store, cat))

For some reason it puts single quotes around my variables! This doesn't
happen if I change that comma for a percent sign! What do?

How about this one:

  sql = 'select * from options%s where ID=%%s', (opTable[0].upper() +
opTable[1:])
#  cursor.execute(sql, id)
  cursor.execute('select * from options%s where ID=%s' %
(opTable[0].upper() + opTable[1:], id))

The last one works, but if I comment it out and uncomment the middle line,
it doesn't. Same here:

sql = "update options%s set PriceDiff='%%s' where Field='%%s' and
ID=%%s and Store='%%s'" % (opTable[0].upper() + opTable[1:])
#cursor.execute(sql, (value, opName, id, store))
cursor.execute('update options%s set PriceDiff="%s" where Field="%s"
and ID=%s and Store="%s"' % (opTable[0].upper() + opTable[1:], value,
opName, id, store))

Please help.
TIA,
beno

TIA,
beno
-- 
http://mail.python.org/mailman/listinfo/python-list