Descoberta de esquema com mssql-python

A classe cursor mssql-python fornece nove métodos de metadados que correspondem às funções do catálogo ODBC. Use estes métodos para descobrir tabelas, colunas, procedimentos armazenados, chaves e índices de forma programática. Ajudam-no a construir aplicações orientadas por dados que se adaptam ao esquema da base de dados em tempo de execução, como ferramentas de migração, geradores de código ou painéis de administração.

Método Função ODBC Devoluções Quando utilizar
tables() SQLTables Informação sobre tabela e visualização. Bases de dados de inventário. Valide a existência da tabela antes das consultas.
columns() SQLColumns Detalhes da coluna. Gerar DDL, construir consultas dinâmicas ou mapear colunas para código.
procedures() SQLProcedures Informação armazenada sobre procedimentos. Descubra as APIs disponíveis. Gerar wrappers para chamadas de procedimentos.
primaryKeys() SQLPrimaryKeys Colunas principais. Identificar identificadores de linha únicos para operações UPDATE/DELETE.
foreignKeys() SQLForeignKeys Relações-chave estrangeiras. Mapear as relações da tabela, determinar a ordem de eliminação dos scripts de limpeza.
statistics() SQLStatistics Informação de índice e estatísticas. Verifique a cobertura do índice para otimização do desempenho.
rowIdColumns() SQLSpecialColumns (ROWID) Colunas de identificadores de linha únicos. Encontre as melhores colunas para identificar linhas específicas.
rowVerColumns() SQLSpecialColumns (ROWVER) Colunas de versão de linha. Implementar concorrência otimista (detetar modificações simultâneas).
getTypeInfo() SQLGetTypeInfo Informações sobre o tipo de dados. Descubra os tipos suportados para compatibilidade multiplataforma.

Cada método devolve um cursor que podes iterar para aceder aos resultados.

Tables

Listar tabelas e vistas na base de dados:

cursor = conn.cursor()

# List all tables
for row in cursor.tables():
    print(f"{row.table_schem}.{row.table_name} ({row.table_type})")

# Filter by name (supports wildcards % and _)
for row in cursor.tables(table="Product%"):
    print(row.table_name)

# Filter by schema
for row in cursor.tables(schema="Sales"):
    print(row.table_name)

# Filter by type
for row in cursor.tables(tableType="TABLE"):  # Excludes views
    print(row.table_name)

Parâmetros de tables()

Os seguintes parâmetros controlam a descoberta da tabela:

Parâmetro Descrição
table Padrão do nome da tabela (suporta os carateres universais % e _).
catalog Nome do catálogo (base de dados).
schema Padrão de nomes de esquema.
tableType Filtrar por tipo: TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, , . SYNONYM

Colunas de resultado de tables()

O tables() método devolve as seguintes colunas para cada tabela ou vista:

Column Descrição
table_cat Nome do catálogo (base de dados).
table_schem Nome do esquema.
table_name Nome da tabela ou da vista.
table_type TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, SYNONYM.
remarks Descrição ou comentários.

Verifique se existe uma tabela

Verifique se existe uma tabela antes de a consultar:

if cursor.tables(table="Product", schema="Production").fetchone():
    print("Product table exists")
else:
    print("Product table not found")

Columns

Obter informação de colunas para tabelas:

# All columns in a table
for row in cursor.columns(table="Product", schema="Production"):
    print(f"{row.column_name}: {row.type_name}({row.column_size})")
    print(f"  Nullable: {row.nullable}, Position: {row.ordinal_position}")

# Filter by column name
for row in cursor.columns(table="Product", schema="Production", column="List%"):
    print(row.column_name)

parâmetros columns()

Filtros para refinar a identificação da coluna:

Parâmetro Descrição
table Padrão de nomes da tabela.
catalog Nome do catálogo (base de dados).
schema Padrão de nomes de esquema.
column Padrão de nomes das colunas.

columns() colunas de resultados

O columns() método devolve informações detalhadas sobre cada coluna:

