How to Connect to MySQL in Python

09/10/2021

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.

#Python