python advanced-python MySQL

table of Contents

ready to go

Python can be used in database applications.
MySQL is one of the most popular databases.

MySQL database

To be able to experiment with the code examples in this tutorial, you should install MySQL on your computer.

Please download the free MySQL database here: https://www.mysql.com/downloads/.

Install the MySQL driver

Python needs a MySQL driver to access the MySQL database.

In this tutorial, we will use the driver "MySQL Connector".

We recommend that you install "MySQL Connector" using PIP.

PIP is probably already installed in the Python environment.

Navigate the command line to the location of PIP and type the following:

Download and install "MySQL Connector":

C:\...\AppData\Local\Programs\Python\Python36-32\Scripts>python -m pip install mysql-connector

You have now downloaded and installed the MySQL driver.

Test MySQL Connector

To test whether the installation is successful, or whether you have installed "MySQL Connector", please create a Python page with the following content:
demo_mysql_test.py:

import mysql.connector

If there is no error in executing the above code, the "MySQL Connector" has been installed and is ready to use.

Create connection

First create a connection to the database.

Use the username and password in the MySQL database:

demo_mysql_connection.py:
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword"
)

print(mydb)

Now, you can start to query the database using SQL statements.


One, Python to create a database

1. Create a database

To create a database in MySQL, use the "CREATE DATABASE" statement:

Example
Create a database named "mydatabase":

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

If there is no error in executing the above code, you have successfully created the database.

2. Check whether the database exists

You can list all the databases in the system by using the "SHOW DATABASES" statement to check whether the database exists:

Example
Return the list of databases in the system:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword"
)

mycursor = mydb.cursor()

mycursor.execute("SHOW DATABASES")

for x in mycursor:
  print(x)

Or you can try to access the database while establishing a connection:
Example
Try to connect to the database "mydatabase":

import mysql.connector

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

If the database does not exist, you will receive an error.


Two, Python create table

1. Create a table

To create a table in MySQL, use the "CREATE TABLE" statement.

Make sure to define the name of the database when creating the connection.

Example
Create the table "customers":

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

If there is no error in executing the above code, then you have now successfully created a table.

2. Check if the table exists

You can check whether the table exists by listing all the tables in the database using the "SHOW TABLES" statement:

Example
Return the list of databases in the system:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SHOW TABLES")

for x in mycursor:
  print(x)

3. Primary key

When creating a table, you should also create a column with a unique key for each record.

This can be done by defining PRIMARY KEY.

We use the statement "INT AUTO_INCREMENT PRIMARY KEY", which will insert a unique number for each record. Starting from 1, each record is incremented by 1.

Example
Create a primary key when creating a table:

import mysql.connector

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

mycursor = mydb.cursor()

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

If the table already exists, use the ALTER TABLE keyword:

Example
Create a primary key on an existing table:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

Three, Python Insert Into Table

1. Insert the table

To populate a table in MySQL, use the "INSERT INTO" statement.

Example
Insert a record in the table "customers":

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="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.")

Important: Please pay attention to the statement mydb.commit(). It needs to be changed, otherwise there will be no changes to the table.

2. Insert multiple rows

To insert multiple rows in the table, use the executemany() method.

The second parameter of the executemany() method is a list of tuples containing the data to be inserted:

Example
Populate the "customers" table with data:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="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, "was inserted.")

3. Get the inserted ID

You can get the id of the row just inserted by asking the cursor object.

Note: If more than one row is inserted, the id of the last inserted row will be returned.

Example
Insert a row and return id:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Michelle", "Blue Village")
mycursor.execute(sql, val)

mydb.commit()

print("1 record inserted, ID:", mycursor.lastrowid)

Four, Python Select From

1. Select from the table

To select from a table in MySQL, use the "SELECT" statement:

Example
Select all records from the table "customers" and display the results:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Note: We used the fetchall() method, which fetches all rows from the last executed statement.

2. Select the column

To select only certain columns in the table, use the "SELECT" statement followed by the column names:

Example
Select only the name and address columns:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT name, address FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

3. Use the fetchone() method

If you are only interested in one line, you can use the fetchone() method.

The fetchone() method will return the first line of the result:

Example
Get only one row:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchone()

print(myresult)

Five, Python MySQL Where

1. Use filters to select

When selecting records from the table, you can use the "WHERE" statement to filter the selection:

Example
Select the record with the record as "Park Lane 38", the result:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="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)

2. Wildcard

You can also select records that begin, contain, or end with a given letter or phrase.

Please use% to represent wildcards:

Example
Select the record whose address contains the word "way":

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE address LIKE '%way%'"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

3. Prevent SQL injection

When users provide query values, you should escape these values.

