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 espaces réservés 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())

Nombre de lignes incorrect dans des lots à résultats multiples

Si rowcount le compte de la première instruction se répète après nextset(), mettez à jour le pilote logiciel. Chaque actualisation réussie nextset() rafraîchit le nombre pour le nouveau résultat. Un résultat produisant des lignes commence à -1 avant l’extraction. Vérifiez cursor.description avant de récupérer car les résultats de modification de données contiennent des décomptes plutôt que des lignes.

Laissez SET NOCOUNT désactivé lorsque vous avez besoin de résultats de ligne affectée. Pour un exemple complet, voir Suivre chaque résultat dans un lot.

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

Le constructeur d’horodatage nécessite un argument en microsecondes

Si Timestamp(year, month, day, hour, minute, second) génère TypeError pour un argument microsecond manquant, mettez à niveau mssql-python. Le pilote actuel accepte le constructeur DB-API à six arguments et définit microsecond à zéro par défaut. Pour plus d’informations, voir DB-API constructeur d’horodatage.

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

La copie groupée échoue après une mise à jour du package

Si les requêtes classiques fonctionnent mais si bulkcopy() ou bulkcopy_arrow() n’arrive pas à importer ou à charger le module natif de copie en masse, mettez à jour le pilote avec des dépendances :

python -m pip install --upgrade mssql-python
python -m pip check

Le pilote nécessite un environnement d’exécution mssql-python-rs correspondant. Une mise à jour partielle ou une installation avec --no-deps peut laisser des packages incompatibles installés. Si vous utilisez un index de paquets privé, mettez en miroir les deux paquets compagnons et préservez les versions de dépendance épinglées déclarées par le driver. Pour les versions actuellement épinglées, voir Prérequis des dépendances.

Noms de destinations non pris en charge

bulkcopy() et bulkcopy_arrow() acceptent au maximum trois parties identifiantes. Un nom en quatre parties comme server.database.schema.table génère RuntimeError lors de la recherche de métadonnées avant l’envoi de lignes de lignes. Connectez-vous directement au serveur de destination et utilisez un nom de table à une partie, deux ou trois parties. Pour les erreurs d’échappement des crochets et de noms malformés, voir Noms de tables cibles.

Unicode ou fractions de seconde perdues lors de la copie en masse

Mettez à jour le pilote si la copie en bloc corrompt le texte UTF-8 ou tronque les fractions de seconde lors du chargement des valeurs datetime2 compatibles. L’exécution actuelle de la copie en vrac gère le texte UTF-8 et conserve la précision datetime2 disponible dans les types source et destination.

Utilisez nvarchar ou une collation UTF-8 sur varchar pour le texte Unicode. Les valeurs Python datetime et les méthodes Arrow Fetch du pilote sont limitées aux microsecondes. Pour les valeurs de 100 nanosecondes déjà conservées dans une colonne d’horodatage Arrow en nanosecondes, chargez-les dans datetime2(7) sans les convertir d’abord en Pythondatetime. Pour plus d’informations, voir Unicode et précision temporelle ainsi que précision temporelle Arrow.

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

  • Dernière mise à jour :