Python – MySQL – Introduction

MySQL is one of the most popular relational database management systems used across the software industry. It is an open-source software and provides great scalability, reliability and flexibility. On the other hand, Python is one of the most popular programming languages used by developers for building web applications, data analysis, machine learning models and many more. Using Python, developers can easily integrate with a MySQL database and perform various data operations.

In this tutorial, we will be discussing how to connect and work with MySQL using Python programming language. We will be using Python mysql-connector-python package which is a standard Connector/Python driver provided by MySQL. We will be discussing the following topics:

1. Installing `mysql-connector-python` package
2. Setting up a MySQL Database
3. Connecting to a MySQL Database using Python
4. Creating a Table
5. Inserting Data
6. Retrieving Data
7. Updating Data
8. Deleting Data
9. Executing a Query

Installing `mysql-connector-python` package

In order to connect and interact with a MySQL database using Python, we need to first install the Python MySQL library. We can use the pip package installer which comes preinstalled with Python for installing the mysql-connector-python package. Run the following command in your terminal:

pip install mysql-connector-python

Setting up a MySQL Database

We can use the following steps to setup a MySQL Database:

1. Download the MySQL installer from MySQL official website.
2. Run the installer and select “Custom” setup.
3. Select only “MySQL Server” and “MySQL Workbench” from the options to install.
4. Follow the instructions and provide the root username and password. Remember this as we will need it to connect to the database.
5. Once the installation is complete, launch MySQL Workbench and connect to the server using the root username and password.

Now that we have a MySQL server running, we can use the Python mysql-connector-python package to interact with the database through our Python scripts.

Connecting to a MySQL Database using Python

Before we start with connecting to a MySQL Database using Python, we need to ensure that our Python environment has access to the mysql-connector-python package. We can do this by importing the package and check for any errors. Run the following code:

import mysql.connector

# check if the package is installed
print(mysql.connector.__version__)

If there are no errors, then the package has been installed and we can proceed with connecting to the MySQL database. We can use the connect() method of the mysql.connector package to make a connection to our database. We need to provide the host, user, password and database name to establish a successful connection. Run the following command:

import mysql.connector

# connect to the MySQL server
cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='mydb')

# close the connection
cnx.close()

If the database with the provided name exists on the MySQL server and the provided password is correct, then the above code will establish a connection to the MySQL database. However, if there is an error, then Python will raise an exception, and the connection will not be established. We can add appropriate exception handling to our code to take care of any errors that may occur while connecting to the MySQL database. Once the connection is established, we can perform various operations like creating a table, inserting, updating, and deleting data from the MySQL database.

Creating a Table

We can create tables in our MySQL database using Python. We can do this by writing a CREATE TABLE statement and execute it in Python using the execute() method of the cursor object. Run the following code:

import mysql.connector

# connect to the MySQL server
cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='mydb')

# get a cursor object
cursor = cnx.cursor()

# create the table
table = '''
CREATE TABLE students (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age INT(11) NOT NULL,
  address VARCHAR(255),
  PRIMARY KEY (id)
)
'''

cursor.execute(table)

# commit the changes
cnx.commit()

# close the cursor and connection
cursor.close()
cnx.close()

The above code will create a table students with columns id, name, age and address. id column is an auto-increment primary key.

Inserting Data

We can insert data into our MySQL database using Python. We can write an INSERT statement and execute it in Python using the execute() method of the cursor object. Run the following code:

import mysql.connector

# connect to the MySQL server
cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='mydb')

# get a cursor object
cursor = cnx.cursor()

# insert data into the table
data = [
    ('John', 22, '123 Main St.'),
    ('Lisa', 25, '124 Elm St.'),
    ('Kelly', 18, '125 Oak St.')
]

insert_stmt = 'INSERT INTO students (name, age, address) VALUES (%s, %s, %s)'

cursor.executemany(insert_stmt, data)

# commit the changes
cnx.commit()

# close the cursor and connection
cursor.close()
cnx.close()

The above code will insert 3 rows into the students table.

Retrieving Data

We can retrieve data from our MySQL database using Python. We can write a SELECT statement and execute it in Python using the execute() method of the cursor object. Run the following code:

import mysql.connector

# connect to the MySQL server
cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='mydb')

# get a cursor object
cursor = cnx.cursor()

# retrieve data from the table
select_stmt = 'SELECT * FROM students'

cursor.execute(select_stmt)

result = cursor.fetchall()

for row in result:
  print(row)

# close the cursor and connection
cursor.close()
cnx.close()

The above code will retrieve all of the data from the students table and print it to the console.

Updating Data

We can update data in our MySQL database using Python. We can write an UPDATE statement and execute it in Python using the execute() method of the cursor object. Run the following code:

import mysql.connector

# connect to the MySQL server
cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='mydb')

# get a cursor object
cursor = cnx.cursor()

# update data in the table
update_stmt = '''UPDATE students
    SET address = %s
    WHERE name = %s
'''

data = ('111 Main St.', 'John')

cursor.execute(update_stmt, data)

# commit the changes
cnx.commit()

# close the cursor and connection
cursor.close()
cnx.close()

The above code will update the row for John in the students table with a new address.

Deleting Data

We can delete data from our MySQL database using Python. We can write a DELETE statement and execute it in Python using the execute() method of the cursor object. Run the following code:

import mysql.connector

# connect to the MySQL server
cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='mydb')

# get a cursor object
cursor = cnx.cursor()

# delete data from the table
delete_stmt = 'DELETE FROM students WHERE name = %s'

data = ('Kelly',)

cursor.execute(delete_stmt, data)

# commit the changes
cnx.commit()

# close the cursor and connection
cursor.close()
cnx.close()

The above code will delete the row for Kelly in the students table.

Executing a Query

We can execute any SQL query in our MySQL database using Python. We can write a query and execute it in Python using the execute() method of the cursor object. Run the following code:

import mysql.connector

# connect to the MySQL server
cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='mydb')

# get a cursor object
cursor = cnx.cursor()

# execute a query
query = "SELECT age FROM students WHERE name = 'Lisa'"

cursor.execute(query)

result = cursor.fetchone()

print(result)

# close the cursor and connection
cursor.close()
cnx.close()

The above code will execute a SELECT query and return the age for the student named Lisa from the students table.

Conclusion

In this tutorial, we learned how to connect and work with MySQL database in Python. We covered various topics like installing the Python MySQL library, setting up a MySQL database, connecting to a MySQL database using Python, creating a table, inserting data, retrieving data, updating data, deleting data and executing a query. MySQL is a very powerful database management system and it is extremely useful to know how to connect and interact with it using Python. By using Python and MySQL together, developers can create robust and scalable web applications or data analytical solutions easily.