Как создать базу данных в питоне

База данных SQLite в Python

Модуль Python SQLite — это легкая библиотека, которая обеспечивает простой способ решения сложной задачи управления базами данных типа SQL. В отличие от других систем баз данных, не требует выделенного серверного процесса.

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

Давайте быстро пройдемся по этой библиотеке и создадим нашу собственную базу данных для нашего приложения!

Python SQLite

Python SQLite поставляется вместе с любой установленной версией Python, поэтому нет необходимости устанавливать его с помощью pip . Правильно, он у вас уже есть!

Чтобы импортировать его, поскольку мы будем использовать Python3, мы импортируем модуль sqlite3 .

Начнем с основных методов модуля.

Как создать базу данных?

Хотя мы упоминали, что внешний сервер не работает, и все находится внутри текущего компьютера, поскольку это стандартный протокол для систем баз данных.

Мы создадим соединение с базой данных Python SQLite. Это выделяет память для базы данных и извлекает любые данные из файла базы данных, так что база данных создается из файла db .

Здесь database.db — это файл базы данных, в котором будут храниться данные. Итак, мы вызываем базу данных из этого файла.

Если этот файл не существует, база данных автоматически создаст его для вас и будет использовать этот файл для обновления базы данных в следующий раз.

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

Однако есть еще один вариант — напрямую создать базу данных в ОЗУ, если вам просто нужна временная база данных, с помощью специального файла :memory:

Проблема с вышеуказанным методом заключается в том, что нам нужно явно закрыть соединение в конце, используя conn.close() . У Python есть очень хорошее решение для этого — менеджеры контекста. Менеджер контекста оператора with автоматически сделает это за вас, поэтому теперь наш модифицированный код будет:

Создайте объект курсора

Как только соединение установлено, у нас есть объект соединения, из которого мы можем построить объект Cursor .

Этот курсор является указателем на базу данных, которая используется для навигации по ней, чтобы мы могли перемещаться в места и выполнять SQL-запросы.

Схема базы данных

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

Строки соответствуют фактическим значениям данных, а столбцы соответствуют именам их атрибутов.

Создадим простую схему для хранения изображений. Мы определим любое изображение имеющее имя, размер и дату создания изображения. Фактические данные будут где-то в памяти. Схема только определяет, как организованы данные.

Поскольку мы собираемся определить таблицу таким образом, схема таблицы будет выглядеть, как показано на рисунке ниже.

Мы можем выполнять запросы с помощью курсора, передав cursor.execute(‘SQL_QUERY’) , используя запрос SQL.

Напишем SQL-запрос для создания этой схемы.

Мы можем передать этот запрос в виде строки, но если вы хотите выполнять более длинные запросы, проще просто создать файл .sql а затем вместо этого читать его, используя cursor.executescript() .

Итак, давайте поместим наш запрос в файл с именем schema.sql и прочитаем из него.

Получение значения из таблицы

Теперь, когда у нас есть таблица с начальными значениями, давайте запросим таблицу, чтобы получить все ее строки. Мы не можем просто напечатать объект курсора, поэтому нам нужно явно получить атрибуты с помощью cursor.fetchall() и распечатать их, перебирая его.

Транзакции в SQLite

Управление транзакциями — одна из функций баз данных SQL, и SQLite также обрабатывает их. Транзакция — это последовательность изменений, в которой вы можете безопасно изменить базу данных, выполнив запрос и затем разместив commit .

Если по какой-то причине непосредственно перед фиксацией вы не хотите завершать транзакцию, вы можете вернуться в предыдущее состояние перед фиксацией, используя rollback .

Точно так же мы также можем просматривать состояние базы данных через эти типы изменений.

Учебник по SQLite3 в Python

SQLite – это C библиотека, реализующая легковесную дисковую базу данных (БД), не требующую отдельного серверного процесса и позволяющую получить доступ к БД с использованием языка запросов SQL. Некоторые приложения могут использовать SQLite для внутреннего хранения данных. Также возможно создать прототип приложения с использованием SQLite, а затем перенести код в более многофункциональную БД, такую как PostgreSQL или Oracle.

Модуль sqlite3 реализует интерфейс SQL, соответствующий спецификации DB-API 2.0, описанной в PEP 249.

