【MySQL连接器(Python)指南】05-通过连接器操作MySQL数据库

---

前言

MySQL连接器(Python)，用于让Python程序能够访问MySQL数据库。要想让Python应用程序正确高效地使用MySQL数据，就需要深入了解MySQL连接器的特性和使用方法。

上篇文章👉《【MySQL连接器(Python)指南】04-通过连接器连接 MySQL》 介绍了如何通过连接器连接MySQL Server，这篇接着介绍通过连接器操作MySQL数据库的方法。

---

通过连接器创建表

所有的DDL（数据定义语言）语句的执行，都是基于游标句柄结构。以下示例展示了如何创建员工示例数据库的表。

在MySQL服务器中，表是长期存在的对象，通常可以被基于不同语言实现的多个应用程序访问。你通常应该使用已经创建好的表，而不是在自己的应用程序中创建它们。因为创建和删除表是一种成本很高的操作，所以应该避免反复创建和删除表。有一种例外，那就是临时表，它专门用于临时存储数据，可以在应用程序中快速地创建和删除临时表。

from __future__ import print_function
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"