Welcome to this comprehensive tutorial on MySQL Connector/Python, an interface for connecting to MySQL databases using Python.
Table of contents
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.
What is it for?
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.
Why should I learn it?
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.
FREE COURSES AT ZENVA
LEARN GAME DEVELOPMENT, PYTHON AND MORE
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. At Zenva, we offer a wide range of beginner to professional level courses in programming, game development, and AI. As part of our over 250 supported courses, you can further solidify your coding skills, create games, dive into forward-looking technologies like AI, and even earn certificates to showcase your accomplishments.
Next Step: Python Mini-Degree
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.
Broaden Your Learning Horizon with Python
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.
At Zenva, we believe in fostering learning to empower you to create, design, and code beyond limitations. Keep learning and keep growing!
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!
FREE COURSES
FINAL DAYS: Unlock coding courses in Unity, Unreal, Python, Godot and more.