Создание соединения

Чтобы воспользоваться SQLite3 в Python необходимо импортировать модуль sqlite3, а затем создать объект подключения к БД.

Объект подключения создается с помощью метода connect():

Курсор SQLite3

Для выполнения операторов SQL, нужен объект курсора, создаваемый методом cursor().

Курсор SQLite3 – это метод объекта соединения. Для выполнения операторов SQLite3 сначала устанавливается соединение, а затем создается объект курсора с использованием объекта соединения следующим образом:

Теперь можно использовать объект курсора для вызова метода execute() для выполнения любых запросов SQL.

Создание базы данных

После создания соединения с SQLite, файл БД создается автоматически, при условии его отсутствия. Этот файл создается на диске, но также можно создать базу данных в оперативной памяти, используя параметр «:memory:» в методе connect. При этом база данных будет называется инмемори.

Рассмотрим приведенный ниже код, в котором создается БД с блоками try, except и finally для обработки любых исключений:

Сначала импортируется модуль sqlite3, затем определяется функция с именем sql_connection. Внутри функции определен блок try, где метод connect() возвращает объект соединения после установления соединения.

Затем определен блок исключений, который в случае каких-либо исключений печатает сообщение об ошибке. Если ошибок нет, соединение будет установлено, тогда скрипт распечатает текст «Connection is established: Database is created in memory».

Далее производится закрытие соединения в блоке finally. Закрытие соединения необязательно, но это хорошая практика программирования, позволяющая освободить память от любых неиспользуемых ресурсов.

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

Чтобы создать таблицу в SQLite3, выполним запрос Create Table в методе execute(). Для этого выполним следующую последовательность шагов:

1. Создание объекта подключения
2. Объект Cursor создается с использованием объекта подключения
3. Используя объект курсора, вызывается метод execute с запросом create table в качестве параметра.

Давайте создадим таблицу Employees со следующими колонками:

Код будет таким:

В приведенном выше коде определено две функции: первая устанавливает соединение; а вторая — используя объект курсора выполняет SQL оператор create table.

Метод commit() сохраняет все сделанные изменения. В конце скрипта производится вызов обеих функций.

Для проверки существования таблицы воспользуемся браузером БД для sqlite.

Вставка данных в таблицу

Чтобы вставить данные в таблицу воспользуемся оператором INSERT INTO. Рассмотрим следующую строку кода:

Также можем передать значения / аргументы в оператор INSERT в методе execute (). Также можно использовать знак вопроса (?) в качестве заполнителя для каждого значения. Синтаксис INSERT будет выглядеть следующим образом:

Где картеж entities содержат значения для заполнения одной строки в таблице:

Код выглядит следующим образом:

Обновление таблицы

Предположим, что нужно обновить имя сотрудника, чей идентификатор равен 2. Для обновления будем использовать инструкцию UPDATE. Также воспользуемся предикатом WHERE в качестве условия для выбора нужного сотрудника.

Рассмотрим следующий код:

Это изменит имя Эндрю на Роджерс.

Оператор SELECT

Оператор SELECT используется для выборки данных из одной или более таблиц. Если нужно выбрать все столбцы данных из таблицы, можете использовать звездочку (*). SQL синтаксис для этого будет следующим:

В SQLite3 инструкция SELECT выполняется в методе execute объекта курсора. Например, выберем все стрики и столбцы таблицы employee:

Если нужно выбрать несколько столбцов из таблицы, укажем их, как показано ниже:

Оператор SELECT выбирает все данные из таблицы employees БД.

Выборка всех данных

Чтобы извлечь данные из БД выполним инструкцию SELECT, а затем воспользуемся методом fetchall() объекта курсора для сохранения значений в переменной. При этом переменная будет являться списком, где каждая строка из БД будет отдельным элементом списка. Далее будет выполняться перебор значений переменной и печатать значений.

Код будет таким:

Также можно использовать fetchall() в одну строку:

Если нужно извлечь конкретные данные из БД, воспользуйтесь предикатом WHERE. Например, выберем идентификаторы и имена тех сотрудников, чья зарплата превышает 800. Для этого заполним нашу таблицу большим количеством строк, а затем выполним запрос.

