This post will guide you on how you can connect Python with MySQL using MySQL Connector. To begin with this post, you must have Python and pip installed in your system.
Prerequisite
The Pre-req for this post is to install pip and Python in your system. Install pip and Python in your system and then check whether they are successfully installed or not.
For python, type this command:
Here it is visible that a version number is showing so python is installed in your system.
For pip, type this command:
It is visible that pip is also installed in your system.
Connect Python with MySQL Using MySQL Connector
To install the MySQL connector library by typing:
It will show a success message on the successful installation of the library:
Create a folder and open it or open any folder containing the python application where you want to connect your MySQL database in any code editor:
Create a python file and for this post, it is named “connection.py”:
Write the code for connectivity, import MySQL connector library:
Import the Error library to handle errors:
Use the “mysql.connector.connect()” function and pass the parameters “host=dbhost, database=dbname, user=uname, password=upassword”.
Take the value for the host by typing:
dbhost = input()
Take the value for the database name by typing:
dbname = input()
Input the username by typing:
uname = input()
For the password, type:
upassword = input()
This command aids in the execution of statements to communicate with the MySQL database:
Write the connection code in the try block:
connection = mysql.connector.connect(host=dbhost, database=dbname, user=uname, password=upassword)
if connection.is_connected():
cursor = connection.cursor()
cursor.execute("select database();")
record = cursor.fetchone()
print("Connected to ", uname,'@',dbhost)
print("Connected Database: ", record)
Handle the error exception using this piece of code:
print("Error while connecting to MySQL", e)
To close the connection use this code:
if connection.is_connected():
cursor.close()
connection.close()
print("MySQL Connection is Closed")
Your python file will look like this:
Save and run the code for connectivity:
It is visible in the output that python connected with the MySQL database and then the connection closed successfully.
Show Tables of MySQL Database through Python File Using MySQL Connector
If you want to show the tables of the database so for that you can simply type this code inside the try block:
print("Tables: ")
for table_name in cursor:
print(table_name)
Save the file:
Run the file:
It is visible that you have successfully connected Python with MySQL database using MySQL connector and have displayed the tables from MySQL database.
Conclusion
Install the MySQL connector library using pip by running “pip install mysql-connector-python” command. After the installation of the library, create a python file and take input from the user for database details and provide these parameters in “mysql.connector.connect()” for the connectivity, save and run the file.
