Découverte de schéma avec mssql-python

La classe curseur mssql-python fournit neuf méthodes de métadonnées qui correspondent aux fonctions du catalogue ODBC. Utilisez ces méthodes pour découvrir les tableaux, colonnes, procédures stockées, clés et index de manière programmatique. Ils vous aident à construire des applications basées sur les données qui s’adaptent au schéma de la base de données à l’exécution, telles que des outils de migration, des générateurs de code ou des tableaux de bord d’administration.

Méthode Fonction ODBC Returns Quand utiliser
tables() SQLTables Informations sur les tables et les vues. Bases de données d’inventaire. Validez l’existence de la table avant les requêtes.
columns() SQLColumns Détails de la colonne. Générez DDL, créez des requêtes dynamiques ou mappez des colonnes en code.
procedures() SQLProcedures Informations sur les procédures stockées. Découvrez les API disponibles. Générez des enveloppes d’appels de procédure.
primaryKeys() SQLPrimaryKeys Colonnes clés principales. Définir les identifiants uniques de ligne pour les opérations UPDATE/DELETE.
foreignKeys() SQLForeignKeys Relations clés étrangères. Mapper les relations de table, déterminer l’ordre de suppression pour les scripts de nettoyage.
statistics() SQLStatistics Informations sur l’index et les statistiques. Vérifiez la couverture de l’indice pour le réglage des performances.
rowIdColumns() SQLSpecialColumns (ROWID) Colonnes d’identifiants de ligne uniques. Trouvez les meilleures colonnes à utiliser pour identifier des lignes spécifiques.
rowVerColumns() SQLSpecialColumns (ROWVER) Colonnes de version de ligne. Implémenter la concurrence optimiste (détecter les modifications concurrentes).
getTypeInfo() SQLGetTypeInfo Informations sur le type de données. Découvrez les types pris en charge pour la compatibilité multiplateforme.

Chaque méthode renvoie un curseur que vous pouvez itérer pour accéder aux résultats.

Tables

Lister les tables et les vues dans la base de données :

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)

tables() paramètres

Les paramètres suivants contrôlent la découverte de la table :

Paramètre Description
table Modèle de nom de table (prend en charge les caractères génériques % et _).
catalog Nom du catalogue (base de données).
schema Motif de nom de schéma.
tableType Filtrer par type : TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, , . SYNONYM

Colonnes du résultat tables()

La tables() méthode renvoie les colonnes suivantes pour chaque table ou vue :

Column Description
table_cat Nom du catalogue (base de données).
table_schem Nom du schéma.
table_name Nom de table ou de vue.
table_type TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, , . SYNONYM
remarks Description ou commentaires.

Vérifiez si un tableau existe

Vérifiez qu’une table existe avant de l’interroger :

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

Columns

Récupérer les informations de colonnes pour les tables :

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

paramètres column()

Filtres pour affiner l’identification des colonnes :

Paramètre Description
table Modèle de nom de table.
catalog Nom du catalogue (base de données).
schema Motif de nom de schéma.
column Modèle de nom de colonne.

colonnes() colonnes de résultats

La columns() méthode renvoie des informations détaillées sur chaque colonne :

Column Description
table_cat, table_schem, table_name Identifiants de localisation.
column_name Nom de la colonne.
data_type Code de type de données SQL.
type_name Nom du type de données (par exemple, varchar, int).
column_size Longueur maximale ou précision.
buffer_length Taille du tampon pour les transferts.
decimal_digits Échelle pour les types numériques.
nullable 0 pour NOT NULL, 1 pour nullable.
column_def Valeur par défaut.
ordinal_position Position de la colonne (basée sur 1).
is_nullable "YES" ou "NO".

Procédures stockées

Découvrez les procédures stockées :

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

Paramètres des procédures()

Filtrez les procédures stockées par nom ou schéma :

Paramètre Description
procedure Modèle de nom de procédure.
catalog Nom du catalogue (base de données).
schema Modèle de nom de schéma

Colonnes de résultats Procedure()

La procedures() méthode renvoie les métadonnées pour chaque procédure stockée :

Column Description
procedure_cat, procedure_schem Identifiants de localisation.
procedure_name Nom de la procédure.
num_input_params Nombre de paramètres d’entrée.
num_output_params Nombre de paramètres de sortie.
num_result_sets Nombre d’ensembles de résultats.
remarks Description.
procedure_type Indicateur de type.

