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.
[…] 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 […]