Skip to main content
100%

SQL Alchemy Database Introspection through MetaData

✓ Published0🌍 Public
WWilliamQLiu
Last edited Apr 10, 2018
Created on Apr 10, 2018

This example demonstrates how to introspect a MySQL database schema and its tables using SQLAlchemy’s reflection API. It connects to a database via an engine, retrieves table names through `Inspector.get_table_names()`, and filters them by substring. It then loads a specific table into a pandas DataFrame with `read_sql_table`, and prints the table’s metadata, columns, and data types. The code also creates a `MetaData` instance, accesses a table object like `metadata.tables['stark_master']`, and calls `sa.inspect(engine)` to list all tables.

AI-generated description

Similar vizzes

Loading thumbnail…

sqlite query to surface the relationships from metadata.sqlite in PowerBI

This example visualizes the relationships between tables in a PowerBI data model by querying the metadata.sqlite database. It shows a table-level schema diagram where columns from different tables are connected based on foreign key relationships, with cardinality information included. The code uses SQLite queries through the PowerBI metadata store, joining the `relationship`, `table`, and `column` tables to extract source and target table/column pairs. The visualization renders the resulting graph structure, highlighting how tables interconnect through their columns. The implementation relies on standard SQL joins and explicit column selections, with the final query outputting the relationship endpoints and cardinalities for a clear overview of the model's data flow.

HHugoberry
51% match
Loading thumbnail…

Gist be32a2ac117700d3dbde6b0824367ba5

This example demonstrates how to create a simple database table visualization from a SQL schema definition. The code defines a `Demo` table with three columns—`ID`, `Key`, and `Value`—and shows how to map those column types and constraints into a visual entity-relationship style diagram. Using the provided `CREATE TABLE` statement as the data source, the visualization renders each column as a node, with the primary key and nullable attributes indicated through distinct visual encodings. The implementation leverages `d3.forceSimulation` for the node layout and `d3.geoPath` for any geometric rendering, while relying on standard DOM elements for interactivity. No additional behavior beyond the table’s structural representation is implied.

HHugoberry
50% match
Loading thumbnail…

Get Table Sizes (in MB) for a database

This visualization shows the size of each table in a MySQL database in megabytes, sorted from largest to smallest. It displays the table names alongside their computed sizes using a bar chart or similar comparison layout. The SQL query in `get_table_size.sql` calculates each table’s total size by summing `data_length` and `index_length` from the `information_schema.TABLES` metadata, then divides by 1024 twice and rounds to two decimal places. The query orders results by size in descending order, and the visualization renders this data as its primary output.

WWilliamQLiu
49% match
Loading thumbnail…

Hidden Tabular SSAS specific DMVs

This visualization exposes the hidden structure of a tabular SSAS (SQL Server Analysis Services) model by issuing a series of `Discover` requests for TMSCHEMA metadata, including models, tables, columns, and measures. The code queries a specific database GUID via XMLA and presents the resulting schema objects as an interactive graph. Using D3.js, the example renders nodes and links that map out relationships between tables, hierarchies, and KPIs, revealing how the model is organized behind the scenes. The visualization relies on D3's force-directed layout to position elements, making the otherwise invisible metadata hierarchy tangible and explorable.

HHugoberry
48% match
Loading thumbnail…

Created on Cognitive Class Labs

This notebook demonstrates how to access and query a Db2 database from JupyterLab using the SQL magic extension (`ipython-sql`). It shows the process of loading the SQL extension, connecting to a database via a SQLAlchemy connection string (using the `ibm_db_sa` dialect), and executing SQL commands. The example includes a connection attempt with provided credentials and illustrates error handling when the connection string is malformed. The code relies on `%load_ext sql`, `%sql` for single-line queries, and `%%sql` for cell-level SQL operations.

DDAVINDER
48% match
Loading thumbnail…

SQL to upload the smaller tables of the MOT test dataset

This example demonstrates how to prepare smaller reference tables from the MOT test dataset for database loading, showing the SQL commands that import three text files—failure_location, item_detail, and item_group—into their corresponding MySQL tables. The script uses the `LOAD DATA LOCAL INFILE` API to parse pipe-delimited files with newline-terminated records, explicitly specifying field and line terminators. The code relies on standard SQL syntax within a MySQL environment, first selecting the MOT database with `USE MOT` before executing each import. This approach highlights a straightforward, file-based data ingestion pattern rather than a visual rendering technique, focusing on the database population step that precedes any charting or visualization work.

IIanHopkinson
47% match
Loading thumbnail…

Gist 6bae1006f0a86eb81bd0914e96710170

This example demonstrates how to inspect and export metadata from Power BI files on macOS without Power BI Desktop. Using the Python library PBIXRay, it loads a `.pbix` file with `on_disk=True` for memory-mapped access, then exports model schema, DAX measures, calculated columns, relationships, and Power Query expressions to CSV files. The code also shows extracting stored table data via `model.get_table()` and pandas DataFrames, with a separate example using `iter_table()` for handling larger models in chunks.

HHugoberry
46% match
Loading thumbnail…

MySQL Cheat Sheet

This example presents a plain-text MySQL cheat sheet as a structured reference for database management and SQL querying. It demonstrates how to organize commands for user administration, database and table creation, data manipulation, and complex queries such as joins and foreign key relationships. The text is formatted with markdown headers and code blocks, showing SQL statements for operations like `CREATE TABLE`, `SELECT`, `INSERT`, and `INNER JOIN`. It also includes configuration tips for adding MySQL to the system PATH via bash commands, while the underlying source is a GitHub Gist authored by Lyndoman.

LLyndoman
46% match