Python Mysql Tutorial – Complete Guide

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.

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.

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.

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.

CTA Small Image
FREE COURSES AT ZENVA
LEARN GAME DEVELOPMENT, PYTHON AND MORE
ACCESS FOR FREE
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.

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!

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!

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.