txt3rob
Posts: 365
Joined: Sat Aug 11, 2012 3:45 pm
Contact: Website

Python date and Sql Query

Tue Jun 25, 2013 9:35 am

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.
The Raspberry Pi Hell Guy - Random Ramblings to assist me and others.
http://raspberrypihell.blogspot.com
My Github - http:/www.github.com/random-robbie/
http://www.smspi.co.uk - send free uk sms via your raspberry pi from here

User avatar
Davespice
Forum Moderator
Forum Moderator
Posts: 1662
Joined: Fri Oct 14, 2011 8:06 pm
Location: The Netherlands
Contact: Twitter

Re: Python date and Sql Query

Tue Jun 25, 2013 10:23 am

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
Posts: 365
Joined: Sat Aug 11, 2012 3:45 pm
Contact: Website

Re: Python date and Sql Query

Tue Jun 25, 2013 10:57 am

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
The Raspberry Pi Hell Guy - Random Ramblings to assist me and others.
http://raspberrypihell.blogspot.com
My Github - http:/www.github.com/random-robbie/
http://www.smspi.co.uk - send free uk sms via your raspberry pi from here

User avatar
rpdom
Posts: 15581
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: Python date and Sql Query

Tue Jun 25, 2013 11:29 am

Something like

Code: Select all

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

txt3rob
Posts: 365
Joined: Sat Aug 11, 2012 3:45 pm
Contact: Website

Re: Python date and Sql Query

Tue Jun 25, 2013 12:05 pm

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
The Raspberry Pi Hell Guy - Random Ramblings to assist me and others.
http://raspberrypihell.blogspot.com
My Github - http:/www.github.com/random-robbie/
http://www.smspi.co.uk - send free uk sms via your raspberry pi from here

txt3rob
Posts: 365
Joined: Sat Aug 11, 2012 3:45 pm
Contact: Website

Re: Python date and Sql Query

Tue Jun 25, 2013 12:06 pm

Legend it has worked :D
The Raspberry Pi Hell Guy - Random Ramblings to assist me and others.
http://raspberrypihell.blogspot.com
My Github - http:/www.github.com/random-robbie/
http://www.smspi.co.uk - send free uk sms via your raspberry pi from here

txt3rob
Posts: 365
Joined: Sat Aug 11, 2012 3:45 pm
Contact: Website

Re: Python date and Sql Query

Tue Jun 25, 2013 12:06 pm

no wait it's wiped the data from the database haha i'll reimport that data and try again.
The Raspberry Pi Hell Guy - Random Ramblings to assist me and others.
http://raspberrypihell.blogspot.com
My Github - http:/www.github.com/random-robbie/
http://www.smspi.co.uk - send free uk sms via your raspberry pi from here

txt3rob
Posts: 365
Joined: Sat Aug 11, 2012 3:45 pm
Contact: Website

Re: Python date and Sql Query

Tue Jun 25, 2013 1:03 pm

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?
The Raspberry Pi Hell Guy - Random Ramblings to assist me and others.
http://raspberrypihell.blogspot.com
My Github - http:/www.github.com/random-robbie/
http://www.smspi.co.uk - send free uk sms via your raspberry pi from here

User avatar
rpdom
Posts: 15581
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: Python date and Sql Query

Tue Jun 25, 2013 3:11 pm

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
Posts: 365
Joined: Sat Aug 11, 2012 3:45 pm
Contact: Website

Re: Python date and Sql Query

Wed Jun 26, 2013 7:56 am

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?
The Raspberry Pi Hell Guy - Random Ramblings to assist me and others.
http://raspberrypihell.blogspot.com
My Github - http:/www.github.com/random-robbie/
http://www.smspi.co.uk - send free uk sms via your raspberry pi from here

Return to “Python”