Меню

Создание и подключение к базе данных



Пишем запросы через модуль sqlite в Python 3

Для работы с базой SQLite в Python уже есть модуль с аналогичным названием. Модуль относится к базовым и устанавливается вместе с интерпретатором. Вам может понадобится установка через pip, если вы используете какую-то другую сборку Python. Рассмотрим модуль на примерах подключения к базе и создания запросов.

Навигация по посту

  • Особенности SQLite
  • Создание базы данных и подключение к ней
  • Курсор и execute
  • Выполнение запросов
    • Создание таблицы и вставка значений с CREATE и INSERT
    • Обновление данных с UPDATE
    • Чтение данных через SELECT
    • Удаление данных с DELETE
    • Параметризация запроса
  • Получение таблицы в виде словаря
  • Транзакции
  • timeout транзакции

Особенности SQLite

У самой базы SQLite есть несколько особенностей, которые отличают ее от остальных баз. В первую очередь — эта база данных не нуждается в установке, а работает через модули языков. У нее так же нет клиент-серверной архитектуры. Приложение и база, обычно, располагаются рядом на одном хосте. Из-за этого эта база популярна у мобильных приложений, которые не нуждаются в подключении к интернету, но это не единственное место где ее используют.

Создание и подключение к базе данных

Хоть SQLite не является клиент-серверной базой мы все равное должны подключаться к ней. Это можно сделать так:

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

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

Так как база — это все тот же файл и в SQL существует понятие транзакций, которые должны корректно завершаться, мы должны закрывать каждое подключение к базе. Это можно сделать через метод ‘.close()’ или использовать менеджер контекста:

Использовать библиотеку contextlib и метод closing — совет со stackoverflow, где объясняется что иначе соединение не будет закрыто.

Любые операции изменения базы должны подтверждаться методом commit() или откатываться методом rollback(). Если вы этого не сделаете, то может выполнится автоматический rollback().

Курсор и execute

Для выполнения SQL запросов в библиотеке есть несколько методов. Обычный способ — это создание курсора, который будет перемещать по полученным значениям:

После его создания мы сможем выполнять запрос:

Или можно использовать метод .scriptexecute(), который учитывает что вы передаете несколько запросов разделенных ‘;’. В этом случае коммиты выполняются автоматически:

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

Выполнение запросов

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

name size date
pic1.jpg 50KB 2021-12-19
pic2.jpg 60KB 2021-12-20

Создание такой таблицы выполняется в несколько шагов.

Создание таблицы и вставка значений с CREATE и INSERT

Любая таблица состоит из колонок. Эти колонки определяют типы данных. В нашем случае, чистый SQL запрос, который создаст таблицу, будет следующим:

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

Что бы эти запросы были выполнены нужно использовать курсор одним из методов, которые были описаны выше. Это может выглядеть так:

Узнать сколько строк было изменено через запрос можно следующим образом:

Выполнение запроса к sqlite в Python и получение количества измененных строк

Еще один метод total_changes(), который показывает сколько строк было изменено во всей базе с момента подключения:

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

Обновление данных с UPDATE

В языке SQL, обновить одно из значений в базе, можно с инструкцией UPDATE. Так мы, за счет чистого SQL, изменим название одной из строк созданных раннее:

С помощью sqlite и python этот запрос выполняется так же как и с INSERT:

Обновление значений с UPDATE в sqlite в Python

Если commit не будет выполнен, то результат не будет сохранен в базе.

Чтение данных через SELECT

Чтение данных из базы выполняется через перемещение курсора. Этот процесс выполняется через методы fetchone или fetchall, которые возвращают кортеж:

Выполнение SELECT запроса в sqlite в Python

fetchone() будет возвращать по одной строке после каждого вызова:

Выполнение SELECT запроса fetchone в sqlite в Python

Удаление данных с DELETE

SQL запрос, который удалит строку используя колонку ‘name’, выглядит так:

Через код Python это будет выглядеть так:

Выполнение commit в sqlite3 Python

Параметризация запроса

У вас может быть необходимость передать в SQL дополнительные параметры. Вы можете использовать форматирования строк Python, но это будет ошибкой:

Проблема этого способа в том, что пользователь может передать вместо ‘value’ другой SQL запрос. Такой действие называется SQL инъекцией. Каждый передаваемый параметр в SQL нуждается в дополнительных проверках и они уже реализованы в методе execute:

Параметризация запроса в sqlite в Python

То есть вместо знаков ‘?’ у нас будут подставляться значения из кортежа.

Читайте также:  10 лучших источников растительного белка для здорового питания

