Résoudre les problèmes de requêtes, de données et d’exploitation avec mssql-python

Utilisez cet article pour diagnostiquer l’exécution des requêtes, le type de données, les performances, les transactions et les problèmes de copie en vrac avec le mssql-python pilote.

Problèmes d’exécution des requêtes

Table ou objet non trouvé

Symptômes :

ProgrammingError: [42S02] (208) Invalid object name 'TableName'.

Causes et solutions possibles :

  • Contexte incorrect de la base de données

    cursor.execute("SELECT DB_NAME()")
print(cursor.fetchone()[0])

  • Schéma non spécifié

    cursor.execute("SELECT * FROM dbo.TableName")

  • La table n’existe pas

    cursor.execute("""
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'TableName'
""")

Erreur de syntaxe

Symptômes :

ProgrammingError: [42000] (102) Incorrect syntax near '...'.

Solutions :

  1. Testez l’instruction SQL dans SQL Server Management Studio (SSMS) pour vérifier la syntaxe.

  2. Utilisez une requête paramétrée au lieu d’une interpolation de chaînes :

    # Don't use string interpolation for query parameters.
cursor.execute(f"SELECT * FROM Production.Product WHERE Name = '{name}'")

# Use parameters.
cursor.execute(
    "SELECT * FROM Production.Product WHERE Name = %(name)s",
    {"name": name},
)

Erreurs de paramètres

Symptômes :

ProgrammingError: [07001] Wrong number of parameters

Solutions :

  1. Comptez les placeholders et les paramètres. Les décomptes doivent correspondre.

  2. Choisissez le bon style de paramètres :

    # Qmark style: positional parameters
cursor.execute(
    "SELECT * FROM Production.Product "
    "WHERE ProductID = ? AND Name LIKE ?",
    (1, "Adjustable%"),
)
print(cursor.fetchone())

# Pyformat style: named parameters
cursor.execute(
    "SELECT * FROM Production.Product "
    "WHERE ProductID = %(id)s AND Name LIKE %(name)s",
    {"id": 1, "name": "Adjustable%"},
)
print(cursor.fetchone())

Problèmes de type de données

Erreurs de conversion de date et d’heure

Symptômes :

DataError: [22007] Invalid datetime format

Solution:

Utilisez des objets Python datetime au lieu de chaînes de caractères.

from datetime import datetime

cursor.execute("CREATE TABLE #Events (EventDate DATETIME)")

# This value raises an error because the date is invalid.
try:
    cursor.execute(
        "INSERT INTO #Events (EventDate) VALUES (%(event_date)s)",
        {"event_date": "2024-13-45"},
    )
except Exception as e:
    print(f"Expected error: {e}")

# Use a Python datetime object.
cursor.execute(
    "INSERT INTO #Events (EventDate) VALUES (%(event_date)s)",
    {"event_date": datetime(2024, 3, 15)},
)
cursor.execute("SELECT EventDate FROM #Events")
print(cursor.fetchone())

Problèmes de précision décimale

Symptômes :

Les chiffres apparaissent tronqués ou arrondis incorrectement.

Solution:

Utilisation decimal.Decimal pour des valeurs numériques précises :

from decimal import Decimal

cursor.execute("CREATE TABLE #PriceDemo (ListPrice DECIMAL(10, 2))")
cursor.execute(
    "INSERT INTO #PriceDemo (ListPrice) VALUES (%(list_price)s)",
    {"list_price": Decimal("19.99")},
)

Problèmes d’encodage Unicode

Symptômes :

Les caractères spéciaux apparaissent brouillés ou causent des erreurs.

Solutions :

  1. Utilisez les colonnes nvarchar pour les données Unicode dans votre base de données.

  2. Passez directement les chaînes de caractères. Le pilote gère l’encodage :

    cursor.execute("CREATE TABLE #UnicodeDemo (Name NVARCHAR(50))")
cursor.execute(
    "INSERT INTO #UnicodeDemo (Name) VALUES (%(name)s)",
    {"name": "日本語"},
)
cursor.execute("SELECT Name FROM #UnicodeDemo")
print(cursor.fetchone())

Problèmes de performance

Exécution lente des requêtes

Causes et solutions possibles :

  • Index manquants : Vérifiez le plan d’exécution des requêtes dans SSMS.

  • Grands ensembles de résultats : Utilisez fetchmany() au lieu de fetchall():

    cursor.arraysize = 1000
