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()