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

.. code-block:: python

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

Database: QA Direct
^^^^^^^^^^^^^^^^^^^

.. code-block:: python

    # 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
^^^^^^^^^^^^^^^^^^^^

.. code-block:: python

    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.

.. code-block:: python

    cursor = conn.cursor()

.. code-block:: python

    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)


.. parsed-literal::

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


.. code-block:: python

    cursor.close()

Execute query using pandas
~~~~~~~~~~~~~~~~~~~~~~~~~~

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

.. code-block:: python

    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)


.. parsed-literal::

               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
~~~~~~~~~~~~~~~~~~~~~

.. code-block:: python

    conn.close()