Это старая версия документа.
Автор — Грэг Валтерс (Greg Walters)
Здравствуйте, девочки и мальчики. Вот и настало время для сказочки. Усаживайтесь поудобнее и слушайте внимательно. Готовы? Ну, поехали!
Давным-давно всем миром правила Бумага. Бумаги, бумажки, бумажечки… Везде бумаги! Для бумаг даже строили особые дома, называли их «шкафы для бумаг». А были ещё и стальные такие штуки, в которых жили Особо Важные Бумаги, и они громоздились и громоздились в офисах…*В каждый шкаф помещались такие штуки, называемые «папки», в которые вкладывали бумаги в попытке организовать их по тематике. Но всё равно, со временем бумаги перемеши-вались или рассыпались от вет-хости, а то и просто от частого использования.
Чтобы научиться правильно использовать шкафы для бумаг нужно было окончить вуз, и всё равно, чтобы найти бумаги, хранящиеся в разных шкафах, нужно было время. Дела стра-дали жестоко. То были трудные дни (и ночи) в истории челове-чества.
И вот однажды с вершины высокой горы (по-моему, это вроде была Колорадо) спусти-лась прекрасная фея. Фея была в голубом и серебряном, с кра-сивыми крыльями и белоснеж-ными волосами, и была она ростом до колена. Звали её — хотите верьте, хотите нет — Эскуэль. Прикольное имя, да? Короче, Эскуэль сказала, что может справиться со всеми Бумагами и шкафами для бумаг, и вернуть потерянное время. И всего-то надо было — поверить в компьютеры и в неё. Эту магию фея назвала «База данных». Она сказала, что «База данных» может заменить все-все шкафы для бумаг. Кто-то поверил ей, и вот их жизнь стала свободной и счастливой. Кот-то не поверил, и продолжал прозябать под горами Бумаг.
Но, разумеется, помощь феи не даётся даром. Для того, чтобы воспользоваться магией Эскуэль, нужно было изучить иностранный язык, хотя и очень простой. Тем, кто знал английский, и вовсе достаточно было научить-ся правильно складывать привычные слова. В общем, просто чуть по-другому назы-вать привычные вещи, да хорошо подумать, прежде чем попросить Эскуэль о чём-нибудь, чтобы она точно знала, что сделать.
Как-то раз к Эскуэль пришёл мальчик, которого родители по приколу назвали Пользователь. Его восхитила её красота и он произнёс: «Эскуэль, научи меня, как использовать твою магию». И Эскуэль согласилась.
«Сначала я должна посмотреть, как у тебя хранится информация. Покажи-ка мне твои Бумаги».
У маленького Пользователя и бумаг было мало. Но Эскуэль произнесла: «Пользователь, сейчас у тебя не много Бумаг, и все они уместятся в одном шкафу. Однако предвижу я будущее, когда Бумаги твои вырастут, и, сложи ты их вместе, они закроют тебя с головой. Давай используем мою магию».
И вот, работая вместе, Пользователь и Эскуэль создали «программулину с базой данных» (это такой термин из практической магии). И Пользователь, которого с тех пор звали уважительно Юзером, жил без Бумаг долго и счастливо.
Конец.
Разумеется, в этой истории есть немного вымысла. Тем не менее, использование баз данных и SQL может здорово облегчить нам жизнь. На этот раз мы изучим несколько простых запросов SQL и применим их в программе. Кто-то может возразить, что это «неправильный» и не лучший путь, но зато этот путь очень практичен. Итак, приступим.
Базы данных — это как шкафы для бумаг в рассказанной истории. Таблицы можно представить как папки. Записи в таблицах — это листы бумаг. Ну, а каждая запись на бумаге называется полем. Пока всё гладко, не так ли? Запросы SQL (обычно это слово произносят «Эскуэль», хотя англичане произносят что-то вроде «Си-куилл») используются для манипулирования данными. SQL — это сокращение от «Structured Query Language» («Язык Структурированных Запросов»), и изначально он был задуман, чтобы упростить использование баз данных. Правда, на прак-тике запросы могут быть очень сложными, но мы для нашего примера будем придерживаться простых запросов.
Собираясь что-нибудь создать, надо сначала придумать, как это создать. Придумайте, как долна выглядеть карточка рецепта, и давайте создадим базу данных рецептов. Там, где я живу, рецепты выглядят по-разному: карточки 3 на 5, бумажки 8 на 10, надписи на платках, листовки из магази-нов, и даже ещё более стран-ные штуки. Хранят их в книгах, коробках, скоросшивателях, и в других странных местах. Но в одном они все похожи — это формат. Почти всегда вверху вы найдёте название рецепта, затем наверняка — количество порций и откуда этот рецепт взят. Дальше идёт список ингредиентов, ну, и в конце — инструкции, в каком порядке класть ингредиенты, сколько готовить, и всё такое. Вот этот общий формат и послужит основой для нашего проекта базы данных. Всю работу мы разобьём на два этапа. Сначала мы создадим базу данных, а потом — приложение (программу), которое будет читать и обновлять эту базу данных.
К примеру, положим, у нас есть такой рецепт, как на иллюстрации справа. Обратите внимание на формат, мы его уже обсудили. Создавая базу данных, мы, конечно, можем отвести отдельную запись под каждое поле в рецепте. Однако, работать с такой неуклюжей базой данных будет очень неудобно. Мы же возьмём описанную карточку рецепта в качестве шаблона. В одной таблице мы будем хранить заголовок рецепта и прочую общую информацию о рецепте. В другую таблицу мы поместим информацию о составе, и в третью - инструкции по приготовлению.
Сначала убедимся, что у нас установлены SQLite и APSW. SQLite — это неболь-шая система управления базами данных, которая не требует выделенного сервера — идеальный выбор для нашего простого приложения. То, чему вы научитесь на этом приме-ре, потом можно будет использовать и в более крупных системах управле-ния базами данных, таких как MySQL и прочих. Другая хорошая черта SQLite — это то, что в ней мало типов данных — это Text (Текст), Numeric (Число), Blob (Неформатированные данные), и Integer Primary Key (Числовой главный ключ). Как вы уже знаете, типом Text может быть всё, что угодно. В нашем случае ингредиенты, инструкции и название рецепта будут текстом, даже если в них будут цифры. Тип Numeric хранит числа; они могут быть целыми, числами с плавающей запятой или рациональными. Blob хранит бинарные данные, например, картинки или что-нибудь другое. Особый случай — тип Integer Primary Key; система SQLite автоматически помещает в него целое значение и обеспечивает его уникальность, в дальнейшем это будет важно.
APSW — это аббревиатура от «Another Python SQLite Wrapper» (Ещё одна реализация работы с SQLite в Python). APSW разработан для облегчения работы с SQLite в языке Python. Теперь давайте быстро пробежимся по языку запросов SQL.
Чтобы получить запись из базы данных, используется запрос типа SELECT. Его формат такой:
SELECT [Что] FROM [Из каких таблиц] WHERE [Условия] Прим.перев.: переводя с английского, «ВЫБРАТЬ [Что] ИЗ [Откуда] ГДЕ [Условия]».
Итак, чтобы получить все поля всех записей из таблицы рецептов Recipes, нужен запрос:
SELECT * FROM Recipes
Если нужно выбрать только одну запись по уникальному ключу, нужно знать название поля ключа (в примере — pkID) и включить в запрос условие WHERE:
SELECT * FROM Recipes WHERE pkID = 2
Пока несложно, да? В общем-то, сильно упрощённый английский язык. Теперь, скажем, нам нужно получить по всем рецептам их названия и количество блюд. Как? Очень просто. Нужно включить названия нужных полей в запрос SELECT:
SELECT name, servings FROM Recipes
Чтобы добавить запись, нужно использовать запрос типа INSERT INTO. Его синтаксис:
INSERT INTO [Имя таблицы] (перечень полей) VALUES (вставляемые значения) (Прим. перев.: ВСТАВИТЬ В [Имя таблицы] (перечень полей) ЗНАЧЕНИЯ (вставляемые значения))
Так, чтобы вставить рецепт в таблицу рецептов, нужно использовать команду:
INSERT INTO Recipes (name,servings,source)
VALUES ("Лепёшки",4,"Грег")
(Прим.перев.:
перевод полей таблиы:
name — название,
servings — порций,
source — источник)
Чтобы удалить запись, используем:
DELETE FROM Recipes WHERE pkID = 10
Для обновления записей можно использовать запрос типа UPDATE, но это мы оставим на потом.
И снова о SELECT.
У нас в базе данных три таблицы (Recipes, Instructions и Ingredients), которые связаны друг с другом через поля recipeID, которые указывают на поле pkID таблицы рецептов Recipes. Положим, нам нужны все инструкции к определённому рецепту. Можно сделать так:
SELECT Recipes.name, Recipes.servings, Recipes.source, Instructions.Instructions FROM Recipes LEFT JOIN instructions ON (Recipes.pkid = Instructions.recipeid) WHERE Recipes.pkid = 1 (Прим.перев.: ВЫБРАТЬ Recipes.name, Recipes.servings, Recipes.source, Instructions. Instructions ИЗ Recipes ЛЕВОЕ СОЕДИНЕНИЕ instructions ПО (Recipes.pkid = Instructions. recipeid) ГДЕ Recipes.pkid = 1)
На самом деле, так много печатать необязательно. Можно использовать так называемые псевдонимы. Запрос, указанный выше, можно изменить так:
SELECT r.name, r.servings, r.source, i.Instructions FROM Recipes r LEFT JOIN instructions i ON (r.pkid = i.recipeid) WHERE r.pkid = 1
Так короче и легче читать. Теперь давайте напишем программку, которая создаст базу данных, создаст в ней таблицы, и заполнит их данными для примера, чтобы можно было начать работу. Конечно, МОЖНО включить эти функции в основную програм-му, но мы вынесем их в отдельную программу. Её нужно будет запустить всего один раз; попытка повторного запуска приведёт к ошибке на этапе создания таблиц. Опять же, можно было бы обраба-тывать эту ошибку конструк-цией try…catch, но это уж как-нибудь в другой раз.
Начнём с импорта APSW.
import apsw
Дальше мы должны создать подключение к базе данных. Она будет располагаться в одной папке с нашим приложе-нием. Когда мы создаём соединение, SQLite проверяет существование базы данных. В этом случае она открывается, иначе просто создаётся пустая база данных. Получив соединение, нужно создать то, что называ-ется курсор (cursor) — это механизм работы с базой данных. Итак, запомним: нужны и соединение, и курсор. Создать их можно так:
# Создаём/открываем базу данных
connection=apsw.Connection("cookbook1.db3")
cursor=connection.cursor()
Ну вот, у нас есть и соедине-ние, и курсор. Теперь создадим таблицы. У нас будет три табли-цы: одна для общей информа-ции о рецепте, другая для списка ингредиентов, и третья — для инструкций. Разве нельзя объединить это всё в одну таблицу? Ну, конечно, можно. Только тогда со временем база данных разрастётся неимоверно из-за целой кучи повторяющих-ся данных.
Структуру таблиц можно представить так, как на иллюстрации справа. Каждая колонка — это отдельная таблица.
В каждой таблице будет поле pkID. Это — уникальный в рамках данной таблицы ключ. Очень важно, чтобы в таблице никогда не появлялось двух совершенно одинаковых записей. Ключ — это целое число, назначаемое системой автоматически. Можно ли без него? Да, но возникает риск создания двух одинаковых записей. Мы же кроме того будем использовать это поле в таблице Recipes в качестве ссылки, на которую будут ссылаться таблицы Ingredients и Instructions, чтобы определить, к какому рецепту относятся ингредиенты или инструкции.
Для начала поместим в базу данных информацию; в таблице рецептов заполним название, источник и количество порций. Поле pkID будет назначено автоматически. Предположим, это будет первая наша запись в таблице, так что система назначит полю pkID значение 1. Это значение мы и используем как ссылку в других таблицах. Таблица инструкций очень простая: в ней хранится длинная текстовая строка для каждого рецепта, плюс своё поле pkID и ссылка на ключ в таблице рецептов. Таблица ингредиентов немного сложнее в том смысле, что для каждого ингредиента будет своё поле, включающее кроме названия ингредиента свой ключ pkID и ссылку на таблицу рецептов.
Чтобы создать таблицу рецептов, определим переменную sql и назначим ей команду на создание таблицы:
sql = 'CREATE TABLE Recipes (pkiD INTEGER PRIMARY KEY, name TEXT, servings TEXT, source TEXT)'
Затем скомандуем ASPW выполнить эту команду:
cursor.execute(sql)
Теперь создадим другие таблицы:
sql = 'CREATE TABLE Instructions (pkID INTEGER PRIMARY KEY, instructions TEXT, recipeID NUMERIC)' cursor.execute(sql) sql = 'CREATE TABLE Ingredients (pkID INTEGER PRIMARY KEY, ingredients TEXT, recipeID NUMERIC)' cursor.execute(sql)
Теперь, когда таблицы созданы, используем запрос INSERT INTO, чтобы заполнить каждое поле нужной таблицы.
Так как поле pkID будет назначено автоматически, мы не включаем его в список назначаемых полей. А раз уж мы знаем названия полей, их можно указывать в любом порядке, не обязательно в том порядке, как они создавались. Если мы не перепутаем назва-ния полей, всё будет работать отлично. Итак, команда вставки данных в таблицу рецептов превращается в
INSERT INTO Recipes (name, servings, source)
VALUES ("Рис по-испански",4,"Грег Уолтерс")
Теперь нам надо узнать, какой же именно pkID только что был назначен новой записи в таблице рецептов. Сделать это очень просто запросом
SELECT last_insert_rowid()
Однако в таком виде эта команда не выдаст результата, которым можно воспользовать-ся. Нужно использовать сразу серию таких команд. Вот так:
sql = "SELECT last_insert_rowid()" cursor.execute(sql) for x in cursor.execute(sql): lastid = x[0]
Почему так? Потому что когда мы получаем данные от ASPW, он возвращает кортеж (tuple). Об этом мы пока не говорили. Если коротко, то кортеж — это как список, который нельзя изменить. Многие программисты почти не используют кортежи, другие используют очень часто, это дело вкуса. В последней строке мы извлекаем из кортежа первое помещённое туда значение. Цикл for используется, чтобы заполнить кортеж x возвращаемыми значениями. Пока понятно? Ладно, продолжаем…
Далее, создадим запрос на вставку данных об инструкциях:
sql = 'INSERT INTO Instructions (recipeID, instructions) VALUES( %s,"Поджарить гамбургер. Смешать остальные ингредиенты и залить. Довести до кипения. Помешать. Готовить на медленном огне. Накрыть и готовить 20 минут или до впитывания жидкости.")' % lastid cursor.execute(sql)
Обратите внимание, мы использовали подстановку переменной (%s), чтобы поместить pkID рецепта в текст запроса. Наконец, нам надо поместить каждый из ингредиентов в соответству-ющую таблицу. Я покажу на примере только одной записи:
sql = 'INSERT INTO Ingredients (recipeID,ingredients)
VALUES ( %s,"1 чашка обработанного паром риса
(не варёного)")' % lastid
cursor.execute(sql)
Пока всё было достаточно просто. В следующий раз сделаем кое-что посложнее.
Если вам нужен полный исходный код этого примера, я выложил его на своём веб-сайте. Скачать его вы можете, посетив www.thedesignatedgeek.com.
В следующий раз мы исполь-зуем всё, что изучили в этом цикле статей, чтобы создать полноценное приложение с меню из нашей программы для рецептов. Оно сможет выводить список всех рецептов, просмат-ривать один выбранный рецепт, искать рецепт, добавлять и удалять рецепты.
Я бы пока посоветовал вам почитать на досуге о языке SQL. В дальнейшем вам это пригодится.
