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:
- Installing `mysql-connector-python` package
- Setting up a MySQL Database
- Connecting to a MySQL Database using Python
- Creating a Table
- Inserting Data
- Retrieving Data
- Updating Data
- Deleting Data
- 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:
- Download the MySQL installer from MySQL official website.
- Run the installer and select “Custom” setup.
- Select only “MySQL Server” and “MySQL Workbench” from the options to install.
- Follow the instructions and provide the root username and password. Remember this as we will need it to connect to the database.
- 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.