Gérer les connexions avec mssql-python

La plupart des applications suivent un schéma simple : ouvrir une connexion, lancer des requêtes, fermer la connexion. Les sections suivantes traitent de l’ouverture et de la fermeture des connexions, de l’utilisation de gestionnaires de contexte, de la configuration de l’autocommit et du travail avec les attributs de connexion.

Ouvre une connexion

Utilisez la connect() fonction pour établir un lien. Passez une chaîne de connexion avec vos détails de serveur, de base de données et d’authentification :

import mssql_python

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

La connect() fonction accepte :

  • Une chaîne de connexion en premier argument positionnel ou le mot-clé connection_str.
  • Des mots-clés individuels que le pilote fusionne dans la chaîne de connexion.
  • D’autres options comme autocommit, timeout, et attrs_before.

On peut mélanger les deux approches. Les mots-clés remplacent les valeurs de la chaîne de connexion, ce qui est utile lorsque vous stockez une chaîne de connexion de base dans la configuration et remplacez certains paramètres, comme timeout, à chaque appel :

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

Fermer une connexion

Fermez toujours les connexions une fois terminées pour les renvoyer dans le pool de connexions et libérer les ressources du serveur. Les connexions non fermées contiennent la mémoire côté serveur et peuvent épuiser le pool de connexions, provoquant le blocage ou l’échec des nouvelles tentatives de connexion.

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

Une fois fermée, la connexion ne peut plus être utilisée :

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

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

Appeler close() plusieurs fois ne pose pas de problème (opération idempotente) :

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

Gestionnaires de contexte

Utilisez cette with instruction pour gérer les connexions dans la plupart des applications. Il garantit que le pilote ferme la connexion à la sortie du bloc, même en cas d’exception. Cette approche élimine le risque de fuite des connexions dues à des appels oubliés 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

Le gestionnaire de contexte ferme la connexion à la sortie. Il ne valide pas automatiquement ni ne revient en arrière les transactions :

  • Toujours : Appelle close() lors de la sortie, qu’une exception se produise ou non.
  • close() comportement : Si autocommit=False, tout changement non engagé est annulé lorsque la connexion se ferme.
  • Vous devez appeler conn.commit() explicitement pour persister les changements.

Cette conception suit le comportement PEP 249 et empêche les commits partiels accidentels. Si votre code ouvre une exception avant d’atteindre commit(), la transaction en cours est annulée en toute sécurité :

# 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

Mode de validation automatique

Par défaut, autocommit=False, ce qui signifie que chaque instruction s’exécute à l’intérieur d’une transaction implicite. Vous devez appeler conn.commit() pour enregistrer les modifications ou conn.rollback() pour les ignorer. Les transactions implicites sont le choix le plus sûr pour modifier les données car elles permettent de regrouper plusieurs instructions en une seule opération atomique.

Activez la validation automatique lorsque vous souhaitez que chaque instruction soit validée immédiatement. L’autocommit est utile pour les opérations DDL (CREATE TABLE, ALTER INDEX), les charges de travail en lecture seule ou les scripts administratifs où le regroupement des transactions n’est pas nécessaire :

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

Activez la validation automatique pour que chaque instruction soit validée immédiatement. Utilisez autocommit=True lors de la connexion, ou activez-le/désactivez-le après la connexion à l’aide de setautocommit() ou par affectation directe de propriété :

# 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

Délai d’expiration de la connexion

Réglez le délai d’attente de la connexion pour contrôler combien de temps le pilote attend avant d’afficher une erreur. Un délai d’attente raisonnable est important pour les applications déployées dans des environnements à réseaux peu fiables ou pour les pannes rapides lorsqu’un serveur est injoignable :

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

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

Un délai d’expiration de 0 signifie aucune expiration du délai (attente indéfinie). Définissez des délais d’attente raisonnables en production ; une tentative de connexion bloquée sans délai d’attente bloque définitivement le thread appelant.

Attributs de connexion

À utiliser set_attr() pour modifier le comportement de connexion à l’exécution. Les attributs de connexion contrôlent des paramètres de pilotes bas niveau comme le mode d’accès, l’isolation des transactions et la taille des paquets. La plupart des applications n’ont pas besoin de modifier ces attributs, mais ils sont utiles pour des scénarios spécifiques :

  • Mode lecture seule : Empêche les écritures accidentelles dans les requêtes de rapport.
  • Isolation des transactions : Contrôle la manière dont les transactions concurrentes interagissent (utilisation SERIALIZABLE pour une cohérence stricte, READ_COMMITTED pour un usage général).
  • Taille du paquet : Ajustez pour des réseaux à haute latence ou à haut débit.
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)

Attributs disponibles :

Constante Description
SQL_ATTR_CONNECTION_TIMEOUT Délai d’expiration de la connexion en secondes.
SQL_ATTR_LOGIN_TIMEOUT Délai d’expiration de la connexion en secondes.
SQL_ATTR_PACKET_SIZE Taille de paquet réseau.
SQL_ATTR_ACCESS_MODE Mode lecture seule ou lecture-écriture.
SQL_ATTR_TXN_ISOLATION Niveau d’isolation des transactions.
SQL_ATTR_CURRENT_CATALOG Nom actuel de la base de données.

Attributs de préconnexion

Certains attributs doivent être définis avant que le pilote établisse la connexion (par exemple, le délai d’attente de connexion). Faites-les passer par attrs_before :

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

Obtenir des informations de connexion

Utilisez getinfo() pour récupérer les métadonnées du pilote et du serveur à des fins de journalisation, de diagnostic ou pour adapter le comportement en fonction des capacités du serveur :

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

Obtenez une liste des constantes d’information disponibles :

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

Caractère d’échappement de recherche

La propriété searchescape renvoie le caractère utilisé pour échapper aux caractères génériques (% et _) dans les motifs LIKE. Utilisez-le pour rechercher en toute sécurité des caractères jokers littéraux dans les saisies de l’utilisateur :

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

Encodage et décodage

Configurez l’encodage de texte pour les instructions SQL et les résultats. Les réglages par défaut fonctionnent pour la plupart des applications. Changez-les seulement si vous vous connectez à un serveur qui utilise un codage non UTF-8 pour char/varchar les colonnes. L’encodage utilisé par un serveur dépend de la collation de colonnes :

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

Codages par défaut :

Direction Type SQL Codage par défaut
Sortant (str) SQL_WCHAR utf-16le
Trafic entrant SQL_CHAR utf-8
Trafic entrant SQL_WCHAR utf-16le
Trafic entrant SQL_WMETADATA utf-16le

Bonnes pratiques

  • Utilisez des gestionnaires de contexte (with blocs) pour toutes les connexions dans le code applicatif. Ils garantissent le nettoyage même en cas d’exceptions.
  • Utilisez le pooling de connexion pour de meilleures performances (activé par défaut). Voir pool de connexions.
  • Fixez des délais appropriés pour votre environnement réseau. Un délai d’arrêt de 30 secondes convient à la plupart des déploiements cloud ; augmentez-le pour les connexions interrégionales ou VPN.
  • Utilisation autocommit=False (par défaut) pour les scénarios de modification des données où il faut une atomicité transactionnelle.
  • Utilisation autocommit=True pour les opérations DDL, les requêtes en lecture seule et les scripts admin.
  • Ne partagez pas de liens entre fils de discussion. Le niveau de sécurité des threads du pilote est de 1 (les threads peuvent partager le module, mais pas les connexions).

Contenu connexe

  • Last updated on