while True:
    rows = cursor.fetchmany()
    if not rows:
        break
    process_rows(rows)

  • Pool de connexions désactivé : Activez le pool de connexions :

    import mssql_python

mssql_python.pooling(max_size=20, idle_timeout=300)

Problèmes de mémoire avec des résultats volumineux

Symptômes :

Le processus Python manque de mémoire.

Solutions :

  1. Diffusez les résultats en flux au lieu de charger toutes les lignes en mémoire.

    cursor.execute("SELECT * FROM LargeTable")
for row in cursor:
    process_row(row)

  2. Utilisez la pagination côté serveur.

    page_size = 1000
offset = 0

while True:
    cursor.execute(
        "SELECT * FROM LargeTable ORDER BY ID "
        "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY",
        (offset, page_size),
    )
    rows = cursor.fetchall()
    if not rows:
        break
    process_rows(rows)
    offset += page_size

Problèmes de transaction

Portée de table temporaire avec validation automatique

Les tables temporaires de session (#tablename) que vous créez à l’intérieur d’une transaction disparaissent lorsque la transaction est annulée. Ce comportement cause souvent de la confusion lorsque l’autocommit est désactivé, ce qui est le principe par défaut :

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

cursor.execute("CREATE TABLE #TempData (ID INT, Name NVARCHAR(50))")
cursor.execute("INSERT INTO #TempData VALUES (1, 'test')")

# An explicit rollback or an error removes #TempData.
conn.rollback()

# This statement fails with "Invalid object name '#TempData'".
cursor.execute("SELECT * FROM #TempData")

Commet immédiatement après avoir créé une table temporaire, ou utilise le mode d’autocommit :

cursor.execute("CREATE TABLE #TempData (ID INT, Name NVARCHAR(50))")
conn.commit()

cursor.execute("INSERT INTO #TempData VALUES (1, 'test')")
conn.commit()

Les instructions DDL qui nécessitent le mode de validation automatique, telles que CREATE DATABASE, échouent dans une transaction ouverte. Définissez l’autocommit avant de les lancer :

conn.autocommit = True
cursor.execute("CREATE DATABASE TestDB")
conn.autocommit = False

Transaction non engagée

Symptômes :

Les changements de données ne persistent pas après la fermeture de la connexion.

Solution:

Avec autocommit=False, qui est la valeur par défaut, appelez commit() :

cursor.execute("CREATE TABLE #Products (Name NVARCHAR(100))")
cursor.execute(
    "INSERT INTO #Products (Name) VALUES (%(name)s)",
    {"name": "Widget"},
)
conn.commit()

Sinon, utilisez le mode d’engagement automatique :

conn = mssql_python.connect(connection_string, autocommit=True)

Erreurs d’interblocage

Symptômes :

OperationalError: [40001] (1205) Transaction ... was deadlocked on lock resources with another process

Solution:

La logique de réessai gère la défaillance immédiate, mais des blocages récurrents indiquent un problème de conception. Capturez le graphique de blocage et analysez les instructions et types de verrous. Les solutions courantes incluent ces changements :

  • Réordonnez les opérations afin que les transactions concurrentes acquièrent des verrous dans le même ordre.
  • Réduisez la portée des transactions.
  • Ajoutez des index appropriés pour réduire la durée du verrou.

Pour un guide complet de l’analyse des blocages, consultez le guide des blocages. Si vous utilisez Azure SQL Database, consultez Analyser et prévenir les blocages.

Problèmes de chargement en masse

Violations de contraintes lors d’une copie en bloc

Symptômes :

RuntimeError: CHECK constraint ... Conflict occurred in database ...
RuntimeError: Cannot insert duplicate key ... violation of PRIMARY KEY constraint

Cause :

Les données de votre lot violent les contraintes de table telles que les contraintes de clé primaire, unique, de vérification ou de clé étrangère.

Solution:

Validez les données avant de les charger. Pour les grands ensembles de données, chargez les données dans une table de staging, puis fusionnez-les dans la cible :

cursor.execute("CREATE TABLE ##Staging (ID INT, Name NVARCHAR(100))")
cursor.bulkcopy("##Staging", rows)

# Check for duplicate rows before the merge.
cursor.execute("""
    SELECT s.ID
    FROM ##Staging AS s
    INNER JOIN dbo.Target AS t
        ON s.ID = t.ID
""")
dupes = cursor.fetchall()
if dupes:
    print(f"Skipping {len(dupes)} duplicate rows")

# Insert rows that don't exist in the target.
cursor.execute("""
    INSERT INTO dbo.Target (ID, Name)
    SELECT s.ID, s.Name
    FROM ##Staging AS s
    WHERE NOT EXISTS (
        SELECT 1
        FROM dbo.Target AS t
        WHERE t.ID = s.ID
    )
""")
conn.commit()

Pour les modèles d’upsert avec des tables intermédiaires, voir Modèles de chargement et de déplacement des données.

Erreurs de correspondance de colonnes

Symptômes :

RuntimeError: Bulk copy failure - column count mismatch

Cause :

Le nombre de colonnes dans vos données ne correspond pas au nombre de colonnes cibles dans la table, ou les colonnes sont dans le mauvais ordre.

Solution:

Assurez-vous que vos données correspondent au schéma de la table dans l’ordre et le nombre :

from decimal import Decimal

cursor.execute("""
    SELECT COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'MyTable'
    ORDER BY ORDINAL_POSITION
""")
for col in cursor.fetchall():
    print(col)

rows = [
    (1, "Widget", Decimal("19.99")),
    (2, "Gadget", Decimal("29.99")),
]
cursor.bulkcopy("dbo.MyTable", rows)

Désaccords de type lors de la copie en vrac

Symptômes :

Les données sont chargées, mais les valeurs sont tronquées, arrondies ou incorrectes.

Cause :

Les valeurs de Python ne correspondent pas clairement aux types de colonnes cibles. Des exemples courants incluent float des valeurs chargées dans des colonnes décimales , qui peuvent perdre en précision, et des chaînes surdimensionnées chargées dans des colonnes de longueur fixe.

Solution:

Utilisez des types Python qui correspondent à votre schéma :

from decimal import Decimal

rows = [
    # Use Decimal for decimal and numeric columns.
    (1, "Widget", Decimal("19.99")),
    # Avoid float values because they can lose precision.
    # (1, "Widget", 19.99),
]
cursor.bulkcopy("dbo.Products", rows)

Défaillances de liaison de type NumPy

Symptômes :

Les paramètres échouent silencieusement ou génèrent des erreurs de type lorsque vous utilisez des types entiers ou à virgule flottante de NumPy.

Cause :

Les types NumPy comme numpy.int64 et numpy.int32 ne passent isinstance(x, int) pas dans NumPy 2.x. L’inférence de type du conducteur ne les reconnaît pas, ce qui provoque un comportement inattendu.

Solution:

Convertissez les valeurs NumPy en types natifs Python avant de les lier :

import numpy as np

cursor.execute(
    "SELECT * FROM Production.Product WHERE ProductID = %(product_id)s",
    {"product_id": int(np.int64(42))},
)

for _, row in df.iterrows():
    cursor.execute(
        "INSERT INTO #Orders (ProductID, Qty) "
        "VALUES (%(product_id)s, %(qty)s)",
        {
            "product_id": int(row["ProductID"]),
            "qty": int(row["Qty"]),
        },
    )

Pour des ensembles de données plus volumineux, utilisez les chemins d’intégration Arrow ou pandas . Ces parcours gèrent la conversion de type en interne.

Copie en bloc avec tables temporaires

Symptômes :

cursor.bulkcopy("#TempTable", data) déclenche RuntimeError: Invalid object name '#TempTable'.

Cause :

bulkcopy() Je ne peux pas résoudre les tables temporaires de session (#tablename) à cause des limitations de recherche de métadonnées. Les tables temporaires globales (##tablename) et les tables permanentes fonctionnent.

Solution:

Utilisez une table temporaire globale ou une table de mise en scène classique :

# A global temp table is visible to all sessions and is dropped
# when the last session disconnects.
cursor.execute("CREATE TABLE ##Staging (ID INT, Name NVARCHAR(50))")
cursor.bulkcopy("##Staging", rows)

# Alternatively, use a permanent staging table.
cursor.execute("CREATE TABLE dbo.Staging (ID INT, Name NVARCHAR(50))")
cursor.bulkcopy("dbo.Staging", rows)

Pour de petits ensembles de données où vous préférez une table temporaire de session, utilisez executemany():

cursor.execute("CREATE TABLE #Staging (ID INT, Name NVARCHAR(50))")
cursor.executemany(
    "INSERT INTO #Staging (ID, Name) VALUES (?, ?)",
    rows,
)

Contenu connexe

  • Last updated on