Pandas Tutorial | Hedaro >

Lesson 8¶

*These tutorials are also available through an email course, please visit http://www.hedaro.com/pandas-tutorial to sign up today.*

How to pull data from a microsoft sql database

In [1]:

# Import libraries
import pandas as pd
import sys
from sqlalchemy import create_engine, MetaData, Table, select, engine

In [2]:

print('Python version ' + sys.version)
print('Pandas version ' + pd.__version__)

Python version 3.7.4 (default, Aug  9 2019, 18:34:13) [MSC v.1915 64 bit (AMD64)]
Pandas version 1.0.5

Version 1¶

In this section we use the *sqlalchemy* library to grab data from a sql database. Make sure to use your own *ServerName, Database, TableName*.

In [3]:

# Parameters
TableName = "data"

DB = {
    'drivername': 'mssql+pyodbc',
    'servername': 'DAVID-THINK',
    #'port': '5432',
    #'username': 'lynn',
    #'password': '',
    'database': 'BizIntel',
    'driver': 'SQL Server Native Client 11.0',
    'trusted_connection': 'yes',  
    'legacy_schema_aliasing': False
}

# Create the connection
engine = create_engine(DB['drivername'] + '://' + DB['servername'] + '/' + DB['database'] + '?' + 'driver=' + DB['driver'] + ';' + 'trusted_connection=' + DB['trusted_connection'], legacy_schema_aliasing=DB['legacy_schema_aliasing'])
conn = engine.connect()

# Required for querying tables
metadata = MetaData(conn)

# Table to query
tbl = Table(TableName, metadata, autoload=True, schema="dbo")
#tbl.create(checkfirst=True)

# Select all
sql = tbl.select()

# run sql code
result = conn.execute(sql)

# Insert to a dataframe
df = pd.DataFrame(data=list(result), columns=result.keys())

# Close connection
conn.close()

print('Done')

Done

Select the contents in the dataframe.

In [4]:

df.head()

Out[4]:

	Date	Symbol	Volume
0	2013-01-01	A	0.00
1	2013-01-02	A	200.00
2	2013-01-03	A	1200.00
3	2013-01-04	A	1001.00
4	2013-01-05	A	1300.00

In [5]:

df.dtypes

Out[5]:

Date      datetime64[ns]
Symbol            object
Volume            object
dtype: object

Convert to specific data types. The code below will have to be modified to match your table.

Version 2¶

In [6]:

import pandas.io.sql
import pyodbc

In [7]:

# Parameters
server = 'DAVID-THINK'
db = 'BizIntel'

# Create the connection
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + DB['servername'] + ';DATABASE=' + DB['database'] + ';Trusted_Connection=yes')

# query db
sql = """

SELECT top 5 *
FROM data

"""
df = pandas.io.sql.read_sql(sql, conn)
df.head()

Out[7]:

	Date	Symbol	Volume
0	2013-01-01	A	0.0
1	2013-01-02	A	200.0
2	2013-01-03	A	1200.0
3	2013-01-04	A	1001.0
4	2013-01-05	A	1300.0

Version 3¶

In [8]:

from sqlalchemy import create_engine

In [9]:

# Parameters
ServerName = "DAVID-THINK"
Database = "BizIntel"
Driver = "driver=SQL Server Native Client 11.0"

# Create the connection
engine = create_engine('mssql+pyodbc://' + ServerName + '/' + Database + "?" + Driver)

df = pd.read_sql_query("SELECT top 5 * FROM data", engine)
df

Out[9]:

	Date	Symbol	Volume
0	2013-01-01	A	0.0
1	2013-01-02	A	200.0
2	2013-01-03	A	1200.0
3	2013-01-04	A	1001.0
4	2013-01-05	A	1300.0

This tutorial was created by HEDARO