Penemuan skema dengan mssql-python

Kelas kursor mssql-python menyediakan sembilan metode metadata yang dipetakan ke fungsi katalog ODBC. Gunakan metode ini untuk menemukan tabel, kolom, prosedur tersimpan, kunci, dan indeks secara terprogram. Mereka membantu Anda membangun aplikasi berbasis data yang beradaptasi dengan skema database saat runtime, seperti alat migrasi, pembuat kode, atau dasbor admin.

Metode Fungsi ODBC Returns Kapan digunakan
tables() SQLTables Informasi tabel dan tampilan. Basis data inventaris. Validasi keberadaan tabel sebelum kueri.
columns() SQLColumns Rincian kolom. Hasilkan DDL, buat kueri dinamis, atau petakan kolom ke kode.
procedures() SQLProcedures Informasi prosedur tersimpan. Temukan API yang tersedia. Hasilkan pembungkus panggilan prosedur.
primaryKeys() SQLPrimaryKeys Kolom kunci utama. Identifikasi ID baris unik untuk operasi UPDATE/DELETE.
foreignKeys() SQLForeignKeys Relasi kunci asing. Memetakan hubungan tabel, menentukan urutan penghapusan untuk skrip pembersihan.
statistics() SQLStatistics Informasi indeks dan statistik. Verifikasi cakupan indeks untuk penyetelan kinerja.
rowIdColumns() SQLSpecialColumns (ROWID) Kolom pengidentifikasi baris unik. Temukan kolom terbaik untuk digunakan untuk mengidentifikasi baris tertentu.
rowVerColumns() SQLSpecialColumns (ROWVER) Kolom versi baris. Terapkan konkurensi optimis (mendeteksi modifikasi bersamaan).
getTypeInfo() SQLGetTypeInfo Informasi jenis data. Temukan jenis yang didukung untuk kompatibilitas lintas platform.

Setiap metode mengembalikan kursor yang dapat Anda ulangi untuk mengakses hasilnya.

Tables

Cantumkan tabel dan tampilan dalam database:

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)

parameter tables()

Parameter berikut mengontrol penemuan tabel:

Parameter Deskripsi
table Pola nama tabel (mendukung karakter wildcard % dan _).
catalog Nama katalog (database).
schema Pola nama skema.
tableType Filter berdasarkan jenis: TABLE, , VIEWSYSTEM TABLEGLOBAL TEMPORARYLOCAL TEMPORARYALIASSYNONYM

Kolom hasil dari tables()

Metode ini tables() mengembalikan kolom berikut untuk setiap tabel atau tampilan:

Column Deskripsi
table_cat Nama katalog (database).
table_schem Nama skema.
table_name Nama tabel atau tampilan.
table_type TABLE, VIEW, , SYSTEM TABLEGLOBAL TEMPORARY, LOCAL TEMPORARY, ALIASSYNONYM,
remarks Deskripsi atau komentar.

Memeriksa apakah tabel ada

Verifikasi bahwa tabel ada sebelum mengkuerinya:

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

Kolom-kolom

Mengambil informasi kolom untuk tabel:

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

parameter columns()

Filter untuk mempersempit pencarian kolom:

Parameter Deskripsi
table Pola nama tabel.
catalog Nama katalog (database).
schema Pola nama skema.
column Pola nama kolom.

kolom hasil dari columns()

Metode ini columns() mengembalikan informasi terperinci tentang setiap kolom:

Column Deskripsi
table_cat, table_schem, table_name Pengidentifikasi lokasi.
column_name Nama kolom.
data_type Kode tipe data SQL.
type_name Nama tipe data (misalnya, varchar, ). int
column_size Panjang atau presisi maksimum.
buffer_length Ukuran buffer untuk transfer.
decimal_digits Skala untuk tipe numerik.
nullable 0 untuk NOT NULL, 1 untuk nullable.
column_def Nilai bawaan.
ordinal_position Posisi kolom (berbasis 1).
is_nullable "YES" atau "NO".

Prosedur yang disimpan

Temukan prosedur tersimpan:

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

Filter prosedur tersimpan berdasarkan nama atau skema:

Parameter Deskripsi
procedure Pola nama prosedur.
catalog Nama katalog (database).
schema Pola nama skema.

prosedur() kolom hasil

Metode ini procedures() mengembalikan metadata untuk setiap prosedur yang disimpan:

