Python SQL cursor giving different results?

Question

I have the following function in Python:

def get_emo_results(emo, operator):
    cursor.execute("SELECT avg(?) FROM [LIWC Post Stats] "
                   "WHERE goals_scored {0} goals_taken "
                   "GROUP BY post_number "
                   "ORDER BY post_number ASC "
                   "LIMIT ?".format(operator), [emo, posts_limit])
    print "SELECT avg({1}) FROM [LIWC Post Stats] "\
            "WHERE goals_scored {0} goals_taken "\
            "GROUP BY post_number "\
            "ORDER BY post_number ASC "\
            "LIMIT {2}".format(operator, emo, posts_limit)
    return [x[0] for x in cursor.fetchall()]

I call it with get_emo_results('posemo', '>') and get this output to stdout:

SELECT avg(posemo) FROM [LIWC Post Stats] WHERE goals_scored > goals_taken GROUP BY post_number ORDER BY post_number ASC LIMIT 200

However, the function itself returns

[0.0, 0.0, 0.0, 0.0, 0.0, ... 0.0]

I copy and paste that exact expression in stdout to my SQLite process that I have opened, and I get this:

1.8730701754386
2.48962719298246
2.18607456140351
2.15342105263158
2.33107456140351
2.11631578947368
2.37100877192982
1.95228070175439
2.01013157894737
...
3.37183673469388

So not 0 at all. Why does my Python function return something different despite using the same query? What am I doing wrong?

EDIT:

It now works when I get rid of the question marks and format the string directly. Why don't parameterized queries work in this case?

Are you sure the statement printed is the exact same as the query in cursor.execute? There is a subtle difference in how you are formatting the string. Does cursor.execute replace each ? with the corresponding clause from the list? — alanmanderson
– alanmanderson, Commented Nov 13, 2012 at 19:43
Are you connecting to a different database when you do it directly vs through python? — Gerrat
– Gerrat, Commented Nov 13, 2012 at 19:47
@ajon ahhh, you are right, it works when I format the string instead of doing a parameterized query... — wrongusername
– wrongusername, Commented Nov 13, 2012 at 19:51
@Gerrat no, I am sure its the same one, everything else works fine — wrongusername
– wrongusername, Commented Nov 13, 2012 at 19:54

TimothyAWiseman · Accepted Answer · 2012-11-13 20:04:15Z

2

It is being handled differently because you are parametirzing your query. You can't really parameterize a column name like that. It is trying to protect you from SQL injection so it is (I'm simplifying here, but basically) encapsulating any strings in quotes before passing it to the SQL engine.

Essentially, SQLlite is trying to average the string literal 'posemo'

You can keep your limit parameterized, but when it comes to column names you need to have them hardcoded or else put them in the string with something like format.

answered Nov 13, 2012 at 20:04

TimothyAWiseman

15k12 gold badges42 silver badges49 bronze badges

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

Collectives™ on Stack Overflow

Python SQL cursor giving different results?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related