Ubuntu下通过python使用MySQL

参考资料

1. MySQL Connector/Python Developer Guide
2. python_mysql_tutorial

环境

1. Ubuntu 20.04
2. Python 3.8.10
3. MySQL Server 8.0.34
4. mysql-connector-python 8.1.0

安装MySQL

pip install mysql-connector-python	# 注意，不要安装成 mysql-connector 了

环境测试

注意：

1. 千万不能命名为mysql.py!
2. 千万不能命名为mysql.py!
3. 千万不能命名为mysql.py!

import mysql.connector

def get_connection():
    connection = mysql.connector.connect(host='localhost',
                                         database='python_db',
                                         user='root',
                                         password='root')
    return connection

def close_connection(connection):
    if connection:
        connection.close()

def read_database_version():
    try:
        connection = get_connection()
        cursor = connection.cursor()
        cursor.execute("SELECT version();")
        db_version = cursor.fetchone()
        print("You are connected to MySQL version: ", db_version)
        close_connection(connection)
    except (Exception, mysql.connector.Error) as error:
        print("Error while getting data", error)

print("Question 1: Print Database version")
read_database_version()

MySQL 的连接和关闭

关于下列代码中 ** 的解释：python中星号的意义

import mysql.connector
from mysql.connector import errorcode

config = {
    'user': 'root',
    'password': 'root',
    'database': 'mydb',
    'host': 'localhost',
    'raise_on_warnings': True
}

try:
    cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:    # if except happen will execute
    print("Database operation ERR")
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:   # if no except happen will execute
    cnx.close()
    print("Database operation OK")
finally:    # always execute
    print("Finish")

MySQL创建数据库

import mysql.connector

conn = mysql.connector.connect(user='root', password='root')
cursor = conn.cursor()
cursor.execute("drop database if exists my_database")		# 通过 if exists 防止数据不存在而报错
sql = "create database my_database"
cursor.execute(sql)					# 创建数据库
print("List of databases: ")
cursor.execute("show databases")	# 执行 SQL 语句
print(cursor.fetchall())			# 获取 MySQL 的输出，有 fetchone() fetchmany() fetchall() 三个接口
conn.close()

MySQL创建表格及数据库

以下表格数据下载：employees database

import mysql.connector
from mysql.connector import errorcode

DB_NAME = 'employees'

TABLES = {}
TABLES['employees'] = (
        "create table `employees` ("
        "   `emp_no` int(11) NOT NULL AUTO_INCREMENT,"
        "   `birth_date` date NOT NULL,"
        "   `first_name` varchar(14) NOT NULL,"
        "   `last_name` varchar(16) NOT NULL,"
        "   `gender` enum('M', 'F') NOT NULL,"
        "   `hire_date` date NOT NULL,"
        "   primary key (`emp_no`)"
        ") engine=InnoDB")

TABLES['departments'] = (
        "create table `departments` ("
        "   `dept_no` char(4) NOT NULL,"
        "   `dept_name` varchar(40) NOT NULL,"
        "   primary key (`dept_no`), unique key `dept_name` (`dept_name`)"
        ") engine=InnoDB")

TABLES['salaries'] = (
        "create table `salaries` ("
        "   `emp_no` int(11) NOT NULL,"
        "   `salary` int(11) NOT NULL,"
        "   `from_date` date NOT NULL,"
        "   `to_date` date NOT NULL,"
        "   primary key (`emp_no`, `from_date`), key `emp_no` (`emp_no`),"
        "   constraint `salaries_ibfk_1` foreign key (`emp_no`) "
        "       references `employees` (`emp_no`) on delete cascade"
        ") engine=InnoDB")

TABLES['dept_emp'] = (
        "create table `dept_emp` ("
        "   `emp_no` int(11) NOT NULL,"
        "   `dept_no` char(4) NOT NULL,"
        "   `from_date` date NOT NULL,"
        "   `to_date` date NOT NULL,"
        "   primary key (`emp_no`, `dept_no`), key `emp_no` (`emp_no`),"
        "   key `dept_no` (`dept_no`),"
        "   constraint `dept_emp_ibfk_1` foreign key (`emp_no`) "
        "       references `employees` (`emp_no`) on delete cascade,"
        "   constraint `dept_emp_ibfk_2` foreign key (`dept_no`) "
        "       references `departments` (`dept_no`) on delete cascade"
        ") engine=InnoDB") 

TABLES['dept_manager'] = (
        "create table `dept_manager` ("
        "   `emp_no` int(11) NOT NULL,"
        "   `dept_no` char(4) NOT NULL,"
        "   `from_date` date NOT NULL,"
        "   `to_date` date NOT NULL,"
        "   primary key (`emp_no`, `dept_no`),"
        "   key `emp_no` (`emp_no`),"
        "   key `dept_no` (`dept_no`),"
        "   constraint `dept_manager_ibfk_1` foreign key (`emp_no`) "
        "       references `employees` (`emp_no`) on delete cascade,"
        "   constraint `dept_manager_ibfk_2` foreign key (`dept_no`) "
        "       references `departments` (`dept_no`) on delete cascade"
        ") engine=InnoDB")

