how to make pandas.read_sql() not convert all headers to lower case
I think PyPyODBC does it for you:
Here what i found in the source code of PyPyODBC
ver. 1.3.3 lines: 28-29:
version = '1.3.3'
lowercase=True
and lines 1771-1772:
if lowercase:
col_name = col_name.lower()
so you can change the behaviour if you want:
import pypyodbc
pypyodbc.lowercase = False # force the ODBC driver to use case-sensitive column names
I know the question uses SQL Server and PyODBC, but for everyone that comes here via Google and uses PostgreSQL / psycopg2 instead: PostgreSQL automatically converts unquoted column names to lowercase, so if you have a query like
SELECT foo AS MY_FOO FROM some_table
then you will get back a my_foo
column from pd.read_sql
.
To get back the intended spelling, quote the column alias as follows:
SELECT foo AS "MY_FOO" FROM some_table
Note, however, that this only works without problems for aliases. With regards to the actual column name you have to use the spelling used when the column was created, which will probably be lowercase (either on purpose or via auto-conversion).
See this SO question for details.