Schema-ontdekking met mssql-python

De mssql-python cursorklasse biedt negen metadata-methoden die worden gekoppeld aan ODBC-catalogusfuncties. Gebruik deze methoden om tabellen, kolommen, opgeslagen procedures, sleutels en indexen programmatisch te ontdekken. Zij helpen je bij het bouwen van datagedreven applicaties die zich tijdens runtime aanpassen aan het databaseschema, zoals migratietools, codegeneratoren of beheerdersdashboards.

Methode ODBC-functie Returns Wanneer gebruiken
tables() SQLTables Tabel en bekijk informatie. Voorraaddatabases. Valideer het bestaan van de tabel vóór de query.
columns() SQLColumns Kolomdetails. Genereer DDL, bouw dynamische queries, of map kolommen aan code.
procedures() SQLProcedures Informatie over opgeslagen procedures. Ontdek beschikbare API's. Genereer wrappers voor procedureaanroepen.
primaryKeys() SQLPrimaryKeys Primaire sleutelkolommen. Bepaal unieke rij-id's voor UPDATE/DELETE-bewerkingen.
foreignKeys() SQLForeignKeys Buitenlandse sleutelrelaties. Kaart tabelrelaties in kaart en bepaal de verwijdervolgorde voor cleanup scripts.
statistics() SQLStatistics Index- en statistische informatie. Controleer de indexdekking voor prestatie-tuning.
rowIdColumns() SQLSpecialColumns (ROWID) Unieke rij-id-kolommen. Zoek de beste kolommen om specifieke rijen te identificeren.
rowVerColumns() SQLSpecialColumns (ROWVER) Kolommen van rijversie. Voer optimistische gelijktijdigheid uit (detecteer gelijktijdige aanpassingen).
getTypeInfo() SQLGetTypeInfo Gegevenstypegegevens. Ontdek ondersteunde types voor cross-platform compatibiliteit.

Elke methode geeft een cursor terug die je kunt itereren om de resultaten te bereiken.

Tables

Geef de tabellen en weergaven in de database weer:

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

De volgende parameters regelen de tabeldetectie:

Parameter Beschrijving
table Tabelnaampatroon (ondersteunt %- en _-jokertekens).
catalog Naam van de catalogus (database).
schema Schemanaampatroon.
tableType Filteren op type: TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, SYNONYM.

kolommen van het resultaat van tables()

De tables() methode geeft de volgende kolommen terug voor elke tabel of weergave:

Column Beschrijving
table_cat Naam van de catalogus (database).
table_schem Naam van schema.
table_name Naam van de tabel of weergave.
table_type TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, , ALIAS. SYNONYM
remarks Beschrijving of opmerkingen.

Controleer of er een tabel bestaat

Controleer of er een tabel bestaat voordat je deze bevraagt:

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

Columns

Kolominformatie ophalen voor tabellen:

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

columns() parameters

Filters voor het verfijnen van het ontdekken van kolommen:

Parameter Beschrijving
table Tabelnaampatroon.
catalog Naam van catalogus (database).
schema Schemanaampatroon.
column Kolomnaampatroon.

columns() resultaatkolommen

De columns() methode geeft gedetailleerde informatie over elke kolom:

Column Beschrijving
table_cat, table_schem, table_name Locatie-identificaties.
column_name Kolomnaam.
data_type code van het SQL-gegevenstype
type_name Naam van het datatype (bijvoorbeeld varchar, int).
column_size Maximale lengte of precisie.
buffer_length Buffergrootte voor overdrachten.
decimal_digits Schaal voor numerieke types.
nullable 0 voor NIET NULL, 1 voor nullable.
column_def Standaardwaarde.
ordinal_position Kolompositie (1-gebaseerd).
is_nullable "YES" of "NO".

opgeslagen procedures

Ontdek opgeslagen procedures:

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

procedures() parameters

Filter opgeslagen procedures op naam of schema:

Parameter Beschrijving
procedure Procedurenaampatroon.
catalog Naam van de catalogus (database).
schema Schemanaampatroon.

resultaatkolommen van procedures()

De procedures() methode levert metadata terug voor elke opgeslagen procedure:

Column Beschrijving
procedure_cat, procedure_schem Locatie-identificaties.
procedure_name Naam van de procedure.
num_input_params Aantal invoerparameters.
num_output_params Aantal outputparameters.
num_result_sets Aantal resultaatsets.
remarks Description.
procedure_type Typeaanduiding.

