3

related: pandas read_sql drops dot in column names

I use pandas.read_sql to create a data frame from an sql query from a postgres database. some column aliases\names use mixed case, and I want it to propagate to the data frame. however, pandas (or the underlining engine - SQLAlchemy as much as I know) return only lower case field names.

is there a workaround? (besides using a lookup table and fix the values afterwards)

Community
  • 1
  • 1
Ophir Yoktan
  • 8,149
  • 7
  • 58
  • 106
  • You can call `df.rename(columns=some_dict,inplace=True)` to rename the cols but I suspect that the loss of case is not pandas doing – EdChum Feb 29 '16 at 08:59
  • this is my fallback plan, but I prefer not loosing the case information in the 1st place. – Ophir Yoktan Feb 29 '16 at 09:02
  • Are you using pypyodbc? This fixed the problem for me (Python 2 to Python 3 migration) https://stackoverflow.com/questions/36314255/how-to-make-pandas-read-sql-not-convert-all-headers-to-lower-case – Franz Forstmayr May 29 '17 at 14:35

1 Answers1

4

Postgres normalizes unquoted column names to lower case. If you have such a table:

create table foo ("Id" integer, "PointInTime" timestamp);

PostgreSQL will obey the case, but you will have to specify table names quoted as such:

select "Id", "PointInTime" from foo;

A better solution is to add column aliases, eg:

select name as "Name", value as "Value" from parameters;

And Postgres will return properly cased column names. If the problem is SQLAlchemy or pandas, then this will not suffice.

hruske
  • 2,205
  • 19
  • 27