My search in MySQL it's not working (Python)

Question

I'm trying to get information from a database I have. But everytime I search it says there is nothing that follows the query. This is what I have.

import datetime
import MySQLdb

db = MySQLdb.connect(user='root', passwd='', host='localhost', db = 'data') or die ('Error while connecting')
cursor = db.cursor()
sqlread = "SELECT * FROM humidity WHERE Date_Hour BETWEEN %s AND %s"

ts1 = ('%s/%s/%s 07:00:00') % (now.year, now.month, now.day)
ts2 = ('%s/%s/%s 03:00:00') % (now.year, now.month, now.day+1)

tsdata = (ts1,ts2)
cursor.excecute(sqlread,tsdata)
db.commit()
result = cursor.fetchall()
print result

Therefore, the results is 0. But I made the same search on phpMyAdmin and it worked. What I'm doing wrong?

UPDATE:

Here is my result as Jason commented. However, I think Jason is right. I'm doing a GUI that make some charts. And I have 2 windows. One is making a graph with an animation using matplotlib, the program make the graphics with new data that is recolected from the database, so it is always asking. The other window just make one query but it doesn't work, so it could be possible because I'm asking "twice"?.

CodeLikeBeaker · Accepted Answer · 2016-09-16 19:09:44Z

2

Your db.commit() might be throwing it off. However, lots of factors could play a part in this. You might also consider printing out your SQL query to see what is being put so like this:

Try setting up your code like this, it should lead you in the right direction:

import MySQLdb as mdb
conn = mdb.connect('localhost', 'user', 'password', 'database_name')
try:
    cur = conn.cursor()
    ts1 = ('%s/%s/%s 07:00:00') % (now.year, now.month, now.day)
    ts2 = ('%s/%s/%s 03:00:00') % (now.year, now.month, now.day + 1)
    sqlread = "SELECT * FROM my_table where blah between {} and {}".format(ts1, ts2)
    print(sqlread)
    cur.execute(sqlread)
    res = cur.fetchall()
except mdb.Error as e:
    pass
finally:
    if conn:
        conn.close()

edited Sep 16, 2016 at 19:09

answered Sep 16, 2016 at 19:04

CodeLikeBeaker

21.4k14 gold badges83 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alejandro Harris Bonet Over a year ago

Hi Jason, thanks for your time. I updated my post, maybe you're right.

CodeLikeBeaker Over a year ago

@AlejandroHarrisBonet How is your date stored in the database? Is it with - or /? What happens if you change your date to Year-Month-Day instead of Year/Month/Day.

Alejandro Harris Bonet Over a year ago

Hi Jason, Actually I'm saving it as a string. YYYY/MM/DD, I don't know if maybe I should do it as a DATETIME format, because I was having some issues before trying to collect the date from the database.

CodeLikeBeaker Over a year ago

Yes, you cannot do a between or any date range function on a string without casting it. If you change your data to a datetime, your queries will probably start working :)

Alejandro Harris Bonet Over a year ago

Yeah, I think the same. But the strange thing is that it works with the other graph I'm doing. It's like MySQL recognize the same structure but using strings.

|

Collectives™ on Stack Overflow

My search in MySQL it's not working (Python)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related