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

前面的程式碼顯示我們如何將 CREATE 語句儲存在名為 TABLES 的 Python 字典中。我們也在名為 DB_NAME 的全域變數中定義資料庫，這讓您可以輕鬆地使用不同的綱要。

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)

我們首先嘗試使用連線物件 cnx 的 database 屬性來變更為特定的資料庫。如果發生錯誤，我們會檢查錯誤號碼來確認資料庫是否存在。如果不存在，我們會呼叫 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()

為了處理資料表已經存在時的錯誤，我們會通知使用者它已經存在。其他錯誤會被列印出來，但我們會繼續建立資料表。(此範例顯示如何為了說明目的而處理「資料表已存在」的情況。在實際的應用程式中，我們通常會完全避免錯誤情況，方法是使用CREATE TABLE 語句的 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

若要填入 employees 資料表，請使用 員工範例資料庫 的 dump 檔案。請注意，您只需要在名為 employees_db-dump-files-1.0.5.tar.bz2 的封存檔中找到的資料 dump 檔案。在下載 dump 檔案後，請執行下列命令，如有必要，請將連線選項新增至 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