今回はPythonからSQLServer データベースに接続し、データを更新したり取得する方法について解説します。
Microsoft からは無料版のSQLServerも公開されていますが、さすがに趣味ではあまり使わないかと思いますので、あくまでも仕事で必要に迫られたという前提です。
SQLServer というデータベースについて基本的な知識があり、Pythonから接続する方法を知りたいという方が対象の記事になっています。
接続する前の準備
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 pyodbcAnacondaやMinicondaでPythonをインストールして使っている方DOSプロンプトから下記コマンドを実行します。
conda install pyodbcSQLServerに接続し、SQLを実行するための手順
SQLやDDLを実行するための手順は、SQLite、PostgreSQL、SQLServer など他のデータベースと同様です。
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ちなみに、SQLServerのデフォルトのポートは 1433 になります。
pyodbc は名前の通りODBCドライバを利用するパッケージであり、接続文字列はODBC用の接続文字列の記述ルールに合わせなければなりません。
DRIVER＝は、ODBCドライバを指定する記述ですが、冒頭で Microsoft ODBC Driver for SQL Server on SQLServer をインストールしたことにより、下記の様にドライバが使える状態になっています。
DRIVER={SQL Server} という記述によりODBCドライバを使う宣言をしています。もしこの記述でエラーになる場合、ODBCドライバの名前（上記の例では、 ODBC Driver 17 for SQL Server）を指定して下さい。
複数バージョンのドライバがインストールされており、どれを使うかを指定する場合も同様です。
接続文字列が完成したら、connect メソッドの引数に指定するだけでSQLServerに接続出来るようになります。
#SQLServer接続のサンプル
constr = "DRIVER={SQL Server};SERVER=myserver;PORT=1433;DATABASE=mydatabase;UID=yamada;PWD=hoge1234"
conn = pyodbc.connect(constr)ちなみに、接続文字列に変数の値を埋め込むために、文字列の先頭に fを付けて変数名を{ } で括っています。
また、接続文字列中のドライバを指定する {SQL Server} という記述は、{{SQL Server}}という具合に２つ連ねてエスケープしています。
host='myserver'
port=1433
database='mydatabase'
user='yamada'
password='hoge1234'
constr=pyodbc.connect(f"DRIVER={{SQL Server}};SERVER={host};PORT={port};DATABASE={dbname};UID={user};PWD={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()データの更新、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()下記は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()まとめ
今回は Python を使って SQLServerに接続するために必要な Microsoft ODBC Driver for SQL Server on Windows のインストール方法 、pyodbcのインストール方法、そして SQLServer に接続した後にデータの抽出や更新を行うための簡単なサンプルを紹介しまた。
Pythonのデータベース接続はデータベースの種類によって少しづつ異なりますが、接続した後の手順（データ抽出、データ更新、DDL発行）は同じです。
この記事がPythonで SQLServer に接続するための一助になれば幸いです。
コメント