Можете использовать оператор INSERT для заполнения данных или ввести их вручную в программе браузера БД.

Теперь, выберем имена и идентификаторы тех сотрудников, у кого зарплата больше 800:

В приведенном выше операторе SELECT вместо звездочки (*) были указаны атрибуты id и name.

SQLite3 rowcount

Счетчик строк SQLite3 используется для возврата количества строк, которые были затронуты или выбраны последним выполненным запросом SQL.

Когда вызывается rowcount с оператором SELECT, будет возвращено -1, поскольку количество выбранных строк неизвестно до тех пор, пока все они не будут выбраны. Рассмотрим пример:

Поэтому, чтобы получить количество строк, нужно получить все данные, а затем получить длину результата:

Когда оператор DELETE используется без каких-либо условий (предложение where), все строки в таблице будут удалены, а общее количество удаленных строк будет возвращено rowcount.

Если ни одна строка не удалена, будет возвращено 0.

Список таблиц

Чтобы вывести список всех таблиц в базе данных SQLite3, нужно обратиться к таблице sqlite_master, а затем использовать fetchall() для получения результатов из оператора SELECT.

Sqlite_master — это главная таблица в SQLite3, в которой хранятся все таблицы.

Проверка существования таблицы

При создании таблицы необходимо убедиться, что таблица еще не существует. Аналогично, при удалении таблицы она должна существовать.

Чтобы проверить, если таблица еще не существует, используем «if not exists» с оператором CREATE TABLE следующим образом:

Точно так же, чтобы проверить, существует ли таблица при удалении, мы используем «if not exists» с инструкцией DROP TABLE следующим образом:

Также проверим, существует ли таблица, к которой нужно получить доступ, выполнив следующий запрос:

Если указанное имя таблицы не существует, будет возвращен пустой массив.

Удаление таблицы

Удаление таблицы выполняется с помощью оператора DROP. Синтаксис оператора DROP выглядит следующим образом:

Чтобы удалить таблицу, таблица должна существовать в БД. Поэтому рекомендуется использовать «if exists» с оператором DROP. Например, удалим таблицу employees:

Исключения SQLite3

Исключением являются ошибки времени выполнения скрипта. При программировании на Python все исключения являются экземплярами класса производного от BaseException.

В SQLite3 у есть следующие основные исключения Python:

DatabaseError

Любая ошибка, связанная с базой данных, вызывает ошибку DatabaseError.

IntegrityError

IntegrityError является подклассом DatabaseError и возникает, когда возникает проблема целостности данных, например, когда внешние данные не обновляются во всех таблицах, что приводит к несогласованности данных.

ProgrammingError

Исключение ProgrammingError возникает, когда есть синтаксические ошибки или таблица не найдена или функция вызывается с неправильным количеством параметров / аргументов.

OperationalError

Это исключение возникает при сбое операций базы данных, например, при необычном отключении. Не по вине программиста.

NotSupportedError

При использовании некоторых методов, которые не определены или не поддерживаются базой данных, возникает исключение NotSupportedError.

Массовая вставка строк в Sqlite

Для вставки нескольких строк одновременно использовать оператор executemany.

Рассмотрим следующий код:

Здесь создали таблицу с двумя столбцами, тогда у «данных» есть четыре значения для каждого столбца. Эта переменная передается методу executemany() вместе с запросом.

Обратите внимание, что использовался заполнитель для передачи значений.

Закрытие соединения

Когда работа с БД завершена, рекомендуется закрыть соединение. Соединение может быть закрыто с помощью метода close ().

Чтобы закрыть соединение, используйте объект соединения с вызовом метода close() следующим образом:

SQLite3 datetime

В базе данных Python SQLite3 можно легко сохранять дату или время, импортируя модуль datatime. Следующие форматы являются наиболее часто используемыми форматами для даты и времени:

Рассмотрим следующий код:

В этом коде модуль datetime импортируется первым, далее создали таблицу с именем assignments с тремя столбцами.

Тип данных третьего столбца — дата. Чтобы вставить дату в столбец, воспользовались datetime.date. Точно так же можно использовать datetime.time для обработки времени.

Вывод

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

Встроенная база данных Python

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