Primaire sleutels

De primaire-sleutelkolommen van een tabel ophalen:

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

primaryKeys() parameters

Parameters om primaire sleutelinformatie op te halen:

Parameter Beschrijving
table Tabelnaam (vereist).
catalog Naam van de catalogus (database).
schema Naam van schema.

primaryKeys() resultaatkolommen

De primaryKeys() methode geeft de volgende informatie terug:

Column Beschrijving
table_cat, table_schem, table_name Locatie-identificaties.
column_name Kolom in de primaire sleutel.
key_seq Positie in de sleutel met meerdere kolommen (vanaf 1 geteld).
pk_name Naam van primaire sleutelbeperking.

Vreemde sleutels

Ontdek buitenlandse sleutelrelaties:

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

foreignKeys()-parameters

Specificeer primaire sleutel- of vreemde sleuteltabellen om relaties te ontdekken:

Parameter Beschrijving
table Naam van primaire sleuteltabel.
catalog Primaire sleutelcatalogus.
schema Schema van de primaire sleutel.
foreignTable Naam van de tabel met de vreemde sleutel.
foreignCatalog Catalogus van vreemde sleutels.
foreignSchema Vreemde sleutel schema.

foreignKeys() resultaatkolommen

De foreignKeys() methode geeft de volgende kolommen terug die relaties beschrijven:

Column Beschrijving
pktable_cat, pktable_schem, pktable_name Gerefereerde (primaire) tabel.
pkcolumn_name Kolom waarnaar wordt verwezen.
fktable_cat, fktable_schem, fktable_name Verwijzende tabel (vreemde).
fkcolumn_name Referentiekolom.
key_seq Positie in sleutel met meerdere kolommen.
update_rule Actie op UPDATE uitvoeren.
delete_rule Actie op DELETE uitvoeren.
fk_name Naam van de vreemde sleutelbeperking.
pk_name Naam van primaire sleutelbeperking.

Indexen en statistieken

Krijg indexinformatie voor een tabel:

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

Statistiek()parameters

Configureer indexontdekking met deze filters:

Parameter Default Beschrijving
table (required) Tabelnaam.
catalog None Naam van de catalogus (database).
schema None Naam van schema.
unique Onwaar Geef alleen unieke indexen terug.
quick True Sla kostbare kardinaliteit en het ophalen van pagina's over.

resultaatkolommen van statistics()

De statistics() methode geeft index- en statistiekinformatie terug:

Column Beschrijving
table_cat, table_schem, table_name Locatie-identificaties.
non_unique 0 voor uniek, 1 voor niet-uniek.
index_name Indexnaam.
type Indextype.
ordinal_position Kolompositie in index.
column_name Kolomnaam.
asc_or_desc A voor stijgend, D voor dalend.
cardinality Schatting van het aantal rijen.
pages Aantal pagina's.

Kolommen met rijidentificatie

Zoek kolommen die een rij uniek identificeren:

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

Deze methode geeft de beste set kolommen terug om een rij uniek te identificeren, wat de primaire sleutel of een unieke index kan zijn.

Kolommen van rijversies

Zoek kolommen die automatisch worden bijgewerkt wanneer een rijwaarde verandert. Gebruik kolommen van de rijversie voor optimistische gelijktijdigheidscontrole, waarbij je de versie van een rij leest, wijzigingen aanbrengt en vervolgens controleert of de huidige rijversie hetzelfde is voordat je schrijft:

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

Het resultaat bevat doorgaans kolommen rowversion/timestamp die worden gebruikt bij optimistische gelijktijdigheidscontrole.

Gegevenstypegegevens

Informatie over ondersteunde SQL-datatypes:

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

getTypeInfo()-parameters

Optionele parameters om ondersteunde SQL-types te filteren:

Parameter Beschrijving
sqlType SQL-typeconstante (weglaten voor alle typen).

Beveiligingsoverwegingen

Waarschuwing

Deze methoden maken metadata van databaseschema's bloot. Hoewel de methoden zelf veilig uit te voeren zijn, onthult de teruggegeven informatie je databasestructuur (tabelnamen, kolomnamen, relaties, datatypes).

  • Maak geen ruwe metadata bloot aan onbetrouwbare gebruikers.
  • Reinigen of filteren resulteert in multitenant-applicaties.
  • Beperk toegang in extern gerichte applicaties.

Voorbeeld: Genereer een schemarapport

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

Verwante onderwerpen

  • Last updated on