【SQL Server】【Python】pyodbcでSQL Serverのテーブルからデータを取得する

CSVなどをデータ分析や可視化する際に個人的によくPythonのPandasを利用するのですが、SQLServer2016内に格納してあるデータを直接参照し、集計やグラフ作成する必要がありましたのでやってみました。
※Python Anacondaを事前にインストール済みです。

事前にpyodbcをインストール

pip install pyodbc

SQLを実行

import pyodbc
import pandas as pd

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )
df = pd.read_sql(
                '''SELECT * FROM [dbo].[EMPLOYEES]'''
                ,conn
                ,index_col = 'HIRE_DATE'
                ,parse_dates = 'HIRE_DATE'
                )
print df.head(10)

ストアドプロシージャを実行

import pyodbc
import pandas as pd

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )

query = 'EXEC sp_test'
df1 = pd.read_sql(
                query
                ,conn
                ,index_col = 'HIRE_DATE'
                ,parse_dates = 'HIRE_DATE'
                )
print df1.head(10)

ストアドプロシージャ（引数あり）を実行

import pyodbc
import pandas as pd

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )

query = 'EXEC sp_test2 @Param = 2'
df2 = pd.read_sql(
                query
                ,conn
                ,index_col = 'HIRE_DATE'
                ,parse_dates = 'HIRE_DATE'
                )
print df2.head(10)

resampleを使用して歯抜けなく1年ごとの各値の平均をとる等、SQLだけでやるより簡単に行えて便利です。

import pyodbc
import pandas as pd

pd.set_option('line_width', 100)

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )

query = 'EXEC sp_test2 @Param = 2'
df2 = pd.read_sql(
                query
                ,conn
                ,index_col = 'HIRE_DATE'
                ,parse_dates = 'HIRE_DATE'
                )

df_mean = df2.resample('1A', loffset = '1A').mean().fillna(0)
print df_mean.head(10)

集計から可視化まで

import pyodbc
import pandas as pd
import seaborn as sns

pd.set_option('line_width', 100)

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )

query = 'EXEC sp_test2 @Param = 2'
df2 = pd.read_sql(
                query
                ,conn
                ,index_col = 'HIRE_DATE'
                ,parse_dates = 'HIRE_DATE'
                )

df_mean = df2.resample('1A', loffset = '1A').mean().fillna(0)

# create graph(SALARY)
df_mean.plot.bar(
            x= [df_mean.index],
            y= [r'SALARY'], alpha=0.5, figsize=(10,5))