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
– e4c5, Commented Dec 17, 2016 at 6:52
@e4c5 my friend, I am following this tutorial 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
– user121, Commented Dec 17, 2016 at 6:56
@teppic thank you for the suggestion. I have thought of this, is there any other alternative? Is postgres sensitive to uppercase/lowercase? — user121
– user121, Commented Dec 17, 2016 at 6:58
@R.A.P: You already have an answer to address that. The tutorial you link to is not even targeted at Postgres to begin with. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 17, 2016 at 6:59

Community · Accepted Answer · 2017-05-23 11:45:44Z

1

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.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Dec 17, 2016 at 6:57

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related