Pandas read_sql Challenging syntax for postgres query

Question

I am querying a postgres db using python/pandas with sqlalchemy. I have an sql query that looks like this:

    SELECT table_name
    FROM fixed_602fcccd0f189c2434611b14.information_schema."tables" t 
    WHERE table_type='BASE TABLE'
    and table_schema='di_602fccd10f189c2434611be9'
    and (
    table_name like 'general_journal%'
    or table_name like 'output_journal_line%'
    or table_name like 'output_journal_tx%'
    or table_name like 'flats%'
    )

I've tested it in dBeaver and it works perfectly. I am now trying to pass the same query through pandas read_sql as follows:

from_string = pg_db + '.information_schema."tables"'
print(from_string)
pg_query = queries.id_tables.format(from_string,di_id)

The idea is that I construct the query with variables 'pg_db' (string) and 'di_id' (string) as I make a series of queries. The problem is the query returns empty array when done this way. No error is thrown.

I suspected the challenge is the "tables" attribute that when pandas interprets the query eg. strips off the ", but that doesn't actually seem to matter. Any thoughts on how to make this work with pandas?

UPDATE: I have tried parameterized and met with the same problem. It seems to boil down to the FROM parameter gets passed in with double quotes. I have tried to strip these but it looks like pandas appends them anyways. In principle double quotes should be fine according to postgres docs but that doesn't seem to be the case even when doing the query in dBeaver. If I pass in the query via pandas as it is written at the top of this post, no problem. The challenge is when I try to use variables for the FROM and table_schema parameters, I get syntax errors.

MikeB2019x · Accepted Answer · 2021-02-22 20:23:49Z

1

It turns out that the problem disappeared when I removed the parentheses I put around the 'or' statements. I think the message is to pay attention to how you construct the query eg. form and join all the strings and variables before passing them to pandas.

That said I have used parentheses with much more complex queries in pandas and they were not a problem.

answered Feb 22, 2021 at 20:23

MikeB2019x

1,3073 gold badges14 silver badges37 bronze badges

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

Comments

Paul Brennan · Accepted Answer · 2021-02-20 15:37:46Z

0

I would first suggest that you use a parameterized query for input but in some cases its just easier to use a built in function repr()

s = "SQL sometimes likes \"here\" and %s \"now\" problems"
print(repr(s))

gives

'SQL sometimes likes "here" and %s "now" problems'

answered Feb 20, 2021 at 15:37

Paul Brennan

2,7364 gold badges23 silver badges27 bronze badges

3 Comments

MikeB2019x Over a year ago

repr() causes a syntax error to be thrown. not familiar with parameterized query I will look in to that.

MikeB2019x Over a year ago

yup. repr() adds the single quotes and that messes up the syntax according to the error message

MikeB2019x Over a year ago

I updated the OP that I'm querying a postgres db and using sqlalchemy.

Collectives™ on Stack Overflow

Pandas read_sql Challenging syntax for postgres query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related