CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
Yagiz Degirmenci
Yagiz Degirmenci

Reputation: 20766

Postgresql & Psycopg2 unable to properly select data between two dates

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"

Upvotes: 0

Views: 883

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

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'

Upvotes: 1

Related Questions