File size: 3,574 Bytes
e789e15 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | import pandas as pd
import sqlalchemy as sql
from sqlalchemy import inspect
def get_db_metadata(connection, n_samples=3) -> dict:
"""
Collects metadata and sample data from a database, with safe identifier quoting and
basic dialect-aware row limiting. Prevents issues with spaces/reserved words in identifiers.
Parameters:
----------
connection : Union[sql.engine.base.Connection, sql.engine.base.Engine]
An active SQLAlchemy connection or engine.
n_samples : int
Number of sample values to retrieve for each column.
Returns:
-------
dict
A dictionary with database metadata, including some sample data from each column.
"""
is_engine = isinstance(connection, sql.engine.base.Engine)
conn = connection.connect() if is_engine else connection
metadata = {
"dialect": None,
"driver": None,
"connection_url": None,
"schemas": [],
}
try:
sql_engine = conn.engine
metadata["dialect"] = sql_engine.dialect.name
metadata["driver"] = sql_engine.driver
metadata["connection_url"] = str(sql_engine.url)
inspector = inspect(sql_engine)
preparer = inspector.bind.dialect.identifier_preparer
for schema_name in inspector.get_schema_names():
schema_obj = {"schema_name": schema_name, "tables": []}
tables = inspector.get_table_names(schema=schema_name)
for table_name in tables:
table_info = {"table_name": table_name, "columns": [], "primary_key": [], "foreign_keys": [], "indexes": []}
columns = inspector.get_columns(table_name, schema=schema_name)
for col in columns:
col_name = col["name"]
col_type = str(col["type"])
table_name_quoted = f"{preparer.quote_identifier(schema_name)}.{preparer.quote_identifier(table_name)}"
col_name_quoted = preparer.quote_identifier(col_name)
query = build_query(col_name_quoted, table_name_quoted, n_samples, metadata["dialect"])
try:
df = pd.read_sql(query, conn)
samples = df[col_name].head(n_samples).tolist()
except Exception as e:
samples = [f"Error retrieving data: {str(e)}"]
table_info["columns"].append({"name": col_name, "type": col_type, "sample_values": samples})
pk_constraint = inspector.get_pk_constraint(table_name, schema=schema_name)
table_info["primary_key"] = pk_constraint.get("constrained_columns", [])
fks = inspector.get_foreign_keys(table_name, schema=schema_name)
table_info["foreign_keys"] = [{"local_cols": fk["constrained_columns"], "referred_table": fk["referred_table"], "referred_cols": fk["referred_columns"]} for fk in fks]
idxs = inspector.get_indexes(table_name, schema=schema_name)
table_info["indexes"] = idxs
schema_obj["tables"].append(table_info)
metadata["schemas"].append(schema_obj)
finally:
if is_engine:
conn.close()
return metadata
def build_query(col_name_quoted: str, table_name_quoted: str, n: int, dialect_name: str) -> str:
if "sqlite" in dialect_name:
return f"SELECT {col_name_quoted} FROM {table_name_quoted} ORDER BY RANDOM() LIMIT {n}"
return f"SELECT {col_name_quoted} FROM {table_name_quoted} WHERE ROWNUM <= {n}" |