Introduction
MySQL is a Relational Database Management System (RDBMS). It is a popular database management system used by many organizations and developers to store and manage data. Python is also a popular programming language used for web development, data analysis, and automation. In this article, we will discuss how to connect MySQL to Python using the MySQL Connector/Python library. To connect MySQL with Python, you should have MySQL installed on your computer. You can download a MySQL database at https://www.mysql.com/downloads/.
How to Install MySQL Driver?
MySQL Connector/Python is a Python driver that helps in connecting to MySQL servers and working with databases using Python. To install the Python-MySQL-connector module, one must have Python and PIP preinstalled on their system. To check if your system already contains Python, Open the Command line and run the following command.
Syntax
python --version
Output
![Output]()
We will use PIP to install "MySQL Connector". PIP is most likely already installed in the Python environment. If not, then install it first. Now navigate the command line to the location of PIP, and type the following, and it will install "MySQL Connector".
Syntax
python -m pip install mysql-connector-python
Now we will use "MySQL.connector" to conncect the Python script to the MySQL database. First, we will import the MySQL Connector/Python library into your Python script using the following code.
Syntax
import mysql.connector
Next, we will create a connection to the MySQL server. You can do this by specifying the host, user, and password.
Syntax
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword"
)
Now we need to confirm whether the database is connected or not. We can use the "is_connected()" method to check if the connection is established or not. From the below code, If the connection is established, the message "Database is connected!" is printed; otherwise, the message "Database is not connected!" is printed.
Syntax
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
)
if mydb.is_connected():
print("Database is connected!")
else:
print("Database is not connected!")
Once the connection is created and we want to perform some queries on the database, then we can create a cursor object to execute queries on the database. Here is an example code to create a cursor.
Syntax
mycursor = mydb.cursor()
Now we can execute queries on the database using the cursor object. Here is an example code to execute a "SELECT" query.
Syntax
mycursor.execute("SELECT * FROM tablename")
result = mycursor.fetchall()
for x in result:
print(x)
Suppose we have table "employee" saved in MySQL database, and we want to connect through Python; then we need to write the following code.
Syntax
import mysql.connector
# Establish connection to MySQL server
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="root@123",
database="employee"
)
# Create a cursor object
mycursor = mydb.cursor()
# Execute SELECT query on employee table
mycursor.execute("SELECT * FROM employee")
# Fetch all rows from the result
result = mycursor.fetchall()
# Print each row of the result
for row in result:
print(row)
Output
![Output]()
We can also execute other INSERT, UPDATE, and DELETE queries using the cursor object; then, we need to commit the changes using the commit() method. Below query will insert a new record in the employee table.
Syntax
mycursor.execute("INSERT INTO employee (name, age) VALUES (%s, %s, %s, %s)", ("Harries", 45,55000.00,'sales'))
mydb.commit()
Finally, if you have finished working with the database, then we can close the connection using the close() method.
Syntax
mydb.close()
Conclusion
Connect MySQL with Python requires a MySQL Connector/Python library; it will create a connection object, and with the help of a cursor object, we can execute queries on the database. If we are performing any dml operation, then we need to commit changes using the commit() method. And close the connection once done working with the database using the close() method. By leveraging the capabilities of the MySQL Connector/Python library, developers can seamlessly integrate MySQL databases into their Python applications, enabling efficient data management, analysis, and retrieval, thus enhancing the overall functionality and usability of their software systems.
FAQ's
Question. What is MySQL Connector/Python?
Answer. MySQL Connector/Python is a Python driver that allows Python programs to connect to and interact with MySQL databases. It provides an interface to connect to MySQL servers and perform various database operations using Python.
Question. How do I install the MySQL Connector/Python library?
Answer. You can install the MySQL Connector/Python library using pip, which is a package management system for Python. Open the command line and run the command "python -m pip install mysql-connector-python". This will download and install the library.
Question. How can I establish a connection to a MySQL server in Python?
Answer. You can establish a connection to a MySQL server using the "mysql.connector.connect()" function. Specify the host, user, password, and optionally the database name in the connection parameters.
Question. How can I check if the MySQL database connection is successful?
Answer. You can use the "is_connected()" method of the connection object to check if the connection is established.
Question. How do I execute SQL queries on a MySQL database using Python?
Answer. To execute SQL queries on a MySQL database, you need to create a cursor object using the "cursor()" method of the connection object. You can then use the cursor's "execute()" method to execute the query.
Question. Can I execute INSERT, UPDATE, and DELETE queries using Python and MySQL Connector/Python?
Answer. Yes, you can execute INSERT, UPDATE, and DELETE queries using the "execute()" method of the cursor object. After executing these queries, you need to commit the changes using the "commit()" method of the connection object.
Question. How do I close the MySQL database connection in Python?
Answer. You can close the MySQL database connection by calling the "close()" method of the connection object.
Question. Are there any alternative libraries for connecting MySQL with Python?
Answer. Yes, besides MySQL Connector/Python, there are other popular libraries for connecting MySQL with Python, such as PyMySQL, mysql-connector-python-rf, and SQLAlchemy. These libraries provide similar functionality and can be used based on personal preference or specific project requirements.