This is to prevent SQL injection, a common network hacking technique that can damage or abuse your database.

The mysql.connector module has methods for escaping query values:

Example
Use the placeholder %s method to escape the query value:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )

mycursor.execute(sql, adr)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Six, Python MySQL Order By

1. Sort results

Please use the ORDER BY statement to sort the results in ascending or descending order.

The ORDER BY keyword sorts the results in ascending order by default. To sort the results in descending order, use the DESC keyword.

Example
Sort the names in character order, the result:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers ORDER BY name"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

2. Sort in descending order

Please use the DESC keyword to sort the results in descending order.

Example
Sort the results of names in reverse alphabetical order:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT * FROM customers ORDER BY name DESC"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Seven, Python MySQL Delete From

1. Delete records

You can use the "DELETE FROM" statement to delete records from an existing table:

Example To
delete any record whose address is "Mountain 21":

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="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")

Important: Please pay attention to the statement mydb.commit(). It needs to be changed, otherwise there will be no changes to the table.

Note the WHERE clause in the DELETE syntax: The WHERE clause specifies which records should be deleted. If the WHERE clause is omitted, all records will be deleted!

2. Prevent SQL injection

In the delete statement, it is also a good habit to escape the value of any query.

This is to prevent SQL injection, a common network hacking technique that can damage or abuse your database.

The mysql.connector module uses the placeholder %s to escape the value in the delete statement:

Example
Use the placeholder %s method to escape the value:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )

mycursor.execute(sql, adr)

mydb.commit()

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

Eight, Python MySQL Drop Table

1. Delete the table

You can use the "DROP TABLE" statement to delete an existing table:

Example To
delete the "customers" table:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "DROP TABLE customers"

mycursor.execute(sql)

2. Delete only when the table exists

If the table to be deleted has been deleted, or does not exist for any other reason, you can use the IF EXISTS keyword to avoid errors.

Example To
delete the table "customers" (if it exists):

import mysql.connector

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

mycursor = mydb.cursor()

sql = "DROP TABLE IF EXISTS customers"

mycursor.execute(sql)

Nine, Python update table

1. Update table

You can use the "UPDATE" statement to update existing records in the table:

Example
Overwrite "Valley 345" in the address column with "Canyoun 123":

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="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")

Important : Please pay attention to the statement mydb.commit(). Need to make changes, otherwise there will be no changes to the table.

Note the WHERE clause in the UPDATE syntax: The WHERE clause specifies the records that should be updated. If the WHERE clause is omitted, all records will be updated!

2. Prevent SQL injection

In the update statement, it is a good habit to escape any query value.

This is to prevent SQL injection, a common network hacking technique that can damage or abuse your database.

The mysql.connector module uses the placeholder %s to escape the value in the delete statement:

Example
Use the placeholder %s method to escape the value:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")

mycursor.execute(sql, val)

mydb.commit()

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

Ten, Python MySQL Limit

1. Limit the results

You can use the "LIMIT" statement to limit the number of records returned from the query:

Example
Select the first five records in the "customers" table:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers LIMIT 5")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

2. Start from another location

If you want to return five records starting from the third record, you can use the "OFFSET" keyword:

Example
Return 5 records starting from position 3:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Eleven, Python MySQL Join

1. Combine two or more tables

You can use JOIN statements to combine rows in two or more tables based on the related columns between them.

Suppose you have a "users" table and a "products" table:

users

{ id: 1, name: 'John', fav: 154},
{ id: 2, name: 'Peter', fav: 154},
{ id: 3, name: 'Amy', fav: 155},
{ id: 4, name: 'Hannah', fav:},
{ id: 5, name: 'Michael', fav:}

products

{ id: 154, name: 'Chocolate Heaven' },
{ id: 155, name: 'Tasty Lemons' },
{ id: 156, name: 'Vanilla Dreams' }

You can use the fav field of users and the id field of products to combine the two tables.
Example
Combine users and products to view the name of the user’s favorite product:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  INNER JOIN products ON users.fav = products.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Note: You can use JOIN instead of INNER JOIN. You will all get the same result.

2. LEFT JOIN

In the above example, Hannah and Michael are excluded from the results because INNER JOIN only displays matching records.

If you want to display all users, even if they do not have a favorite product, use the LEFT JOIN statement:

Example
Select all users and their favorite products:

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  LEFT JOIN products ON users.fav = products.id"

3. RIGHT JOIN

If you want to return all products and users who like them, even if no users like these products, use the RIGHT JOIN statement:

Examples
Select all products and users who like them:

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  RIGHT JOIN products ON users.fav = products.id"

Note: Hannah and Michael who are not interested in any products are not included in the results.