Gestire le connessioni con mssql-python

La maggior parte delle applicazioni segue uno schema semplice: aprire una connessione, eseguire query, chiudere la connessione. Le sezioni seguenti trattano l'apertura e la chiusura delle connessioni, l'uso dei gestori di contesto, la configurazione dell'autocommit e il lavoro con gli attributi di connessione.

Apri una connessione

Usa la connect() funzione per stabilire un legame. Specifica una stringa di connessione con i dettagli del server, del database e dell'autenticazione:

import mssql_python

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

La connect() funzione accetta:

• Una stringa di connessione come primo argomento posizionale o la parola chiave connection_str.
• Singole parole chiave che il driver unisce nella stringa di connessione.
• Altre opzioni come autocommit, timeout, e attrs_before.

Puoi mescolare entrambi gli approcci. Le parole chiave sovrascrivono i valori della stringa di connessione, il che è utile quando si memorizza una stringa di connessione di base nella configurazione e si sovrascrivono impostazioni come timeout a ogni chiamata:

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

Chiudi una connessione

Chiudi sempre le connessioni quando è finito per restituirle al pool di connessione e liberare le risorse del server. Le connessioni non chiuse mantengono memoria lato server e possono eventualmente esaurire il pool di connessioni, causando il blocco o il fallimento dei nuovi tentativi di connessione.

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

Una volta chiusa, la connessione non può essere utilizzata:

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

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

Chiamare close() più volte è sicuro (idempotente):

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

Gestori di contesto

Usa la with dichiarazione per gestire le connessioni nella maggior parte delle applicazioni. Garantisce che il driver chiuda la connessione all'uscita dal blocco, anche se si verifica un'eccezione. Questo approccio elimina il rischio di connessioni trapelate dovute a chiamate dimenticate 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

Il responsabile del contesto chiude la connessione all'uscita. Non effettua automaticamente il commit o il roll back delle transazioni:

• Sempre: chiama close() in uscita, indipendentemente dal fatto che si sia verificata o meno un'eccezione.
• close() comportamento: Se autocommit=False, eventuali modifiche non dichiarate vengono annullate quando la connessione si chiude.
• Devi chiamare conn.commit() esplicitamente per salvare le modifiche.

Questa progettazione si conforma al comportamento previsto da PEP 249 ed evita commit parziali accidentali. Se il tuo codice solleva un'eccezione prima di raggiungere commit(), la transazione in corso viene annullata in sicurezza:

# 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

Modalità autocommit

Per impostazione predefinita, autocommit=False, il che significa che ogni istruzione viene eseguita all'interno di una transazione implicita. Devi chiamare conn.commit() per persistere i cambiamenti o conn.rollback() per scartarli. Le transazioni implicite sono la scelta più sicura per le modifiche ai dati perché permettono di raggruppare più istruzioni in un'unica operazione atomica.

Abilita l'autocommit quando vuoi che ogni istruzione venga commit immediatamente. L'autocommit è utile per operazioni DDL (CREATE TABLE, ALTER INDEX), carichi di lavoro di sola lettura o script amministrativi dove il raggruppamento delle transazioni non è necessario:

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

Abilita l'autocommit per ogni istruzione, in modo che il commit venga eseguito immediatamente. Usare autocommit=True durante la connessione oppure commutarlo dopo la connessione con setautocommit() o assegnando direttamente la proprietà:

# 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

Timeout della connessione

Imposta il timeout della connessione per controllare quanto tempo il driver attese a stabilire una connessione prima di generare un errore. Un timeout di connessione ragionevole è importante per applicazioni distribuite in ambienti con reti inaffidabili o per guasti rapidi quando un server è irraggiungibile:

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

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

Un timeout di 0 significa nessun timeout (attendere indefinitamente). Impostare timeout ragionevoli in produzione; un tentativo di connessione che resta bloccato senza timeout blocca permanentemente il thread chiamante.

Attributi di connessione

Usare set_attr() per modificare il comportamento della connessione in fase di esecuzione. Gli attributi di connessione controllano impostazioni di driver di basso livello come la modalità di accesso, l'isolamento delle transazioni e la dimensione del pacchetto. La maggior parte delle applicazioni non ha bisogno di modificare questi attributi, ma sono utili per scenari specifici:

• Modalità di sola lettura: Previene scritture accidentali nelle query di segnalazione.
• Isolamento delle transazioni: Controlla come interagiscono le transazioni concorrenti (da usare SERIALIZABLE per coerenza stretta, READ_COMMITTED per uso generale).
• Dimensione del pacchetto: Ottimizza per reti ad alta latenza o con throughput elevato.

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)

Attributi disponibili:

Attributi di preconnessione

Alcuni attributi devono essere impostati prima che il driver stabilisca la connessione (ad esempio, timeout di accesso). Passale attraverso attrs_before:

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

Ottenere informazioni di connessione

Usa getinfo() per recuperare i metadati del driver e del server per la registrazione degli eventi, la diagnostica o per adattare il comportamento in base alle funzionalità supportate dal server:

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

Ottieni un elenco delle costanti informative disponibili:

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

Carattere di escape per la ricerca

La proprietà searchescape restituisce il carattere utilizzato per l'escape dei caratteri jolly (% e _) nei modelli LIKE. Usalo per cercare in sicurezza i caratteri jolly letterali negli input dell'utente:

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

Codifica e decodifica

Configura la codifica del testo per le istruzioni e i risultati SQL. Le impostazioni predefinite funzionano per la maggior parte delle applicazioni. Cambiale solo se ti connetti a un server che utilizza una codifica non UTF-8 per le char/varchar colonne. La codifica utilizzata da un server dipende dalla collazione delle colonne:

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

Codifica predefinita:

Procedure consigliate

• Usa i gestori di contesto (with blocchi) per tutte le connessioni nel codice applicativo. Garantiscono la pulizia anche quando si verificano eccezioni.
• Usa il pool di connessione per migliori prestazioni (abilitato di default). Vedi Pooling di connessioni.
• Imposta timeout appropriati per l'ambiente di rete. Un timeout di 30 secondi è adatto alla maggior parte delle implementazioni cloud; aumentalo per connessioni cross-region o VPN.
• Utilizzo autocommit=False (il predefinito) per scenari di modifica dei dati in cui serve atomicità transazionale.
• Utilizzo autocommit=True per operazioni DDL, query di sola lettura e script amministrativi.
• Non condividere connessioni tra thread. Il livello di sicurezza del thread del driver è 1 (i thread possono condividere il modulo ma non le connessioni).

Contenuti correlati

• Stringhe di connessione
• Pool di connessioni
• Gestione delle transazioni
• Gestione del cursore