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
– ilmarinen, Commented Jul 4, 2020 at 6: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
– Mike Organek, Commented Jul 4, 2020 at 6:49
Should i use bind paramters in every query @MikeOrganek? Because i use f strings in my every cursor.execute() — Yagiz Degirmenci
– Yagiz Degirmenci, Commented Jul 4, 2020 at 7: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
– Mike Organek, Commented Jul 4, 2020 at 7:15

Mike Organek · Accepted Answer · 2020-07-04 09:04:48Z

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 4, 2020 at 9:04

answered Jul 4, 2020 at 7:22

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

10 Comments

Yagiz Degirmenci Over a year ago

Thanks, now i added queries instead of f strings everything is working but i am still unable to get the dates properly.

Mike Organek Over a year ago

@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?

Yagiz Degirmenci Over a year ago

Yes i fixed it, it was sending string inside string. I do have access to the database server.

Mike Organek Over a year ago

@YagizcanDegirmenci You have a couple of choices, then. You can substitute the LoggingConnection, or you can enable query logging on the server: microhowto.info/howto/…

Mike Organek Over a year ago

@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.

|

Collectives™ on Stack Overflow

Postgresql & Psycopg2 unable to properly select data between two dates

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related