5.2 Creating Tables Using Connector/Python — MySQL Connectors 1.0

5.2 Создание таблиц с помощью Connector/Python

Все операторы языка определения данных (Data Definition Language) выполняются с использованием структуры обработчика, известной как курсор. Приведенные ниже примеры демонстрируют, как создать таблицы из базы данных Employee Sample Database. Вам понадобятся эти таблицы для других примеров.

В сервере 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"
    ") 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 DEFAULT NULL,"
    "  PRIMARY KEY (`emp_no`,`title`,`from_date`), KEY `emp_no` (`emp_no`),"
    "  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`)"
    "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
    ") ENGINE=InnoDB")

В приведенном коде показано, как мы храним операторы языка определения данных в словаре Python под названием CREATE. Также мы определяем базу данных в глобальной переменной под названием TABLES, что позволяет легко использовать другую схему.

cnx = mysql.connector.connect(user='scott')
cursor = cnx.cursor()

Один сервер MySQL может управлять множеством баз данных. Обычно вы указываете базу данных, на которую необходимо переключиться при подключении к серверу MySQL. В этом примере подключение к базе данных не выполняется при подключении, чтобы убедиться в существовании базы данных и создать ее, если она отсутствует:

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)

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)

Сначала мы пытаемся переключиться на конкретную базу данных, используя свойство database объекта соединения cnx. Если возникает ошибка, мы проверяем номер ошибки, чтобы убедиться, что база данных не существует. Если это так, мы вызываем функцию create_database, чтобы создать ее для нас.

При любой другой ошибке приложение завершается и выводится сообщение об ошибке.

После успешного создания или переключения на целевую базу данных мы создаем таблицы, перебирая элементы словаря TABLES:

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 exists.")
        else:
            print(err.msg)
    else:
        print("OK")

cursor.close()
cnx.close()

Для обработки ошибки, когда таблица уже существует, мы сообщаем пользователю, что она уже присутствует. Другие ошибки выводятся на экран, но мы продолжаем создание таблиц. (В примере показано, как обработать условие «таблица уже существует» в целях иллюстрации. В реальном приложении мы, как правило, полностью избегаем этой ситуации, используя предложение IF NOT EXISTS оператора языка определения данных.)

Вывод будет примерно таким:

Database employees does not exists.
Database employees created successfully.
Creating table employees: OK
Creating table departments: already exists.
Creating table salaries: already exists.
Creating table dept_emp: OK
Creating table dept_manager: OK
Creating table titles: OK

Для заполнения таблиц сотрудников используйте файлы дампов базы данных Employee Sample Database. Обратите внимание, что вам нужны только файлы дампов данных, которые вы найдете в архиве с именем, подобным employees_db-dump-files-1.0.5.tar.bz2. После загрузки файлов дампов выполните следующие команды, добавив опции подключения к командам mysql, если это необходимо:

$> tar xzf employees_db-dump-files-1.0.5.tar.bz2
$> cd employees_db
$> mysql employees < load_employees.dump
$> mysql employees < load_titles.dump
$> mysql employees < load_departments.dump
$> mysql employees < load_salaries.dump
$> mysql employees < load_dept_emp.dump
$> mysql employees < load_dept_manager.dump