Python MySQL

Posted on 2020-02-05 Edited on 2022-02-15 In Tech , Python , DB , DB , MySQL Symbols count in article: 12k Reading time ≈ 11 mins.

Python Tutorial

Python MySQL

w3school.com
MySQL 8.0 Reference Manual

Introduction

Python can be used in database applications.

One of the most popular databases is MySQL.

MySQL Database

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

Download MySQL Server from Homebrew

OR Download MySQL Server from MySQL Official Website

Install MySQL Driver

Python needs a MySQL driver to access the MySQL database.

In this tutorial we will use the driver “MySQL Connector”.

I recommend PIP or Conda to install “MySQL Connector”.

PIP is most likely already installed in your Python environment.

If you never heard PIP, click here

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

Install mysql-connector-python. DO NOT install mysql-connector:

Shell

# If you are using Anaconda python:
conda install mysql-connector-python

# Else:
python3 -m pip install mysql-connector-python

Now you have downloaded and installed a MySQL driver.

Test MySQL Connector

To test if the installation was successful, or if you already have “MySQL Connector” installed, create a Python page with the following content:

Python

1	import mysql.connector

if this page is executed with no errors, you have the mysql.connector module installed.

Create Connection

Start by creating a connection to the database.

Use the username and password from your MySQL database:

Python

import mysql.connector

mysql_connect = mysql.connector.connect(
    host = "localhost",
    user = "your_username",
    passwd = "your_password",
    database = "your_database"
    )

Now you can start querying the database using SQL statements.

Test Connection

Python

1 2	mysql_connect.is_connected() # True

Security Setting

MySQL Create User

https://dev.mysql.com/doc/refman/8.0/en/create-user.html

Official Statement:

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
  | FAILED_LOGIN_ATTEMPTS N
  | PASSWORD_LOCK_TIME {N | UNBOUNDED}
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

Log into MySQL as root user:

import mysql.connector

mysql_root = mysql.connector.connect(
    host="localhost",
    user="your_username",
    passwd="your_password"
)

Get the cursor:

1	root_cursor = mysql_root.cursor()

root_cursor = mysql_root.cursor()

Try to create a user named "test" with password "passwd":

root_cursor.execute("CREATE USER 'test'@'localhost' \
    IDENTIFIED WITH caching_sha2_password BY 'passwd' \
    PASSWORD EXPIRE INTERVAL 30 DAY \
    FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;")

Give test user ALL privilege for all database. Notice it is NOT safe to grant a user all privilege. It is only for testing conveniently.
If you do not know privilege in MySQL, click here

1 2	root_cursor.execute("GRANT ALL ON . TO 'test'@'localhost' WITH GRANT OPTION;") root_cursor.execute("FLUSH PRIVILEGE")

Test

import mysql.connector

mysql_test = mysql.connector.connect(
    host="localhost",
    user="test",
    passwd="passwd"
)

print(mysql_test)

MySQL Create Database

Creating a Database

Assume you have follow all steps above, you should have a administrator user named test with password passwd.

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

Create a database named "mydatabase":

import mysql.connector

mysql_test = mysql.connector.connect(
    host="localhost",
    user="test",
    passwd="passwd"
)

test_cursor = mysql_test.cursor()

test_cursor.execute("CREATE DATABASE mydatabase")

Check if Database Exists

You can check if a database exist by listing all databases in your system by using the SHOW DATABASES statement:

Return a list of your system's databases:

import mysql.connector

mysql_test = mysql.connector.connect(
    host="localhost",
    user="test",
    passwd="passwd"
)

test_cursor = mysql_test.cursor()

test_cursor.execute("SHOW DATABASES;")

for i in test_cursor:
    print(i)

If you print your cursor, it only returns the last command.

Or you can try to access the database when making the connection:

Directly access mydatabase while your are logging in.

import mysql.connector

mysql_test = mysql.connector.connect(
    host="localhost",
    user="test",
    passwd="passwd",
    database="mydatabase"
)

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

MySQL Select

Assume we have:

a user named test with password passwd
a database named BOS311 located in the localhost
a table named test saved in BOS311.

Select From a Table

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

Select all records from the "test" table, and display the result:

import mysql.connector

mysql_test = mysql.connector.connect(
    host="localhost",
    user="test",
    passwd="passwd",
    database="BOS311"
)

test_cursor = mysql_test.cursor()
test_cursor.execute("SELECT * FROM test")
result = test_cursor.fetchall()

type(result)
# list
type(result[0])
# tuple

result[0] # show the first row.

for row in result:
    print(row)
# pass, too many rows

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

Selecting Columns

To select only some of the columns in a table, use the SELECT statement followed by the column name(s):

Select only the reason and location_zipcode columns:

import mysql.connector

