Ubuntu下通过python使用MySQL

参考资料

MySQL Connector/Python Developer Guide

环境

Ubuntu 20.04
Python 3.8.10
MySQL Server 8.0.34
mysql-connector-python 8.1.0

安装MySQL

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

环境测试

注意：

千万不能命名为mysql.py!
千万不能命名为mysql.py!
千万不能命名为mysql.py!

import mysql.connectordef get_connection():connection = mysql.connector.connect(host='localhost',database='python_db',user='root',password='root')return connectiondef 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 errorcodeconfig = {'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 executeprint("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 executecnx.close()print("Database operation OK")
finally:    # always executeprint("Finish")

MySQL创建表格及数据库

以下表格数据下载：employees database

import mysql.connector
from mysql.connector import errorcodeDB_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_NAMEelse: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()
cnx.close()

错误及原因

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