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