SQL – это язык баз данных, если говорить простыми словами. И если вы хотите научиться грамотно анализировать большие объемы информации с помощью Python, то без умения пользоваться этим инструментом никуда. Сегодня мы поговорим о библиотеке SQLite3 – встроенной библиотеке Python, позволяющей выполнять множество операций с базами данных – загружать библиотеку, создавать базы данных и соединяться с ними, создавать таблицы баз данных, добавлять данные, получать необходимые сведения, удалять информацию.
И так далее. Преимущество SQLite3 в том, что он входит в комплект Python. Следовательно, ничего больше устанавливать не нужно.
- Создание базы данных в SQLite на Python
- Типы данных SQLite в Python
- С чего начать изучение SQLite в Python?
- Создание БД
- Резидентная база данных
- Создание объекта cursor
- Создание таблиц
- Добавление
- SQLite и предотвращение SQL-инъекций
- Скрипты для добавления данных
- Как получить данные с SQLite в Python
- fetchone() в SQLite
- fetchmany() в SQLite
- fetchall() в SQLite
- Как удалить данные в SQLite в Python
- Как объединить таблицы в SQLite в Python
Создание базы данных в SQLite на Python
Сегодня вы научитесь создавать базы данных SQL на Python, настраивать отношения между разными элементами. Также мы детально разберемся в том, какие типы данных SQLite бывают (к слову, их меньше, чем в классическом SQL), как создавать таблицы баз данных, как правильно получать информацию из базы данных, объединять несколько таблиц.
Отдельно рассмотрим вопросы автоматизации баз данных: создание скриптов для обработки информации. Присаживайтесь поудобнее. Если зима, укройтесь мягким пледом. Если лето – включайте вентилятор и приступайте к чтению.
Типы данных SQLite в Python
К сожалению, другие реализации SQL содержат больше типов данных. Это имеет как плюсы, так и минусы. С одной стороны, новичку значительно проще разобраться в том, как организуется работа с SQL. С другой же стороны, профессионалам будет очень тесно. Тем не менее, SQLite – это прекрасная библиотека для того, чтобы стартовать в анализе данных. А дальше можно использовать уже более продвинутые инструменты, в том числе и сделанные сторонними разработчиками.
Python поддерживает следующие типы данных SQL:
- NULL — значение NULL
- INTEGER — целое число
- REAL — число с плавающей точкой
- TEXT — текст
- BLOB — бинарное представление крупных объектов, хранящееся в точности с тем, как его ввели
С чего начать изучение SQLite в Python?
Перед тем, как использовать любую библиотеку Python, независимо от того, она встроенная или сторонняя, необходимо ее импортировать. Эта операция выполняется с помощью созвучного ключевого слова.
import sqlite3
После этого можно приступать к созданию базы данных. Чтобы выполнить эту задачу, необходимо воспользоваться одним из нескольких способов.
Создание БД
Python располагает специальным объектом, с помощью которого осуществляется подключение к базе данных. Он называется Connection. С его помощью вызываются все методы для работы с базами данных.
Чтобы создать базу данных, необходимо использовать функцию connect(). В качестве аргумента используется название файла формата .db.
conn = sqlite3.connect(‘orders.db’)
Этот фрагмент кода создает объект connection в переменной conn. Также в рабочей директории создается новый файл с названием, указанным в скобках. Если необходимо задать другую папку, это можно сделать с помощью записи аргумента функции в следующем виде.
conn = sqlite3.connect(r’ПУТЬ-К-ПАПКИ/orders.db’)
А что будет, если прописать название файла, который уже существует? Эта функция универсальная. Если оказывается, что уже по этому пути файл с таким же названием есть, то будет осуществлено подключение к базе данных.
В коде выше мы использовали перед содержимым строки символ r, чтобы дать понять интерпретатору, что мы не используем слэш в качестве функционального содержимого.
Таким образом, функция connect прекрасно подходит для создания базы данных или подключения к уже существующей.
Резидентная база данных
Кроме хранения базы данных непосредственно в файловой системе, также можно ее размещать в оперативной памяти. Это прекрасный способ создавать тестовые варианты баз данных или временные. Они нигде не сохраняются, но при этом к ним можно быстро получить доступ. Собственно, в этом главное преимущество оперативной памяти.
Чтобы создать базу данных в «оперативке», используйте следующий код.
conn = sqlite3.connect(:memory:)
Тем не менее, на практике ситуации, когда нужно создавать базу данных в оперативной памяти, случаются довольно редко.
Создание объекта cursor
Итак, мы уже создали объект соединения с базой данных. Но его недостаточно для выполнения SQL-запросов. Чтобы это сделать, необходимо создать еще один объект, который называется cursor. Предположим, нам надо хранить этот объект в переменной cur.
cur = conn.cursor()
Чтобы выполнить запрос, используется метод execute, в аргументе которого и указывается он.
cur.execute(«ВАШ-SQL-ЗАПРОС-ЗДЕСЬ;»)
Внимание! Запрос необходимо всегда размещать в кавычках. Они могут быть любыми, хоть тройными. Последние нередко используются, чтобы писать сложные запросы, которые необходимо разместить на нескольких строках.
Создание таблиц
Предположим, нам нужно сделать такую таблицу.
Начнем с создания первой таблицы. Для этого необходимо выполнить следующий код.
cur.execute(«»»CREATE TABLE IF NOT EXISTS users(
userid INT PRIMARY KEY,
fname TEXT,
lname TEXT,
gender TEXT);
«»»)
conn.commit()
Разберем эти операции подробнее:
- С помощью функции execute мы непосредственно создали запрос.
- После этого уже SQL создает таблицу с именем users.
- Далее прописывается запрос, который проверяет наличие таблицы. Он нужен для того, если понадобится к таблице подключаться повторно.
- Далее следует перечень колонок, которые нужно создать. Как видим, идет сначала название первой колонки, который служит ключом, а вслед за ним, через отступ, идет значение второй колонки.
Далее используем метод commit, чтобы сохранить объект.
Логика создания второй таблицы такая же самая. Просто нужно указать другие ключи и другие значения, а потом сохранить ее с помощью функции conn.commit().
Теперь наша база данных содержит две таблицы.
Добавление
Предположим, нам необходимо добавить определенную информацию в таблицу SQL. Чтобы это сделать, нам также требуется использовать объект cursor.
cur.execute(«»»INSERT INTO users(userid, fname, lname, gender)
VALUES(‘00001’, ‘Alex’, ‘Smith’, ‘male’);»»»)
conn.commit()
Добавлять единичные данные, в целом, просто. Но что делать, если нужно добавить кортеж? В таком случае создается необходимое количество переменных в виде знаков вопроса внутри SQL, а потом добавляется кортеж таким образом.
cur.execute(«INSERT INTO users VALUES(?, ?, ?, ?);», user)
conn.commit()
Если же нужно использовать несколько кортежей, то тогда применяется метод executemany.
cur.executemany(«INSERT INTO users VALUES(?, ?, ?, ?);», more_users)
Не забываем каждый раз использовать функцию commit для сохранения данных.
SQLite и предотвращение SQL-инъекций
Описанный выше способ со знаками вопросов тем хорош, что позволяет предотвратить SQL-инъекции. Поэтому настоятельно рекомендуется его использовать.
Скрипты для добавления данных
Если необходимо добавлять сразу большое количество данных, то необходимо заблаговременно создать кортеж, а потом использовать специальные скрипты для массового добавления данных. Напоминаем, что они создаются с использованием метода executemany(). Также необходимо использовать метод commit() для подтверждения внесенных изменений.
Как получить данные с SQLite в Python
Итак, нам нужно отобрать данные из базы данных. Допустим, у нас есть таблица с описанием наименований товаров и их ценой. И наша задача – получить стоимость, допустим, кроссовок. Что нужно сделать для этого?
В целом, запрос выполняется таким же способом, но есть один нюанс – необходимо в него добавить еще один элемент. Какой? Читайте дальше, и узнаете.
fetchone() в SQLite
Самый простой вариант использования SQLite – получение только одного результата. Для этого необходимо использовать функцию fetchclone. Она не имеет аргументов и присваивается переменной, которая должна хранить результат.
Приведем пример кода для наглядности.
cur.execute(«SELECT * FROM users;»)
one_result = cur.fetchone()
print(one_result)
Первая строка программы отбирает результат. С помощью второй мы передаем его в переменную. И, наконец, третья строка выводит его на экран.
fetchmany() в SQLite
Если же нам необходимо получить набор данных, то для этого используем функцию fetchmany(). Допустим, нам надо сгенерировать три результата. Для этого используется эта функция с аргументом 3. Например, так.
three_results = cur.fetchmany(3)
Если вывести получившийся результат, то это будет словарь, в состав которого входят кортежи элементов, которые входят в соответствующие позиции базы данных.
fetchall() в SQLite
Хорошо. А что делать, если нам нужно получить сразу несколько результатов? Для этого используется функция fetchall(), которая не принимает ни одного аргумента, но возвращает все данные, содержащиеся в базе.
Точно так же, как и остальные методы из этого раздела, он вызывается из объекта cur и присваивается той переменной, которая и будет принимать результат.
Приведем пример.
all_results = cur.fetchall()
Как удалить данные в SQLite в Python
Удаление осуществляется с помощью следующей команды SQLite.
cur.execute(«DELETE FROM users WHERE lname=’Parker’;»)
Если вы попробуете потом воспользоваться функцией fetchall(), чтобы вывести содержимое базы данных, то этого не получится сделать.
Как объединить таблицы в SQLite в Python
А что делать, если нам надо сделать более сложный вопрос? Как осуществляется объединение данных в этом случае? Допустим, нам требуется выполнить генерацию запроса, который включает имя и фамилию каждого покупателя.
Чтобы это сделать, необходимо воспользоваться такой командой.
cur.execute(«»»SELECT *, users.fname, users.lname FROM orders
LEFT JOIN users ON users.userid=orders.userid;»»»)
Если нужны другие SQL-операции, то подход, в целом, не изменяется.