0

I think to fulfill the syntax requirements and already tried a lot...

I have subsequent variables set up:

db_uri = "postgres://{}:{}@{}/{}".format(user, pwd, server, db)
engine = create_engine(db_uri)
con = engine.connect()

What already works:

df_sql = pd.read_sql_table('TABLE', engine)

What also works: 

 query = 'SELECT * FROM "TABLE" WHERE id_column = 12564993'
 df = pd.read_sql_query(query, con)

But when I change the id_column to a date_column nothing works anymore:

query = 'SELECT * FROM "TABLE" WHERE CAST(ts_column as date) = ts_column "2019-06-19"'
df = pd.read_sql_query(query, con)

Indepently from all syntax options available I get an error code:

ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near ""2019-06-19""
LINE 1: ...LECT * FROM "TABLE" WHERE CAST(ts_column as date) = ts_column "2019-06-1...

There is a ^ below the " of "2019-06.1... Any idea what to fix? I consulted the docs and searched for any kind of conditional where statement topic, but I still don't get it. Why can't I just select a specific date attribute to get matching rows?

Christian
  • 515
  • 1
  • 6
  • 17
  • Double quotes are used for quoted identifiers, not literals. Use single quotes. Also, why would you prefix the literal with the column name (ts_column)? – Ilja Everilä Aug 09 '19 at 10:48
  • Since I keep that SQL statement in a variable I have to use ' in the beginning as well as end. When I use ' in between, it breaks my statement. My table name is also capitalized. If you want to use capitalized tables in a select statement, you have to use " instead of none or '. – Christian Aug 09 '19 at 10:58
  • Please start by declaring your Postgres version and your actual table definition (`CREATE TABLE` statement). – Erwin Brandstetter Aug 09 '19 at 11:56
  • 1
    Either escape the single quote in the SQL statement that is in your **Python** string literal, or use triple quotes (in Python). – Ilja Everilä Aug 09 '19 at 12:07
  • @Erwin I created that table directly from Pandas. The datatype of that tables was: "datetime64[ns]" and is only containing the date without time information. – Christian Aug 09 '19 at 12:40
  • @Ilja, you are right. Triple quotations are working! – Christian Aug 09 '19 at 12:41
  • 1
    @Christian: you can get the relevant information in psql with `\d "TABLE" `. (And rather don't use "TABLE" as table name. Ever. https://stackoverflow.com/a/20880247/939860 ) – Erwin Brandstetter Aug 09 '19 at 12:58

2 Answers2

0

What type of syntax is this?

WHERE CAST(ts_column as date) = ts_column "2019-06-19"'

You can write this as:

WHERE CAST(ts_column as date) = '2019-06-19'

Or more colloquially in Postgres as:

WHERE ts_column::date = '2019-06-19'::date
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • since I declared a variable containing the query string, I have to use: '. `WHERE CAST(ts_column as date) = '2019-06-19'`: Throws a syntax error. As well as your last recommendation. That is why I used ". Using ' are breaking my string declaration. – Christian Aug 09 '19 at 10:55
  • @Christian . . . Both of these work fine in Postgres. Are you sure you are using Postgres? In fact the first should work in just about any database. – Gordon Linoff Aug 09 '19 at 12:07
  • Yes, its definetly Postgres :D – Christian Aug 09 '19 at 12:28
0

Thanks, subsequent definitions are working: 

query = '''SELECT * FROM "TABLE" WHERE ts_date::date = date '2019-06-19' '''
query = '''SELECT * FROM "TABLE" WHERE ts_date::date = '2019-06-19' '''

Required syntax:

  1. The table name has to be surrounded by double quotes since the table is name is 100% capitalized. Single quotes aren't working here. I found it somewhere, but don't find the link anymore. Sorry.
  2. Triple quotes for the statement itself seem to be the only reliable variant.
  3. Between the triple quotes and single quote for the date attribute has to be a whitespace. Without whitespace the query doesn't work.
Christian
  • 515
  • 1
  • 6
  • 17