Column Deskripsi
procedure_cat, procedure_schem Pengidentifikasi lokasi.
procedure_name Nama prosedur.
num_input_params Jumlah parameter input.
num_output_params Jumlah parameter keluaran.
num_result_sets Jumlah kumpulan hasil.
remarks Description.
procedure_type Indikator jenis.

Kunci primer

Dapatkan kolom kunci utama dari sebuah tabel:

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

Parameter primaryKeys()

Parameter untuk mengambil informasi kunci utama:

Parameter Deskripsi
table Nama tabel (wajib).
catalog Nama katalog (database).
schema Nama skema.

kolom hasil primaryKeys()

Metode ini primaryKeys() mengembalikan informasi berikut:

Column Deskripsi
table_cat, table_schem, table_name Pengidentifikasi lokasi.
column_name Kolom pada kunci primer.
key_seq Posisi dalam kunci multikolom (indeks dimulai dari 1).
pk_name Nama batasan kunci utama.

Kunci asing

Temukan hubungan kunci asing:

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

parameter untuk foreignKeys()

Tentukan kunci primer atau tabel kunci asing untuk menemukan hubungan:

Parameter Deskripsi
table Nama tabel kunci primer.
catalog Katalog kunci utama.
schema Skema kunci utama.
foreignTable Nama tabel kunci asing.
foreignCatalog Katalog kunci asing.
foreignSchema Skema kunci asing.

kolom hasil foreignKeys()

Metode ini foreignKeys() mengembalikan kolom berikut yang menjelaskan hubungan:

Column Deskripsi
pktable_cat, pktable_schem, pktable_name Tabel yang dirujuk (utama).
pkcolumn_name Kolom yang direferensikan.
fktable_cat, fktable_schem, fktable_name Mereferensikan tabel (asing).
fkcolumn_name Kolom yang dirujuk.
key_seq Posisi pada kunci multi-kolom.
update_rule Tindakan pada UPDATE.
delete_rule Tindakan pada DELETE.
fk_name Nama batasan kunci asing.
pk_name Nama batasan kunci utama.

Indeks dan statistik

Mendapatkan informasi indeks untuk 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}")

Parameter statistik()

Konfigurasikan penemuan indeks dengan filter berikut:

Parameter Default Deskripsi
table (required) Nama tabel
catalog None Nama katalog (database).
schema None Nama skema.
unique False Hanya menampilkan indeks unik.
quick Benar Lewati kardinalitas/pengambilan halaman yang mahal.

Kolom hasil dari statistics()

Metode ini statistics() mengembalikan informasi indeks dan statistik:

Column Deskripsi
table_cat, table_schem, table_name Pengidentifikasi lokasi.
non_unique 0 untuk unik, 1 untuk non-unik.
index_name Nama indeks.
type Jenis indeks.
ordinal_position Posisi kolom dalam indeks.
column_name Nama kolom.
asc_or_desc A untuk naik, D untuk turun.
cardinality Perkiraan jumlah baris.
pages Jumlah halaman.

Kolom pengidentifikasi baris

Temukan kolom yang mengidentifikasi baris secara unik:

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

Metode ini mengembalikan kumpulan kolom terbaik untuk mengidentifikasi baris secara unik, yang mungkin merupakan kunci utama atau indeks unik.

Kolom versi baris

Temukan kolom yang diperbarui secara otomatis saat nilai baris berubah. Gunakan kolom versi baris untuk kontrol konkurensi yang optimis, tempat Anda membaca versi baris, membuat perubahan, lalu memverifikasi versi baris saat ini sama sebelum menulis:

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

Hasilnya biasanya mencakup rowversion/timestamp kolom yang digunakan untuk konkurensi optimis.

Informasi tipe data

Dapatkan informasi tentang jenis data SQL yang didukung:

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

Parameter opsional untuk memfilter jenis SQL yang didukung:

Parameter Deskripsi
sqlType Konstanta tipe SQL (abaikan untuk semua tipe).

Pertimbangan keamanan

Caution

Metode ini mengekspos metadata skema database. Meskipun metode itu sendiri aman untuk dieksekusi, informasi yang dikembalikan mengungkapkan struktur database Anda (nama tabel, nama kolom, hubungan, tipe data).

  • Jangan mengekspos metadata mentah ke pengguna yang tidak tepercaya.
  • Bersihkan atau saring hasil dalam aplikasi multi-penyewa.
  • Batasi akses dalam aplikasi yang menghadap ke eksternal.

Contoh: Membuat laporan skema

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

Konten terkait

  • Last updated on