Мы можем создать несколько запросов используя .executemany(). В примере ниже будет выполнено 2 разных ‘INSERT’ для каждого из кортежей (с точки зрения SQL такой подход считается плохой практикой):

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

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

Преобразование запроса в словарь с SQLite и Python

Вам не обязательно использовать преобразование в словарь, что бы получить значение по ключу. Такая возможно уже есть у класса Row:

Получение результата запроса по ключу в SQLite и Python

Транзакции

В реляционных базах данные есть принцип ACID. Если не вдаваться в технические подробности, то он объясняет как должны работать транзакции. Сама транзакция — это один или несколько запросов, которые выполняются вместе. Эти запросы могут быть успешно выполнены или все вместе отменены. Простой пример — это 2 таблицы, в которые вы вставляете данные. Если в 1 таблице произойдет ошибка, то и операция во 2-ой таблице должна отмениться.

Условия для успешного выполнения транзакций могут быть заданы самим разработчиком. Подтверждаются транзакции через метод commit(), а отменяются через rollback().

Если вы закрываете подключение к базе корректно (используя менеджер контекста with или метод .close()), то все не завершенные запросы выполнят commit. Если у вас произойдет сбой (аварийное выключение компьютера например), то должен будет сработать один из принципов ACID для корректного завершения транзакций, но может произойти и rollback.

В следующем примере мы создадим 2 подключения к базе и в одном из них данные не будут записаны в базу из-за отсутствия commit():

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

rollback работает противоположным образом. Т.е. данные, которые были созданы в первом insert запросе будут удалены из базы вовсе. Транзакция может либо завершиться, либо откатится. Выполнить первое, после второго, как и наоборот, нельзя.

Лучше практикой является явное закрытие каждого подключения и транзакции:

timeout транзакции

Если транзакция не будет закрыта, то таблица будет заблокирована (зависит так же от типа используемой транзакции). Сколько эта транзакция останется в таком виде зависит от параметра ‘timeout’. Он устанавливается при подключении:

Источник

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

Aug 26, 2020 · 5 min read

Если вы разработчик программного обеспечения, то, скорее всего, вы знакомы с невероятно легкой базой данных 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 в качестве типа БД:

Источник

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

Aug 26, 2020 · 5 min read

Если вы разработчик программного обеспечения, то, скорее всего, вы знакомы с невероятно легкой базой данных 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 в качестве типа БД:

Источник

Модуль sqlite — Работаем с базой данных

SQLite – это автономный, работающий без сервера транзакционный механизм базы данных SQL. Python получил модуль sqlite3 в версии 2.5, что значит что вы можете создавать базу данных SQLite в любой настоящей версии Python, без необходимости скачивания дополнительных инструментов. Mozilla использует базы данных SQLite в своем популярном браузере Firefox для хранения закладок и прочей различной информации. В данной статье мы рассмотрим следующее:

  • Как создать базу данных SQLite
  • Как вставить данные в таблицу
  • Как редактировать данные
  • Как удалять данные
  • Базовые запросы SQL

Другими словами, вместо того чтобы собирать по кусочкам сам модуль, мы непосредственно ознакомимся с тем, как его использовать.
Если вы хотите визуально проверить свою базу данных, вы можете использовать плагин SQLite Manager в Firefox (рекомендуем погуглить данный вопрос), или, если вы предпочитаете командную строку, вы можете использовать оболочку командной строки SQLite Python.

Как создавать базу данных и вставлять различные данные

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

Сначала нам нужно импортировать модуль sqlite3 и создать связь с базой данных. Вы можете передать название файла или просто использовать специальную строку “:memory:” для создания базы данных в памяти. В нашем случае, мы создаем его на диске в файле под названием mydatabase.db.

Далее мы создаем объект cursor, который позволяет нам взаимодействовать с базой данных и добавлять записи, помимо всего прочего. Здесь мы используем синтаксис SQL для создания таблицы под названием альбомы с пятью следующими полями: title, artist, release_date, publisher и media_type. SQLite поддерживает только пять типов данных: null, integer, real, text и blob. Давайте напишем этот код и вставим кое-какие данные в нашей новой таблице. Запомните, если вы запускаете команду CREATE TABLE, при этом база данных уже существует, вы получите сообщение об ошибке.

Читайте также:  Делопроизводство компании основные документы