• встроенные устройства и интернет вещей;
• анализ данных;
• передача данных;
• архив файлов и/или контейнер данных;
• внутренние или временные базы данных;
• замена корпоративной базы данных в период демо-версий или тестирования;
• обучение и тестирование;
• экспериментальные расширения языка SQL.

Если вам нужна SQLite для каких-либо других целей, то обратитесь к документации.

Но самое главное — SQLite встроена в библиотеку Python. То есть вам не нужно устанавливать серверное или клиентское ПО и поддерживать работу какого-либо сервиса. Если вы импортировали библиотеку в Python и приступили к работе, значит вы уже используете систему управления реляционными базами данных!

Импортирование и использование

«Встроенность» предполагает, что вам не нужно запускать pip install для получения библиотеки. Просто импортируйте ее с помощью:

Создание соединения с БД

Не беспокойтесь о драйверах, строках подключения и т.д. Вы можете создать базу данных SQLite и задать такой простой объект подключения, как:

После запуска этой строки кода происходит создание с БД и активируется подключение к ней. Дело в том, что базы данных, к которой мы просим подключиться Python, не существует, поэтому он автоматически создает пустую. Также мы можем ввести точно такой же код для подключения к уже существующей базе данных.

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

Теперь создадим таблицу:

Мы добавили три столбца в таблицу USER . Как видите, SQLite действительно легка и при этом поддерживает все основные функции обычной реляционной СУБД, такие как тип данных, обнуляемый тип, первичный ключ и автоинкремент.

После запуска этого кода создается таблица, но она ничего не выводит.

Включение записей

Вставим несколько записей в только что созданную таблицу USER , чтобы доказать, что она действительно создана.

Предположим, мы хотим вставить сразу несколько записей. Выполним:

Определяем оператор SQL с вопросительными знаками ? в качестве заполнителя. Теперь создадим образцы данных для вставки, а затем вставим их с помощью объекта подключения:

После запуска кода не появилось никаких предупреждений, значит все прошло успешно.

Запрос к таблице

Пришло время удостовериться, что все сделано правильно. Выполним запрос к таблице на возврат образцов строк.

Как видите, все очень просто!

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

Чаще всего я использую инструмент DBeaver. Рассмотрим его на примере.

Подключение к базе данных SQLite из клиента SQL (DBeaver)

Поскольку я использую Google Colab, я буду загружать файл my-test.db на свой компьютер. При запуске Python на локальном компьютере можно использовать клиент SQL для прямого подключения к файлу баз данных.

Создаем новое соединение в DBeaver и выбираем SQLite в качестве типа БД:

Затем переходим к файлу БД:

Теперь к базе данных можно выполнить любой SQL-запрос, как и в любых других реляционных БД:

Непрерывная интеграция с Pandas

Но это еще не все. Дело в том, что, являясь встроенной функцией Python, SQLite может легко интегрироваться с фреймом данных Pandas.

Определяем фрейм данных:

Затем просто вызываем метод фрейма данных to_sql() , чтобы сохранить его в базе данных:

И это все, что нужно сделать! Вам даже не придется создавать таблицу заранее — типы данных и длина столбцов будут определены автоматически. Конечно, при желании вы также можете определить ее заранее.

Допустим, мы хотим объединить таблицу USER и SKILL и прочитать результат во фрейме данных Pandas. Это тоже можно выполнить без проблем.

Результаты запишем в новую таблицу под названием USER_SKILL :

Теперь мы также можем использовать клиент SQL для получения таблицы:

Выводы

В Python есть множество скрытых сюрпризов. Но скрыты они не специально: дело лишь в том, что в Python настолько много функций «из коробки», что невозможно раскрыть их все сразу.

В этой статье мы узнали, как использовать встроенную библиотеку Python sqlite3 для создания таблиц и манипулирования ими в базе данных SQLite. Конечно, она также поддерживает обновление и удаление, которые вы можете попробовать самостоятельно.

Но самое главное, мы можем легко прочитать таблицу из базы данных SQLite во фрейме данных Pandas и наоборот. Такая возможность еще больше упрощает взаимодействие с этой легкой реляционной базой данных.

Возможно, вы заметили, что в SQLite нет аутентификации. С ней бы данная библиотека перестала быть такой легкой.