|
Подключение к БД, создание и удаление таблицДля добавления возможности использования СУБД SQLite в программе на Python необходимо импортировать модуль: import sqlite3После чего станут доступны API-функции этого расширения. Подробнее о них мы будем говорить на последующих занятиях. А вначале воспользуемся вот такой простой заготовкой для создания и управления БД: import sqlite3 as sq con = sq.connect("saper.db") cur = con.cursor() cur.execute(""" """) con.close() Смотрите, в первую очередь мы должны вызвать метод connect, чтобы установить связь с определенной БД. В данном случае – это файл saper.db, который должен располагаться в том же каталоге, что и файл программы на Питоне. В качестве расширений этого файла, обычно, используют следующие: *.db, *.db3, *.sqlite и *.sqlite3 Я взял первый вариант, т.к. он короткий и понятный. При выполнении команды connect файл saper.db либо будет открыт, либо будет создан, если он не существует. В результате создается (или открывается) БД с именем saper.db. При успешном соединении с БД метод connect возвращает экземпляр объекта Connection, на который ссылается переменная con. И, далее, мы должны использовать объект Cursor для взаимодействия с БД и выполнения SQL-запросов. Например, это можно сделать с помощью метода execute, которому в качестве аргумента как раз и передается строка с SQL-запросом. Но мы пока там ничего указывать не будем. В конце программы при завершении работы с БД необходимо закрыть соединение. Это делается с помощью метода close. Вот так в двух словах происходит взаимодействие с СУБД SQLite. Запустим программу, видим, что никаких ошибок не возникло и, кроме того, была создана БД saper.db в виде отдельного файла в каталоге с исполняемым файлом Питона. Однако, соединяться с БД лучше все-таки через менеджер контекста: with sq.connect("saper.db") as con: cur = con.cursor() cur.execute(""" """) Он автоматически сохраняет данные в БД (вызывает метод commit()) даже при возникновении ошибочных ситуаций. Поэтому, в дальнейшем мы им и будем пользоваться. Конечно, после него, когда соединение с БД уже не нужно, его нужно закрыть все той же командой close(). Давайте теперь перейдем в программу DB Browser и откроем эту БД. На экране увидим вот такое окно и, как видите, у нас пока здесь нет ни одной таблицы.
Создание и удаление таблицКак мы говорили на предыдущем занятии, данные хранятся в виде таблиц. И каждая БД может содержать множество таких таблиц. Давайте создадим первую вот с такой структурой:
Чтобы знать как прописывать типы полей, приведем их полный список:
Далее, пропишем вот такой SQL-запрос: cur.execute("""CREATE TABLE users ( name TEXT, sex INTEGER, old INTEGER, score INTEGER )""") Мы здесь указываем команду CREATE TABLE (создать таблицу), затем, имя таблицы – users и, далее, в круглых скобках через запятую перечисляем поля с указанием их типов. Названия команд и типов принято писать заглавными буквами, а имена таблиц и их полей – строчными. Мы будем придерживаться этого негласного правила. Запустим программу, снова откроем БД в DB Browser и увидим созданную таблицу заданной структуры. Это будет наше первое хранилище данных. Однако, смотрите, если запустить программу еще раз, то появится ошибка, т.к. мы пытаемся создать таблицу, которая уже существует. Поэтому SQL-запрос лучше записать в таком виде: cur.execute("""CREATE TABLE IF NOT EXISTS users ( name TEXT, sex INTEGER, old INTEGER, score INTEGER )""") То есть, создавать таблицу только если она не существует. Теперь, запуская программу, никаких ошибок появляться не будет и, кроме того, мы точно будем уверены, что таблица users присутствует в нашей БД. Давайте в программе DB Browser добавим несколько записей. Для этого нужно нажать на кнопку «Добавить запись» и ввести значения: Алексей 1 22 1000
После этого обязательно нажать на кнопку «Записать изменения» и данные будут сохранены в БД. Затем, в этой же программе на вкладке «SQL», мы можем прописывать SQL-запросы и выполнять их. В качестве примера выполним очень распространенную команду SELECT для выбора записей из таблицы users. В самом простом варианте это можно сделать так: SELECT * FROM users Здесь * указывает взять все поля из таблицы users. Подробнее мы еще поговорим об этом запросе, а пока я покажу следующее. Каждая таблица SQLite содержит скрытое поле rowid, хранящее уникальный идентификатор записи. Выведем его на экран с помощью запроса: SELECT rowid, * FROM usersТеперь мы видим значения этого поля для каждой записи. Используя rowid, в частности, можно осуществлять связывание нескольких таблиц между собой для формирования сводного отчета. И в дальнейшем мы увидим как это делается. Наконец, если требуется удалить таблицу, то для этого прописывается такой SQL-запрос: cur.execute("DROP TABLE users") Как видите, все довольно просто. PRIMARY KEY, AUTOINCREMENT, NOT NULL и DEFAULTПри необходимости можно самостоятельно создать поле подобное rowid в любой таблице. Для этого, при создании ее структуры, указывается следующая строчка: user_id INTEGER PRIMARY KEY AUTOINCREMENT Здесь ограничитель PRIMARY KEY (первичный ключ) означает, что поле user_id должно содержать уникальные значения, а ограничитель AUTOINCREMENT указывает СУБД автоматически увеличивать значение user_id при добавлении новой записи. Удалим таблицу users из БД, выполним программу и увидим, что автоматически создается еще одна вспомогательная таблица sqlite_sequence, которая будет хранить служебную информацию для поля user_id. Но, в нашей работе она не нужна, мы по-прежнему пользуемся только одной таблицей users. Далее, если нам нужно указать, что поле обязательно должно содержать какие-либо данные, то ему следует добавить ограничитель NOT NULL, а для задания значения по умолчанию – ограничитель DEFAULT, например, так: cur.execute("""CREATE TABLE IF NOT EXISTS users ( name TEXT NOT NULL, sex INTEGER DEFAULT 1, old INTEGER, score INTEGER )""") Теперь, при добавлении новой записи, обязательно нужно будет указывать имя игрока, но можно не указывать его пол, если предполагается мужской: INSERT INTO users (name, old, score) VALUES('Алексей', 18, 1000) В результате получим запись: Алексей 1 18 1000 Часто ограничители NOT NULL и DEFAULT объединяют между собой и пишут так: sex INTEGER NOT NULL DEFAULT 1, Тогда у нас поле sex обязательно будет содержать значение и по умолчанию оно будет равно 1. Вот так происходит обращение к БД и создание таблиц в SQLite. На следующем занятии мы продолжим изучение команд языка SQL. Видео по теме |
