Zarządzanie połączeniami za pomocą mssql-python

Większość aplikacji stosuje prosty schemat: otwórz połączenie, wykonaj zapytania, zamknij połączenie. Poniższe sekcje obejmują otwieranie i zamykanie połączeń, korzystanie z menedżerów kontekstu, konfigurację automatycznego zatwierdzania oraz pracę z atrybutami połączeń.

Otwórz połączenie

Użyj connect() tej funkcji, aby nawiązać połączenie. Przekaż parametry połączenia zawierające informacje o serwerze, bazie danych i uwierzytelnianiu:

import mssql_python

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

Funkcja connect() akceptuje:

• Parametr połączenia jako pierwszy argument pozycyjny lub słowo kluczowe connection_str.
• Pojedyncze słowa kluczowe, które sterownik łączy z parametry połączenia.
• Inne opcje, takie jak autocommit, timeout, i attrs_before.

Możesz łączyć oba podejścia. Słowa kluczowe zastępują wartości w parametrach połączenia, co jest przydatne, gdy przechowujesz bazowe parametry połączenia w konfiguracji i nadpisujesz ustawienia, takie jak timeout, dla każdego wywołania:

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

Zamknij połączenie

Zawsze zamykaj połączenia po zakończeniu, aby przywrócić je do puli połączeń i zwolnić zasoby serwera. Niezamknięte połączenia przechowują pamięć po stronie serwera i mogą ostatecznie wyczerpać pulę połączeń, powodując blokowanie lub niepowodzenie nowych prób połączenia.

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

Po zamknięciu połączenia nie można już użyć:

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

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

Wielokrotne wywołanie close() jest bezpieczne (idempotentne):

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

Menedżerowie kontekstu

Użyj instrukcji with do zarządzania połączeniami w większości aplikacji. Gwarantuje, że sterownik zamknie połączenie przy wyjściu z bloku, nawet jeśli wystąpi wyjątek. Takie podejście eliminuje ryzyko wycieku połączeń z powodu zapomnianych close() połączeń:

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

Menedżer kontekstu zamyka połączenie po wyjściu. Nie zatwierdza ani cofa automatycznie transakcji:

• Zawsze: Wywołuje close() podczas wychodzenia, niezależnie od tego, czy wystąpił wyjątek.
• close() zachowanie: Jeśli autocommit=False, wszelkie niezatwierdzone zmiany są cofane po zamknięciu połączenia.
• Aby utrwalić zmiany, musisz jawnie wywołać conn.commit().

Ten projekt podąża za zachowaniem PEP 249 i zapobiega przypadkowym częściowym zatwierdzeniom. Jeśli Twój kod zgłasza wyjątek przed osiągnięciem commit(), transakcja w toku jest bezpiecznie cofana z powrotem:

# 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

Tryb automatycznego zatwierdzania

Domyślnie autocommit=False, co oznacza, że każda instrukcja jest wykonywana w ramach niejawnej transakcji. Musisz wywołać conn.commit(), aby zachować zmiany, lub conn.rollback(), aby je odrzucić. Transakcje niejawne są najbezpieczniejszym wyborem dla modyfikacji danych, ponieważ pozwalają grupować wiele instrukcji w jedną atomową operację.

Włącz automatyczne zatwierdzanie, jeśli chcesz, aby każda instrukcja była zatwierdzana natychmiast. Autocommit jest przydatny w przypadku operacji DDL (CREATE TABLE, ALTER INDEX), obciążeń tylko do odczytu lub skryptów administracyjnych, gdzie grupowanie transakcji nie jest potrzebne:

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

Włącz automatyczne zatwierdzanie dla każdej instrukcji, aby była zatwierdzana natychmiast. Użyj autocommit=True podczas nawiązywania połączenia lub przełącz tę opcję po połączeniu za pomocą setautocommit() albo przez bezpośrednie przypisanie właściwości:

# 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

Przekroczenie limitu czasu połączenia

Kierowca ma dwa niezależne timeouty. Ustawienie jednego nie wpływa na drugie.

# Bound the authentication attempt (in seconds)
conn = mssql_python.connect(connection_string, timeout=30)

# Bound each statement that this connection runs
conn.timeout = 60

Jeśli ustawimy SQL_ATTR_LOGIN_TIMEOUT w attrs_before, ta wartość ma pierwszeństwo przed argumentem timeout .

