Python: Difference between query in code and query in MySQL

Question

My Code:

date_today = datetime.date.today()
date_curr_year = date_today.year
date_curr_month = date_today.month
date_curr_month_1d = datetime.date(day=1, month=date_curr_month, year=date_curr_year)
date_prev_month = date_curr_month_1d - datetime.timedelta(days=1)
string_date_prev_month = date_prev_month.strftime("%Y-%m-1")
string_date_curr_month_1d = date_curr_month_1d.strftime("%Y-%m-%d")

try:
    #Query:
    q_getstuff = "SELECT `timestamp`, `number`, `url`, `procedure` FROM transaction WHERE `number` IS NOT NULL AND (`timestamp` >= \'" + string_date_prev_month + "\' AND `timestamp` < \'" + string_date_curr_month_1d + "\')"
    print q_getstuff

    #Database Connection
    con = mdb.connect(hostname, username, password, database)
    cur = con.cursor()
    cur.execute(q_getstuff)
    result = cur.fetchall()
    numrows = cur.rowcount

    for row in result:
        print row

except mdb.Error, e:
    print "Error " + str(e.args[0]) + " " + str(e.args[1])
    sys.exit(1)

con.close()

The whole date crap is simply to get the first date of this month, and the first date of the previous month. The query is supposed to get the data from last month. If there's a better way to do this, please let me know.

Here's the result of the print q_getstuff:

SELECT `timestamp`, `number`, `url`, `procedure` FROM transaction
WHERE `number` IS NOT NULL AND (`timestamp` >= '2015-02-1' AND
`timestamp` < '2015-03-01')

Take note that the field names are enclosed in tildes. It seems right, no errors. If I print out numrows, it returns 2, as in, 2 rows.

The Problem:

I copy the output of print q_getstuff, and just straight up paste it (no edits) into MySQL, and it returns 78 rows.

So obviously something's wrong with the code, but I'm not sure what's happening. Where did I go wrong?

lapinkoira · Accepted Answer · 2015-03-26 09:57:13Z

1

Can you try something like this:

q_getstuff = "SELECT timestamp, number, url, procedure FROM transaction WHERE number IS NOT NULL AND (timestamp >= '%s' AND timestamp < '%s')" % (string_date_prev_month, string_date_curr_month_1d)

answered Mar 26, 2015 at 9:57

lapinkoira

9,1089 gold badges59 silver badges108 bronze badges

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

Comments

Martin Kalcok · Accepted Answer · 2015-03-26 10:05:46Z

1

Your way of constructing the query seems odd. More standard way would be:

q_getstuff = "SELECT timestamp, number, url, procedure FROM transaction WHERE number IS NOT NULL AND (timestamp >= ? AND timestamp < ?)"
cur.execute(q_get_stuff, (string_date_prev_month, string_date_curr_month_1d))

Database API will take care of proper quoting and escaping your variables.

answered Mar 26, 2015 at 10:05

Martin Kalcok

1575 bronze badges

3 Comments

zack_falcon Over a year ago

I get a TypeError: not all arguments converted during string formatting when I did that. But basically, I just did that so I can print it out.

Martin Kalcok Over a year ago

What type of DB are you connecting to? Question marks are proper place holder when connecting to Sqlite. For other, like MySQL, you would use %s instead. However logic still stands that you should let DB connector do the work of inserting variables into query.

zack_falcon Over a year ago

MySQL, as tagged, though I forgot to mention it in the question itself.

zack_falcon · Accepted Answer · 2015-03-26 15:08:15Z

0

I don't know how, but restarting everything (database, computer) fixed it.

Guess it wasn't the code after all, and more something to do with MySQL itself.

answered Mar 26, 2015 at 15:08

zack_falcon

4,38423 gold badges70 silver badges111 bronze badges

Collectives™ on Stack Overflow

Python: Difference between query in code and query in MySQL

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related