Postgresql & Psycopg2 unable to properly select data between two dates

Question

I created an API service that returns ticker,rate and date as JSON, But when i'm trying to select multiple parameters, it doesn't works properly like here is an example,lets say end_at = 2010-05-10 and start_at = 2010-05-15:

Datebase model:

class Currency(Base):
   __tablename__ = "currency"
   ticker = Column(String)
   date = Column(Date)
   rates = Column(JSONB, primary_key=True)

Updated query code from Mike Orgenek's answer:

if end_at and start_at:
        currency = cursor.execute("""
        SELECT rates,date,ticker
        FROM currency
        WHERE ticker = %s
        AND date BETWEEN SYMMETRIC %s AND %s """, (base, start_at, end_at, ))

After printing the query for start_at = 2010-05-10 & end_at = 2010-05-15

Out: 2020-07-04T09:32:30.898337+00:00 app[web.1]: b"\n        SELECT rates,date,ticker\n        FROM currency\n        WHERE ticker = 'EUR'\n        AND date BETWEEN SYMMETRIC '2010-05-10' AND '2010-05-15' "

It doesn't recognizes my start_at parameter even with the right query FULL API Output

It includes dates older than the start_at like "2010-01-28", "2010-01-07", "2010-04-16"

What type is the date column? Date, datetime, other? If datetime, is it timezone aware? — ilmarinen, Jul 04 '20 at 06:10
You should be using bind parameters to protect against SQL injection attacks. Until you get that fixed, construct your query separately from the `execute` step and examine what is actually being constructed in your query. — Mike Organek, Jul 04 '20 at 06:49
Should i use bind paramters in every query @MikeOrganek? Because i use f strings in my every `cursor.execute()` — Yagiz Degirmenci, Jul 04 '20 at 07:01
Yes. Please get into the habit as soon as you can. I will put an example snippet for you as an answer. — Mike Organek, Jul 04 '20 at 07:15
Can you please add the updated, parameterized query code (python) used to create the query log? — Mike Organek, Jul 04 '20 at 09:18
@YagizcanDegirmenci The query looks correct. Did you get the results you expected? — Mike Organek, Jul 04 '20 at 10:52
Even with right query, it gives wrong result, still looking for an answer. — Yagiz Degirmenci, Jul 04 '20 at 21:40

Mike Organek · Accepted Answer · 2020-07-04T09:04:48.440

1

Please parameterize your queries to avoid SQL Injection attacks.

if end_at and start_at:
     currency = cursor.execute("""SELECT rates,date,ticker 
     FROM currency
     WHERE ticker = %s 
     AND date BETWEEN SYMMETRIC %s AND %s """, (base, start_at, end_at, ))
     print(cursor.query)  # <--- This should log the complete query sent to the db server

To diagnose the problem at hand, do something like this to see what you are sending, but do not put your application live before changing all your execute() calls to the parameterized form.

if end_at and start_at:
     query = f"""SELECT rates,date,ticker 
                   FROM currency
                  WHERE ticker = '{base}' 
                    AND date BETWEEN SYMMETRIC '{start_at}' AND '{end_at}' """
     print(query)
     currency = cursor.execute(query)

After fixing your queries to use parameters, to see what is being sent in the query to the server, use the LoggingConnection as a drop-in replacement for your existing psycopg2.Connection as lifted from How do I use Psycopg2's LoggingConnection?.

import logging
import psycopg2
from psycopg2.extras import LoggingConnection

logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)

conn=psycopg2.connect(connection_factory=LoggingConnection, database='some_database')
conn.initialize(logger)

c = conn.cursor()

c.execute("select count(*) from some_table where id > %s", (1000, ))

With my basic config, logging goes to the console:

DEBUG:__main__:b'select count(*) from some_table where id > 1000'

edited Jul 04 '20 at 09:04

answered Jul 04 '20 at 07:22

Mike Organek

11,647
3
11
26

Thanks, now i added queries instead of f strings everything is working but i am still unable to get the dates properly. – Yagiz Degirmenci Jul 04 '20 at 08:01
1

@YagizcanDegirmenci Have you logged the actual statement being sent? There is an error in the first query in my answer. I will correct it, but I think you already fixed it if it isn't crashing on you. The key thing is getting the logged query. Do you have access to the database server? – Mike Organek Jul 04 '20 at 08:10
Yes i fixed it, it was sending string inside string. I do have access to the database server. – Yagiz Degirmenci Jul 04 '20 at 08:13
1

@YagizcanDegirmenci You have a couple of choices, then. You can substitute the `LoggingConnection`, or you can enable query logging on the server: http://www.microhowto.info/howto/log_all_queries_to_a_postgresql_server.html – Mike Organek Jul 04 '20 at 08:27
@YagizcanDegirmenci Sorry. You can add `print(cursor.query)' after your execute to see what was actually sent to the server. Can you please edit to add that in your question? – Mike Organek Jul 04 '20 at 08:48
1

@YagizcanDegirmenci You added the query string that came in the http request. Can you please add the query that is being sent to PostgreSQL? You can get it by putting a `print(cursor.query)` immediately after the `execute()` call. I will update my answer to show what I mean. – Mike Organek Jul 04 '20 at 09:03
Ups. changed that sorry. – Yagiz Degirmenci Jul 04 '20 at 09:08
@YagizcanDegirmenci Is that the complete query? It does not include the `end_date` criterion. Can you please post the block that generated that query? – Mike Organek Jul 04 '20 at 09:12
Yup, thats the complete query. Also added web info – Yagiz Degirmenci Jul 04 '20 at 09:15
Thanks for everything @Mike Organek, i didn't changed anything but it started working somehow.. – Yagiz Degirmenci Jul 05 '20 at 22:00

Postgresql & Psycopg2 unable to properly select data between two dates

1 Answers1