Python と Pyodbc を使用して Microsoft SQL Server に接続する

pyodbc を使うと、ODBC 経由で Python アプリケーションを Microsoft SQL Server に接続できます。Python パッケージと互換性のある SQL Server 用 ODBC ドライバーの両方をインストールし、サーバー、データベース、ドライバー、認証の設定を pyodbc.connect() に渡します。このガイドでは SQL を直接実行します。データベースのテーブルを Python オブジェクトとして扱いたい場合は、ORM を使う方法もあります。

pyodbc と SQL Server 用 ODBC ドライバーをインストールする

アプリケーションを実行する Python 環境に pyodbc をインストールします。たとえば、python -m pip install pyodbc を実行します。これで Python インターフェースはインストールされますが、Microsoft の ODBC ドライバーはインストールされません。プログラムを実行するマシンまたはコンテナーに SQL Server 用 ODBC ドライバーを別途インストールし、その名前が実際にインストールされたドライバー名と完全に一致することを確認してください。よく使われるドライバー名は ODBC Driver 18 for SQL Server ですが、システムで利用できる名前を指定します。

接続前に、サーバーのアドレスとデータベース名を確認してください。サーバーにはホスト名、アドレス、または db.example.net\SQLEXPRESS のようなホスト名とインスタンス名を指定できます。ホスティング環境によってはポートも必要です。選択したデータベースへのアクセス権をアカウントに付与するよう、管理者に依頼する必要がある場合もあります。SQL Server の接続情報は Python の作業ディレクトリとは無関係ですが、ドライバーはオペレーティングシステムの ODBC マネージャーから利用できなければなりません。

ドライバーと信頼された認証を使って接続する

統合認証が設定された Windows 環境では、Trusted_Connection=yes を指定したドライバーベースの接続文字列を使います。ドライバー、サーバー、データベースの値は、利用環境に合わせて置き換えてください。次の短いプログラムは接続文字列を組み立てて表示するため、各項目がどのように連結されるかを確認できます。データベースには接続しません。出力ラベルがある場合も、コード例のバイト一致を保つため英語のままです。

次の例を実行します。

driver = "ODBC Driver 18 for SQL Server"
server = "db.example.net"
database = "Sales"
connection_string = (
    f"Driver={{{driver}}};"
    f"Server={server};"
    f"Database={database};"
    "Trusted_Connection=yes;"
)
print(connection_string)

Driver={ODBC Driver 18 for SQL Server};Server=db.example.net;Database=Sales;Trusted_Connection=yes;

アプリケーションから接続するには、その文字列を pyodbc.connect(connection_string) に渡します。統合認証ではプロセスに割り当てられた ID が使用され、オペレーティングシステム、ドライバー、SQL Server の設定に依存します。SQL Server のユーザー名とパスワードを渡す方法とは異なります。TLS の設定は組織の証明書ポリシーに従ってください。ドライバーのバージョンによって暗号化の既定値が異なる場合があるため、接続を成功させる目的で証明書の検証を無効にしないでください。

DSN と認証情報を使って接続する

データソース名（DSN）は、ホスト上で作成する ODBC 設定です。サーバーやドライバーの共通設定を保持できるため、アプリケーションには DSN 名と、必要に応じて認証情報を渡します。接続文字列は DSN=SalesReadOnly;UID=app_reader;PWD=<secret>; の形式です。DSN は同じ実行環境に存在し、Python プロセスから参照できる必要があります。対話型ユーザー向けに設定した DSN は、サービスアカウントやコンテナーから利用できない場合があります。

認証情報はソース管理に登録せず、シークレットマネージャーまたは環境ごとの設定を通じて渡してください。シークレットに特殊文字が含まれる場合は、ODBC 接続文字列でサポートされている引用符の規則を使うか、認証情報を別途設定します。そうしないと、セミコロンが項目の区切りとして解釈されることがあります。パスワードがログに漏れる可能性があるため、完成した接続文字列を出力しないでください。DSN 文字列を使って同じ pyodbc.connect(...) を呼び出し、DSN とサーバーの設定に応じて統合認証または SQL 認証を選びます。

