• 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

    环境测试

    注意：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    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()

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    插入数据 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()

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    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()

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    查看数据 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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    错误及原因

    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;
  • 相关阅读:
    Makefile泛谈
    零基础学Java一定要注意这些问题！
    java 字符串只保留数字、字母、中文
    CH552T可以这样接si24r1吗
    基于element UI 实现大文件分片上传
    Golang标准库限流器rate使用
    【openwrt】libubox组件——uloop_process
    由ASP.NET Core根据路径下载文件异常引发的探究
    关于Docker挂载的问题！
    前端开发中遇到的复杂数据求和问题
  • 原文地址：https://blog.csdn.net/qq_29506411/article/details/132759167