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?

score 12 · Accepted Answer · edited May 16 '17 at 18:56

12

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

edited May 16 '17 at 18:56

Community

1
1

answered Mar 31 '16 at 13:10

MaxU - stand with Ukraine

205,989
36
386
419

Ah I see! That's dangerous territory but I might try haha. Thank you – jjvandermade Mar 31 '16 at 14:29
changed lowercase to False and re-installed that custom package. This worked. Thank you so much! – jjvandermade Mar 31 '16 at 15:58
@jjvandermade, always happy to help :) – MaxU - stand with Ukraine Mar 31 '16 at 16:12
1

It's interesting that PyODBC [also has the `lowercase` option](https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API#lowercase) but defaults to `False`. – Nickolay Dec 13 '19 at 13:35

score 4 · Answer 2 · answered Jul 02 '20 at 10:35

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.

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

2 Answers2

Linked