Are you ready to boost your programming skills by diving into the world of databases? Welcome to this comprehensive tutorial on interacting with MySQL databases using Python. Packed with engaging examples and valuable lessons, it’s perfect for early learners and experienced coders alike.
Table of contents
What’s Python MySQL?
Python MySQL is a term that describes the practice of interacting with MySQL databases using the Python programming language. MySQL is a popular open-source database management system, while Python is a high-level programming language known for its simplicity and wide range of applications, including database interactions.
Why Learn Python MySQL?
Python MySQL is an essential component of many major websites, games, and applications today. Understanding how to interact with databases using Python helps efficiently store, analyze, and manipulate data, unlocking a vast range of possibilities in development and data science.
Utilizing Python MySQL
Python MySQL allows developers to parse large amounts of data, automate data entry or retrieval tasks, and even work on advanced machine learning projects. Whether you’re building your first game or developing a full-fledged application, a solid grasp of Python MySQL is an invaluable tool in your arsenal.
FREE COURSES AT ZENVA
LEARN GAME DEVELOPMENT, PYTHON AND MORE
AVAILABLE FOR A LIMITED TIME ONLY
Python MySQL Basics – Setting Up a Connection
Before interacting with a MySQL database using Python, we need to set up a connection. To do this, we’ll use a Python library specifically designed for this task, called mysql-connector-python.
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword" ) print(mydb)
These lines of code import the necessary library, define the connection parameters (server name, username, and password), and establish the connection. If the connection is successful, it will print a connection object.
Creating a Database
Once we’ve established a connection, we can create a database. Let’s call this database “mydatabase”. Here’s how:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")This code connects to the server and then sends a SQL command to create a new database called “mydatabase”.
Creating a Table
After creating a database, we can create tables within it. A table contains data in rows and columns. Here’s how to create a simple table called “customers”.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")This code will create a new table called “customers” with two columns “name” and “address”.
Inserting Data Into a Table
Now let’s add some rows of data to our “customers” table. To do this, we use the INSERT INTO command:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")This script connects to the database, defines an SQL query to add a new row of data (“John”, “Highway 21”) to the “customers” table, executes the query, commits the transaction, and then prints the number of rows inserted.
Inserting Multiple Rows Into a Table
More often, you’ll want to add multiple rows of data at once. Here’s how you’d do that:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
('Peter', 'Lowstreet 4'),
('Amy', 'Apple st 652'),
('Hannah', 'Mountain 21'),
('Michael', 'Valley 345'),
('Sandy', 'Ocean blvd 2'),
('Betty', 'Green Grass 1'),
('Richard', 'Sky st 331'),
('Susan', 'One way 98'),
('Vicky', 'Yellow Garden 2'),
('Ben', 'Park Lane 38'),
('William', 'Central st 954'),
('Chuck', 'Main Road 989'),
('Viola', 'Sideway 1633')
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")This script works similar to the previous, but this time, we insert multiple rows of data into the “customers” table. The script prints the number of rows inserted.
Selecting Data
Now that we’ve got some data in our “customers” table, we’d like to select some or all of it. For this we use the SELECT command:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
print(x)This script selects all data from the “customers” table and prints each row of data.
Selecting Specific Data
If you want to select a specific row from the “customers” table, you can use the WHERE command:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM customers WHERE address ='Park Lane 38'" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
This script selects the row(s) where the address is ‘Park Lane 38’ from the “customers” table and prints it.
Updating Data
If you need to update data in the “customers” table, you can use the UPDATE command:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, "record(s) affected")
This script updates the address to ‘Canyon 123’ where the address was previously ‘Valley 345’. It then prints the number of rows affected.
Deleting Data
To delete a row in the “customers” table, we can use the DELETE command:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "DELETE FROM customers WHERE address = 'Mountain 21'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, "record(s) deleted")
This script deletes the row where the address is ‘Mountain 21’, commits the transaction, and then prints the number of rows deleted.
Where to Go Next – Keep Learning!
With your newfound knowledge of Python MySQL, it’s time to keep going! Continual learning and application are key to mastering any new skill, and programming is no exception.
For an in-depth and complete learning pathway, check out our comprehensive Python Mini-Degree. This program is your ticket to learning not just Python and database interactions, but also a wide array of other skills and concepts.
Our Python Mini-Degree features topics ranging from coding basics and algorithms to object-oriented programming, game development, and app creation. It’s suitable for everyone, regardless of whether you’re a beginner just getting started or an experienced programmer looking to expand your toolkit.
By joining our Python Mini-Degree, you’re signing up to step outside the limits of typical lectures. With us, you can create your own games, apps, and real-world projects. Along the way, you’ll have access to expert mentorship and updated courses designed to keep pace with industry trends.
Remember, learning Python opens doors to exciting opportunities in data science and many other industry sectors. Not only is Python a powerful tool for your personal projects, but it also shines brightly on your resumé, making your career prospects even brighter.
Whether you’re aiming to shift careers or just looking to achieve a set of professional goals, many students have found success after completing our Python Mini-Degree. What’s more, there’s no rush. Our courses are designed for self-paced learning, which means you can make progress in your own time without any strict deadlines.
Finally, for a broader collection of Python topics, do take a look at our Python courses. Everyone at Zenva is here cheering you along in your coding journey!
At Zenva, we provide practical knowledge through project-based courses. Our learners have gained in-demand skills to help them build games, apps, and inventions across various fields. No matter the path you follow, we’re here to help and guide you towards achieving your goals.
Conclusion
Python with MySQL forms a powerful combination that propels many successful ventures, from innovative web projects to data-driven applications. It’s a skill set we recommend for anyone looking to grow as a programmer, game creator, or software developer.
Ready for the next big leap? Don’t hesitate to dive into our comprehensive Python Mini-Degree. We’ve prepared a world of discovery and challenge where you can apply these lessons and much more to become an unstoppable coder. The journey with Python and MySQL is thrilling – it’s time to embark upon it with Zenva!
FREE COURSES
FINAL DAYS: Unlock coding courses in Unity, Unreal, Python, Godot and more.
