i'm running MySQLdb 1.0.0 on a Windows 2000 connected to a MySQL 4.0.12 server (on Linux).
i was wondering what's the preferred way to update a field with a new datetime value. since the mysqldb 1.0.0 returns datetime values as Py 2.3 datetime objects i thought that the opposite was also working.
unfortunately this seems not the case. by issueing an UPDATE command using:
row['date_created'] = datetime.now()
causes MySQL to issue a warning and hence an exception will be raised.
so i tried this:
row['date_created'] = datetime.now().isoformat()
but even this fails. i have to manually cut isoformat() string to 19 chars.
the strange thing is that INSERT works, UPDATE does not.
here's the first INSERT query (working), plus values:
It ought to work. Try examining the conv attribute of your database connection. The keys are MySQL field types (integers) or Python types/classes. There should be one in there that is type(datetime).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
thanks for your interest. here's mine conv(erter) dictionary for the connection object
{0: <type 'float'>,
1: <type 'int'>,
2: <type 'int'>,
3: <type 'long'>,
4: <type 'float'>,
5: <type 'float'>,
7: <function mysql_timestamp_converter at 0x00BDB330>,
8: <type 'long'>,
9: <type 'int'>,
10: <function Date_or_None at 0x00BDB2F0>,
11: <function Time_or_None at 0x00BDB2B0>,
12: <function DateTime_or_None at 0x00BDB230>,
13: <type 'int'>,
248: <function Str2Set at 0x00BE0630>,
252: [(128, <function char_array at 0x00BE07F0>),
(None, <function <lambda> at 0x00BD2A70>),
(None, <function <lambda> at 0x00BE05F0>),
(None, <function <lambda> at 0x00BE0130>),
(None, <function <lambda> at 0x00BE0870>),
(None, <function <lambda> at 0x00BE08B0>),
(None, None)],
253: <function <lambda> at 0x00BD2A70>,
254: <function <lambda> at 0x00BD2A70>,
<type 'array.array'>: <function array2Str at 0x00BE0830>,
<type 'instance'>: <function Instance2Str at 0x00BE07B0>,
<type 'dict'>: <built-in function escape_dict>,
<type 'float'>: <function Float2Str at 0x00BE06F0>,
<type 'int'>: <function Thing2Str at 0x00BE0670>,
<type 'list'>: <built-in function escape_sequence>,
<type 'long'>: <function Thing2Str at 0x00BE0670>,
<type 'NoneType'>: <function None2NULL at 0x00BE0730>,
<type 'str'>: <built-in method string_literal of Connection object at 0x00C0CC1
8>,
<type 'tuple'>: <built-in function escape_sequence>,
<type 'type'>: <function DateTimeDelta2literal at 0x00BDB0B0>,
<type 'object'>: <function Instance2Str at 0x00BE07B0>,
<type 'unicode'>: <bound method Connection.unicode_literal of <_mysql.connectio
n open to '62.94.190.43' at c0cc18>>}
but it might be a mysql problem. i've tried the following from python console (target date_created column is a datetime field).
>>> import MySQLdb
>>> k = MySQLdb.connect(host='xxx.xxx.xxx.xxx, db='test', user='demo', passwd='secret', unicode='latin-1')
>>> k
<_mysql.connection open to 'xxx.xxx.xxx.xxx' at ce9620>
>>> c = k.cursor()
>>> from datetime import datetime
>>> c.execute('update newsletter_entry set date_created = %s where id = 1', [datetime.now()])
Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "C:\Python\lib\site-packages\MySQLdb\cursors.py", line 95, in execute
return self._execute(query, args)
File "C:\Python\lib\site-packages\MySQLdb\cursors.py", line 114, in _execute
self.errorhandler(self, exc, value)
File "C:\Python\lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
Warning: Rows matched: 1 Changed: 1 Warnings: 1
>>> c.execute('update newsletter_entry set date_created = %s where id = 1', [datetime.now().isoformat()])
Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "C:\Python\lib\site-packages\MySQLdb\cursors.py", line 95, in execute
return self._execute(query, args)
File "C:\Python\lib\site-packages\MySQLdb\cursors.py", line 114, in _execute
self.errorhandler(self, exc, value)
File "C:\Python\lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
Warning: Rows matched: 1 Changed: 1 Warnings: 1
>>> c.execute('update newsletter_entry set date_created = %s where id = 1', [datetime.now().isoformat()[:19]])
1L
this starts to be a bit puzzling.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
One of the things that is not showing up in your dictionary is <type 'datetime.datetime'>, which means the datetime output converters aren't being put in there, and so it simply applies str() to your value by default. This stuff is done with times.py and pytimes.py and coverters.py.
Could you try this with 1.1.5, which is the release candidate for 1.2.0, and see if you can reproduce this? 1.1.5 is definitely a better version for Python-2.3.4 anyway.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
update newsletter_entry set content = 'Edited xyz', date_created = '2004-09-22 10:42:56.750000', title = 'Some title' where id = 91;
but the UPDATE does not. it issues a warning, hence an exception in mysqldb.
i've fired up mysqlcc GUI tool an just copy pasted to the above queries with the same results. i can see the line:
"Rows matched: 1 Changed: 0 Warnings: 1"
in mysqlcc after the UPDATE statment.
it eludes me why the long format "2004-09-22 10:42:56.632000" it's passed to mysql. the fact that mysql accepts an extended ISO format for datetime values on INSERTs and not in UPDATEs if a quirk, but that another problem altogether.
cheers,
deelan.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You might be happier with MySQLdb-1.1.5: The 1.1 series uses the Python warning module instead of raising exceptions as in 1.0. The warning module did not exist for Python-1.5.2, which is why it's not used in 1.0.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
hi there,
i'm running MySQLdb 1.0.0 on a Windows 2000 connected to a MySQL 4.0.12 server (on Linux).
i was wondering what's the preferred way to update a field with a new datetime value. since the mysqldb 1.0.0 returns datetime values as Py 2.3 datetime objects i thought that the opposite was also working.
unfortunately this seems not the case. by issueing an UPDATE command using:
row['date_created'] = datetime.now()
causes MySQL to issue a warning and hence an exception will be raised.
so i tried this:
row['date_created'] = datetime.now().isoformat()
but even this fails. i have to manually cut isoformat() string to 19 chars.
the strange thing is that INSERT works, UPDATE does not.
here's the first INSERT query (working), plus values:
insert into newsletter_entry (content, date_created, id, title) values (%(
content)s, %(date_created)s, %(id)s, %(title)s)
Values passed to execute are:
{'content': 'xyz', 'date_created': datetime.datetime(2004, 9, 20, 10, 19,
17, 17000), 'id': 0, 'title': 'Some title'}
here's the first UPDATE query (not working), plus values:
update newsletter_entry set content = %(content)s, date_created = %(date_created)s, title = %(title)s where id = %(id)s
Values passed to execute are:
{'content': 'Edited xyz', 'date_created': datetime.datetime(2004, 9, 20, 10, 28, 7, 43000), 'id': 33, 'title': 'Some title'}
any hint will be greatly appreciated.
cheers,
deelan.
It ought to work. Try examining the conv attribute of your database connection. The keys are MySQL field types (integers) or Python types/classes. There should be one in there that is type(datetime).
hi andy,
thanks for your interest. here's mine conv(erter) dictionary for the connection object
{0: <type 'float'>,
1: <type 'int'>,
2: <type 'int'>,
3: <type 'long'>,
4: <type 'float'>,
5: <type 'float'>,
7: <function mysql_timestamp_converter at 0x00BDB330>,
8: <type 'long'>,
9: <type 'int'>,
10: <function Date_or_None at 0x00BDB2F0>,
11: <function Time_or_None at 0x00BDB2B0>,
12: <function DateTime_or_None at 0x00BDB230>,
13: <type 'int'>,
248: <function Str2Set at 0x00BE0630>,
252: [(128, <function char_array at 0x00BE07F0>),
(None, <function <lambda> at 0x00BD2A70>),
(None, <function <lambda> at 0x00BE05F0>),
(None, <function <lambda> at 0x00BE0130>),
(None, <function <lambda> at 0x00BE0870>),
(None, <function <lambda> at 0x00BE08B0>),
(None, None)],
253: <function <lambda> at 0x00BD2A70>,
254: <function <lambda> at 0x00BD2A70>,
<type 'array.array'>: <function array2Str at 0x00BE0830>,
<type 'instance'>: <function Instance2Str at 0x00BE07B0>,
<type 'dict'>: <built-in function escape_dict>,
<type 'float'>: <function Float2Str at 0x00BE06F0>,
<type 'int'>: <function Thing2Str at 0x00BE0670>,
<type 'list'>: <built-in function escape_sequence>,
<type 'long'>: <function Thing2Str at 0x00BE0670>,
<type 'NoneType'>: <function None2NULL at 0x00BE0730>,
<type 'str'>: <built-in method string_literal of Connection object at 0x00C0CC1
8>,
<type 'tuple'>: <built-in function escape_sequence>,
<type 'type'>: <function DateTimeDelta2literal at 0x00BDB0B0>,
<type 'object'>: <function Instance2Str at 0x00BE07B0>,
<type 'unicode'>: <bound method Connection.unicode_literal of <_mysql.connectio
n open to '62.94.190.43' at c0cc18>>}
but it might be a mysql problem. i've tried the following from python console (target date_created column is a datetime field).
>>> import MySQLdb
>>> k = MySQLdb.connect(host='xxx.xxx.xxx.xxx, db='test', user='demo', passwd='secret', unicode='latin-1')
>>> k
<_mysql.connection open to 'xxx.xxx.xxx.xxx' at ce9620>
>>> c = k.cursor()
>>> from datetime import datetime
>>> c.execute('update newsletter_entry set date_created = %s where id = 1', [datetime.now()])
Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "C:\Python\lib\site-packages\MySQLdb\cursors.py", line 95, in execute
return self._execute(query, args)
File "C:\Python\lib\site-packages\MySQLdb\cursors.py", line 114, in _execute
self.errorhandler(self, exc, value)
File "C:\Python\lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
Warning: Rows matched: 1 Changed: 1 Warnings: 1
>>> c.execute('update newsletter_entry set date_created = %s where id = 1', [datetime.now().isoformat()])
Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "C:\Python\lib\site-packages\MySQLdb\cursors.py", line 95, in execute
return self._execute(query, args)
File "C:\Python\lib\site-packages\MySQLdb\cursors.py", line 114, in _execute
self.errorhandler(self, exc, value)
File "C:\Python\lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
Warning: Rows matched: 1 Changed: 1 Warnings: 1
>>> c.execute('update newsletter_entry set date_created = %s where id = 1', [datetime.now().isoformat()[:19]])
1L
this starts to be a bit puzzling.
One of the things that is not showing up in your dictionary is <type 'datetime.datetime'>, which means the datetime output converters aren't being put in there, and so it simply applies str() to your value by default. This stuff is done with times.py and pytimes.py and coverters.py.
Could you try this with 1.1.5, which is the release candidate for 1.2.0, and see if you can reproduce this? 1.1.5 is definitely a better version for Python-2.3.4 anyway.
i would be very glad to try them out, but i need a win binary. unfortunately at the moment i can only test things on windows.
i've just tried to replace 1.0.0 python files with newer ones in 1.1.5 but some version check is done by mysqldb.
cheers,
deelan.
still playing around with 1.0.0.
i've tried to add this lines to MySQLdb's cursors.py _execute() method, to see what values are really passed to mysql:
q = query % self.connection.literal(args)
print '*** q', q
r = self._query(q)
the two significat results are:
insert into newsletter_entry (content, date_created, id, title) values ('xyz', '2004-09-22 10:42:56.632000', 0, 'Some title');
as i've written before the INSERT works.
update newsletter_entry set content = 'Edited xyz', date_created = '2004-09-22 10:42:56.750000', title = 'Some title' where id = 91;
but the UPDATE does not. it issues a warning, hence an exception in mysqldb.
i've fired up mysqlcc GUI tool an just copy pasted to the above queries with the same results. i can see the line:
"Rows matched: 1 Changed: 0 Warnings: 1"
in mysqlcc after the UPDATE statment.
it eludes me why the long format "2004-09-22 10:42:56.632000" it's passed to mysql. the fact that mysql accepts an extended ISO format for datetime values on INSERTs and not in UPDATEs if a quirk, but that another problem altogether.
cheers,
deelan.
You might be happier with MySQLdb-1.1.5: The 1.1 series uses the Python warning module instead of raising exceptions as in 1.0. The warning module did not exist for Python-1.5.2, which is why it's not used in 1.0.