Python date and Sql Query

[txt3rob]

Hi need a bit of help.

I have a MySql database with train times in and what i would like to do is to run a query using python to remove any rows with a date that is in the past so say

i have a list of todays trains and when i run the script anything before the current time is removed from the rows.

database is called trains the table is called time and the row is also called time.

Cheers for anyone that can help.

[Davespice]

This is something I think you can probably find out yourself with a bit of Google foo.
Have a read of this page;
http://zetcode.com/db/mysqlpython/

[txt3rob]

i have that sort of code already there it's more the SQL query i need to work out.

I've googled before asking and got lost on how i would do it

[rpdom]

Something like

Code: Select all

DELETE FROM trains WHERE traintime < NOW();

Assuming 'traintime' is stored as a datetime or timestamp field.

[txt3rob]

Code: Select all

DELETE FROM `Time` WHERE `Time` < '15:29 on time'

is what i've got to work so far i will try the now command to see what if it works.

Thanks

[txt3rob]

Legend it has worked

[txt3rob]

no wait it's wiped the data from the database haha i'll reimport that data and try again.

[txt3rob]

i've managed to use this to select anything with a time greater than the current.

Code: Select all

SELECT * FROM `Time` WHERE `Time` > UTC_TIME() LIMIT 0 , 1 ;

if any one else needs to borrow the code

now can i put a variable in to the sql code i have a var that gets the time called nowtime

any one know an easy way to throw it in to the sql code?

[rpdom]

Oh, I see. You really wanted to only get times that are now or later, not as you said "remove any rows with a date that is in the past". Sorry, misunderstood you

[txt3rob]

Code: Select all

DELETE FROM `Time` WHERE `Time` < '16:04%'

This works to delete but i have the following to get the current time

Code: Select all

currentime = time.strftime("%H:%M")

when i put

Code: Select all

    cursor.execute ("DELETE FROM `Time` WHERE `Time` < '%s on time'")% currentime'

i get

Code: Select all

TypeError: unsupported operand type(s) for %: 'long' and 'str

Any ideas?