MySQL Connector/Python

Failing to run queries with "EXTRACT()" and WEEK_DAY in Django using MySQL Connector/Python

Asked by R. Tyler Ballance

I'm working with the Django backend for myconnpy (http://github.com/rtyler/connector-django-mysql) and running through some of the Django model regression tests.

Hitting an error on this query:
"SELECT `basic_article`.`id`, `basic_article`.`headline`, `basic_article`.`pub_date` FROM `basic_article` WHERE (EXTRACT(MONTH FROM `basic_article`.`pub_date`) = 7 AND `basic_article`.`pub_date` BETWEEN '2005-01-01 00:00:00' and '2005-12-31 23:59:59.99' AND EXTRACT(DAY FROM `basic_article`.`pub_date`) = 28) ORDER BY `basic_article`.`pub_date` ASC, `basic_article`.`headline` ASC"

Which raises the following traceback with myconnpy but not with MySQLdb:
Exception raised:
..
      File "/usr/local/lib/python2.6/site-packages/mysql/connector/protocol.py", line 134, in _handle_error
        raise errors.get_mysql_exception(err.errno,err.errmsg)
    ProgrammingError: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WEEK_DAY FROM `basic_article`.`pub_date`) = 5 ORDER BY `basic_article`.`pub_date' at line 1

Python: Python 2.6.2
MySQL: 5.1.42-log
Linux: Linux kiwi 2.6.31.8-0.1-desktop #1 SMP PREEMPT 2009-12-15 23:55:40 +0100 x86_64 x86_64 x86_64 GNU/Linux

Question information

Language:
English Edit question
Status:
Solved
For:
MySQL Connector/Python Edit question
Assignee:
Geert JM Vanderkelen Edit question
Last query:
Last reply:

This question was originally filed as bug #524205.

Revision history for this message
Geert JM Vanderkelen (geertjmvdk) said : 		#1

The syntax error is correct. MySQL's EXTRACT() function doesn't have 'WEEK_DAY' as unit value. MySQLdb will consequently also fail on this.

The reason why the 'mysql' backend using MySQLdb in Django is not failing, is because it has an exception for this in date_extract_sql(). Check django.db.backends.mysql.base.py

Revision history for this message
R. Tyler Ballance (tyler-monkeypox) said : 		#2

Wow, thanks, can't believe I missed that :)

I'll update the connector's backend accordingly :)

Revision history for this message
Geert JM Vanderkelen (geertjmvdk) said : 		#3

Question answered in note sent 2010-02-19