Jeśli obiektem docelowym jest Azure SQL Database w warstwie bezserwerowej z włączonym automatycznym wstrzymywaniem, użyj co najmniej 60. Baza danych wstrzymana automatycznie zostaje wznowiona przy pierwszej próbie połączenia, a krótszy limit czasu upływa, zanim wznowienie zostanie zakończone. Próba może również zakończyć się błędem 40613 podczas wznowienia działania bazy danych, więc aplikacja musi spróbować ponownie. Więcej informacji znajdziesz w sekcji Automatyczne wstrzymywanie i automatyczne wznawianie.

Atrybuty połączenia

Użyj set_attr(), aby zmodyfikować zachowanie połączenia w czasie wykonywania. Atrybuty połączenia kontrolują niskopoziomowe ustawienia sterowników, takie jak tryb dostępu, izolacja transakcji oraz rozmiar pakietu. Większość aplikacji nie musi zmieniać tych atrybutów, ale są przydatne w konkretnych sytuacjach:

• Tryb tylko do odczytu: Zapobiega przypadkowym zapisom w zapytaniach raportujących.
• Izolacja transakcji: Określa sposób interakcji współbieżnych transakcji (użyj SERIALIZABLE do ścisłej spójności, a READ_COMMITTED do ogólnych zastosowań).
• Rozmiar pakietu: Dostrajanie dla sieci o wysokim opóźnieniu lub dużej przepustowości.

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)

Dostępne atrybuty:

Atrybuty wstępnego połączenia

Niektóre atrybuty muszą być ustawione przed nawiązaniem połączenia przez kierowcę (na przykład timeout logowania). Przepuść je przez attrs_before:

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

Uzyskaj informacje o połączeniu

Zastosowanie getinfo() do pobierania metadanych sterowników i serwerów do logowania, diagnostyki lub dostosowywania zachowań w zależności od możliwości serwera:

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)}")

Uzyskaj listę dostępnych stałych informacji:

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

Wyszukiwanie znaku ucieczki

Właściwość searchescape zwraca znak używany jako znak ucieczki dla symboli wieloznacznych (% i _) we wzorcach LIKE. Użyj tego do bezpiecznego wyszukiwania dosłownych znaków wieloznacznych w danych wejściowych użytkownika:

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

Kodowanie i dekodowanie

Konfiguruj kodowanie tekstu dla instrukcji SQL i wyników. Domyślne ustawienia działają w większości aplikacji. Zmień je tylko wtedy, gdy łączysz się z serwerem, który używa kodowania nie-UTF-8 dla char/varchar kolumn. Kodowanie używane przez serwer zależy od kolacji kolumn:

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

Domyślne kodowania:

Najlepsze rozwiązania

• Używaj menedżerów kontekstu (bloków with) we wszystkich połączeniach w kodzie aplikacji. Gwarantują sprzątanie nawet w przypadku wyjątków.
• Użyj poolingu połączeń dla lepszej wydajności (domyślnie włączonego). Zobacz pulę połączeń.
• Ustaw odpowiednie limity czasu dla środowiska sieciowego. 30-sekundowy timeout jest odpowiedni dla większości wdrożeń chmurowych; zwiększ go dla połączeń międzyregionalnych lub VPN. Używaj co najmniej 60 dla Azure SQL Database w warstwie bezserwerowej z włączonym automatycznym wstrzymywaniem, ponieważ baza danych wstrzymana automatycznie zostaje wznowiona przy pierwszej próbie połączenia.
• Ustaw MultiSubnetFailover=yes w parametrach połączenia, gdy elementem docelowym jest Azure SQL Database, Azure SQL Managed Instance, baza danych SQL w usłudze Microsoft Fabric, detektor grupy dostępności lub wystąpienie klastra trybu failover. Jest to bezpieczne w przypadku obiektów docelowych z pojedynczym adresem IP, więc pozostaw tę opcję włączoną dla wszystkich punktów końcowych TCP z rodziny Microsoft SQL.
• Użyj autocommit=False (ustawienie domyślne) w scenariuszach modyfikacji danych, w których wymagana jest atomowość transakcyjna.
• Zastosowanie autocommit=True do operacji DDL, zapytań tylko do odczytu oraz skryptów administracyjnych.
• Nie dziel się połączeniami między wątkami. Poziom bezpieczeństwa wielowątkowego sterownika wynosi 1 (wątki mogą współdzielić moduł, ale nie połączenia).

Treści powiązane

• Stringi połączeń dla mssql-python
• Pulowanie połączeń z mssql-python
• Zarządzanie transakcjami za pomocą mssql-python
• Zarządzanie kursorami i zestawami wyników