mysql_test = mysql.connector.connect(
    host="localhost",
    user="test",
    passwd="passwd",
    database="BOS311"
)

test_cursor = mysql_test.cursor()
test_cursor.execute("SELECT reason, location_zipcode FROM test")
result = test_cursor.fetchall()

type(result)
# list
type(result[0])
# tuple

result[0] # show the first row.
# ('Trees', '02116')

Using the fetchone() Method

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

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

Fetch only one row:

import mysql.connector

mysql_test = mysql.connector.connect(
    host="localhost",
    user="test",
    passwd="passwd",
    database="BOS311"
)

test_cursor = mysql_test.cursor()
test_cursor.execute("SELECT * FROM test")
result = test_cursor.fetchone()

print(result)

MySQL Where

Assume we have:

a user named test with password passwd
a database named BOS311 located in the localhost
a table named test saved in BOS311.

Select With a Filter

When selecting records from a table, you can filter the selection by using the “WHERE” statement:

Select record(s) where the location_zipcode is '02199':

import mysql.connector

mysql_test = mysql.connector.connect(
    host="localhost",
    user="test",
    passwd="passwd",
    database="BOS311"
)

test_cursor = mysql_test.cursor()

sql = "SELECT * FROM test WHERE location_zipcode='02199'"

test_cursor.execute(sql)

result = test_cursor.fetchall()

for row in result:
    print(row)

Wildcard Characters

You can also select the records that starts, includes, or ends with a given letter or phrase.

Use the % to represent wildcard characters:

Select records where the location contains the word 'Huntington Ave':

import mysql.connector

mysql_test = mysql.connector.connect(
    host="localhost",
    user="test",
    passwd="passwd",
    database="BOS311"
)

test_cursor = mysql_test.cursor()

sql = "SELECT * FROM test WHERE location like '%Huntington Ave%'"

test_cursor.execute(sql)

result = test_cursor.fetchall()

for row in result:
    print(row)

Prevent SQL Injection

When query values are provided by the user, you should escape the values.

This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.

The mysql.connector module has methods to escape query values:

Escape query values by using the placholder %s method:

Query data from a variable:

import mysql.connector

mysql_test = mysql.connector.connect(
    host="localhost",
    user="test",
    passwd="passwd",
    database="BOS311"
)

test_cursor = mysql_test.cursor()

sql = "SELECT * FROM test WHERE location_zipcode = %s"
zip = ("02199", )

test_cursor.execute(sql, zip)
result = test_cursor.fetchall()

len(result)

Query data from more variables:

import mysql.connector

mysql_test = mysql.connector.connect(
    host="localhost",
    user="test",
    passwd="passwd",
    database="BOS311"
)

test_cursor = mysql_test.cursor()

zip1 = "02199"
zip2 = "02203"
sql = "SELECT * FROM test WHERE location_zipcode = '{0}' OR location_zipcode = '{1}'".format(zip1, zip2)

test_cursor.execute(sql)
result = test_cursor.fetchall()

len(result)

print(sql)

MySQL Update

Update Table

You can update existing records in a table by using the “UPDATE” statement:

Overwrite the priority column from 2 to 1:

import mysql.connector

# Define a function to connect MySQL
def mysql_login(mysql_host, mysql_user, mysql_passwd, mysql_database):
    global mysql_connect

    mysql_connect = mysql.connector.connect(
        host = mysql_host,
        user = mysql_user,
        passwd = mysql_passwd,
        database = mysql_database
    )

# Set login information
mysql_login(
    mysql_host = "localhost",
    mysql_user = "test",
    mysql_passwd = "passwd",
    mysql_database = "BOS311"
    )

# Set MySQL cursor
mysql = mysql_connect.cursor()

# Set MySQL table
mysql_table = "test_2020"

# Set the values
case_information = {}
case_information['priority'] = 1
case_information['case_status'] = 'Open'
case_information['reason'] = 'Street Lights'
case_information['location'] = '15 Sudbury St  Boston  MA  02203'

# Define a function raise the priority
def mysql_raise_priority():
    sql = "UPDATE {0} SET priority = {1} WHERE case_status = '{2}' AND reason = '{3}' AND location = '{4}'".format(
        mysql_table,
        case_information['priority'],
        case_information['case_status'],
        case_information['reason'],
        case_information['location']
    )

    mysql.execute(sql)
    mysql_connect.commit()
    print(mysql.rowcount, "record(s) affected")

mysql_raise_priority()

Important!: Notice the statement: mysql.connector.connect().commit(). It is required to make the changes, otherwise no changes are made to the table.

Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Prevent SQL Injection

It is considered a good practice to escape the values of any query, also in update statements.

This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.

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

Escape values by using the placholder %s method:

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")