How to show all the tables present in the database and server in MySQL using Python?

We may sometimes require to get the list of all the tables present in our database. This can be done by using the SHOW TABLES command.

The SHOW TABLES command is used to display the table names in a database as well as the server.

Syntax

To show the tables present in a database −

SHOW TABLES

The above statement when executed using the cursor object returns the names of the tables present in our database.

To show the tables present in a server

SELECT table_name FROM information_schema.tables

Steps to show all tables present in a database and server using MySQL in python

• import MySQL connector

• establish connection with the connector using connect()

• create the cursor object using cursor() method

• create a query using the appropriate mysql statements

• execute the SQL query using execute() method

• close the connection

Show the tables present in a database

Example

import mysql.connector

db=mysql.connector.connect(host="your host", user="your username", password="your_password",database="database_name")

cursor=db.cursor()

cursor.execute("SHOW TABLES")

for table_name in cursor:
   print(table_name)

Show the tables present in a server

Example

import mysql.connector

db=mysql.connector.connect(host="your host", user="your username", password="your_password",database="database_name")

cursor=db.cursor()

cursor.execute("SELECT table_name FROM information_schema.tables")

for table_name in cursor:
   print(table_name)

The above codes output the list of tables present in your database or the server .

Output

Employees
Students
MyTable