Column Descrição
table_cat, table_schem, table_name Identificadores de localização.
column_name Nome da coluna.
data_type Código de tipo de dados SQL.
type_name Nome do tipo de dado (por exemplo, varchar, int).
column_size Comprimento máximo ou precisão.
buffer_length Tamanho do buffer para transferências.
decimal_digits Escala para tipos numéricos.
nullable 0 para NOT NULL, 1 para nulável.
column_def Valor padrão.
ordinal_position Posição da coluna (baseada em 1).
is_nullable "YES" ou "NO".

Procedimentos armazenados

Descubra procedimentos guardados:

# List all procedures
for row in cursor.procedures():
    print(f"{row.procedure_schem}.{row.procedure_name}")

# Filter by name pattern
for row in cursor.procedures(procedure="Get%"):
    print(row.procedure_name)

Parâmetros de procedimentos()

Filtrar procedimentos armazenados por nome ou esquema:

Parâmetro Descrição
procedure Padrão de nome do procedimento.
catalog Nome do catálogo (base de dados).
schema Padrão de nomes de esquema.

Colunas de resultados de procedimentos()

O procedures() método devolve metadados para cada procedimento armazenado:

Column Descrição
procedure_cat, procedure_schem Identificadores de localização.
procedure_name Nome do procedimento.
num_input_params Número de parâmetros de entrada.
num_output_params Número de parâmetros de saída.
num_result_sets Número de conjuntos de resultados.
remarks Description.
procedure_type Indicador de tipo.

Chaves primárias

Obter as colunas da chave primária de uma tabela:

for row in cursor.primaryKeys(table="Product", schema="Production"):
    print(f"PK column: {row.column_name} (position {row.key_seq})")
    print(f"Constraint name: {row.pk_name}")

parâmetros primaryKeys()

Parâmetros para recuperar a informação da chave primária:

Parâmetro Descrição
table Nome da tabela (obrigatório).
catalog Nome do catálogo (base de dados).
schema Nome do esquema.

Colunas de resultados primaryKeys()

O primaryKeys() método devolve a seguinte informação:

Column Descrição
table_cat, table_schem, table_name Identificadores de localização.
column_name Coluna na chave primária.
key_seq Posição em chave de múltiplas colunas (baseada em 1).
pk_name Nome da restrição de chave primária.

Chaves estrangeiras

Descobrir relações de chave estrangeira:

# Foreign keys from a table (outbound references)
for row in cursor.foreignKeys(table="SalesOrderDetail", schema="Sales"):
    print(f"FK {row.fk_name}:")
    print(f"  {row.fktable_name}.{row.fkcolumn_name}")
    print(f"  -> {row.pktable_name}.{row.pkcolumn_name}")

# Foreign keys to a table (inbound references)
for row in cursor.foreignKeys(foreignTable="Product", foreignSchema="Production"):
    print(f"{row.fktable_name} references Product")

parâmetros foreignKeys()

Especifique tabelas de chave primária ou estrangeira para descobrir relações:

Parâmetro Descrição
table Nome principal da tabela de chaves.
catalog Catálogo de chaves primárias.
schema Esquema de chave primária.
foreignTable Nome da tabela de chaves estrangeiras.
foreignCatalog Catálogo de chaves estrangeiras.
foreignSchema Esquema de chave externa.

Colunas de resultados foreignKeys()

O foreignKeys() método devolve as seguintes colunas que descrevem as relações:

Column Descrição
pktable_cat, pktable_schem, pktable_name Tabela de referência (primária).
pkcolumn_name Coluna referenciada.
fktable_cat, fktable_schem, fktable_name Tabela de referência (estrangeira).
fkcolumn_name Coluna de referência.
key_seq Posição na chave de múltiplas colunas.
update_rule Ação em UPDATE.
delete_rule Ação em DELETE.
fk_name Nome de restrição de chave estrangeira.
pk_name Nome da restrição de chave primária.

Índices e estatísticas

Obter informações do índice de uma tabela:

# All indexes on a table
for row in cursor.statistics(table="Product", schema="Production"):
    if row.index_name:  # Skip table statistics row
        print(f"Index: {row.index_name}")
        print(f"  Column: {row.column_name} (position {row.ordinal_position})")
        print(f"  Unique: {not row.non_unique}")

# Only unique indexes
for row in cursor.statistics(table="Product", schema="Production", unique=True):
    print(f"Unique index: {row.index_name}")

parâmetros de estatística()

Configure a descoberta de índices com estes filtros:

Parâmetro Default Descrição
table (required) Nome da tabela.
catalog None Nome do catálogo (base de dados).
schema None Nome do esquema.
unique Falso Só devolve índices únicos.
quick Verdade Ignorar a obtenção dispendiosa da cardinalidade e do número de páginas.

Colunas de resultado de statistics()

O statistics() método devolve informações de índice e estatísticas:

Column Descrição
table_cat, table_schem, table_name Identificadores de localização.
non_unique 0 para único, 1 para não-único.
index_name Nome do índice.
type Tipo de índice.
ordinal_position Posição da coluna no índice.
column_name Nome da coluna.
asc_or_desc A para subir, D para descer.
cardinality Estimativa do número de linhas.
pages Contagem de páginas.

Colunas de identificadores de linha

Encontre colunas que identifiquem de forma única uma linha:

for row in cursor.rowIdColumns(table="Product", schema="Production"):
    print(f"Row ID column: {row.column_name} ({row.type_name})")

Este método devolve o melhor conjunto de colunas para identificar de forma única uma linha, que pode ser a chave primária ou um índice único.

Colunas de versão de linha

Encontre colunas que sejam automaticamente atualizadas quando qualquer valor de linha muda. Utilize colunas de versão de linha para controlo de concorrência otimista, em que lê a versão de uma linha, faz alterações e, em seguida, verifica se a versão atual da linha é a mesma antes de gravar:

for row in cursor.rowVerColumns(table="Product", schema="Production"):
    print(f"Version column: {row.column_name}")

O resultado inclui tipicamente as colunas rowversion/timestamp utilizadas para concorrência otimista.

Informações sobre o tipo de dados

Obtenha informações sobre os tipos de dados SQL suportados:

# All supported types
for row in cursor.getTypeInfo():
    print(f"{row.type_name}: {row.data_type}")
    print(f"  Max size: {row.column_size}")
    print(f"  Nullable: {row.nullable}")

# Specific type
for row in cursor.getTypeInfo(sqlType=mssql_python.SQL_VARCHAR):
    print(f"VARCHAR max size: {row.column_size}")

parâmetros getTypeInfo()

Parâmetros opcionais para filtrar os tipos SQL suportados:

Parâmetro Descrição
sqlType Constante de tipo SQL (omitir para todos os tipos).

Considerações de segurança

Atenção

Estes métodos expõem metadados do esquema da base de dados. Embora os próprios métodos sejam seguros de executar, a informação devolvida revela a estrutura da sua base de dados (nomes de tabelas, nomes de colunas, relações, tipos de dados).

  • Não exponha metadados brutos a utilizadores não confiáveis.
  • Sanitizar ou filtrar resulta em aplicações multiinquilino.
  • Restrinja o acesso em aplicações expostas externamente.

Exemplo: Gerar um relatório de esquema

def describe_table(conn, table_name):
    """Generate a schema description for a table."""
    cursor = conn.cursor()
    
    print(f"\n=== {table_name} ===\n")
    
    # Columns
    print("Columns:")
    for col in cursor.columns(table=table_name):
        nullable = "NULL" if col.nullable else "NOT NULL"
        print(f"  {col.column_name}: {col.type_name}({col.column_size}) {nullable}")
    
    # Primary key
    print("\nPrimary Key:")
    pk_cols = cursor.primaryKeys(table=table_name).fetchall()
    if pk_cols:
        pk_names = ", ".join(row.column_name for row in pk_cols)
        print(f"  {pk_cols[0].pk_name}: ({pk_names})")
    else:
        print("  (none)")
    
    # Foreign keys
    print("\nForeign Keys:")
    for fk in cursor.foreignKeys(table=table_name):
        print(f"  {fk.fk_name}: {fk.fkcolumn_name} -> {fk.pktable_name}.{fk.pkcolumn_name}")
    
    # Indexes
    print("\nIndexes:")
    for idx in cursor.statistics(table=table_name):
        if idx.index_name:
            unique = "UNIQUE " if not idx.non_unique else ""
            print(f"  {unique}{idx.index_name}: {idx.column_name}")

# Usage
describe_table(conn, "Product")

Conteúdo relacionado

  • Last updated on