How to write PostgreSQL queries in Python without uppercase/lowercase problems?

Question

When I write a PostgresSQL engine query in Python using the sqlalchemy toolkit, I get errors such as 'column name does not exist'.

For example:

df = pd.read_sql_query('SELECT Descriptor FROM data LIMIT 3', engine)

But with the following modification there is no error:

df = pd.read_sql_query('SELECT \"Descriptor\" FROM data LIMIT 3', engine)

Is there any way I can avoid having to do this additional step (i.e., adding slashes) by perhaps using some type of global setting?

if you are using sqlalchemy why are you writing raw queries like this? — e4c5, Dec 17 '16 at 06:52
@e4c5 my friend, I am following this [tutorial](https://plot.ly/python/big-data-analytics-with-pandas-and-sqlite/) since I am a new learner to sql and sqlalchemy. Can you suggest an example of an alternative way to write the query I have in my initial post. thank you. — user121, Dec 17 '16 at 06:56
@teppic thank you for the suggestion. I have thought of this, is there any other alternative? Is postgres sensitive to uppercase/lowercase? — user121, Dec 17 '16 at 06:58
@R.A.P: You already have an answer to address that. [The tutorial you link to](https://plot.ly/python/big-data-analytics-with-pandas-and-sqlite/) is not even targeted at Postgres to begin with. — Erwin Brandstetter, Dec 17 '16 at 06:59
@ErwinBrandstetter thank you Erwin, yes, I am experimenting with postgres instead of sqlite. — user121, Dec 17 '16 at 07:05

score 1 · Accepted Answer · edited May 23 '17 at 11:45

Use legal, lower-case identifiers. Then you don't need double-quotes to preserve mixed-case spelling for identifiers.

Are PostgreSQL column names case-sensitive?

The tutorial you link to is targeted at SQLite, not Postgres. But all the identifiers would work in Postgres all the same if you did not create tables and columns with double-quotes to preserve mixed-case spelling, since unquoted identifiers are lower-cased automatically in Postgres.

How to write PostgreSQL queries in Python without uppercase/lowercase problems?

1 Answers1