MySQL server is an open-source relational database management system that is a major support for web-based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. In order to access MySQL databases from a web server, we use various modules in Python such as PyMySQL, mysql.connector, etc.
In this article, we are going to get the tables having a specific column irrespective of the database. First, we are going to connect to a server. The SQL query that is going to be used is:
select tab.table_schema as database_name,tab.table_name from information_schema.tables as tab inner join information_schema.columns as col on col.table_schema = tab.table_schema and col.table_name = tab.table_name and column_name = 'Specified_Column_Name' where tab.table_type = 'BASE TABLE' order by tab.table_schema, tab.table_name;
Below are some programs which depict how to get the MySQL tables having a particular column:
Example 1
Below is the table description of all the tables, the sample database having the following tables which are going to be accessed by a Python script:
Below is the program to get the details of MySQL tables having a particular column:
Python3
# Module Imports import mysql.connector as mariadb import sys # Connect to MariaDB Platform try: conn = mariadb.connect( user="root", password="", database="gfg" ) except mariadb.Error as e: print(f"Error connecting to MariaDB Platform: {e}") sys.exit(1) # Get cursor object cur = conn.cursor() # Get the table and database details having a particular column cur.execute("select tab.table_schema as database_name,tab.table_name \ from information_schema.tables as tab \ inner join information_schema.columns as col \ on col.table_schema = tab.table_schema \ and col.table_name = tab.table_name \ and column_name = 'Worker_ID' \ where tab.table_type = 'BASE TABLE' \ order by tab.table_schema, tab.table_name;") # Display the tables for(database_name, tab) in cur: print(f"Table Name: {tab}Database Name: {database_name}") |
Output:
Example 2
Here is another example to get table details irrespective of the database having a particular column, below is the description of all the tables:
Below is the python script to get table details having particular column Names:
Python3
# Module Imports import mysql.connector as mariadb import sys # Connect to MariaDB Platform try: conn = mariadb.connect( user="root", password="", database="gfg" ) except mariadb.Error as e: print(f"Error connecting to MariaDB Platform: {e}") sys.exit(1) # Get cursor object cur = conn.cursor() # Get the table and database details having a particular column cur.execute("select tab.table_schema as database_name,tab.table_name \ from information_schema.tables as tab \ inner join information_schema.columns as col \ on col.table_schema = tab.table_schema \ and col.table_name = tab.table_name \ and column_name = 'Names' \ where tab.table_type = 'BASE TABLE' \ order by tab.table_schema, tab.table_name;") # Display the tables for(database_name, tab) in cur: print(f"\n\nTable Name: {tab}\nDatabase Name: {database_name}") |
Output:
