Python: Работа с базой данных, часть 1/2: Используем DB-API
В статье рассмотрены основные методы DB-API, позволяющие полноценно работать с базой данных. Полный список можете найти по ссылкам в конец статьи.
Требуемый уровень подготовки: базовое понимание синтаксиса SQL и Python.
Готовим инвентарь для дальнейшей комфортной работы
Примечание: внося изменения в базу не забудьте их применить, так как база с непримененными изменениями остается залоченной.
Вы можете использовать (последние два варианта кросс-платформенные и бесплатные):
Python DB-API модули в зависимости от базы данных
| База данных | DB-API модуль |
|---|---|
| SQLite | sqlite3 |
| PostgreSQL | psycopg2 |
| MySQL | mysql.connector |
| ODBC | pyodbc |
Соединение с базой, получение курсора
Для начала рассмотрим самый базовый шаблон DB-API, который будем использовать во всех дальнейших примерах:
При работе с другими базами данных, используются дополнительные параметры соединения, например для PostrgeSQL:
Чтение из базы
Обратите внимание: После получения результата из курсора, второй раз без повторения самого запроса его получить нельзя — вернется пустой результат!
Запись в базу
Разбиваем запрос на несколько строк в тройных кавычках
Длинные запросы можно разбивать на несколько строк в произвольном порядке, если они заключены в тройные кавычки — одинарные (»’…»’) или двойные («»». «»»)
Конечно в таком простом примере разбивка не имеет смысла, но на сложных длинных запросах она может кардинально повышать читаемость кода.
Объединяем запросы к базе данных в один вызов метода
Данный метод также удобен, когда у нас запросы сохранены в отдельной переменной или даже в файле и нам его надо применить такой запрос к базе.
Делаем подстановку значения в запрос
Важно! Никогда, ни при каких условиях, не используйте конкатенацию строк (+) или интерполяцию параметра в строке (%) для передачи переменных в SQL запрос. Такое формирование запроса, при возможности попадания в него пользовательских данных – это ворота для SQL-инъекций!
Возможны два варианта:
Примечание 1: В PostgreSQL (UPD: и в MySQL) вместо знака ‘?’ для подстановки используется: %s
Примечание 2: Таким способом не получится заменять имена таблиц, одно из возможных решений в таком случае рассматривается тут: stackoverflow.com/questions/3247183/variable-table-name-in-sqlite/3247553#3247553
UPD: Примечание 3: Благодарю Igelko за упоминание параметра paramstyle — он определяет какой именно стиль используется для подстановки переменных в данном модуле.
Вот ссылка с полезным приемом для работы с разными стилями подстановок.
Он всегда возвращает кортеж или None. если запрос пустой.
Курсор как итератор
UPD: Повышаем устойчивость кода
Благодарю paratagas за ценное дополнение:
Для большей устойчивости программы (особенно при операциях записи) можно оборачивать инструкции обращения к БД в блоки «try-except-else» и использовать встроенный в sqlite3 «родной» объект ошибок, например, так:
UPD: Использование with в psycopg2
Благодарю KurtRotzke за ценное дополнение:
Последние версии psycopg2 позволяют делать так:
Некоторые объекты в Python имеют __enter__ и __exit__ методы, что позволяет «чисто» взаимодействовать с ними, как в примере выше.
UPD: Ипользование row_factory
Благодарю remzalp за ценное дополнение:
Использование row_factory позволяет брать метаданные из запроса и обращаться в итоге к результату, например по имени столбца.
По сути — callback для обработки данных при возврате строки. Да еще и полезнейший cursor.description, где есть всё необходимое.
Пример из документации:
Дополнительные материалы (на английском)
Руководство по SQLite в Python
SQL и Python — обязательные инструменты для любого специалиста в сфере анализа данных. Это руководство — все, что вам нужно для первоначальной настройки и освоения основ работы с SQLite в Python. Оно включает следующие пункты:
SQLite3 (часто говорят просто SQLite) — это часть стандартного пакета Python 3, поэтому ничего дополнительно устанавливать не придется.
Что будем создавать
В процессе этого руководства создадим базу данных в SQLite с помощью Python, несколько таблиц и настроим отношения:
Типы данных SQLite в Python
SQLite для Python предлагает меньше типов данных, чем есть в других реализациях SQL. С одной стороны, это накладывает ограничения, но, с другой стороны, в SQLite многое сделано проще. Вот основные типы:
К сожалению, других привычных для SQL типов данных в SQLite нет.
Первые шаги с SQLite в Python
Начнем руководство с загрузки библиотеки. Для этого нужно использовать следующую команду:
Следующий шаг — создание базы данных.
Создание базы данных SQLite в Python
Если файл уже существует, то функция connect осуществит подключение к нему.
перед строкой с путем стоит символ «r». Это дает понять Python, что речь идет о «сырой» строке, где символы «/» не отвечают за экранирование.
Функция connect создает соединение с базой данных SQLite и возвращает объект, представляющий ее.
Резидентная база данных
Еще один способ создания баз данных с помощью SQLite в Python — создание их в памяти. Это отличный вариант для тестирования, ведь такие базы существуют только в оперативной памяти.
Однако в большинстве случаев (и в этом руководстве) будет использоваться описанный до этого способ.
Создание объекта cursor
Теперь выполнять запросы можно следующим образом:
Обратите внимание на то, что сами запросы должны быть помещены в кавычки — это важно. Это могут быть одинарные, двойные или тройные кавычки. Последние используются в случае особенно длинных запросов, которые часто пишутся на нескольких строках.
Создание таблиц в SQLite в Python
Пришло время создать первую таблицу в базе данных. С объектами соединения ( conn ) и cursor ( cur ) это можно сделать. Будем следовать этой схеме.
В коде выше выполняются следующие операции:
Для создания второй таблицы просто повторим последовательность действий, используя следующие команды:
После исполнения этих двух скриптов база данных будет включать две таблицы. Теперь можно добавлять данные.
Добавление данных с SQLite в Python
В Python часто приходится иметь дело с переменными, в которых хранятся значения. Например, это может быть кортеж с информацией о пользователе.
Если его нужно загрузить в базу данных, тогда подойдет следующий формат:
В данном случае все значения заменены на знаки вопроса и добавлен параметр, содержащий значения, которые нужно добавить.
Важно заметить, что SQLite ожидает получить значения в формате кортежа. Однако в переменной может быть и список с набором кортежей. Таким образом можно добавить несколько пользователей:
Но нужно использовать функцию executemany вместо обычной execute :
SQLite и предотвращение SQL-инъекций
Скрипты для загрузки данных
Следующие скрипты можно скопировать и вставить для добавления данных в обе таблицы:
Используйте следующие запросы:
Получение данных с SQLite в Python
Следующий момент касательно SQLite в Python — выбор данных. Структура формирования запроса та же, но к ней будет добавлен еще один элемент.
Использование fetchone() в SQLite в Python
Она вернет следующее:
Использование fetchmany() в SQLite в Python
Он вернет следующее:
Использование fetchall() в SQLite в Python
Функцию fetchall() можно использовать для получения всех результатов. Вот что будет, если запустить скрипт:
Удаление данных в SQLite в Python
Теперь рассмотрим процесс удаления данных с SQLite в Python. Здесь та же структура. Предположим, нужно удалить любого пользователя с фамилией «Parker». Напишем следующее:
Если затем сделать следующей запрос:
Будет выведен пустой список, подтверждающий, что запись удалена.
Объединение таблиц в SQLite в Python
Наконец, посмотрим, как использовать объединение данных для более сложных запросов. Предположим, нужно сгенерировать запрос, включающий имя и фамилию каждого покупателя заказа.
Для этого напишем следующее:
Тот же подход работает с другими SQL-операциями.
Выводы
В этом материале вы узнали все, что требуется для работы с SQLite в Python: загрузка библиотеки, создание баз и таблиц, добавление, запрос и удаление данных.
Методы объекта Connection SQLite3.
Синтаксис:
В разделе рассмотрены методы объекта Connection модуля sqlite3 с подробным описанием и примерами.
Содержание.
Соединение с базой данных SQLite имеет следующие атрибуты и методы:
connect.isolation_level :
Смотрите раздел «Управление транзакциями» для более подробного объяснения.
connect.in_transaction :
Атрибут только для чтения.
connect.cursor(factory=Cursor) :
connect.commit() :
Если встретили ситуацию при которой не видны данные, которые были переданы в базу данных, то необходимо убедится, что метод connect.commit был вызван.
connect.rollback() :
connect.close() :
Метод connect.close() закрывает соединение с базой данных.
connect.execute(sql[, parameters]) :
Пример обоих стилей:
connect.executemany(sql[, seq_of_parameters]) :
connect.executescript(sql_script) :
connect.create_function(name, num_params, func, *, deterministic=False) :
Метод connect.create_function() создает пользовательскую функцию, которую позже можно использовать из операторов SQL под именем функции.
connect.create_aggregate(name, num_params, aggregate_class) :
Метод connect.create_aggregate() создает пользовательскую агрегатную функцию.
connect.create_collation(name, callable) :
Метод connect.create_collation() создает сопоставление с указанным именем name и возможностью вызова.
Вызываемому объекту callable будут переданы два строковых аргумента. Объект callable должен возвращать:
В следующем примере показана пользовательская сортировка.
Можно вызвать этот метод из другого потока, чтобы прервать любые запросы, которые могут выполняться в соединении. Затем запрос будет прерван, и вызывающая сторона получит исключение.
connect.set_authorizer(authorizer_callback) :
Обратный вызов должен возвращать:
connect.set_progress_handler(handler, n) :
Такое поседение полезно, если необходимо получить вызов из SQLite во время длительных операций, например для обновления графического интерфейса. Другими словами позволяет сделать индикатор выполнения PROGRESS BAR
Возврат ненулевого значения из функции-обработчика завершит текущий выполняющийся запрос и вызовет исключение OperationalError.
connect.set_trace_callback(trace_callback) :
Метод connect.set_trace_callback() регистрирует trace_callback для вызова каждого оператора SQL, который фактически выполняется бакэндом SQLite
Передача trace_callback=None отключит обратный вызов трассировки.
connect.enable_load_extension(enabled) :
Метод connect.enable_load_extension() разрешает/запрещает движку SQLite загружать расширения SQLite из общих библиотек. Расширения SQLite могут определять новые функции, агрегаторы или новые реализации виртуальных таблиц. Одним из хорошо известных расширений является расширение полнотекстового поиска (модули FTS3 и FTS4), распространяемое с помощью SQLite.
Загружаемые расширения по умолчанию отключены.
connect.load_extension(path) :
Метод connect.load_extension() загружает расширение SQLite из общей библиотеки.
Загружаемые расширения по умолчанию отключены.
connect.row_factory :
Можно изменить атрибут connect.row_factory на вызываемый объект, который принимает курсор и исходную строку в качестве кортежа и возвращает строку реального результата. Таким образом, можно реализовать более продвинутые способы возврата результатов, например функцию которая может обращаться к столбцам по имени.
connect.text_factory :
Также можно установить для атрибута connect.text_factory любой другой вызываемый объект, который принимает один параметр bytestring и возвращает полученный объект.
Смотрите следующий пример кода для лучшего понимания:
connect.total_changes :
Атрибут connect.total_changes возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены с момента открытия соединения с базой данных.
connect.iterdump() :
Метод connect.iterdump() возвращает итератор для выгрузки базы данных в текстовом формате SQL.
connect.backup(target, *, pages=0, progress=None, name=»main», sleep=0.250) :
По умолчанию или когда pages равно 0 или отрицательному целому числу, вся база данных копируется за один шаг, в противном случае метод выполняет циклическое копирование до страниц pages за один раз.
Аргумент sleep определяет количество секунд, в течение которых происходит переход в спящий режим между последовательными попытками резервного копирования оставшихся страниц и может быть задан либо в виде целого числа, либо в виде значения с плавающей запятой.
Методы объекта Cursor SQLite3.
Синтаксис:
В разделе рассмотрены методы объекта Cursor модуля sqlite3 с подробным описанием и примерами.
Содержание:
A Cursor instance has the following attributes and methods.
cursor.execute(sql[, parameters]) :
Метод cursor.execute() выполняет команду SQL. Команды SQL могут быть параметризованными, то есть передаются заполнители вместо литералов SQL.
Пример использования обоих стилей:
cursor.executemany(sql, seq_of_parameters) :
Модуль sqlite3 также позволяет использовать итератор, который выдает параметры.
cursor.executescript(sql_script) :
cursor.fetchone() :
cursor.fetchmany(size=cursor.arraysize) :
cursor.fetchall() :
Метод cursor.fetchall() выбирает все оставшиеся строки результата запроса, возвращая список.
Обратите внимание, что атрибут cursor.arraysize() курсора может повлиять на производительность этой операции. Метод возвращает пустой список, когда нет доступных строк.
cursor.close() :
cursor.rowcount :
cursor.lastrowid :
Атрибут cursor.lastrowid только для чтения, предоставляет идентификатор последней измененной строки в базе данных.
Атрибут cursor.lastrowid будет возвращен, если оператору INSERT или REPLACE не удалось успешно вставить предыдущий идентификатор строки.
cursor.arraysize :
Значение по умолчанию равно 1, что означает, что для каждого вызова будет выбрана одна строка.
cursor.description :
Он также устанавливается для операторов SELECT без соответствующих строк.
Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.
Часть 9.2: Команда COMMIT в базах данных SQLite (оператор COMMIT в SQLite3)
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Мы уже разобрались с тем, как нам начать транзакцию в базах данных SQLite, а в этой записи мы разберемся с тем, как успешно завершить транзакцию в SQLite, под словом успешно стоит понимать: завершить транзакцию с подтверждением изменения. Для этих целей в СУБД SQLite реализована команда COMMIT, так же SQLite поддерживает псевдоним END, замечу, что END намного чаще используется, чем COMMIT и, аналогично, позволяет завершить транзакцию и подтвердить все изменения в базе данных SQLite.
Команда COMMIT в базах данных SQLite (оператор COMMIT в SQLite3)
Команда COMMIT в базах данных SQLite (оператор COMMIT в SQLite3)
Рассмотрим вторую команду управления транзакциями в базах данных SQLite. COMMIT TRANSACTION в SQLite3 позволяет подтвердить операции, выполненные внутри транзакции. Хочу обратить ваше внимание, что SQL предложение, содержащее команду BEGIN и предложение, содержащее команду COMMIT – это две разных команды для СУБД, между которыми может быть множество запросов к базе данных распределенных во времени. Оператор COMMIT в базах данных SQLite подтверждает изменения, внесенные транзакцией. Давайте взглянем на синтаксис команды COMMIT в SQLite3. Хотя я и использую термин оператор, вместо команда, позволю напомнить, что SQL операторы и команды — это разные языковые конструкции.
Синтаксис команды COMMIT в базах данных SQLite
Синтаксис оператора COMMIT TRANSACTION в SQLite3 очень прост: ключевое слово COMMIT, которое можно заменить словом END, за которым идет ключевое слово TRANSACTION. SQLite3 позволяет не писать ключевое слово TRANSACTION. Давайте посмотрим на примере работу команды COMMIT TRANSACTION в базах данных SQLite3.