クエリを実行してリソースを解放する

接続したらカーソルを作成して SQL を実行し、返された行を取得するか順に処理します。ユーザーやアプリケーションのデータから値を渡す場合は、パラメーターマーカーを使ってください。pyodbc では位置指定マーカーに ? を使います。値を SQL 文とは別に渡すことで、両者を分離できます。パラメーターに指定できるのはデータ値であり、テーブル名や列名ではありません。識別子を切り替える必要がある場合は、固定の許可リストから選び、SQL Server の規則に従って引用符で囲んでください。

一般的な処理の流れは、connection = pyodbc.connect(connection_string) で接続し、続けて cursor = connection.cursor() と cursor.execute("SELECT name FROM dbo.Customers WHERE customer_id = ?", customer_id) を実行する形です。行を処理するには cursor を反復処理するか、結果がメモリに収まる程度に小さい場合は fetchall() を呼び出します。結果が大きい場合は、行を少しずつ処理してください。このクエリは、指定したテーブルと列が選択したデータベースに存在することを前提としています。実際のスキーマに合わせて置き換えてください。

connection = pyodbc.connect(connection_string) が成功したら、カーソルの処理を try ブロック内で行い、その finally 節で接続を閉じます。形式は try: ... finally: connection.close() です。カーソルを解放するには、このブロック内で with connection.cursor() as cursor: を使います。カーソルのコンテキストを抜けるとカーソルが閉じます。with connection: のような pyodbc 接続コンテキストはトランザクションの範囲を管理します。正常終了時にはコミットし、例外が外へ伝播した場合はロールバックしますが、接続自体は閉じません。try 内でトランザクションとカーソルのコンテキストを組み合わせ、最後に finally で connection.close() を呼び出せます。これにより、接続の確立後は、正常終了時も例外発生時もクエリ処理の後に接続が閉じられます。データを変更する場合は、どこでトランザクションをコミットするかを明示的に決めてください。この使い方には設定済みの SQL Server、ドライバー、スキーマが必要です。この記事の例は実行していません。

よくある接続エラーを診断する

pyodbc がドライバーを見つけられないと報告した場合は、プログラムの実行環境に ODBC ドライバーがインストールされていることと、Driver={...} の値が登録済みのドライバー名（空白やバージョンを含む）と一致していることを確認します。サーバーに接続できない場合は、DNS やホスト名の綴り、ポートとインスタンスの設定、ファイアウォールのアクセス、SQL Server がネットワーク接続を受け付けているかを確認してください。タイムアウトは通常、接続性またはサーバー負荷の問題を示します。タイムアウト値を増やしても、接続先が間違っていれば解決しません。

ログインに失敗する場合は、接続で実際に使われている ID を確認してください。信頼された認証ではプロセスの ID が使われますが、UID と PWD は SQL 認証情報を要求します。この方式を取り違えると、誤った ID が使われることがあります。続いて、データベースへのアクセス権とデータベース名を確認します。証明書や暗号化のエラーが出た場合は、検証を弱めるのではなく、サーバー証明書のチェーンとドライバーの暗号化設定を管理者に確認してください。最後に、接続文字列のセミコロン抜け、ドライバー名を囲む波括弧の不整合、キーへの不要な空白、エスケープされていない特殊文字を確認します。診断ログは役立つ情報を残しつつ、パスワードなどのシークレットを必ず伏せてください。

ドライバーと接続文字列の詳細は、pyodbc の SQL Server 接続ガイドとpyodbc の公式ドキュメントを参照してください。クエリ結果を読み取る関連例として、Python で fetchall() を使ってデータベースから要素を取得する方法も参照できます。日本語のリンク先は提供されたインベントリにないため、relref の英語の参照先をそのまま維持しています。