mysql and DATE format

[Hans Nowak]

On 24 Nov 99, 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?

Do I understand correctly that you only want to expand *some* of the 
%-constructions? In this case, %s for table and id, but not %W and 
such. Python apparently tries to convert them all and looks for 
matching variables in the tuple.

To get around this you can construct the SQL strings in multiple 
steps:

sql2 = ' from %s order by %s' % (table, id)
sql1 = "SELECT blah, DATE_FORMAT(date, \"%W, %d %b %Y\") " 

SQL = sql1 + sql2

# execute SQL...

This is only an example... not guaranteed to work as-is. But you'll 
get the idea.

HTH,

--Hans Nowak (zephyrfalcon at hvision.nl)
Homepage: http://fly.to/zephyrfalcon
You call me a masterless man. You are wrong. I am my own master.