For the query below, I'm getting the error noted. I can fix two ways: 1) cast the selected datetime/timestamp columns as integers, or 2) replace MySQLdb.converter[7] with a (dummy) procedure. I pulled the relevant sections from my script and show the error and 'fix' results from the python shell below.
Any suggestions for fixing this without changing my queries or replacing the timestamp converter?
>>> query_select = """select id,value,totalpages,cast(starttime as int) as starttime,cast(last_modified as int) as last_modified from ws_semaphores where name='host' and owner='stop' and now()>stoptime"""
>>> db = MySQLdb.connect(host=DBhost, user=DBuser, passwd=DBpw, db=DBdb,compress=1,cursorclass=MySQLdb.cursors.DictCursorNW)
>>> cursor=db.cursor()
>>> cursor.execute( query_select )
Traceback (most recent call last):
File "<pyshell#60>", line 1, in -toplevel-
cursor.execute( query_select )
File "C:\Python23\lib\site-packages\MySQLdb\cursors.py", line 95, in execute
return self._execute(query, args)
File "C:\Python23\lib\site-packages\MySQLdb\cursors.py", line 114, in _execute
self.errorhandler(self, exc, value)
File "C:\Python23\lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
ValueError: invalid literal for int(): 4-
Fix 1
>>> query_select="select id,value,totalpages,cast(starttime as unsigned) as starttime,cast(last_modified as unsigned) as last_modified from ws_semaphores where name='host' and owner='stop' and now()>stoptime"
>>> cursor.execute( query_select )
1L
Fix 2 (based on http://forums.devshed.com/t169469/s.html)
Note, this uses the original query (w/o 'cast')
>>> db.converter[7]
<function mysql_timestamp_converter at 0x00ABF030>
>>> def timestampConverter(s):
return s
>>> db.converter[7]=timestampConverter
>>> db.converter[7]
<function timestampConverter at 0x00ACDAB0>
>>> cursor.execute( query_select )
1L
my setup:
in table ws_semaphores: start time is type datetime, last_modified is type timestamp
Python 2.3.4 (#53, May 25 2004, 21:17:02) [MSC v.1200 32 bit (Intel)] on win32
>>> MySQLdb.version_info
(1, 0, 0, 'final', 1)
>>> MySQLdb.revision
'1.25.2.3'
But I get the same result on Linux:
Python 2.3.4 (#1, Sep 28 2004, 23:14:54) [GCC 3.2.2 20030222 (Red Hat Linux 3.2.2-5)] on linux2
(MySQLdb: same version/revision values)
Thanks much.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Indeed. You never said what MySQL version you had, but I'll guess it's a 4.1 version. MySQLdb-1.0 doesn't support 4.1, and 1.2 does.Your Python version was OK, but 2.4 is also supported.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
For the query below, I'm getting the error noted. I can fix two ways: 1) cast the selected datetime/timestamp columns as integers, or 2) replace MySQLdb.converter[7] with a (dummy) procedure. I pulled the relevant sections from my script and show the error and 'fix' results from the python shell below.
Any suggestions for fixing this without changing my queries or replacing the timestamp converter?
>>> query_select = """select id,value,totalpages,cast(starttime as int) as starttime,cast(last_modified as int) as last_modified from ws_semaphores where name='host' and owner='stop' and now()>stoptime"""
>>> db = MySQLdb.connect(host=DBhost, user=DBuser, passwd=DBpw, db=DBdb,compress=1,cursorclass=MySQLdb.cursors.DictCursorNW)
>>> cursor=db.cursor()
>>> cursor.execute( query_select )
Traceback (most recent call last):
File "<pyshell#60>", line 1, in -toplevel-
cursor.execute( query_select )
File "C:\Python23\lib\site-packages\MySQLdb\cursors.py", line 95, in execute
return self._execute(query, args)
File "C:\Python23\lib\site-packages\MySQLdb\cursors.py", line 114, in _execute
self.errorhandler(self, exc, value)
File "C:\Python23\lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
ValueError: invalid literal for int(): 4-
Fix 1
>>> query_select="select id,value,totalpages,cast(starttime as unsigned) as starttime,cast(last_modified as unsigned) as last_modified from ws_semaphores where name='host' and owner='stop' and now()>stoptime"
>>> cursor.execute( query_select )
1L
Fix 2 (based on http://forums.devshed.com/t169469/s.html)
Note, this uses the original query (w/o 'cast')
>>> db.converter[7]
<function mysql_timestamp_converter at 0x00ABF030>
>>> def timestampConverter(s):
return s
>>> db.converter[7]=timestampConverter
>>> db.converter[7]
<function timestampConverter at 0x00ACDAB0>
>>> cursor.execute( query_select )
1L
my setup:
in table ws_semaphores: start time is type datetime, last_modified is type timestamp
Python 2.3.4 (#53, May 25 2004, 21:17:02) [MSC v.1200 32 bit (Intel)] on win32
>>> MySQLdb.version_info
(1, 0, 0, 'final', 1)
>>> MySQLdb.revision
'1.25.2.3'
But I get the same result on Linux:
Python 2.3.4 (#1, Sep 28 2004, 23:14:54)
[GCC 3.2.2 20030222 (Red Hat Linux 3.2.2-5)] on linux2
(MySQLdb: same version/revision values)
Thanks much.
from the self-help department:
Fix 3: upgrade to current versions (python 2.4.1 and MySQLdb 1.2.0
result: :-)
Indeed. You never said what MySQL version you had, but I'll guess it's a 4.1 version. MySQLdb-1.0 doesn't support 4.1, and 1.2 does.Your Python version was OK, but 2.4 is also supported.
Yes, your psychic codec is working perfectly today. Both the Win and Linux are working off of MySQL 4.1.8. Thanks for the reply.