TABLES['titles'] = (
        "create table `titles` ("
        "   `emp_no` int(11) NOT NULL,"
        "   `title` varchar(50) NOT NULL,"
        "   `from_date` date NOT NULL,"
        "   `to_date` date NOT NULL,"
        "   primary key (`emp_no`, `title`, `from_date`), key `emp_no` (`emp_no`),"
        "   constraint `title_ibfk_1` foreign key (`emp_no`)"
        "       references `employees` (`emp_no`) on delete cascade"
        ") engine=InnoDB")

def create_database(cursor):
    try:
        cursor.execute(
                "create database {} default character set 'utf8'".format(DB_NAME))
    except mysql.connector.Error as err:
        print("Failed creating database: {}".format(err))
        exit(1)

cnx = mysql.connector.connect(user='root', password='root')
cursor = cnx.cursor()

try:
    cursor.execute("USE {}".format(DB_NAME))
except mysql.connector.Error as err:
    print("Database {} does not exists.".format(DB_NAME))
    if err.errno == errorcode.ER_BAD_DB_ERROR:
        create_database(cursor)
        print("Database {} created successfully.".format(DB_NAME))
        cnx.database = DB_NAME
    else:
        print(err)
        exit(1)

for table_name in TABLES:
    table_description = TABLES[table_name]
    try:
        print("Creating table {}: ".format(table_name), end='')
        cursor.execute(table_description)
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
            print("already exist.")
        else:
            print(err.msg)
    else:
        print("OK")

cursor.close()		# cursor 也需要关闭
cnx.close()

import mysql.connector

conn = mysql.connector.connect(user='root', password='root')
cursor = conn.cursor()
cursor.execute("show databases")
print(cursor.fetchall())		# 获取并打印 MySQL 的输出
cursor.execute("use mydb")		# 选择数据库
cursor.execute("drop table if exists employee")		# 如果已经存在 employee 表格，则删除

# 除了上面在双引号中添加SQL语句，还可以利用 python 多行字符串的语法，在三引号中添加 SQL 语句，这样就没有这么多的引号，看起来清晰很多
sql = '''
    create table employee(
        first_name char(20) not null,
        last_name char(20),
        age int,
        sex char(1),
        income float
    )
'''
cursor.execute(sql)		# 新建 employee 表格
cursor.execute("desc employee")		# 显示表格详情
print(cursor.fetchall())
cursor.execute("show create table employee")	# 显示创建表格的源码！
print(cursor.fetchall())
cursor.close()
conn.close()

插入数据 INSERT INTO

import mysql.connector

conn = mysql.connector.connect(user='root', password='root', database='mydb')
cursor = conn.cursor()

sql = '''
    insert into employee(first_name, last_name, age, sex, income) values('Mac', 'Mohan', 20, 'M', 2000)
'''

try:
    cursor.execute(sql)
    conn.commit()
    print("commit")
except:
    conn.rollback()
    print(rollback)

conn.close()

import mysql.connector

conn = mysql.connector.connect(user='root', password='root', database='mydb')
cursor = conn.cursor()
# 还可以通过这种方式执行 SQL 语句，这样就不需要重复写 SQL 语句了，只需要写需要使用的数据即可
sql = '''
    insert into employee(first_name, last_name, age, sex, income) values(%s, %s, %s, %s, %s)
'''
data1 = ('Ramya', 'Ramapriya', 25, 'F', 5000)
data2 = ('shadow3d', 'Denis', 28, 'M', 30000)

try:
    cursor.execute(sql, data1)
    cursor.execute(sql, data2)
    conn.commit()
    print("insert OK, commit")
except:
    print("insert failed, rollback")
    conn.rollback()

conn.close()

查看数据 select

import mysql.connector

conn = mysql.connector.connect(user='root', password='root', database='mydb')
cursor = conn.cursor()

sql = "select * from employee"

# 通过 fetchone() 每次只读一行，可以更直观的显示查询结果
print("read one by one:")
cursor.execute(sql)
result = cursor.fetchone()
while result:
    print("\t", result)
    result = cursor.fetchone()

# 通过 fetchmany() 指定每次读取的行数
print("\nread two by two:")
cursor.execute(sql)
result = cursor.fetchmany(size=2)
while result:
    print("\t", result)
    result = cursor.fetchmany(size=2)

conn.close()

错误及原因

1. ModuleNotFoundError: No module named 'mysql.connector'; 'mysql' is not a package
   以上错误是由于将本地的python文件命名成了mysql.py导致，改个名字就好了；
2. mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported
   以上错误是由于安装的模块错了，我们需要安装mysql-connector-python，而安装成了mysql-connector;