Здесь мы использовали команду INSERT INTO SQL чтобы вставить запись в нашу базу данных. Обратите внимание на то, что каждый объект находится в одинарных кавычках. Это может усложнить работу, если вам нужно вставить строчки, которые содержат одинарные кавычки. В любом случае, чтобы сохранить запись в базе данных, нам нужно создать её. Следующая часть кода показывает, как добавить несколько записей за раз при помощи метода курсора executemany. Обратите внимание на то, что мы используем знаки вопроса (?), вместо строк замещения (%) чтобы вставить значения. Обратите внимание, что использование строки замещения не безопасно, так как может стать причиной появления атаки инъекций SQL . Использование знака вопроса намного лучше, а использование SQLAlchemy тем более, так как он делаете все необходимое, чтобы уберечь вас от правки встроенных одинарных кавычек на то, что SQLite в состоянии принимать.

Есть вопросы по Python?

На нашем форуме вы можете задать любой вопрос и получить ответ от всего нашего сообщества!

Telegram Чат & Канал

Вступите в наш дружный чат по Python и начните общение с единомышленниками! Станьте частью большого сообщества!

Паблик VK

Одно из самых больших сообществ по Python в социальной сети ВК. Видео уроки и книги для вас!

Редактирование и удаление записей

Возможность обновлять записи в вашей базе данных это ключ к тому, чтобы ваши данные велись аккуратно, и был полный порядок. Если вы не можете редактировать данные, тогда ваша база станет бесполезной достаточно скоро. Иногда вам, в том числе, нужно будет удалять и строки. Мы рассмотрим оба вопроса в данном разделе. Для начала, давайте немного обновимся.

Здесь мы использовали команду SQL UPDATE, чтобы обновить таблицу альбомов. Здесь вы можете использовать команду SET, чтобы изменить поле, так что в нашем случае мы изменим имя исполнителя на John Doe в каждой записи, где поле исполнителя указано для Andy Hunter. Весьма просто, не так ли? Обратите внимание на то, что если вы не подтвердите изменения, то они не будут внесены в базу данных. Команда DELETE настолько же проста. Давайте посмотрим.

Удаление еще проще, чем обновление. У SQL это занимает всего две строчки. В данном случае, все, что нам нужно сделать, это указать SQLite, из какой таблицы удалить (albums), и какую именно запись при помощи пункта WHERE. Таким образом, был выполнен поиск записи, в которой присутствует имя “John Doe” в поле исполнителей, после чего эти данные были удалены.

Основные запросы SQLite

Запросы в SQLite очень похожи на те, которые вы используете в других базах данных, таких как MySQL или Postgres. Мы просто используем обычный синтаксис SQL для выполнения запросов, после чего объект cursor выполняет SQL. Вот несколько примеров:

Первый запрос, который мы выполнили, называется SELECT *, что означает, что мы хотим выбрать все записи, подходящие под переданное имя исполнителя, в нашем случае это “Red”. Далее мы выполняем SQL и используем функцию fetchall() для получения результатов. Вы также можете использовать функцию fetchone() для получения первого результата. Обратите внимание на то, что здесь есть прокомментированный раздел, связанный с таинственным row_factory. Если вы не прокомментируете эту строку, результат вернется, так как объекты Row, подобны словарям Python и дают вам доступ к полям строк точь в точь, как и словарь. В любом случае, вы не можете выполнить назначение пункта, используя объект Row. Второй запрос очень похож на первый, но возвращает каждую запись в базе данных и упорядочивает результаты по имени артиста в порядке возрастания. Это также показывает, как мы можем зациклить результаты выдачи. Последний запрос показывает, как команда LIKE используется при поиске частичных фраз. В нашем случае, мы искали по всей таблице заголовки, которые начинаются с артикля The. Знак процента (%) является подстановочным оператором.

Подведем итоги

Теперь вы знаете, как использовать Python для создания базы данных SQLite. Вы также научились создавать, обновлять, редактировать и удалять записи, а также выполнять запросы по базе данных.

Являюсь администратором нескольких порталов по обучению языков программирования Python, Golang и Kotlin. В составе небольшой команды единомышленников, мы занимаемся популяризацией языков программирования на русскоязычную аудиторию. Большая часть статей была адаптирована нами на русский язык и распространяется бесплатно.

E-mail: vasile.buldumac@ati.utm.md

Образование
Universitatea Tehnică a Moldovei (utm.md)

  • 2014 — 2018 Технический Университет Молдовы, ИТ-Инженер. Тема дипломной работы «Автоматизация покупки и продажи криптовалюты используя технический анализ»
  • 2018 — 2020 Технический Университет Молдовы, Магистр, Магистерская диссертация «Идентификация человека в киберпространстве по фотографии лица»

Источник