114      Глава 5. Работа с базами данных

На этот раз мы добавляем ограничение внешнего ключа внутри команды CREATE TABLE, тем самым определяя внешний ключ сразу после создания таблицы.

Вставка данных в БД

Теперь все готово, чтобы вставлять строки во вновь созданные таблицы. Хотя это можно сделать в командной строке mysql>, данная операция обычно выполняется из приложения. Мы будем взаимодействовать с базой данных из Python-кода через драйвер MySQL Connector/Python. Его можно установить с помощью команды pip, как показано ниже:

$ pip install mysql-connector-python

Запустите следующий скрипт, чтобы заполнить таблицы БД данными:

import mysql.connector

try:

cnx = mysql.connector.connect(user='root', password='your_pswd', host='127.0.0.1', database='sampledb')

cursor = cnx.cursor()

# объявление строк с сотрудниками

emps = [

(9001, "Jeff Russell", "sales"),

(9002, "Jane Boorman", "sales"),

(9003, "Tom Heints", "sales")

]

# объявление запроса

query_add_emp = ("""INSERT INTO emps (empno, empname, job)

VALUES (%s, %s, %s)""")

#вставка строк с сотрудниками

for emp in emps:

cursor.execute(query_add_emp, emp)

#определение и вставка размеров оклада

salary = [

(9001, 3000), (9002, 2800), (9003, 2500)

]

query_add_salary = ("""INSERT INTO salary (empno, salary) VALUES (%s, %s)""")

for sal in salary: cursor.execute(query_add_salary, sal)

# объявление и вставка заказов

Понимание инструкций SQL      115

]

query_add_order = ("""INSERT INTO orders(pono, empno, total) VALUES (%s, %s, %s)""")

for order in orders: cursor.execute(query_add_order, order)

# делаем вставку в БД постоянной

cnx.commit()

except mysql.connector.Error as err: print("Error-Code:", err.errno) print("Error-Message: {}".format(err.msg))

finally: cursor.close() cnx.close()

В скрипте мы импортируем драйвер MySQL Connector/Python как mysql. connector. Затем открываем блок try/except, который предоставляет шаблон для любых операций, связанных с базой данных, выполняемых в скрипте. Код для операции пишем в блоке try, и если при ее выполнении возникает ошибка, срабатывает переход в блок except.

Прежде всего, в блоке try устанавливаем соединение с базой данных, указывая имя пользователя, пароль, IP-адрес хоста (в данном случае локального хоста) и имя БД . Затем получаем объект cursor, связанный с этим соединением . Объект cursor предоставляет средства для выполнения инструкции, а также интерфейс для получения результатов.

Определяем строки для таблицы emps как список кортежей . Затем объявляем инструкцию SQL для вставки этих строк в таблицу . В инструкции INSERT указываем поля, которые должны быть заполнены данными, а также заполнители %s, которые сопоставляют эти поля с элементами каждого кортежа. Выполняем инструкцию в цикле, вставляя строки по одной с помощью метода cursor.execute() . Аналогичным образом вставляем строки в таблицы salary и orders. В конце блока try делаем все вставки в базу данных с помощью метода commit() .

Если какая-либо операция, связанная с базой данных, завершается сбоем, остальная часть блока try пропускается и выполняется пункт except , выводя код ошибки, сгенерированный сервером MySQL, и соответствующее сообщение об ошибке.

Понимание инструкций SQL      117

представляют собой значения полей этой строки . Значения полей выводятся строка за строкой:

9002, Jane Boorman, sales 9003, Tom Heints, sales

Можно написать инструкции SELECT, которые объединяют строки из разных таблиц. Объединение таблиц реляционной базы данных повторяет процесс объединения датафреймов pandas, который был описан в главе 3. Обычно таблицы объединяются с помощью отношений внешнего ключа, определяемого при настройке базы данных.

Предположим, что необходимо объединить таблицы emps и salary, сохраняя условие о том, что значение empno должно быть больше 9001. Мы делаем это через их общие столбцы (empno), поскольку определили это поле в таблице salary как внешний ключ, ссылающийся на empno в таблице emps. Это соединение можно реализовать с помощью еще одной модификации блока try внутри скрипта:

--фрагмент-- try:

cnx = mysql.connector.connect(user='root', password='your_pswd', host='127.0.0.1', database='sampledb')

cursor = cnx.cursor()

query = ("""SELECT e.empno, e.empname, e.job, s.salary

FROM emps e JOIN salary s ON e.empno = s.empno WHERE e.empno > %s""")

empno = 9001 cursor.execute(query, (empno,))

for (empno, empname, job, salary) in cursor: print("{}, {}, {}, {}".format(

empno, empname, job, salary))

--фрагмент--

На этот раз запрос содержит оператор SELECT, объединяющий таблицы emps и salary. В блоке SELECT перечисляются столбцы из обеих таблиц, которые необходимо включить в объединение . В блоке FROM расположены ключевое слово JOIN и названия таблиц для объединения, вместе с короткими именами e и s, которые необходимы, чтобы различать столбцы с одинаковым именем в обеих таблицах . В блоке ONмы определяем условие объединения, указывая, что значения в столбцах empno обеих таблиц должны совпадать . В блоке WHERE, как и в предыдущем примере, используем заполнитель %s, чтобы установить минимальное значение empno .