Gerir ligações com mssql-python

A maioria das aplicações segue um padrão simples: abrir uma ligação, executar consultas, fechar a ligação. As secções seguintes abordam a abertura e o encerramento de ligações, utilização de gestores de contexto, configuração do autocommit e trabalho com atributos de ligação.

Abrir uma ligação

Use a connect() função para estabelecer uma ligação. Passe uma cadeia de ligação com o seu servidor, base de dados e detalhes de autenticação:

import mssql_python

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

A connect() função aceita:

• Uma cadeia de ligação como primeiro argumento posicional ou a palavra-chave connection_str.
• Palavras-chave individuais que o controlador combina na cadeia de ligação.
• Outras opções como autocommit, timeout, e attrs_before.

Podes misturar ambas as abordagens. As palavras-chave substituem os valores na cadeia de ligação, o que é útil quando armazena uma cadeia de ligação de base na configuração e substitui definições como timeout em cada chamada:

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

Fecha uma ligação

Fecha sempre as ligações quando terminado para as devolver ao pool de ligações e libertar os recursos do servidor. As ligações não fechadas mantêm memória do lado do servidor e podem eventualmente esgotar o pool de ligações, fazendo com que novas tentativas de ligação bloqueem ou falhem.

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

Uma vez fechada, a ligação não pode ser utilizada:

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

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

Chamar close() várias vezes é seguro (idempotente):

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

Gestores de contexto

Use a with instrução para gerir ligações na maioria das aplicações. Garante que o controlador fecha a ligação quando o bloco termina, mesmo que ocorra uma exceção. Esta abordagem elimina o risco de ligações vazadas devido a chamadas esquecidas 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

O gestor de contexto fecha a ligação ao sair. Não confirma automaticamente nem efetua rollback das transações:

• Sempre: Chama close() ao sair, quer tenha ocorrido ou não uma exceção.
• close() comportamento: Se autocommit=False, quaisquer alterações não comprometidas são revertidas quando a ligação fecha.
• Deve chamar conn.commit() explicitamente para guardar as alterações.

Esta conceção segue o comportamento da PEP 249 e evita confirmações parciais não intencionais. Se o seu código lançar uma exceção antes de chegar a commit(), a transação em curso é anulada de forma segura:

# 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

Modo de confirmação automática

Por defeito, autocommit=False, o que significa que cada instrução é executada dentro de uma transação implícita. Tem de chamar conn.commit() para guardar as alterações ou conn.rollback() para as descartar. As transações implícitas são a escolha mais segura para modificações de dados porque permitem agrupar múltiplas instruções numa única operação atómica.

Ative a confirmação automática quando pretender que cada instrução seja confirmada de imediato. O autocommit é útil para operações DDL (CREATE TABLE, ALTER INDEX), cargas de trabalho apenas de leitura ou scripts administrativos onde não é necessário agrupar transações:

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

Ative a confirmação automática para que cada instrução seja imediatamente confirmada. Utilize autocommit=True no momento da ligação, ou altere-o depois de ligar com setautocommit() ou por atribuição direta da propriedade:

# 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

Tempo limite de ligação

Defina o timeout da ligação para controlar quanto tempo o driver espera para estabelecer a ligação antes de gerar um erro. Um tempo de espera razoável de ligação é importante para aplicações implementadas em ambientes com redes pouco fiáveis ou para falhas rápidas quando um servidor é inacessível:

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

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

Um tempo limite de 0 significa que não existe tempo limite (esperar indefinidamente). Defina tempos de espera razoáveis na produção; Uma tentativa de ligação suspensa sem timeout bloqueia permanentemente o fio que chama.

Se o objetivo for Base de Dados SQL do Azure serverless com autopausa ativada, use pelo menos 60. Uma base de dados em pausa automática recomeça na primeira ligação, e a retomada pode demorar entre 30 a 60 segundos ou mais. Um timeout mais curto expira antes da retomada terminar e a tentativa de ligação falhar.

Atributos de ligação

Use set_attr() para modificar o comportamento da ligação em tempo de execução. Os atributos de ligação controlam definições de driver de baixo nível, como modo de acesso, isolamento de transações e tamanho do pacote. A maioria das aplicações não precisa de alterar estes atributos, mas são úteis para cenários específicos:

• Modo apenas de leitura: Previne escritas acidentais em consultas de relatório.
• Isolamento de transações: Controla como as transações concorrentes interagem (uso SERIALIZABLE para consistência estrita, READ_COMMITTED para uso geral).
• Tamanho do pacote: Ajuste para redes de alta latência ou alto débito.

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)

Atributos disponíveis:

Atributos de pré-conexão

Alguns atributos devem ser definidos antes de o driver estabelecer a ligação (por exemplo, o tempo limite de início de sessão). Passa-os por attrs_before:

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

Obter informações de conexão

Use getinfo() para recuperar metadados do driver e do servidor para registo, diagnóstico ou adaptação de comportamento com base nas capacidades do servidor:

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

Obtenha uma lista de constantes de informação disponíveis:

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

Personagem Search Escape

A propriedade searchescape devolve o carácter utilizado para escapar os carateres universais (% e _) nos padrões LIKE. Use-o para procurar em segurança caracteres coringa literais nas entradas do utilizador:

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ção e descodificação

Configure a codificação de texto para instruções e resultados SQL. As definições padrão funcionam para a maioria das aplicações. Muda-os apenas se te ligares a um servidor que use uma codificação não UTF-8 para char/varchar colunas. A codificação que um servidor utiliza depende da intercalação da coluna:

# 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ções padrão:

Melhores práticas

• Use gestores de contexto (blocos with) para todas as ligações no código da aplicação. Garantem a limpeza mesmo quando ocorrem exceções.
• Utilize o agrupamento de ligações para um melhor desempenho (ativado por predefinição). Consulte agrupamento de ligações.
• Defina tempos apropriados para o seu ambiente de rede. Um timeout de 30 segundos é adequado para a maioria das implementações na cloud; aumente-a para ligações interregionais ou VPN. Use pelo menos 60 para Base de Dados SQL do Azure serverless com autopausa ativada, porque uma base de dados com pausa automática pode demorar entre 30 a 60 segundos ou mais a retomar na primeira ligação.
• Define MultiSubnetFailover=yes na cadeia de ligação quando o destino é Base de Dados SQL do Azure, Azure SQL Managed Instance, SQL database no Microsoft Fabric, um ouvinte de grupo de disponibilidade ou uma instância de cluster de failover. É seguro em alvos de IP único, por isso mantenha-o ativado para todos os endpoints TCP da família Microsoft SQL.
• Utilize autocommit=False (o padrão) para cenários de modificação de dados em que necessita de atomicidade transacional.
• Utilize autocommit=True para operações DDL, consultas apenas de leitura e scripts de administração.
• Não partilhes ligações entre tópicos. O nível de segurança das threads do controlador é 1 (as threads podem partilhar o módulo, mas não as ligações).

Conteúdo relacionado

• Cadeias de ligação
• Pool de conexões
• Gestão de transações
• Gestão do cursor