mssql-pythonで接続を管理する

ほとんどのアプリケーションは単純なパターンに従います:接続を開き、クエリを実行し、接続を閉じます。 以下のセクションでは、接続の開閉、コンテキストマネージャーの使用、オートコミットの設定、接続属性の操作について説明します。

接続を開けてください

connect()機能を使って接続を確立してください。 サーバー、データベース、認証の詳細を含む接続文字列を渡します:

import mssql_python

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;Database=<database>;"
    "Authentication=ActiveDirectoryDefault;Encrypt=yes"
)

connect()関数は以下を受け入れます:

• 接続文字列を、最初の位置引数または connection_str キーワードとして指定します。
• ドライバーが接続文字列に統合する個別のキーワード。
• autocommit、timeout、attrs_beforeなどの他の選択肢もあります。

両方のアプローチを組み合わせて使うことができます。 キーワードは接続文字列内の値を上書きします。これは、基本となる接続文字列を構成に保存し、呼び出しごとに timeout などの設定を上書きする場合に便利です:

# Base connection string from config, with per-call overrides
conn = mssql_python.connect(
    "Server=<server>.database.windows.net;Database=<database>;"
    "Authentication=ActiveDirectoryDefault;Encrypt=yes",
    timeout=30,
    autocommit=True
)

接続を閉じる

接続が終わったら必ず閉じて接続プールに戻し、サーバーリソースを解放してください。 非クローズド接続はサーバー側のメモリを保持し、最終的に接続プールを使い果たし、新たな接続の試みがブロックされたり失敗したりすることもあります。

conn = mssql_python.connect(connection_string)
try:
    # Use the connection
    cursor = conn.cursor()
    cursor.execute("SELECT 1")
finally:
    conn.close()

一度閉じると、接続は使用できません:

conn.close()
print(conn.closed)  # True

# This raises an error
cursor = conn.cursor()  # InterfaceError: Cannot create cursor on closed connection

close()を複数回呼ぶのは安全です(冪等):

conn.close()
conn.close()  # No error

コンテキスト マネージャー

ほとんどのアプリケーションで with 文を使って接続管理を行ってください。 例外が発生しても、ブロックが終了した際にドライバーが接続を閉じることを保証します。 この方法は、忘れられた close() 通話による接続漏れのリスクを排除します。

with mssql_python.connect(connection_string) as conn:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE #Demo (Name NVARCHAR(50))")
    cursor.execute("INSERT INTO #Demo (Name) VALUES ('Widget')")
    conn.commit()  # Must commit explicitly when autocommit=False
# Connection automatically closed

コンテキストマネージャーは終了時に接続を閉じます。 トランザクションを自動的にコミットまたはロールバックする ことはありません :

• 常に: 例外が発生したかどうかにかかわらず、終了時に close() を呼び出します。
• close() 動作: autocommit=Falseの場合、接続が終了した際にコミットされていない変更はロールバックされます。
• 変更を永続させるには明示的にconn.commit()呼び出す必要があります。

この設計はPEP 249の動作に従い、誤って部分的なコミットを防ぎます。 もしコードが commit()に到達する前に例外を発生させた場合、進行中のトランザクションは安全にロールバックされます:

# Equivalent manual code:
conn = mssql_python.connect(connection_string)
try:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE #Demo (Name NVARCHAR(50))")
    cursor.execute("INSERT INTO #Demo (Name) VALUES ('Widget')")
    conn.commit()  # Must commit explicitly
finally:
    conn.close()  # Rolls back uncommitted changes if autocommit=False

自動コミット モード

デフォルトでは autocommit=Falseであり、各文は暗黙のトランザクション内で実行されます。 変更を永続させるには conn.commit() を呼び出すか、破棄するには conn.rollback() する必要があります。 暗黙のトランザクションは、複数の文を単一の原子操作にまとめられるため、データ改変に最も安全な選択肢です。

各文をすぐにコミットさせたいときはオートコミットを有効にしてください。 オートコミットは、DDL操作(CREATE TABLE、 ALTER INDEX)、読み取り専用ワークロード、またはトランザクションのグループ化が不要な管理スクリプトで有用です。

conn = mssql_python.connect(connection_string)
print(conn.autocommit)  # False

cursor = conn.cursor()
cursor.execute("CREATE TABLE #Demo (Name NVARCHAR(50))")
cursor.execute("INSERT INTO #Demo (Name) VALUES ('Widget')")
conn.commit()  # Required to persist changes

各文のオートコミットを有効にしてすぐにコミットしてください。 接続時に autocommit=True を使うか、 setautocommit() や直接プロパティ割り当てで接続した後に切り替えてください:

# At connection time
conn = mssql_python.connect(connection_string, autocommit=True)

# Or after connection (both forms work)
conn.setautocommit(True)
conn.autocommit = True
print(conn.autocommit)  # True

# Now changes are committed automatically
cursor = conn.cursor()
cursor.execute("SELECT TOP 1 Name FROM Production.Product")
print(cursor.fetchone().Name)
# No commit() needed

接続タイムアウト

