CODEWITHSUNDEEP
pythonmysqlmysql-python
Richard
Richard

Reputation: 15892

How to retrieve table names in a mysql database with Python and MySQLdb?

I have an SQL database and am wondering what command you use to just get a list of the table names within that database.

Upvotes: 39

Views: 73499

Answers (5)

anakings95
anakings95

Reputation: 71

Here is another answer using the sqlalchemy and pandas libraries

from sqlalchemy import create_engine
import pandas as pd

# Make connection to server
def connect(self):  
    # create sqlalchemy engine
    engine = create_engine("mysql+pymysql://{user}:{pw}@{server}/{db}"
            .format(user=self.username,
            pw=self.password,
            server=self.server,
            db=self.database))
    return engine   

if _name_ == '_main_':
    server = 'myserver'
    database = 'mydatabase'
    username = 'myusername'
    password = 'mypassword'
    table = 'mytable'

    con = connect()
    my_query = f"SHOW COLUMNS FROM {table};"
    df = pd.read_sql_query(sql=my_query, con=con)

Upvotes: 1

Milovan Tomašević
Milovan Tomašević

Reputation: 8673

It is also possible to obtain tables from a specific scheme with execute the single query with the driver below.

python3 -m pip install PyMySQL

import pymysql

# Connect to the database
conn = pymysql.connect(host='127.0.0.1',user='root',passwd='root',db='my_database')

# Create a Cursor object
cur = conn.cursor()

# Execute the query: To get the name of the tables from a specific database
# replace only the my_database with the name of your database
cur.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'my_database'")

# Read and print tables
for table in [tables[0] for tables in cur.fetchall()]:
    print(table)

output:

my_table_name_1
my_table_name_2
my_table_name_3
...
my_table_name_x

Upvotes: 4

Remi
Remi

Reputation: 21175

To be a bit more complete:

import MySQLdb

connection = MySQLdb.connect(
                host = 'localhost',
                user = 'myself',
                passwd = 'mysecret')  # create the connection

cursor = connection.cursor()     # get the cursor


cursor.execute("USE mydatabase") # select the database

cursor.execute("SHOW TABLES")    # execute 'SHOW TABLES' (but data is not returned)

now there are two options:

tables = cursor.fetchall()       # return data from last query

or iterate over the cursor:

 for (table_name,) in cursor:
        print(table_name)

Upvotes: 87

Henning
Henning

Reputation: 16341

show tables will help. Here is the documentation.

Upvotes: 7

Ty W
Ty W

Reputation: 6814

SHOW tables

15 chars

Upvotes: 10

Related Questions