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