2

I am using Jupyter Notebooks and have connected my PostreSQL database like so:

%load_ext sql
from sqlalchemy import create_engine

I am using Python's datetime and the pytz library to create a timezone aware datetime object:

import pandas as pd
import datetime as dt
import pytz
from pytz import timezone

eastern = timezone('EST')

now = dt.datetime.now()
start = dt.datetime(now.year, now.month, now.day - 2, now.hour, now.minute, tzinfo=eastern)

Now when I am trying to make an SQL query, I do something like this to create a pandas DataFrame:

events_query = pd.read_sql(
    """\
        SELECT start_time,
        end_time,
        FROM events 
        WHERE room_id=2 
        AND deleted=false
        AND start_time < {}
        limit 3
    """.format(start), engine)

However, on the line start_time < {}, I get this error:

ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "14"
LINE 10:         and start_time < 2020-06-16 14:07:00-05:00

However, when I check the types and comparison values of the SQL start_time datatime object and my created Python datetime object, the comparison works normally (if I omit the start_time < {} comparison):

test = events_query['start_time'].values[0]
print(test)
print(start)
print(test > start)

This will print out:

2038-11-25 12:00:00-05:00
2020-06-16 14:07:00-05:00
True

What I have tried alternatively Instead of using .format() notation, I have also tried to use Python f-strings to insert the start variable, as well as using the % operator formatting. The error was the same for comparing start_time with a variable.

Sorry, this is my second time ever posting on SO, let me know if I formatted my question correctly.

Nurzh
  • 21
  • 3
  • 1
    The correct way is likely to _not_ use string formatting.. The error is because the literal SQL supplied is invalid (eg. log the out of the result of `"..".format(..)`). Does PD have a notion of "place holders"? – user2864740 Jun 18 '20 at 19:00
  • 1
    Indeed, it does. See the `params` of [read_sql](https://pandas.pydata.org/pandas-docs/version/0.22.0/generated/pandas.read_sql.html) and refer to the Pg driver documentation. Internally, Pg should be able to coerce the ISO date with offset (although the rules for such depend on the type of `start` column, etc). – user2864740 Jun 18 '20 at 19:08
  • 1
    @user2864740 wow, thank you so much! Worked perfectly. After your answer, I found exactly what I needed [here](https://stackoverflow.com/questions/24408557/pandas-read-sql-with-parameters). – Nurzh Jun 18 '20 at 19:11

0 Answers0