Database, Programming,

My Python Journey 03 – How to Fetch MySQL Tables Using Python

by 12 months ago1 comment
0
Shares

So, this is My Python Journey 03. In this article, we will continue my learning about Python and MySQL connection. I am going to connect to MySQL and then execute some queries from Python. In the previous article, we learned how to connect to a MySQL server from Python. Now we are going to move forward a bit.

We’ll explore practical steps, leveraging the capabilities of the mysql-connector-python library, to establish connections, execute queries, and ultimately fetch tables from a MySQL database. Whether you are a budding data enthusiast or an experienced developer, this guide aims to empower you with the knowledge and tools to effortlessly navigate and extract meaningful insights from MySQL tables using the versatile programming language, Python.

Get a List of MySQL Tables

The following code will connect to the “test” database and then show all the tables in that database. It is pretty straightforward.

#import library
import mysql.connector

#Create a new connection
db = mysql.connector.connect(host='192.168.2.109',
                             user='dhani',
                             password='MyPassword',
                             database='test')

if db.is_connected():
    #Create cursor
    cursor = db.cursor()

    #Define sql query
    my_query = "SHOW TABLES"

    #Execute the query
    cursor.execute(my_query)

    #Fetch all the table
    my_tables = cursor.fetchall()

    for table in my_tables:
        print(table[0])
        
    #Close the connection
    db.close()

Output:

Tbl_DHSample_additional
Tbl_DHStructure
Tbl_DHSurvey
Tbl_Dispatch
Tbl_ErrMessage

Let’s improve this code so it will catch and display messages when an error happens.

#import library
import mysql.connector

db = mysql.connector.connect(host='192.168.2.109',
                             user='dhani',
                             password='MyPassword',
                             database='test')
try:
    if db.is_connected():
        #Create cursor
        cursor = db.cursor()

        #Define sql query
        my_query = "SHOW TABLES"

        #Execute the query
        cursor.execute(my_query)

        #Fetch all the table
        my_tables = cursor.fetchall()

        for table in my_tables:
            print(table[0])

except mysql.connector.Error as e:
    print(r"Error: {e}")


finally:
    if db.is_connected():
        cursor.close()
        db.close()

Done. That is how I connect to the MySQL database and fetch all the tables in the test database. Next, we will learn how to fetch the table content.

1 Comment

How to Read MySQL Table to Data Frame in Python Using Panda read_sql - GIS Tutorial

January 10, 2024

[…] the previous article, we learned how to fetch MySQL table data in Python. Now, we will read/fetch MySQL table in Python using Panda. The following code will connect to an […]

Reply

Leave a Reply