Библиотека SQLite 3 в Python. Создание таблиц, добавление данных, настройка

SQL – это язык баз данных, если говорить простыми словами. И если вы хотите научиться грамотно анализировать большие объемы информации с помощью Python, то без умения пользоваться этим инструментом никуда. Сегодня мы поговорим о библиотеке SQLite3 – встроенной библиотеке Python, позволяющей выполнять множество операций с базами данных – загружать библиотеку, создавать базы данных и соединяться с ними, создавать таблицы баз данных, добавлять данные, получать необходимые сведения, удалять информацию.

И так далее. Преимущество SQLite3 в том, что он входит в комплект Python. Следовательно, ничего больше устанавливать не нужно.

Создание базы данных в SQLite на Python

Сегодня вы научитесь создавать базы данных SQL на Python, настраивать отношения между разными элементами. Также мы детально разберемся в том, какие типы данных SQLite бывают (к слову, их меньше, чем в классическом SQL), как создавать таблицы баз данных, как правильно получать информацию из базы данных, объединять несколько таблиц.

Отдельно рассмотрим вопросы автоматизации баз данных: создание скриптов для обработки информации. Присаживайтесь поудобнее. Если зима, укройтесь мягким пледом. Если лето – включайте вентилятор и приступайте к чтению.

Типы данных SQLite в Python

К сожалению, другие реализации SQL содержат больше типов данных. Это имеет как плюсы, так и минусы. С одной стороны, новичку значительно проще разобраться в том, как организуется работа с SQL. С другой же стороны, профессионалам будет очень тесно. Тем не менее, SQLite – это прекрасная библиотека для того, чтобы стартовать в анализе данных. А дальше можно использовать уже более продвинутые инструменты, в том числе и сделанные сторонними разработчиками. 

Python поддерживает следующие типы данных SQL:

1. NULL — значение NULL
2. INTEGER — целое число
3. REAL — число с плавающей точкой
4. TEXT — текст
5. 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()

Разберем эти операции подробнее:

1. С помощью функции execute мы непосредственно создали запрос.
2. После этого уже SQL создает таблицу с именем users.
3. Далее прописывается запрос, который проверяет наличие таблицы. Он нужен для того, если понадобится к таблице подключаться повторно. 
4. Далее следует перечень колонок, которые нужно создать. Как видим, идет сначала название первой колонки, который служит ключом, а вслед за ним, через отступ, идет значение второй колонки. 

Далее используем метод 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-операции, то подход, в целом, не изменяется.