How to Connect to MySQL in Python

Contents
In this article, you will learn how to connect to MySQL in Python.
Connect to MySQL in Python
To connect to a MySQL database in Python, you can use the mysql-connector-python library, which is a Python driver for MySQL.
Here’s how you can connect to a MySQL database in Python:
Install the mysql-connector-python library:
pip install mysql-connector-python
Import the mysql.connector module:
import mysql.connector
Create a connection to the database using the connect() method:
cnx = mysql.connector.connect(
host="host_name",
user="user_name",
password="password",
database="database_name"
)
Replace host_name, user_name, password, and database_name with your actual MySQL host name, user name, password, and database name, respectively.
Create a cursor object using the cursor() method of the connection object:
cursor = cnx.cursor()
Execute a query using the execute() method of the cursor object:
query = "SELECT * FROM table_name"
cursor.execute(query)
Replace table_name with the actual name of the table you want to query.
Fetch the results using the fetchall() method of the cursor object:
results = cursor.fetchall()
for result in results:
print(result)
Close the cursor and the connection using the close() method of the cursor and the connection objects, respectively:
cursor.close()
cnx.close()
Here’s some more information on working with a MySQL database in Python:
Transactions
If you want to execute multiple queries as a single transaction, you can use the autocommit attribute of the connection object:
cnx = mysql.connector.connect(
host="host_name",
user="user_name",
password="password",
database="database_name",
autocommit=False
)
With autocommit set to False, you can execute multiple queries and then commit the transaction using the commit() method of the connection object:
cursor = cnx.cursor()
query1 = "UPDATE table_name SET column1 = value1 WHERE column2 = value2"
cursor.execute(query1)
query2 = "UPDATE table_name SET column3 = value3 WHERE column4 = value4"
cursor.execute(query2)
cnx.commit()
cursor.close()
cnx.close()
Handling exceptions
It’s always a good idea to handle exceptions when working with databases in order to avoid unexpected errors and crashes. You can use a try-except block to catch exceptions:
try:
cnx = mysql.connector.connect(
host="host_name",
user="user_name",
password="password",
database="database_name"
)
cursor = cnx.cursor()
query = "SELECT * FROM table_name"
cursor.execute(query)
results = cursor.fetchall()
for result in results:
print(result)
except mysql.connector.Error as error:
print("Error: {}".format(error))
finally:
cursor.close()
cnx.close()
Inserting data
To insert data into a table, you can use the execute() method of the cursor object along with an INSERT query:
cursor = cnx.cursor()
query = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)"
values = (value1, value2, value3)
cursor.execute(query, values)
cnx.commit()
cursor.close()
cnx.close()
Note that in this example, we’re using placeholders (%s) in the INSERT query and passing the actual values as a tuple in the execute() method. This is a good practice to prevent SQL injection attacks.