Python MySQL API and date data type

[Yong Wang]

Hi:

We have a database system. In the database, we have an
attribute called date0, which contains date data (format:
i. e. 2004-08-12). I wrote a script to fetch the data
in a specified time period:
for exaplme:
date1 = raw_input('start date ')
date2 = raw_input('end date ')

ie. date1 = '2004-08-12'
date2 = '2004-08-20'
run = ('SELECT * FROM account WHERE (date0 >= date1) and (date0 <= date2)')
data = dbh[runn]
.....
When I run the database query, it complains query syntax after where clause.

Please see the below:

Please input your start date of your report: 2004-08-12
Please enter your ending date of your report: 2004-08-17
date1 is 2004-08-12
date2 is 2004-08-17
Traceback (innermost last):
File "report", line 25, in ?
data = dbh[run]
MySQL.error: Unknown column 'date1' in 'where clause'

How should I fix the query statement. I check the MySQL manual. The
syntax is correct.
Thanks a lot.

Yong



--7C97B1589B.1093552427/net.tamu.edu--

 

[Dennis Lee Bieber]

ie. date1 = '2004-08-12'
date2 = '2004-08-20'
run = ('SELECT * FROM account WHERE (date0 >= date1) and (date0 <= date2)')
data = dbh[runn]
.....
When I run the database query, it complains query syntax after where clause.

Please see the below:

MySQL.error: Unknown column 'date1' in 'where clause'

How should I fix the query statement. I check the MySQL manual. The
syntax is correct.

No, it isn't. Run the command line MySQL client and enter your
same query... No doubt you'll get the same error.

It would help if you supplied the actual Python code (or at
least enough to duplicate the problem) -- then we could show you the
proper correction...

Consider cs to be a MySQLdb (uh, are you using MySQLdb or some
other obscure adapter?) cursor. Then your "run" becomes.

cs.execute("select * from account where (date0 >= %s) and (date0 <=
%s)", (date1, date2) )

The MySQLdb .execute() method will correctly substitute the
/values/ of date1 and date2 into the query at the %s tokens.

--