mysql and DATE format

[M.-A. Lemburg]

sp00fD wrote:
> 
> I've currently got some perl code that executes a sql statment which
> looks like:
> 
> SELECT id, subject, author, text, DATE_FORMAT(date, \"%W, %d %b %Y\"),
> DATE_FORMAT(date, \"%h:%i %p\") FROM Table WHERE thread=18 ORDER by
> date desc
> 
> When trying to use that in python like this:
> """SELECT .. DATE... FROM %s WHERE thread=%d""" % (table, id)
> 
> it tries to expand the DATE %W.. variables.
> I've also tried it as
> 
> sql = 'SELECT id, subject, author, text, '
> sql = sql + 'DATE_FORMAT(date, \"%W, %d %b %Y\"), '
> sql = sql + 'DATE_FORMAT(date, \"%h:%i %p\") FROM' + table
> sql = sql + 'WHERE thread=' + id + 'ORD...'
> 
> which gives me a TypeError: illegal argument type for built-in
> operation.  Why?  How can I do this properly?

Best is to use bound variables and raw strings:

cursor.execute(r'select ... DATE_FORMAT(date,"%W, %d %b %Y")...'
	       r'where thread=?',
	       (id,))

Using bound variables has the advantage of enhancing performance
when multiple executes are done using the same command string
(well at least if the database interface supports this, mxODBC does,
BTW).

-- 
Marc-Andre Lemburg
______________________________________________________________________
Y2000:                                                    36 days left
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/