Use variable in SQL query

Question

I want to create a variable now that is set to the current time, and then use it in my psycopg2 queries. I've tried using python's datetime as well as current_timestamp, in the following way but it produces an error and can't recognize the variable:

@app.route('/_ajax', methods=['GET', 'POST'])
def userCount():
    now = current_timestamp
    cur.execute("""SELECT count(DISTINCT(username)) from user_table WHERE tstampz >
                   (now - INTERVAL '60 mins')""")
    userCount=cur.fetchone()[0]
    return jsonify (userCount=userCount)

If I use datetime.datetime.now instead of current_timestamp an error reports that datetime cannot be indexed. How can I set this variable properly?

To clarify: The reason why I do not want to use current_timestamp or 'now()` directly in the query is because the time will default to the current time, but it will be changed at some point (based on user input), therefore changing the query.

just use now() instead of now in query?.. I dont think mixing database server time and client time in ajax is a good idea at all — Vao Tsun
– Vao Tsun, Commented Aug 2, 2017 at 14:48
You aren't using the Python variable now; you are using a (nonexistant) SQL column name. Check your DB library to see how to create a parameterized SQL query. — chepner
– chepner, Commented Aug 2, 2017 at 14:50
@VaoTsun I realize that works within the query (just like using current_timestamp works, but I wanted a variable in order to change it dynamically — Sarah
– Sarah, Commented Aug 2, 2017 at 14:54

Sarah · Accepted Answer · 2017-08-02 15:11:03Z

2

If you want to do this in Python, you need a parameterized query. (Check your documentation for details; you may need something slightly different from what I show.)

@app.route('/_ajax', methods=['GET', 'POST'])
def userCount():
    now = datetime.now()
    cur.execute("""SELECT count(DISTINCT(username)) from user_table WHERE tstampz >
                   (%s - INTERVAL '60 mins')""", (now,))
    userCount=cur.fetchone()[0]
    return jsonify (userCount=userCount)

edited Aug 2, 2017 at 15:11

Sarah

1191 silver badge13 bronze badges

answered Aug 2, 2017 at 14:59

chepner

538k77 gold badges594 silver badges746 bronze badges

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

2 Comments

Sarah Over a year ago

Thanks, I used the parameterized part of your answer! I set now = datetime.now() instead but otherwise this works. I don't know why I tried datetime.datetime.now() the first time, was a silly mistake

holdenweb Over a year ago

Your confusion is understandable: now() is a classmethod of the datetime.datetime class. If you import datetime then you need datetime.datetime.now(). But if you use from datetime import datetime you need datetime.now().

holdenweb · Accepted Answer · 2017-08-02 18:24:18Z

1

The answer you accepted shows a correctly-parameterized query. You should note, however, that PostgreSQL knows what the current time is, and you run the risk of time zone confusion by using the time from the client. If you really need rows from the last hour a safer query is

SELECT count(DISTINCT(username)) from user_table WHERE tstampz >
               (current_timestamp - INTERVAL '60 mins')

Somewhat oddly, the documentation lists current_datetime as a function, but you will find if you add the parentheses to call it you get a syntax error.

answered Aug 2, 2017 at 18:24

holdenweb

37.8k7 gold badges62 silver badges80 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2017-08-02 14:55:38Z

0

I think the query should be:

SELECT count(DISTINCT username)
from user_table
WHERE tstampz > (now() - INTERVAL '60 mins')

Note that now() is a function not a "variable" in Postgres.

answered Aug 2, 2017 at 14:55

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Sarah Over a year ago

I understand that works (so does using current_timestamp directly in the query) but it's not what I'm looking for. I need to set a variable that can be changed, and is not always necessarily the current time.

Collectives™ on Stack Overflow

Use variable in SQL query

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related