接続タイムアウトを設定して、ドライバーがエラーを出すまでの待機時間を制御してください。 信頼性の低いネットワーク環境やサーバーに到達不能な際に高速で故障するアプリケーションには、合理的な接続タイムアウトが重要です。

# At connection time (in seconds)
conn = mssql_python.connect(connection_string, timeout=30)

# Or after connection
conn.timeout = 60
print(conn.timeout)  # 60

タイムアウト値が 0 の場合は、タイムアウトしません（無期限に待機します）。 本番環境での合理的なタイムアウトを定義すること;タイムアウトなしの接続がハングすると、呼び出しスレッドは永久にブロックされます。

接続属性

set_attr()を使って実行時に接続動作を修正できます。 接続属性はアクセスモード、トランザクション分離、パケットサイズなどの低レベルのドライバー設定を制御します。 ほとんどのアプリケーションではこれらの属性を変更する必要はありませんが、特定のシナリオでは有用です:

• 読み取り専用モード:レポートクエリの誤書き込みを防ぎます。
• トランザクション分離:同時トランザクションの相互作用を制御します(厳密な整合性には SERIALIZABLE 、一般的な用途には READ_COMMITTED )。
• パケットサイズ:高遅延または高スループットネットワーク向けに調整します。

import mssql_python

conn = mssql_python.connect(connection_string)

# Set read-only mode
conn.set_attr(mssql_python.SQL_ATTR_ACCESS_MODE, mssql_python.SQL_MODE_READ_ONLY)

# Set transaction isolation level
conn.set_attr(mssql_python.SQL_ATTR_TXN_ISOLATION, mssql_python.SQL_TXN_SERIALIZABLE)

利用可能な属性:

プリコネクション属性

一部の属性は、ドライバーが接続を確立する前に設定する必要があります(例えば、サインインタイムアウト)。 attrs_beforeに通してください:

conn = mssql_python.connect(
    connection_string,
    attrs_before={
        mssql_python.SQL_ATTR_LOGIN_TIMEOUT: 30,
        mssql_python.SQL_ATTR_CONNECTION_TIMEOUT: 60,
    }
)

接続情報の取得

getinfo()を使って、ログ、診断、またはサーバー機能に基づく動作の適応のためにドライバーおよびサーバーのメタデータを取得することができます:

conn = mssql_python.connect(connection_string)

# Server information
print(f"Server name: {conn.getinfo(mssql_python.SQL_SERVER_NAME)}")
print(f"Database name: {conn.getinfo(mssql_python.SQL_DATABASE_NAME)}")

# Driver information
print(f"Driver name: {conn.getinfo(mssql_python.SQL_DRIVER_NAME)}")
print(f"Driver version: {conn.getinfo(mssql_python.SQL_DRIVER_VER)}")

利用可能な情報定数のリストを入手してください:

constants = mssql_python.get_info_constants()
for name, value in constants.items():
    print(f"{name}: {value}")

探索脱出キャラクター

searchescapeプロパティは、ワイルドカード(%および_)からLIKEパターンから逃れるために使われたキャラクターを返します。 ユーザー入力の文字通りのワイルドカード文字を安全に検索するために使う:

escape = conn.searchescape

# Use in queries with wildcard characters
cursor.execute(
    f"SELECT Name FROM Production.Product WHERE Name LIKE '%{escape}%%' ESCAPE '{escape}'"
)
# Matches names containing literal '%' character

エンコードとデコード

SQL文と結果のテキストエンコーディングを設定しましょう。 ほとんどのアプリケーションではデフォルト設定が動作します。 char / varchar 列に UTF-8 以外のエンコーディングを使用しているサーバーに接続する場合にのみ、それらを変更してください。 サーバーが使用するエンコーディングは、 列の照合によって決まります:

# Set encoding for outbound text
conn.setencoding(encoding='utf-8')

# Get current encoding settings
settings = conn.getencoding()
print(settings)  # {'encoding': 'utf-8', 'ctype': ...}

# Set decoding for inbound text from specific SQL types
conn.setdecoding(mssql_python.SQL_CHAR, encoding='utf-8')

# Get current decoding settings
settings = conn.getdecoding(mssql_python.SQL_CHAR)
print(settings)

デフォルトのエンコーディング:

ベスト プラクティス

• アプリケーションコード内のすべての接続にはコンテキストマネージャー(ブロック)withましょう。 例外があってもクリーンアップを保証します。
• パフォーマンス向上のためにコネクションプーリング(デフォルトで有効)を使いましょう。 接続プーリングを参照してください。
• ネットワーク環境に合った適切なタイムアウトを設定しましょう。 30秒のタイムアウトはほとんどのクラウド展開に適しています。地域間やVPN接続のために値を上げましょう。
• autocommit=False (デフォルト)はトランザクションアトミシティが必要なデータ改変シナリオに用いられます。
• DDL 操作、読み取り専用クエリ、管理スクリプトには autocommit=True を使用します。
• スレッドを超えたつながりを共有しないでください。 ドライバーのスレッドセーフティレベルは1(スレッド同士でモジュールを共有しても接続は共有できません)。

関連するコンテンツ

• 接続文字列
• 接続プール
• トランザクション管理
• カーソル管理