Resolução de problemas de consultas, dados e operação com mssql-python

Use este artigo para diagnosticar problemas de execução de consultas, tipo de dados, desempenho, transação e cópia em massa com o mssql-python driver.

Problemas de execução de consultas

Tabela ou objeto não encontrado

Sintomas:

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

Possíveis causas e soluções:

  • Contexto incorreto da base de dados

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

  • Esquema não especificado

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

  • A tabela não existe

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

Erro de sintaxe

Sintomas:

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

Soluções:

  1. Teste a instrução SQL no SQL Server Management Studio (SSMS) para verificar a sintaxe.

  2. Use uma consulta parametrizada em vez de interpolação de strings:

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

Erros de parâmetros

Sintomas:

ProgrammingError: [07001] Wrong number of parameters

Soluções:

  1. Conte os marcadores de posição e os parâmetros. As contagens devem corresponder.

  2. Escolha o estilo de parâmetros correto:

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

Problemas de tipo de dados

Erros de conversão de data e hora

Sintomas:

DataError: [22007] Invalid datetime format

Solution:

Use objetos Python datetime em vez de strings.

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

Questões de precisão decimal

Sintomas:

Os números parecem truncados ou arredondados incorretamente.

Solution:

Utilização decimal.Decimal para valores numéricos precisos:

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

Problemas de codificação Unicode

Sintomas:

Caracteres especiais aparecem distorcidos ou causam erros.

Soluções:

  1. Use colunas nvarchar para dados Unicode na sua base de dados.

  2. Passe as cadeias de caracteres diretamente. O driver trata da codificação:

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

Problemas de desempenho

Execução lenta da consulta

Possíveis causas e soluções:

  • Índices em falta: Verifique o plano de execução da consulta no SSMS.

  • Grandes conjuntos de resultados: Usar fetchmany() em vez de fetchall():

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

  • Agrupamento de ligações desativado: Ativar agrupamento:

    import mssql_python

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

Problemas de memória com resultados de grande dimensão

Sintomas:

O processo Python fica sem memória.

Soluções:

  1. Transmita os resultados em fluxo em vez de carregar todas as linhas para a memória.

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

  2. Use paginação do lado do servidor.

    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

Questões de transação

Âmbito da tabela temporária com autocommit

As tabelas temporárias de sessão (#tablename) criadas dentro de uma transação desaparecem quando a transação é revertida. Este comportamento costuma causar confusão quando o autocommit está desligado, que é o padrão:

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

Confirma logo após criares uma tabela temporária, ou usa o modo de confirmação automática:

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

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

As instruções DDL que exigem modo de autocommit, como CREATE DATABASE, falham dentro de uma transação aberta. Defina o autocommit antes de os executar:

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

Transação não comprometida

Sintomas:

As alterações nos dados não persistem depois de fechar a ligação.

Solution:

Com autocommit=False, que é a predefinição, chame commit():

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

Alternativamente, use o modo de autocommit:

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

Erros de bloqueio

Sintomas:

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

Solution:

A lógica de repetição lida com a falha imediata, mas bloqueios recorrentes indicam um problema de conceção. Capture o grafo de impasse e analise as instruções e os tipos de bloqueio. As correções mais comuns incluem estas alterações:

  • Reordene as operações para que as transações concorrentes adquiram bloqueios na mesma sequência.
  • Reduzir o âmbito das transações.
  • Adicione índices apropriados para reduzir a duração do bloqueio.

Para uma explicação detalhada da análise de deadlocks, consulte o guia sobre Deadlocks. Se usar o Base de Dados SQL do Azure, veja Analisar e prevenir bloqueios.

Problemas de carregamento em massa

Violação de restrições durante a cópia em massa

Sintomas:

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

Causa:

Os dados no lote violam restrições da tabela, como restrições de chave primária, unicidade, verificação ou chave estrangeira.

Solution:

Valida os dados antes de os carregares. Para grandes conjuntos de dados, carregue os dados numa tabela de preparação e, em seguida, intercale-os no destino:

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

Para padrões de upsert com tabelas de preparação, consulte Padrões de carregamento e movimentação de dados.

Erros de mapeamento de colunas

Sintomas:

RuntimeError: Bulk copy failure - column count mismatch

Causa:

O número de colunas nos seus dados não corresponde ao número alvo de colunas da tabela, ou as colunas estão na ordem errada.

Solution:

Certifique-se de que os seus dados correspondam ao esquema da tabela na ordem e no número:

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)

Desajustes de tipo durante a cópia em massa

Sintomas:

Os dados carregam, mas os valores estão truncados, arredondados ou incorretos.

Causa:

Os valores de Python não correspondem de forma clara aos tipos de coluna de destino. Exemplos comuns incluem float valores carregados em colunas decimais , que podem perder precisão, e strings sobredimensionadas carregadas em colunas de comprimento fixo.

Solution:

Usa tipos em Python que correspondam ao teu esquema:

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)

Falhas de ligação do tipo NumPy

Sintomas:

Os parâmetros falham silenciosamente ou geram erros de tipo de dados ao utilizar tipos inteiros ou de vírgula flutuante do NumPy.

Causa:

Tipos NumPy como numpy.int64 e numpy.int32 não passam isinstance(x, int) no NumPy 2.x. A inferência de tipo do condutor não os reconhece, o que causa comportamentos inesperados.

Solution:

Converta valores do NumPy para tipos nativos de Python antes de os atribuir:

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

Para conjuntos de dados maiores, use os caminhos de integração Arrow ou pandas . Estes caminhos tratam da conversão de tipos internamente.

Cópia em massa com tabelas temporárias

Sintomas:

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

Causa:

bulkcopy() Não é possível resolver tabelas temporárias de sessão (#tablename) devido a limitações na consulta de metadados. As tabelas temporárias globais (##tablename) e as tabelas permanentes funcionam.

Solution:

Use uma tabela temporária global ou uma tabela de preparação regular:

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

Para conjuntos de dados pequenos onde prefere uma tabela temporária de sessão, use executemany():

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

Conteúdo relacionado

  • Last updated on