Descubrimiento de esquemas con mssql-python

La clase cursor mssql-python proporciona nueve métodos de metadatos que se asignan a funciones de catálogo ODBC. Utiliza estos métodos para descubrir tablas, columnas, procedimientos almacenados, claves e índices de forma programática. Te ayudan a construir aplicaciones basadas en datos que se adaptan al esquema de la base de datos en tiempo de ejecución, como herramientas de migración, generadores de código o paneles de administración.

Método Función ODBC Devoluciones Cuándo se deben usar
tables() SQLTables Información sobre tablas y vistas. Bases de datos de inventario. Valida la existencia de la tabla antes de las consultas.
columns() SQLColumns Detalles de la columna. Genera DDL, crea consultas dinámicas o asigna columnas al código.
procedures() SQLProcedures Información de procedimientos almacenados. Descubre las APIs disponibles. Genera envoltorios de llamadas a procedimientos.
primaryKeys() SQLPrimaryKeys Columnas clave principales. Identifica identificadores de fila únicos para operaciones UPDATE/DELETE.
foreignKeys() SQLForeignKeys Relaciones clave extranjeras. Identifica las relaciones entre tablas y determina el orden de eliminación para los scripts de limpieza.
statistics() SQLStatistics Información de índice y estadísticas. Verifica la cobertura del índice para la optimización del rendimiento.
rowIdColumns() SQLSpecialColumns (ROWID) Columnas únicas de identificadores de fila. Encuentra las mejores columnas para identificar filas específicas.
rowVerColumns() SQLSpecialColumns (ROWVER) Columnas de la versión de fila. Implementar concurrencia optimista (detectar modificaciones concurrentes).
getTypeInfo() SQLGetTypeInfo Información sobre el tipo de datos. Descubre tipos compatibles para compatibilidad multiplataforma.

Cada método devuelve un cursor que puedes iterar para acceder a los resultados.

Tables

Mostrar las tablas y vistas en la base de datos:

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

Los siguientes parámetros controlan el descubrimiento de la tabla:

Parámetro Descripción
table Patrón de nombre de tabla (admite comodines % y _).
catalog Nombre del catálogo (base de datos).
schema Patrón de nombres de esquema.
tableType Filtrar por tipo: TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, , SYNONYM.

Columnas de resultados de tables()

El tables() método devuelve las siguientes columnas para cada tabla o vista:

Column Descripción
table_cat Nombre del catálogo (base de datos).
table_schem Nombre del esquema.
table_name Nombre de la tabla o vista.
table_type TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, SYNONYM.
remarks Descripción o comentarios.

Comprueba si existe una tabla

Verifica que exista una tabla antes de consultarla:

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

Columnas

Recuperar información de columnas para tablas:

# 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 la identificación de columnas:

Parámetro Descripción
table Patrón de nombre de tabla.
catalog Nombre del catálogo (base de datos).
schema Patrón de nombres de esquema.
column Patrón de nombres de columna.

columnas() columnas de resultados

El columns() método devuelve información detallada sobre cada columna:

Column Descripción
table_cat, table_schem, table_name Identificadores de ubicación.
column_name Nombre de la columna.
data_type Código de tipo de datos SQL.
type_name Nombre del tipo de dato (por ejemplo, varchar, int).
column_size Longitud máxima o precisión.
buffer_length Tamaño del buffer para transferencias.
decimal_digits Escala para tipos numéricos.
nullable 0 para NO NULO, para 1 anulable.
column_def Valor predeterminado.
ordinal_position Posición de la columna (basada en 1).
is_nullable "YES" o "NO".

Procedimientos almacenados

Descubre procedimientos almacenados:

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

Filtra los procedimientos almacenados por nombre o esquema:

Parámetro Descripción
procedure Patrón de nomenclatura del procedimiento.
catalog Nombre del catálogo (base de datos).
schema Patrón de nombres de esquema.

Columnas de resultados de procedimientos()

El procedures() método devuelve metadatos para cada procedimiento almacenado:

