How to find tables that contain a specific column in SQL using Python?
Last Updated :
26 Nov, 2020
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
import mysql.connector as mariadb
import sys
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 )
cur = conn.cursor()
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;")
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
import mysql.connector as mariadb
import sys
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 )
cur = conn.cursor()
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;")
for (database_name, tab) in cur:
print (f "\n\nTable Name: {tab}\nDatabase Name: {database_name}" )
|
Output:
Share your thoughts in the comments
Please Login to comment...