Программирование на Python 3. Руководство издательство СимволПлюс
 Скачать 3.74 Mb.
|
|
516 Глава 11. Программирование приложений баз данных "name TEXT UNIQUE NOT NULL)") cursor.execute("CREATE TABLE dvds (" "id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, " "title TEXT NOT NULL, " "year INTEGER NOT NULL, " "duration INTEGER NOT NULL, " "director_id INTEGER NOT NULL, " "FOREIGN KEY (director_id) REFERENCES directors)") db.commit() return db Функция sqlite3.connect() возвращает объект базы данных, открыва ет указанный файл базы данных или создает пустой файл базы дан ных, если файл не существует. Вследствие этого перед вызовом функ ции sqlite3.connect() необходимо проверить существование файла, чтобы знать, будет ли создаваться новая база данных, потому что в этом случае необходимо создать таблицы, которые используются программой. Все запросы к базе данных выполняются с помощью объ екта курсора, который можно получить вызовом метода cursor() объ екта базы данных. Обратите внимание, что в обеих таблицах присутствует поле ID с огра ничением AUTOINCREMENT – это означает, что SQLite автоматически будет записывать в поля ID уникальные числа, поэтому при добавлении но вых записей можно переложить заботу о заполнении этих полей на SQLite. База данных SQLite поддерживает ограниченный диапазон типов дан ных – по сути, только логические значения, числа и строки, но этот диапазон может быть расширен с помощью «адаптеров», либо предо пределенных – для таких типов данных, как даты и время, либо созда ваемых самостоятельно, которые могут использоваться для представ ления любых желаемых типов данных. В нашей программе этого не требуется, но в случае необходимости вы можете обратиться к описа нию модуля sqlite3, где описываются все подробности. Синтаксис оп ределения внешнего ключа, который мы использовали, возможно, не совпадает с синтаксисом определения внешнего ключа в других базах данных, но в любом случае – это просто описание наших намерений, поскольку база данных SQLite, в отличие от многих других, соблюдает ссылочную целостность. Еще одна особенность sqlite3, которая дейст вует по умолчанию, заключается в неявной поддержке транзакций, поэтому в модуле отсутствует явный метод «запуска транзакции». def add_dvd(db): title = Console.get_string("Title", "title") if not title: return director = Console.get_string("Director", "director") if not director: return Базы данных SQL 517 year = Console.get_integer("Year", "year", minimum=1896, maximum=datetime.date.today().year) duration = Console.get_integer("Duration (minutes)", "minutes", minimum=0, maximum=60*48) director_id = get_and_set_director(db, director) cursor = db.cursor() cursor.execute("INSERT INTO dvds " "(title, year, duration, director_id) " "VALUES (?, ?, ?, ?)", (title, year, duration, director_id)) db.commit() Эта функция начинается так же, как эквивалентная ей функция из программы dvds dbm.py, отличия начинаются после сбора всей необхо димой информации. Имя режиссера, введенное пользователем, может присутствовать, а может отсутствовать в таблице directors, поэтому мы предусмотрели функцию get_and_set_director(), которая добавляет имя режиссера, если оно еще отсутствует в базе данных, и в любом слу чае возвращает его идентификатор для вставки в таблицу dvds. Собрав все данные, функция выполняет инструкцию INSERT языка SQL. Здесь не требуется указывать идентификатор записи, потому что база дан ных SQLite подставит его автоматически. Знаки вопроса в тексте запроса используются в качестве символов за полнителей. Каждый знак ? замещается соответствующим значением из последовательности, следующей за строкой с инструкцией SQL. Имеется также возможность использовать именованные символы за полнители, она будет продемонстрирована в функции редактирования записи. Несмотря на то, что существует возможность отказаться от ис пользования символов заполнителей простым форматированием стро ки SQL, внедряя в нее необходимые данные, тем не менее мы рекомен дуем всегда использовать символы заполнители, а бремя корректного кодирования и экранирования служебных символов в элементах дан ных перекладывать на модуль базы данных. Еще одно преимущество использования символов заполнителей состоит в том, что они повы шают уровень безопасности, предотвращая возможность инъекции в запрос злонамеренного кода SQL. def get_and_set_director(db, director): director_id = get_director_id(db, director) if director_id is not None: return director_id cursor = db.cursor() cursor.execute("INSERT INTO directors (name) VALUES (?)", (director,)) db.commit() return get_director_id(db, director) Эта функция возвращает идентификатор указанного имени режиссера и в случае необходимости добавляет новую запись в таблицу directors. 518 Глава 11. Программирование приложений баз данных Если была добавлена новая запись, идентификатор режиссера извле кается с помощью повторного вызова функции get_director_id(). def get_director_id(db, director): cursor = db.cursor() cursor.execute("SELECT id FROM directors WHERE name=?", (director,)) fields = cursor.fetchone() return fields[0] if fields is not None else None Функция get_director_dvd() возвращает идентификатор для заданного имени режиссера или None, если такого имени в базе данных не сущест вует. Здесь используется метод fetchone(), потому что для данного име ни может существовать либо одна запись, либо ни одной. (Дубликатов записей не может существовать, потому что поле name в таблице direc tors имеет ограничение UNIQUE, и в любом случае, прежде чем добавить новое имя режиссера в таблицу, мы проверяем его существование.) Методы извлечения записей всегда возвращают последовательность полей (или None, если все записи были извлечены), даже если, как в данном случае, выполняется попытка извлечь единственное поле. def edit_dvd(db): title, identity = find_dvd(db, "edit") if title is None: return title = Console.get_string("Title", "title", title) if not title: return cursor = db.cursor() cursor.execute("SELECT dvds.year, dvds.duration, directors.name " "FROM dvds, directors " "WHERE dvds.director_id = directors.id AND " "dvds.id=:id", dict(id=identity)) year, duration, director = cursor.fetchone() director = Console.get_string("Director", "director", director) if not director: return year = Console.get_integer("Year", "year", year, 1896, datetime.date.today().year) duration = Console.get_integer("Duration (minutes)", "minutes", duration, minimum=0, maximum=60*48) director_id = get_and_set_director(db, director) cursor.execute("UPDATE dvds SET title=:title, year=:year, " "duration=:duration, director_id=:director_id " "WHERE id=:id", locals()) db.commit() Чтобы отредактировать запись с информацией о диске, ее сначала не обходимо отыскать. Если запись будет найдена, пользователю предос тавляется возможность изменить название фильма. Затем извлекают ся значения остальных полей, которые будут использоваться в качест Базы данных SQL 519 ве значений по умолчанию, чтобы минимизировать ввод с клавиату ры, так как пользователю достаточно будет просто нажать клавишу Enter, чтобы принять значение по умолчанию. В этой функции исполь зуются именованные символы заполнители (в форме :name), и поэтому значения для них должны поставляться в виде отображения. Для ин струкции SELECT был использован вновь созданный словарь, а для ин струкции UPDATE – словарь, полученный вызовом функции locals(). В обоих случаях можно было бы создавать новый словарь, и тогда для инструкции UPDATE вместо вызова функции locals() можно было бы указать словарь dict(title=title, year=year, duration=duration, direc tor_id=director_id, id=identity) Как только у нас будут значения всех полей и пользователь внесет все необходимые изменения, из базы данных извлекается идентификатор режиссера (при этом в случае необходимости вставляется новая за пись) и затем выполняется запись обновленных данных в базу. Мы предприняли упрощенный подход, выполняя обновление сразу всех полей записи, вместо того чтобы выявлять и обновлять только те, ко торые действительно изменились. При использовании базы данных DBM название диска использовалось в качестве ключа, поэтому при изменении названия создавался новый элемент ключ значение, а прежний элемент удалялся. В данном же случае запись имеет уникальный идентификатор (поле id), который определяется в момент ее добавления, поэтому можно без всяких огра ничений изменять значение любого другого поля без необходимости выполнять какие либо дополнительные действия. def find_dvd(db, message): message = "(Start of) title to " + message cursor = db.cursor() while True: start = Console.get_string(message, "title") if not start: return (None, None) cursor.execute("SELECT title, id FROM dvds " "WHERE title LIKE ? ORDER BY title", (start + "%")) records = cursor.fetchall() if len(records) == 0: print("There are no dvds starting with", start) continue elif len(records) == 1: return records[0] elif len(records) > DISPLAY_LIMIT: print("Too many dvds ({0}) start with {1}; try entering " "more of the title".format(len(records), start)) continue else: for i, record in enumerate(records): 520 Глава 11. Программирование приложений баз данных print("{0}: {1}".format(i + 1, record[0])) which = Console.get_integer("Number (or 0 to cancel)", "number", minimum=1, maximum=len(records)) return records[which 1] if which != 0 else (None, None) Эта функция играет ту же роль, что и функция find_dvd() из програм мы dvds dbm.py, и возвращает кортеж из двух элементов (название, идентификатор диска) или (None, None) в зависимости от того, была ли найдена требуемая запись. Вместо того чтобы выполнять итерации по всем данным, здесь используется оператор шаблонного символа SQL (%), поэтому из базы данных извлекаются только необходимые записи. А поскольку ожидается, что число записей будет невелико, выполня ется извлечение сразу всех записей в последовательность последова тельностей. Если будет найдено более одной записи, соответствующей условию поиска, или не настолько много, чтобы все они одновременно не поместились на экране, функция выводит их, предваряя каждую запись порядковым номером, чтобы пользователь смог сделать выбор вводом числа, как это делалось в программе dvds dbm.py. def list_dvds(db): cursor = db.cursor() sql = ("SELECT dvds.title, dvds.year, dvds.duration, " "directors.name FROM dvds, directors " "WHERE dvds.director_id = directors.id") start = None if dvd_count(db) > DISPLAY_LIMIT: start = Console.get_string("List those starting with " "[Enter=all]", "start") sql += " AND dvds.title LIKE ?" sql += " ORDER BY dvds.title" print() if start is None: cursor.execute(sql) else: cursor.execute(sql, (start + "%",)) for record in cursor: print("{0[0]} ({0[1]}) {0[2]} minutes, by {0[3]}".format( record)) Чтобы получить сведения о каждом диске, создается запрос SELECT, вы полняющий объединение двух таблиц. Если число записей в базе дан ных (возвращается функцией dvd_count()) оказывается больше, чем может поместиться на экране, в предложение WHERE добавляется второй элемент, вводящий дополнительное ограничение. Затем запрос выпол няется и производится обход всех записей в результирующем наборе данных. Каждая запись представляет собой последовательность, в ко торой порядок следования полей определяется запросом SELECT. def dvd_count(db): cursor = db.cursor() В заключение 521 cursor.execute("SELECT COUNT(*) FROM dvds") return cursor.fetchone()[0] Эти строки программного кода были оформлены в виде отдельной функции, потому что они требуются в нескольких функциях. Мы опустили программный код функции list_directors(), так как по своей структуре она очень похожа на функцию list_dvds(), только го раздо проще, потому что она выводит список записей, состоящих из единственного поля (name). def remove_dvd(db): title, identity = find_dvd(db, "remove") if title is None: return ans = Console.get_bool("Remove {0}?".format(title), "no") if ans: cursor = db.cursor() cursor.execute("DELETE FROM dvds WHERE id=?", (identity,)) db.commit() Эта функция вызывается, когда пользователь выбирает операцию уда ления записи, и она очень похожа на эквивалентную функцию в про грамме dvds dbm.py. На этом мы заканчиваем обзор программы dvds sql.py, и теперь мы знаем, как создавать таблицы в базе данных, как выбирать записи, как выполнять итерации по выбранным записям, а также как встав лять, изменять и удалять записи. С помощью метода execute() можно выполнять любые инструкции SQL, какие только поддерживаются ис пользуемой базой данных. База данных SQLite обладает гораздо более широкими возможностя ми, чем было использовано здесь, включая режим автоматического подтверждения транзакций (и другие операции управления транзак циями), и возможность создавать функции, которые могут выпол няться внутри запросов SQL. Имеется также возможность создавать фабричные функции, управляющие представлением возвращаемых записей (например, в виде словарей или собственных типов данных, вместо последовательностей полей). Дополнительно имеется возмож ность создавать базы данных SQLite в памяти, для чего достаточно пе редать строку ":memory" в качестве имени файла. В заключение В главе 7 были продемонстрированы различные способы сохранения на диск и загрузки данных с диска, и в этой главе было показано, как можно взаимодействовать с типами данных, сохраняющих информа цию на диске, а не в памяти. 522 Глава 11. Программирование приложений баз данных В случае использования файлов DBM очень удобным в использовании оказывается модуль shelve, так как он позволяет хранить элементы данных в виде строка объект. В случае необходимости иметь более полный контроль имеется возможность прямого взаимодействия с ис пользуемыми базами данных DBM. Одна замечательная особенность баз данных DBM вообще и модуля shelve в частности состоит в том, что они используют API словаря, обеспечивая простоту получения, добав ления, редактирования и удаления элементов, и позволяют легко пе ревести программу, применяющую словари, на использование DBM. Одно маленькое неудобство применения DBM заключается в том, что в случае реляционных данных каждая таблица элементов ключ значе ние должна храниться в отдельном файле DBM, тогда как база данных SQLite хранит все данные в одном файле. База данных SQLite прекрасно подходит для разработки прототипов программ, которые будут работать с базами данных SQL, и во многих случаях она может использоваться как основная база данных, особен но благодаря тому, что она включается в состав дистрибутива Python. В этой главе было продемонстрировано, как получать объект базы дан ных с помощью функции connect() и как выполнять запросы SQL (та кие как CREATE TABLE, SELECT, INSERT, UPDATE и DELETE) с помощью метода execute() объекта курсора. Язык Python предлагает широкий диапазон средств хранения данных на диске или в памяти, начиная от двоичных файлов, файлов XML и за консервированных объектов и заканчивая базами данных DBM и SQL, что позволяет выбирать нужное средство в зависимости от ситуации. Упражнение Напишите интерактивную консольную программу обслуживания спи ска закладок. Для каждой закладки должны храниться два элемента данных: адрес URL и имя. Ниже приводится пример сеанса работы с программой: Bookmarks (bookmarks.dbm) (1) Programming in Python 3........ http://www.qtrac.eu/py3book.html (2) PyQt........................... http://www.riverbankcomputing.com (3) Python......................... http://www.python.org (4) Qtrac Ltd...................... http://www.qtrac.eu (5) Scientific Tools for Python.... http://www.scipy.org (A)dd (E)dit (L)ist (R)emove (Q)uit [l]: e Number of bookmark to edit: 2 URL [http://www.riverbankcomputing.com]: Name [PyQt]: PyQt (Python bindings for GUI library) Программа должна давать пользователю возможность добавлять, ре дактировать, выводить список и удалять закладки. Чтобы обеспечить максимальную простоту выбора закладки при выполнении операций Упражнение 523 редактирования и удаления, закладки должны выводиться в виде спи ска с порядковыми номерами и пользователю должна предлагаться возможность ввести номер закладки для редактирования или удале ния. Данные должны сохраняться в файле DBM с применением моду ля shelve, где имена должны играть роль ключей, а адреса URL – зна чений. По своей структуре программа очень похожа на программу dvds dbm.py , за исключением функции find_bookmark(), которая полу чится намного проще, чем функция find_dvd(), так как она будет полу чать от пользователя только порядковый номер закладки и выполнять поиск имени по этому номеру. В качестве дополнительного удобства, если пользователь явно не ука зывает протокол, добавляемые или редактируемые адреса URL следу ет предварять строкой http://. Вся программа может уместиться менее чем в 100 строк (предполага ется, что будут использоваться функции Console.get_string() и подоб ные ей из модуля Console). Решение приводится в файле bookmarks.py. 12 Регулярные выражения Регулярное выражение – это компактная форма записи представления о коллекции строк. Огромная мощь регулярных выражений обуслов лена тем, что единственное регулярное выражение может представ лять неограниченное число строк, отвечающих требованиям регуляр ного выражения. Регулярные выражения определяются с помощью мини языка, который совершенно не похож на язык Python, но в со став Python входит модуль re, с помощью которого можно создавать и использовать регулярные выражения. 1 Регулярные выражения используются для достижения следующих че тырех основных целей: |