Column Descripción
procedure_cat, procedure_schem Identificadores de ubicación.
procedure_name Nombre del procedimiento.
num_input_params Número de parámetros de entrada.
num_output_params Número de parámetros de salida.
num_result_sets Número de conjuntos de resultados.
remarks Descripción.
procedure_type Indicador de tipo.

Claves principales

Obtén las columnas clave principales de una tabla:

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

Parámetros para obtener la información clave primaria:

Parámetro Descripción
table Nombre de la tabla (obligatorio).
catalog Nombre del catálogo (base de datos).
schema Nombre del esquema.

Columnas de resultados primaryKeys()

El primaryKeys() método devuelve la siguiente información:

Column Descripción
table_cat, table_schem, table_name Identificadores de ubicación.
column_name Columna en la clave primaria.
key_seq Posición en clave multicolumna (basada en 1).
pk_name Nombre de restricción de clave primaria.

Claves externas

Descubre relaciones con clave extranjera:

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

Especifica tablas de clave primaria o externa para descubrir relaciones:

Parámetro Descripción
table Nombre de la tabla de clave principal.
catalog Catálogo de claves primarias.
schema Esquema de clave primaria.
foreignTable Nombre de la tabla de clave externa.
foreignCatalog Catálogo de claves foráneas.
foreignSchema Esquema de clave extranjera.

Columnas de resultados foreignKeys()

El foreignKeys() método devuelve las siguientes columnas que describen las relaciones:

Column Descripción
pktable_cat, pktable_schem, pktable_name Tabla de referencia (primaria).
pkcolumn_name Columna referenciada.
fktable_cat, fktable_schem, fktable_name Tabla de referencia (extranjera).
fkcolumn_name Columna de referencias.
key_seq Posición en clave de varias columnas.
update_rule Acción en UPDATE.
delete_rule Acción en DELETE.
fk_name Nombre de la restricción de clave foránea.
pk_name Nombre de restricción de clave primaria.

Índices y estadísticas

Obtén información del índice para una tabla:

# 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 estadísticos()

Configura el descubrimiento de índices con estos filtros:

Parámetro Default Descripción
table (requerido) Nombre de la tabla.
catalog None Nombre del catálogo (base de datos).
schema None Nombre del esquema.
unique Falso Solo devuelven índices únicos.
quick True Evita la costosa recuperación de cardinalidad/páginas.

Columnas de resultados estadísticas()

El statistics() método devuelve información de índice y estadísticas:

Column Descripción
table_cat, table_schem, table_name Identificadores de ubicación.
non_unique 0 para único, 1 para no único.
index_name Nombre del índice.
type Tipo de índice.
ordinal_position Posición de columna en el índice.
column_name Nombre de la columna.
asc_or_desc A para ascender, D para descender.
cardinality Estimación del número de filas.
pages Número de páginas.

Columnas identificadoras de filas

Encuentra columnas que identifiquen de forma única una fila:

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

Este método devuelve el mejor conjunto de columnas para identificar de forma única una fila, que puede ser la clave primaria o un índice único.

Columnas de la versión de fila

Busca columnas que se actualicen automáticamente cuando cambia el valor de cualquier fila. Utiliza las columnas de la versión de fila para un control optimista de la concurrencia, donde lees la versión de una fila, haces cambios y luego verificas que la versión actual de la fila es la misma antes de escribir:

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

El resultado suele incluir rowversion/timestamp columnas utilizadas para la concurrencia optimista.

Información de tipo de datos

Obtén información sobre los tipos de datos SQL compatibles:

# 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 opcionales para filtrar los tipos SQL compatibles:

Parámetro Descripción
sqlType Constante de tipo SQL (omitir para todos los tipos).

Consideraciones de seguridad

Caution

Estos métodos exponen metadatos del esquema de la base de datos. Aunque los métodos en sí son seguros de ejecutar, la información retornada revela la estructura de tu base de datos (nombres de tablas, nombres de columnas, relaciones, tipos de datos).

  • No expongas metadatos en bruto a usuarios no confiables.
  • Depurar o filtrar resultados en aplicaciones multicliente.
  • Restringir el acceso en aplicaciones orientadas externamente.

Ejemplo: Generar un informe 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")

Contenido relacionado

  • Last updated on