Page 1 of 1

Python date and Sql Query

Posted: Tue Jun 25, 2013 9:35 am
by 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.

Re: Python date and Sql Query

Posted: Tue Jun 25, 2013 10:23 am
by 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/

Re: Python date and Sql Query

Posted: Tue Jun 25, 2013 10:57 am
by 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

Re: Python date and Sql Query

Posted: Tue Jun 25, 2013 11:29 am
by rpdom
Something like

Code: Select all

DELETE FROM trains WHERE traintime < NOW();
Assuming 'traintime' is stored as a datetime or timestamp field.

Re: Python date and Sql Query

Posted: Tue Jun 25, 2013 12:05 pm
by 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 :D

Re: Python date and Sql Query

Posted: Tue Jun 25, 2013 12:06 pm
by txt3rob
Legend it has worked :D

Re: Python date and Sql Query

Posted: Tue Jun 25, 2013 12:06 pm
by txt3rob
no wait it's wiped the data from the database haha i'll reimport that data and try again.

Re: Python date and Sql Query

Posted: Tue Jun 25, 2013 1:03 pm
by 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?

Re: Python date and Sql Query

Posted: Tue Jun 25, 2013 3:11 pm
by 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 :(

Re: Python date and Sql Query

Posted: Wed Jun 26, 2013 7:56 am
by 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?