【 Python 】pyodbcでSQLServerにアクセスしよう！

今回はPythonからSQLServer データベースに接続し、データを更新したり取得する方法について解説します。

Microsoft からは無料版のSQLServerも公開されていますが、さすがに趣味ではあまり使わないかと思いますので、あくまでも仕事で必要に迫られたという前提です。

SQLServer というデータベースについて基本的な知識があり、Pythonから接続する方法を知りたいという方が対象の記事になっています。

接続する前の準備
1. Microsoft ODBC Driver for SQL Server on Windowsのインストール
2. pyodbc のインストール
SQLServerに接続し、SQLを実行するための手順
まとめ

接続する前の準備

Python から SQLServerに接続するには、次の手順が必要です。

Microsoft ODBC Driver for SQL Server on Windowsのインストール
Pythonのpyodbcパッケージのインストール
Pythonプログラム上で pyodbcのインポート

尚、Microsoft ODBC Driver　は Visual Studio 等の開発ツール類のインストールによって、自動でインストールされている可能性もあります。

Microsoft ODBC Driver for SQL Server on Windowsのインストール

Microsoft ODBC Driver for SQL Server on WindowsはMicrosoftのサイトからダウンロードできます。

下記のファイルがダウンロードされますのでダブルクリックして実行して下さい。

msodbcsql.msi

pyodbc のインストール

Python公式サイトからPythonをダウンロードして使っている方は、DOSプロンプトから下記コマンドを実行します。

pip install pyodbc

1	pip install pyodbc

AnacondaやMinicondaでPythonをインストールして使っている方DOSプロンプトから下記コマンドを実行します。

conda install pyodbc

1	conda install pyodbc

SQLServerに接続し、SQLを実行するための手順

SQLやDDLを実行するための手順は、SQLite、PostgreSQL、SQLServer など他のデータベースと同様です。

import pyodbc

1	import pyodbc

SQLServer への接続

SQLServer に接続するためには、下記の仕様で接続文字列を作成し、connectメソッドに渡す必要があります。

DRIVER={{SQL Server}};SERVER=xxx;PORT=xxx;DATABASE=xxx;UID=xxx;PWD=xxx

XXXの部分は、サーバー名、ポート番号、データベース名、ユーザー名、パスワードに置き換える必要があります。

例えば、サーバー名=’myserver’、ポート=1433、データベース名=’mydatabase’、ユーザーID=’yamada’、パスワード=’hoge1234′ の場合は次のようになります。

DRIVER={SQL Server};SERVER=myserver;PORT=1433;DATABASE=mydatabase;UID=yamada;PWD=hoge1234

1	DRIVER={SQL Server};SERVER=myserver;PORT=1433;DATABASE=mydatabase;UID=yamada;PWD=hoge1234

ちなみに、SQLServerのデフォルトのポートは 1433 になります。

pyodbc は名前の通りODBCドライバを利用するパッケージであり、接続文字列はODBC用の接続文字列の記述ルールに合わせなければなりません。

DRIVER＝は、ODBCドライバを指定する記述ですが、冒頭で Microsoft ODBC Driver for SQL Server on SQLServer をインストールしたことにより、下記の様にドライバが使える状態になっています。

DRIVER={SQL Server} という記述によりODBCドライバを使う宣言をしています。

接続文字列が完成したら、connect メソッドの引数に指定するだけでSQLServerに接続出来るようになります。

#SQLServer接続のサンプル
constr = "DRIVER={SQL Server};SERVER=myserver;PORT=1433;DATABASE=mydatabase;UID=yamada;PWD=hoge1234"

conn = pyodbc.connect(constr)

#SQLServer接続のサンプル

constr = "DRIVER={SQL Server};SERVER=myserver;PORT=1433;DATABASE=mydatabase;UID=yamada;PWD=hoge1234"

conn = pyodbc.connect(constr)

ちなみに、関数の引数にホスト名やユーザID、パスワード等を指定して、関数内部で接続文字列を動的に作成する様な場合、format を使う事があります。

format はテンプレートとなる文字列の中に{0} {1} ・・・と記述し、format() の引数により{0}{1}・・・を置き換えてくれる便利なものなのですが、{ や } は特殊文字になるため、これを忘れてしまうとデータベース接続時にエラーになります。

この場合は DRIVER={{SQL Server}} という具合に { や } を２つ連ねて書くことで解決できます。

host='myserver'
port=1433
database='mydatabase'
user='yamada'
password='hoge1234'

constr=pyodbc.connect("DRIVER={{SQL Server}};SERVER={0};PORT={1};DATABASE={2};UID={3};PWD={4}".format(host,port,dbname,user,password))