Clés primaires

Obtenez les colonnes clés principales pour un tableau :

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

paramètres primaryKeys()

Paramètres pour récupérer les informations de clé primaire :

Paramètre Description
table Nom du tableau (obligatoire).
catalog Nom du catalogue (base de données).
schema Nom du schéma.

Colonnes de résultats primaryKeys()

La primaryKeys() méthode renvoie les informations suivantes :

Column Description
table_cat, table_schem, table_name Identifiants de localisation.
column_name Colonne dans la clé primaire.
key_seq Position dans la clé multi-colonnes (basée sur 1).
pk_name Nom de contrainte de clé primaire.

Clés étrangères

Découvrez les relations clés étrangères :

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

paramètres foreignKeys()

Spécifiez des tables de clés primaires ou étrangères pour découvrir les relations :

Paramètre Description
table Nom de la table de clé primaire.
catalog Catalogue de clés primaires.
schema Schéma de clé primaire.
foreignTable Nom de la table de clés étrangères.
foreignCatalog Catalogue de clés étrangères.
foreignSchema Schéma de clé étrangère.

colonnes de résultats foreignKeys()

La foreignKeys() méthode renvoie les colonnes suivantes décrivant les relations :

Column Description
pktable_cat, pktable_schem, pktable_name Tableau référencé (principal).
pkcolumn_name Colonne référencée.
fktable_cat, fktable_schem, fktable_name Tableau de référence (étranger).
fkcolumn_name Colonne de référence.
key_seq Position dans la clé multi-colonnes.
update_rule Action sur UPDATE.
delete_rule Action sur DELETE.
fk_name Nom de contrainte de clé étrangère.
pk_name Nom de contrainte de clé primaire.

Index et statistiques

Obtenez les informations d’index pour un tableau :

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

paramètres de statistiques()

Configurez la découverte d’index avec ces filtres :

Paramètre Default Description
table (obligatoire) Nom de la table.
catalog None Nom du catalogue (base de données).
schema None Nom du schéma.
unique Faux Ne retournez que les index uniques.
quick True Ignorez la récupération coûteuse de la cardinalité et des pages.

Colonnes de résultats de statistics()

La statistics() méthode restitue les informations sur l’index et les statistiques :

Column Description
table_cat, table_schem, table_name Identifiants de localisation.
non_unique 0 pour unique, 1 pour non-unique.
index_name Nom de l’index.
type Type d’index.
ordinal_position Position de la colonne dans l’index.
column_name Nom de la colonne.
asc_or_desc A pour monter, D pour descendre.
cardinality Estimation du nombre de lignes.
pages Nombre de pages.

Colonnes d’identifiant de ligne

Trouvez des colonnes qui identifient de manière unique une ligne :

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

Cette méthode renvoie le meilleur ensemble de colonnes pour identifier de manière unique une ligne, qui peut être la clé primaire ou un index unique.

Colonnes de version de ligne

Trouvez des colonnes qui sont automatiquement mises à jour lorsqu’une valeur de ligne change. Utilisez des colonnes de version de ligne pour le contrôle de concurrence optimiste, dans lesquelles vous lisez la version d’une ligne, effectuez des modifications, puis vérifiez que la version actuelle de la ligne est identique avant d’écrire :

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

Le résultat inclut généralement des colonnes rowversion/timestamp utilisées pour la concurrence optimiste.

Informations sur le type de données

Obtenez des informations sur les types de données SQL pris en charge :

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

paramètres getTypeInfo()

Paramètres optionnels pour filtrer les types SQL pris en charge :

Paramètre Description
sqlType Constante de type SQL (à omettre pour tous les types).

Considérations relatives à la sécurité

Caution

Ces méthodes exposent les métadonnées des schémas de la base de données. Bien que les méthodes elles-mêmes soient sûres à exécuter, les informations retournées révèlent la structure de votre base de données (noms de tableaux, noms de colonnes, relations, types de données).

  • N’exposez pas les métadonnées brutes à des utilisateurs non fiables.
  • Nettoyer ou filtrer les résultats dans les applications multilocataires.
  • Restreignez l’accès dans les applications orientées vers l’extérieur.

Exemple : générer un rapport de schéma

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

Contenu connexe

  • Last updated on