how to make pandas.read_sql() not convert all headers to lower case

Question

I have a function that pulls tables from our a table in our SQL server into a dataframe in Python, but it forces all the column headers to be lower case. The code is as follows:

connection = pypyodbc.connect('Driver={SQL Server};'
                              'Server='   + server   + ';'
                              'Database=' + database + ';'
                              'uid='      + username + ';'
                              'pwd='      + password + ';')
query = 'SELECT * FROM ' + tableName

#set dict value to dataframe imported from SQL
tableDict[tableName] = pd.read_sql(query, connection)

The headers in SQL are for example: pmiManufacturingHeadline_Level It shows up in my pandas dataframe as: pmimanufacturingheadline_level

Anyone have an idea how to make pandas.read_sql keep the original capitalization?

Answer 1

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.

Answer 2

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