Building multi-line database queries in python

Question

Ran into a problem and something that seemed simple at first.

I want to do this (query has been dumbed down for brevity here, it's actually much longer and more complex):

 query = ("""INSERT INTO %s " % cfg['mysql.table']
 "('hostname', 'timestamp', 'metric', 'average', 'peakhigh', 'peaklow', 'gsamp', 'zsamp', 'nsamp')"""
 "VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s )"
 ) 

So how do I incorpoarate STR_TO_DATE into this query for the timestamp field?

Thanks for any help.

• Gadget: Try building your query inside triple quotes.
• BenH: How do you do this component of the string using the triple quote approach though - "INSERT INTO %s " % cfg['mysql.table']?

Answer 1

Put your string inside triple quotes:

query = ("""INSERT INTO %s " % cfg['mysql.table']
 "('hostname', 'timestamp', 'metric', 'average', 'peakhigh', 'peaklow', 'gsamp', 'zsamp', 'nsamp')"
 "VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s )"""
 ) 

• BenH: Ok let me try that again, I must have fat-fingered something when I tried this approach before posting.
• BenH: Any idea how I incoproate STR_TO_DATE for the timestamp field?
• Gadget: Try changing the second %s with STR_TO_DATE(%s, '%d/%m/%Y') if that matches the dd/mm/YYYY format you require.