読者です 読者をやめる 読者になる 読者になる

小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

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

SQL Server Python

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

事前にpyodbcをインストール
pip install pyodbc

f:id:utiowa:20170212222804p:plain

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)

f:id:utiowa:20170212223058p:plain

ストアドプロシージャを実行
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)

f:id:utiowa:20170212223158p:plain

ストアドプロシージャ(引数あり)を実行
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)

f:id:utiowa:20170212223401p:plain


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)

f:id:utiowa:20170212223920p:plain

集計から可視化まで
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)) 

f:id:utiowa:20170212225116p:plain