Опубликован: 14.06.2015 | Уровень: для всех | Доступ: платный
Лекция 14:

Использование баз данных и языка структурированных запросов (SQL)

26.4. Создание таблицы базы данных

Базы данных требуют более четкой структуры, чем списки или словари Питона1SQLite на самом деле допускает некоторую гибкость при задании типов данных в столбцах, но в этой главе мы ограничимся более строгим подходом, который применим и к другим системам баз данных, например, к MySQL.. При создании таблицы базы данных нужно заранее указать название каждого столбца и тип данных, которые мы собираемся хранить в столбце. Когда для программного обеспечения известны типы данных в столбцах, оно может выбрать наиболее эффективный способ их хранения и поиска.

Можно посмотреть список различных типов данных, поддерживаемых SQLite, по адресу http://www.sqlite.org/datatypes.html.

Определение структуры данных на начальном шаге может показаться неудобным, но зато мы получаем в результате быстрый доступ к данным даже в том случае, когда их объем очень большой.

Код для создания файла базы данных и ее таблицы с именем Tracks, которая содержит два столбца, выглядит следующим образом:

import sqlite3
conn = sqlite3.connect('music.db')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Tracks ')
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')
conn.close()
  

Операция connect устанавливает "соединение" с базой данных, которая хранится в файле music.db в текущем каталоге. Если файл не существует, то он будет создан. Слово "соединение" используется потому, что достаточно часто база хранится на сетевом сервере, отличном от того компьютера, на котором работает приложение. Но в нашем простом случае база данных представляет собой локальный файл в том же каталоге, в котором мы выполняем программу Питона. Переменная cur играет роль файлового дескриптора, мы используем ее для операций с содержимым базы данных. Вызов метода cursor() концептуально близок к вызову open(), когда мы работаем с текстовыми файлами.


Как только мы получили дескриптор cur с помощью метода cursor, можно выполнять команды над содержимым базы данных при помощи метода execute(). Эти команды представляют собой специальный язык, который был стандартизован благодаря усилиям многих разработчиков различных систем баз данных – все системы теперь используют единый язык. Он называется "Язык структирированных запросов" (Structured Query Language) или сокращенно SQL.

http://en.wikipedia.org/wiki/SQL

В нашем примере мы исполняем две SQL-команды в базе данных. По общему соглашению, принято записывать ключевые слова языка SQL прописными буквами, а части команды, которые мы добавляем к ключевым словам (например, имена таблицы и ее столбцов), – строчными буквами. Первая SQL-команда удаляет таблицу с именем Tracks из базы данных, если такая существует. Этот фрагмент кода позволяет многократно выполнять одну и ту же программу, которая каждый раз заново создает таблицу Tracks, избегая ошибок. Отметим, что команда DROP TABLE удаляет из базы данных таблицу со всем ее содержимым без возможности восстановления (отмена операции – "Undo" – не предусмотрена).

cur.execute('DROP TABLE IF EXISTS Tracks ')
  

Вторая команда создает таблицу с именем Tracks, которая содержит два столбца: в стобец с именем title помещается текстовая информация, в столбец с именем plays – целые числа.

cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')

Теперь, когда мы создали таблицу с именем Tracks, мы можем поместить данные в эту таблицу с помощью операции INSERT языка SQL. Как и в предыдущем случае, мы начинаем с установки соединения с базой данных и получения курсора (аналога файлового дескриптора). После этого, используя курсор, мы можем выполнять SQL-команды.

Команда SQL INSERT указывает, какую именно таблицу мы используем, затем задает новую строку таблицы, перечисляя поля, которые мы хотим в нее включить (title, plays), и после ключевого слова VALUES – значения, которые мы хотим поместить в новую строку таблицы. Мы можем задать значения с помощью вопросительных знаков (?, ?), чтобы указать, что реальные значения передаются в виде кортежа ( 'My Way', 15 ) в качестве второго параметра метода execute():

import sqlite3
conn = sqlite3.connect('music.db')
cur = conn.cursor()
cur.execute('INSERT INTO Tracks (title, plays) VALUES ( ?, ? )',
( 'Thunderstruck', 20 ) )
cur.execute('INSERT INTO Tracks (title, plays) VALUES ( ?, ? )',
( 'My Way', 15 ) )
conn.commit()
print 'Tracks:'
cur.execute('SELECT title, plays FROM Tracks')
for row in cur :
print row
cur.execute('DELETE FROM Tracks WHERE plays < 100')
conn.commit()
cur.close()
  

Сначала c помощью команды INSERT мы вставляем две строки в нашу таблицу, затем мы используем метод commit() для форсированной записи данных в файл.


Затем мы используем команду SELECT, чтобы получить из таблицы две строки, которые только что были добавлены в нее. В команде SELECT мы указываем, какие столбцы нам нужны (title, plays), а также имя таблицы, из которой мы извлекаем информацию. После выполнения операции SELECT курсор (т.е. переменная cur) позволяет нам перебирать выбранные данные в цикле for. Для эффективности курсор в действительности не читает все данные из базы сразу при выполнении операции SELECT, вместо этого каждая очередная порция данных считывается по отдельности, когда мы перебираем выбранные строки в цикле for.

На выходе программы получаем:

Tracks:
(u'Thunderstruck', 20)
(u'My Way', 15)
  

В цикле for найдены две строки, каждая из которых представляет собой кортеж в смысле Питона, его первым элементом является заголовок (музыкального произведения), вторым – число его исполнений. Пусть вас не смущает префикс "u", с которого начинаются заголовки, – это просто указание, что строки представлены в кодировке Unicode, которая дает возможность использовать любые символы, а не только латинские буквы. В самом конце программы мы выполняем SQL-команду DELETE, удаляя только что созданные строки, что позволяет нам исполнять программу снова и снова.

Команда DELETE демонстрирует использование условия WHERE, задающего критерий выбора строк, к которым применяется команда. В данном примере получилось так, что критерий применим ко всем строкам, в результате таблица становится пустой. Благодаря этому программу можно запускать много раз.

После выполнения команды DELETE мы также вызываем метод commit() для форсированного удаления данных из файла базы данных.

Алексей Виноградов
Алексей Виноградов

Видеокурс выложен на сайте Altube.ru вместо Youtube и плеер Altube не поддерживает субтитры. Прошу решить вопрос о предоставлении русских субтитров в этом англоязычном видеокурсе.

Петр Олейников
Петр Олейников

Данные файлы неоходимы не только для самостоятельных работ, но и для тестов. А по ссылкам в лекциях они не доступны, выдает ошибку 404.

Александр Душечкин
Александр Душечкин
Россия, Ижевск, ИжГТУ, 2002
Дмитрий Голиков
Дмитрий Голиков
Россия