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() przy wyjściu, niezależnie od tego, czy wystąpił wyjątek.
  • zachowanie close(): Jeśli autocommit=False, wszelkie niezatwierdzone zmiany są cofane po zamknięciu połączenia.
  • Musisz wywołać conn.commit() wyraźnie , aby zachować zmiany.

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.

Setting Co ona ogranicza Domyślny
timeout argument do connect() Próba uwierzytelnienia, w tym połączenie sieciowe. Ustawia wartość SQL_ATTR_LOGIN_TIMEOUT. 0, który używa domyślnego ustawienia sterownika
Connection.timeout właściwość Każda instrukcja wykonywana przez połączenie. 0, co wyłącza limit czasu zapytania
# 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 .

W przypadku połączenia połączonego sterownik również wykorzystuje limit czasu logowania, aby ograniczyć kontrolę przed ponownym użyciem połączenia. Jeśli limit czasu logowania wynosi zero lub nie jest ustawiony, sprawdzenie używa limitu 15 sekund. Jeśli już ustawiłeś krótszy SQL_ATTR_CONNECTION_TIMEOUT na połączeniu, sprawdzenie używa Twojej wartości. Limit czasu dotyczy każdego żądania w kontroli, a nie całej kontroli. Jeśli sprawdzenie się nie powiedzie, sterownik otwiera nowe połączenie przy użyciu własnych poświadczeń, więc connect() może to potrwać dłużej niż limit czasu logowania. Aby uzyskać więcej informacji, zobacz Kontrole stanu połączenia.

Note

Uzyskiwanie tokenu Microsoft Entra ID następuje, zanim sterownik nawiąże połączenie, więc limit czasu uwierzytelniania nie ogranicza tego procesu.

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 raportowych.
  • Izolacja transakcji: Kontroluje, jak współbieżne transakcje ze sobą współdziałają (użyj SERIALIZABLE dla ścisłej spójności, READ_COMMITTED do użytku ogólnego).
  • Rozmiar pakietu: Dostrajaj do 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:

Stały Opis
SQL_ATTR_CONNECTION_TIMEOUT Limit czasu oczekiwania na połączenie w sekundach.
SQL_ATTR_LOGIN_TIMEOUT Limit czasu logowania w sekundach.
SQL_ATTR_PACKET_SIZE Rozmiar pakietu sieciowego.
SQL_ATTR_ACCESS_MODE Tryb tylko do odczytu lub odczytu i zapisu.
SQL_ATTR_TXN_ISOLATION Poziom izolacji transakcji.
SQL_ATTR_CURRENT_CATALOG Aktualna nazwa bazy danych.

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

Sterownik zawsze wysyła instrukcje Transact-SQL (T-SQL) i parametry str do serwera jako UTF-16LE i wiąże parametry tekstowe jako SQL_C_WCHAR. Nie możesz zmienić kodowania wychodzącego. Metoda setencoding() rejestruje wartości, o które prosisz, ale nie zmienia tego, co wysyła sterownik:

  • Wywołanie setencoding() bez argumentów przywraca domyślne ustawienia.
  • Prawidłowe żądanie różniące się od UTF-16LE przez użycie SQL_WCHAR, takie jak encoding='utf-8' lub ctype=mssql_python.SQL_CHAR, emituje UserWarning i nie ma efektu. Żądanie, które wyraźnie podaje nazwę UTF-16LE z SQL_WCHAR, nie powoduje ostrzeżenia. Metoda getencoding() nadal zwraca żądane wartości.
import warnings

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    conn.setencoding(encoding="utf-8")
print(caught[0].category.__name__)  # UserWarning

conn.setencoding()  # Restore the defaults
print(conn.getencoding())  # {'encoding': 'utf-16le', 'ctype': -8}

Konfiguruj dekodowanie dla tekstu, który serwer zwraca. Domyślnie sterownik żąda danych char i varchar jako UTF-16LE z SQL_WCHAR, więc wartości dekodują się poprawnie niezależnie od kolacji kolumny i jej strony kodowej. Większość aplikacji nie musi zmieniać ustawień dekodowania. Wywołanie setdecoding() tylko z typem SQL przywraca wartość domyślną:

# Decode char and varchar data as UTF-8 instead of the default
conn.setdecoding(mssql_python.SQL_CHAR, encoding='utf-8')

# Restore the default
conn.setdecoding(mssql_python.SQL_CHAR)

# Get current decoding settings
settings = conn.getdecoding(mssql_python.SQL_CHAR)
print(settings)  # {'encoding': 'utf-16le', 'ctype': -8}

Domyślne kodowania:

Kierunek Typ SQL Domyślne kodowanie Zmień za pomocą
Wychodzący (str) SQL_WCHAR utf-16le Nie da się zmienić
Inbound SQL_CHAR utf-16le setdecoding()
Inbound SQL_WCHAR utf-16le setdecoding()
Inbound SQL_WMETADATA utf-16le setdecoding()

Najlepsze rozwiązania

  • Używaj menedżerów kontekstu (bloki with) dla wszystkich połączeń w kodzie aplikacji. Gwarantują sprzątanie nawet w przypadku wyjątków.
  • Używaj pulowania połączeń dla lepszej wydajności (domyślnie włączone). 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 parametry połączenia, gdy celem jest Azure SQL Database, Azure SQL Managed Instance, baza danych SQL w Microsoft Fabric, nasłuchiwacz grupy dostępności lub instancja klastra awaryjnego. 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 (domyślnie) do scenariuszy modyfikacji danych, w których potrzebujesz transakcyjnej atomowości.
  • Użyj autocommit=True do operacji DDL, zapytań tylko do odczytu oraz skryptów administracyjnych.
  • Nie udostępniaj połączeń między wątkami. Poziom bezpieczeństwa wielowątkowego sterownika wynosi 1 (wątki mogą współdzielić moduł, ale nie połączenia).