Mysql Connector Python Tutorial – Complete Guide

Welcome to this comprehensive tutorial on MySQL Connector/Python, an interface for connecting to MySQL databases using Python.

What is MySQL Connector/Python?

MySQL Connector/Python is a database driver that allows Python applications to interact with MySQL databases. It implements the MySQL server protocol purely in Python, providing a seamless connection between Python and MySQL.

MySQL Connector/Python is used in various applications that require data storage and manipulation. With this tool, you can link your Python code to a MySQL database, enabling you to read, write, and manipulate the data.

Mastering MySQL Connector/Python opens up a lot of opportunities. For one, you gain a vital skill set in managing databases, a major aspect of web development, enterprise solutions, and even game mechanics where data tracking is required. Secondly, the demand for database-related skills is high in today’s job market, increasing your employability.

Next, we’ll dive into some coding tutorials to help you better understand and practically use MySQL Connector/Python.

CTA Small Image
FREE COURSES AT ZENVA
LEARN GAME DEVELOPMENT, PYTHON AND MORE
ACCESS FOR FREE
AVAILABLE FOR A LIMITED TIME ONLY

Installation of MySQL Connector/Python

Before starting, you need to install the MySQL Connector/Python package. Use pip, which is the Python Package Installer. Simply run this command on your terminal or command prompt:

pip install mysql-connector-python

Connecting to MySQL database

To begin, let’s connect to a MySQL database. To do this, we use the connect() function and pass the host, user, and password as parameters.

import mysql.connector

cnx = mysql.connector.connect(user='username', password='password',
                              host='127.0.0.1',
                              database='database_name')
cnx.close()

When the work is finished, always remember to close the connection using the close() method.

Creating a database

Creating a new database is also straightforward. We begin by creating a connection, then creating a cursor object using the cursor() method. Afterward, we execute the SQL command using the cursor.execute() function.

import mysql.connector

cnx = mysql.connector.connect(user='username', password='password',
                              host='127.0.0.1')

mycursor = cnx.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

cnx.close()

Creating a table

The creation of a table in the database follows a similar fashion. We simply modify our SQL command to create a table instead of a database.

import mysql.connector

cnx = mysql.connector.connect(user='username', password='password',
                              host='127.0.0.1', database='mydatabase')

mycursor = cnx.cursor()

mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

cnx.close()

Inserting data

To insert data into the created table, we use the INSERT INTO statement.

import mysql.connector

cnx = mysql.connector.connect(user='username', password='password',
                              host='127.0.0.1', database='mydatabase')

mycursor = cnx.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

cnx.commit()

print(mycursor.rowcount, "record inserted.")

cnx.close()

Note that we used commit() method after the execute() function, which is required to make the changes in the database. It also provides feedback by returning the number of rows affected, obtained by calling the rowcount property.

Selecting Data

To select data from a MySQL table, we use the SELECT statement. Let’s fetch records from the ‘customers’ table we created earlier.

import mysql.connector

cnx = mysql.connector.connect(user='username', password='password',
                              host='127.0.0.1', database='mydatabase')

mycursor = cnx.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

cnx.close()

The fetchall() method fetches all rows from the last executed statement. It returns a list of tuples.

Updating Data

To update data in a MySQL table, we use the UPDATE statement. Here’s an example:

import mysql.connector

cnx = mysql.connector.connect(user='username', password='password',
                              host='127.0.0.1', database='mydatabase')

mycursor = cnx.cursor()

sql = "UPDATE customers SET address = 'Valley 123' WHERE name = 'John'"
mycursor.execute(sql)

cnx.commit()

print(mycursor.rowcount, "record(s) affected")

cnx.close()

Always make sure to use the commit() method after a query that modifies data, to ensure changes are saved in the database.

Deleting Data

To delete data from a MySQL table, we use the DELETE statement. Let’s delete the record of ‘John’ from the ‘customers’ table:

import mysql.connector

cnx = mysql.connector.connect(user='username', password='password',
                              host='127.0.0.1', database='mydatabase')

mycursor = cnx.cursor()

sql = "DELETE FROM customers WHERE name = 'John'"
mycursor.execute(sql)

cnx.commit()

print(mycursor.rowcount, "record(s) deleted")

cnx.close()

Exception Handling

When things go wrong with our database operations, it is vital to handle these mishaps gracefully. Python’s try…except block comes in handy in these scenarios:

import mysql.connector
from mysql.connector import Error

try:
    cnx = mysql.connector.connect(user='username', password='password',
                                  host='127.0.0.1', database='mydatabase')

    mycursor = cnx.cursor()

    mycursor.execute("SELECT * FROM customers")

    myresult = mycursor.fetchall()

    for x in myresult:
        print(x)

except Error as e:
    print(f"An error occurred: {e}")

finally:
    if cnx.is_connected():
        cnx.close()
        print("MySQL connection is closed.")

This code attempts to select data from the ‘customers’ table. If there’s an error, it gets caught and handled gracefully, and the connection to the database is closed in the ‘finally’ block.

Where to Go Next?

Now that you’ve gotten a taste of MySQL Connector/Python, don’t stop here. Keep expanding your knowledge and skills.

As an effective next step, we encourage you to check out our Python Mini-Degree. This comprehensive curriculum covers everything from Python’s coding basics to complex topics such as algorithms and object-oriented programming. You’ll have opportunities to take on projects like building arcade games, developing a medical diagnosis bot, and designing an escape room game.

– Python is widely used in diverse industries, including data science, machine learning, and robotics.
– The courses are custom-built to suit both beginners and experienced coders.
– With our online learning platform, you can set your own pace.
– Courses come equipped with interactive lessons, coding challenges, and quizzes for comprehensive learning.
– On completing the courses, you can build a robust portfolio, opening gates to multiple job opportunities or even kicking off your own venture.
– We provide regular content updates to keep you in sync with industry developments.

Taking the Python Mini-Degree will not only strengthen your coding skills but also give you hands-on experience of working on Real-World projects.

If you’re interested in discovering what more you can do with Python, you can untap the potential of a broad collection of Python courses available with us. Check out our Python Courses and fuel your journey from beginner to professional.

Conclusion

Mastering MySQL Connector/Python will not only empower you to handle databases with Python but position you advantageously in the sphere of data-driven applications. We’ve only scratched the surface here—dive deeper, play around with code, break things, learn, and create. The path to mastery is paved with trying, learning, and iterating.

Embarking on your own learning journey might seem daunting at first, but remember, at Zenva, we have your back every step of the way. Our robust Python Mini-Degree is designed to take you from beginner to professional, all at your pace. Keep exploring. Keep coding. Keep innovating. Your journey with Python has only just begun!

Did you come across any errors in this tutorial? Please let us know by completing this form and we’ll look into it!

FREE COURSES
Python Blog Image

FINAL DAYS: Unlock coding courses in Unity, Godot, Unreal, Python and more.