To work with MySQL in Python 3, you can use the Python MySQL Connector module. Here are the steps to connect to a MySQL database and perform CRUD (Create, Read, Update, Delete) operations using Python:

Install MySQL Connector

You can install MySQL Connector using pip command in your terminal:

pip install mysql-connector-python

Connect to the MySQL Database

To connect to the MySQL database, you need to import the mysql.connector module and create a connection object by specifying the host, user, password, and database name. Here is an example:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

print(mydb)

Create cursor object

After connecting to the MySQL database, you need to create a cursor object to execute SQL queries. Here is an example:

mycursor = mydb.cursor()

Execute SQL Queries

Once you have the cursor object, you can execute SQL queries using the execute() method. Here are some examples of SQL queries:

Create a table:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

mycursor = mydb.cursor()

# Create a table
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

# Insert a record
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")

Selecting data from a table:

# Select all records
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Updating a row in Table:

# Update a record
sql = "UPDATE customers SET address = 'Canyon 123' WHERE name = 'John'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")

Deleting a record from a Table:

# Delete a record
sql = "DELETE FROM customers WHERE address = 'Highway 21'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")

Close the Connection

After you finish working with the database, you should close the connection using the close() method. Here is an example:

mydb.close()

In the next part, we will cover advance tasks we can perform on MySQL using Pythong.

Host your Django Application for free on PythonAnyWhere.
If you want complete control of your application and server, you should consider DigitalOcean. Create an account with this link and get $200 credits.