host='myserver'

port=1433

database='mydatabase'

user='yamada'

password='hoge1234'

constr=pyodbc.connect("DRIVER={{SQL Server}};SERVER={0};PORT={1};DATABASE={2};UID={3};PWD={4}".format(host,port,dbname,user,password))

データの取得

SQLServerに対して select 文を発行し、データを取得する方法は次のようになります。

fetch には次の3種類が用意されていますので、状況に応じて使い分けます。

機能	メソッド	備考
全件取得	fetchall()	リスト形式で全件を返す。
1件づつ取得	fatchone()	データがあれば1行をタプル形式で返す。データが無くなれば None を返す。
指定件数づつ取得	fetchmeny(件数)

下記はSQLServerに接続し、条件に一致したデータを全件取得するサンプルです。

import pyodbc

constr = "DRIVER={SQL Server};SERVER=myserver;PORT=1433;DATABASE=mydatabase;UID=yamada;PWD=hoge1234"
conn = pyodbc.connect(constr)
cur = conn.cursor()
cur.execute('select * from hoge where id >= 10000')
res = cur.fetchall()
print(res)
cur.close()
conn.close()

import pyodbc

constr = "DRIVER={SQL Server};SERVER=myserver;PORT=1433;DATABASE=mydatabase;UID=yamada;PWD=hoge1234"

conn = pyodbc.connect(constr)

cur = conn.cursor()

cur.execute('select * from hoge where id >= 10000')

res = cur.fetchall()

print(res)

cur.close()

conn.close()

データの更新、DDLの実行

データの更新やDDLの実行は次のようになります。

トランザクションは、最初のSQL実行時に自動で開始されますので、明示的に記述する必要はありません。

もし何らかの事情で明示的に記述する場合は、conn.begin() と記述すればOKです。

トランザクションが自動で開始されるので、トランザクションを明示的に完了させないとデータが更新されません。

トランザクションを完了させるには、conn.commit() を、逆に無効にしたい場合は conn.rollback() メソッドを呼び出します。　

尚、DDLはロールバックが出来ないため、commit() は必要ありませんが、commit() をしてもエラーにはなりません。

下記はinsertのサンプルです。

import pyodbc

constr = "DRIVER={SQL Server};SERVER=myserver;PORT=1433;DATABASE=mydatabase;UID=yamada;PWD=hoge1234"
conn = pyodbc.connect(constr)
cur = conn.cursor()
cur.execute('insert into hoge(id,tag,cost) values('10001','ABCD',120)'
cur.execute('insert into hoge(id,tag,cost) values('10002','BCDE',250)'
cur.execute('insert into hoge(id,tag,cost) values('10003','CDEF',380)'
conn.commit()

cur.close()
conn.close()

import pyodbc

constr = "DRIVER={SQL Server};SERVER=myserver;PORT=1433;DATABASE=mydatabase;UID=yamada;PWD=hoge1234"

conn = pyodbc.connect(constr)

cur = conn.cursor()

cur.execute('insert into hoge(id,tag,cost) values('10001','ABCD',120)'

cur.execute('insert into hoge(id,tag,cost) values('10002','BCDE',250)'

cur.execute('insert into hoge(id,tag,cost) values('10003','CDEF',380)'

conn.commit()

cur.close()

conn.close()

下記はDDLのサンプルです。

import pyodbc

constr = "DRIVER={SQL Server};SERVER=myserver;PORT=1433;DATABASE=mydatabase;UID=yamada;PWD=hoge1234"
conn = pyodbc.connect(constr)
cur = conn.cursor()
cur.execute('create table mage(startday timestamp,item varchar2(100),val number(10,2))')
cur.close()
conn.close()

import pyodbc

constr = "DRIVER={SQL Server};SERVER=myserver;PORT=1433;DATABASE=mydatabase;UID=yamada;PWD=hoge1234"

conn = pyodbc.connect(constr)

cur = conn.cursor()

cur.execute('create table mage(startday timestamp,item varchar2(100),val number(10,2))')

cur.close()

conn.close()

まとめ

今回は Python を使って SQLServerに接続するために必要な Microsoft ODBC Driver for SQL Server on Windows のインストール方法、pyodbcのインストール方法、そして SQLServer に接続した後にデータの抽出や更新を行うための簡単なサンプルを紹介しまた。

Pythonのデータベース接続はデータベースの種類によって少しづつ異なりますが、接続した後の手順（データ抽出、データ更新、DDL発行）は同じです。

この記事がPythonで SQLServer に接続するための一助になれば幸いです。