Hi guys, I've tried to to investigate this problem and I think it's
not web.py bug.
I played with db.py file but with no luck (it was displaying properly
formatted queries but I got error about truncated data for timestamp
column) so I decided to do the same with MySQLdb library and this
code:
cursor.execute('UPDATE ts SET modified = %s WHERE id = 6', ("NOW()",))
produced exactly the same error, so then I wanted to see raw query
that is send to database
I've found some code on MySQLdb forum:
class DebugCursor(MySQLdb.cursors.Cursor):
def _query(self, q):
import sys
print >> sys.stderr, q
return self._do_query(q)
cursor = conn.cursor(DebugCursor)
and it gives:
UPDATE ts SET modified = 'NOW()' WHERE id = 6
mytest.py:27: Warning: Data truncated for column 'modified' at row 1
cursor.execute('UPDATE ts SET modified = %s WHERE id = 6',
("NOW()",))
as you can see NOW() is still quoted...
but this:
cursor.execute('UPDATE ts SET modified = %s WHERE id = 6'%("NOW()",))
gives:
UPDATE ts SET modified = NOW() WHERE id = 6
and it work's well
I've also found that MySQLdb is converting parameters that doesn't
match any internal datatype mapping to string and then quoting.
"quote_conv
A dictionary mapping Python types (from the standard types module or
built-in function type() to MySQL literals. By default, the value is
treated as a string."
http://dustman.net/andy/python/MySQLdb_obsolete/doc/MySQLdb-3.html
So that I think (if it's possible) that db.py should have it's own
mechanism to build queries for functions like web.update and then send
it to cursor.execute() without specifying parameters.
I'm not sure if it's good idea.
Regards
Łukasz
On 12 Gru 2007, 08:00, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> This problem has come up several times, and the only work around I've
> found is to just use web.query() for everything :/
>
> On Dec 11, 3:07 pm, "Hermann Kaser" <[EMAIL PROTECTED]> wrote:
>
> > I see, apparently I'm a jackass. My 'fix' breaks everything else
> > because... if I pass an int as a value I get 4L instead of 4 in the
> > SQL query.
>
> > Before I keep screwing around with the db.py code I'll wait to hear
> > from you guys about the problem I've run into, there's probably a very
> > obvious explanation and workaround.
>
> > On Dec 11, 2007 10:46 PM, Hermann Kaser <[EMAIL PROTECTED]> wrote:
>
> > > Hello,
>
> > > The following query is giving me errors:
>
> > > web.insert('updates', seqname=id, start=web.SQLLiteral('NOW()'))
>
> > > When I run it I get:
>
> > > /usr/lib/python2.5/site-packages/web/db.py:340: Warning: Data
> > > truncated for column 'start' at row 1
>
> > > and the 'start' column in the database will be set to '0000-00-00
> > > 00:00:00'. But when if I do _test=True I get the correct query:
>
> > > INSERT INTO updates (start) VALUES (NOW())
>
> > > If I go to db.py and output the query right before it gets executed I get
>
> > > sql_query.s => INSERT INTO updates (start) VALUES (%s)
> > > sql_query.v => (NOW(),)
>
> > > I've looked at the code and what happens is that the cursor recieves
> > > the string and arguments for it to format rather than the already
> > > formatted string by SQLQuery. I fixed it by changing line 338 from
>
> > > out = cur.execute(sql_query.s, sql_query.v)
>
> > > to
>
> > > out = cur.execute(str(sql_query))
>
> > > Let me know if the old behaviour was on purpose.
>
> > > Regards,
> > > Hermann Käser
> > >http://theragingche.com/
> > >http://semicir.cl/user/hermzz
>
> > --
> > Hermann Käserhttp://theragingche.com/http://semicir.cl/user/hermzz
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web.py" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/webpy?hl=en
-~----------~----~----~----~------~----~------~--~---
