ODBC Connection in Python

This Jupyter notebook demonstrates a connection and query in Python to a SQL Server research database hosted by Chicago Booth.

Compatability: Python 2 and 3 (see pyodbc module requirements)

Requires: pyodbc, pandas

Create ODBC connection

Import required modules, create a connection to the database and create a cursor object. Remember to close your cursor and database connection when finished (see last section of this notebook).

import os
import pyodbc
import pandas as pd
import textwrap
import getpass

Database: QA Direct

# This will prompt you for your Booth password
conn = pyodbc.connect(driver='{FreeTDS}',
                      server='bushtrqa01.chicagobooth.edu',
                      port=1433,
                      database='qai',
                      uid=r'gsb\{0}'.format(os.getenv('USER')),
                      pwd=getpass.getpass('\n\nEnter Booth password> '))

Database: Capital IQ

conn = pyodbc.connect(driver='{FreeTDS}',
                      server='bushcapiq01.chicagobooth.edu',
                      port=1433,
                      database='IvyDBBooth',
                      uid=r'gsb\{0}'.format(os.getenv('USER')),
                      pwd=getpass.getpass('\n\nEnter Booth password> '))

Execute query using cursor

Query a short list of available tables, store the result in cursor object and display results.

cursor = conn.cursor()

sql_query = "SELECT TOP 10 table_name FROM information_schema.tables WHERE table_type = 'base table' ORDER BY table_name ASC"
rows = cursor.execute(sql_query)
table1 = rows.fetchone()
for row in rows:
    print(row)

(u'A4CatData_changes', )
(u'A4CmpChg', )
(u'A4CmpChg_changes', )
(u'A4CmpFData', )
(u'A4CmpFData_changes', )
(u'A4CmpIData', )
(u'A4CmpIData_changes', )
(u'A4CmpInfo', )
(u'A4CmpInfo_changes', )

cursor.close()

Execute query using pandas

Query a table, store the result in a pandas dataframe and display results.

sql_query = textwrap.dedent("""
SELECT TOP 10 table_name FROM information_schema.tables WHERE table_type = 'base table' ORDER BY table_name ASC
""")

df = pd.read_sql(sql_query, conn)
print(df)

           table_name
0           A4CatData
1   A4CatData_changes
2            A4CmpChg
3    A4CmpChg_changes
4          A4CmpFData
5  A4CmpFData_changes
6          A4CmpIData
7  A4CmpIData_changes
8           A4CmpInfo
9   A4CmpInfo_changes

Close ODBC connection

conn.close()