Python: работа с базой данных, часть 1/2: используем db-api / хабр

Пример — выбор отдельных полей из одной таблицы

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

Например:

PgSQL

SELECT employee_id,
last_name,
first_name
FROM employees
WHERE employee_id < 50
ORDER BY last_name ASC, employee_id DESC;

1
2
3
4
5
6

SELECTemployee_id,

last_name,

first_name

FROMemployees

WHEREemployee_id<50

ORDERBYlast_nameASC,employee_idDESC;

В этом SQLite примере SELECT будут возвращаться только поля employee_id, last_name и first_name из таблицы employee, где employee_id меньше 50. Результаты сортируются по last_name в порядке возрастания, а затем employee_id в порядке убывания.

Добавление класса для работы с БД

Для открытия и подготовки БД в Android используется наследник класса . Мы тоже создадим наследник этого класса , но он будет сильно модифицированный, так как мы будем работать с готовой базой данных, а не создавать ей с помощью SQL запросов:

Ниже приведен текст всего класса, который нужно просто скопировать (не трогая свой первой строчки ):

Для работы в последующим с другими базами данных вам ничего не нужно будет менять в данном классе, кроме строчек:

Разберем что означают эти строчки.

— имя файла БД. Какой файл БД вы создали, такое название сюда и копируем.

— путь к БД. Каждое приложение в Android имеет свою область памяти, куда складываются файлы программы. Вдруг вы захотите вывернуть путь к файлу БД. Я бы ничего не трогал.

— самая интересная переменная (причем в примерах в сети по работе с готовой БД её обходят стороной). Это номер версии БД. Ниже описан принцип работы данного класса. Например, вы пишите справочник рецептов под Android и рецепты храните в БД. В момент создания установки приложения программа должна скопировать БД на устройство. Потом через какое-то время вы решили обновить приложение, и БД у вас обновилась: структура БД поменялась, добавились новые рецепты. И вам нужно заменить старую БД на новую. Вот тут вы и пропишите в данной переменной новую версию БД. И при открытии приложения будет произведена проверки версии БД, и файл БД обновится. Вначале версия БД равна 1.

Итак, логика работы класса в подготовке базы данных:

Копируем файл БД, если этого файла нет (при установке приложения).
Если номер БД обновлен, то заменяем один файл базы данных на другой:

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

Исключения базы данных SQLite

. Подкласс . Его можно игнорировать, если нужно, чтобы оно не останавливало выполнение.
. Базовый класс для остальных исключений модуля sqlite3. Подкласс .
. Исключение, которое возвращается при ошибках базы данных. Например, если попытаться открыть файл как базу sqite3, хотя он ею не является, то вернется ошибка «sqlite3.DatabaseError: file is encrypted or is not a database».
. Подкласс . Эта ошибка возвращается, когда затрагиваются отношения в базе, например, например, не проходит проверка внешнего ключа.
. Подкласс . Эта ошибка возникает из-за ошибок программиста: создание таблицы с именем, которое уже занято, синтаксическая ошибка в SQL-запросах.
. Подкласс . Эту ошибку невозможно контролировать. Она появляется в ситуациях, которые касаются работы базы данных, например, обрыв соединения, неработающий сервер, проблемы с источником данных и так далее.
. Это исключение появляется при попытке использовать неподдерживаемое базой данных API. Пример: вызов метода для соединения, которое не поддерживает транзакции. Вызов коммита после команды создания таблицы.

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

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

Копировать

7.2.Проверка целостности с помощью SQL функции rtreecheck()

Скалярная функция SQL rtreecheck (R) или rtreecheck (S, R) запускает проверку целостности таблицы rtree с именем R, содержащейся в базе данных S. Функция возвращает описание любых обнаруженных проблем на человеческом языке или строку ‘ok’, если все в порядке. Запуск rtreecheck () в виртуальной таблице R * Tree аналогичен запуску в базе данных.

Пример:Чтобы проверить,что R*дерево под названием «demo_index» хорошо сформировано и внутренне совместимо,запустите его:

SELECT rtreecheck('demo_index');

Функция rtreecheck()выполняет следующие проверки:

Для каждой ячейки в r-деревянной структуре (таблица %_node)это:
1. для каждого измерения (координата1 <= координата2).
2. если только ячейка не находится в корневом узле,что ячейка ограничена родительской ячейкой в родительском узле.
3. для узлов листа,что в таблице %_rowid есть запись,соответствующая значению rowid ячейки,которая указывает на правильный узел.
4. для ячеек на нелифтовых узлах,что в отображении таблицы %_parent есть запись от дочернего узла ячейки к узлу,на котором она находится.
Чтобы в таблице %_rowid было столько же записей,сколько ячеек листа в структуре r-дерева,и чтобы была ячейка листа,соответствующая каждой записи в таблице %_rowid.
Чтобы в таблице %_parent было столько же записей,сколько и в структуре r-дерева,и чтобы была нелистовая ячейка,соответствующая каждой записи в таблице %_parent.

SQLite находится в публичном домене.https://sqlite.org/rtree.html

sqlite_offset(X)

Функция sqlite_offset(X) возвращает байтовое смещение в файле базы данных для начала записи, из которой будет считано значение. Если X не является столбцом в обычной таблице, то sqlite_offset (X) возвращает NULL. Значение, возвращаемое параметром sqlite_offset (X), может ссылаться на исходную таблицу или индекс в зависимости от запроса. Если значение X обычно извлекается из индекса, то функция sqlite_offset(X) возвращает смещение соответствующей записи индекса. Если значение X извлекается из исходной таблицы, то функция sqlite_offset(X) возвращает смещение записи таблицы.

Функция sqlite_offset(X) доступна если SQLite был откомпилирован с опцией -DSQLITE_ENABLE_OFFSET_SQL_FUNC.

Чтение данных из БД SQLite в C#

Для того, чтобы понять как происходит чтение данных из таблиц БД SQLite в C# мы можем воспользоваться следующим простым примером:

using System.Data.SQLite;
using System.Data;
....
command.CommandText = "SELECT * FROM Person";
DataTable data = new DataTable();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(data);
Console.WriteLine($"Прочитано {data.Rows.Count} записей из таблицы БД");
foreach (DataRow row in data.Rows)
{
    Console.WriteLine($"id = {row.Field<long>("id")} name = {row.Field<string>("name")} family = {row.Field<string>("family")}");
}

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

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

У есть несколько конструкторов, позволяющих создать объект. Мы воспользовались только одним из них

После этого мы заполняем нашу таблицу данными, используя для этого метод адаптера. Как только мы заполнили данными нашу таблицу, мы можем приступать к чтению данных по каждой записи. Для этого в примере использован цикл foreach в котором мы проходимся по каждой строке таблицы и в каждой строке читаем поля id, name и family каждой записи, используя обобщенный метод , который позволяет привести данные поля к необходимому нам типу.

Обратите внимание на чтение поля id — здесь мы приводим значение поля к типу , а не , как могло бы показаться более правильным. В таблице SQLite это поле определено как уникальный идентификатор типа INTEGER, что в C# соответствует типу

Если вы попробуете привести значение этого поля к int, то получите исключение:

HResult=0x80004002 Сообщение = Unable to cast object of type ‘System.Int64’ to type ‘System.Int32’. Источник = System.Private.CoreLib

Создание таблиц БД SQLite в C#

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

static void Main(string[] args)
{
    if (Connect("firstBase.sqlite"))
    {
        Console.WriteLine("Connected");
        command = new SQLiteCommand(connection)
        {
            CommandText = "CREATE TABLE IF NOT EXISTS ( INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,  TEXT,  TEXT,  byte);"
        };
        command.ExecuteNonQuery();
        Console.WriteLine("Таблица создана");
    }
}

Для того, чтобы выполнять команды к базе данных SQLite в C#, мы создали новый объект типа , определили текст команды и вызвали метод . Метод в результате возвращает количество строк затронуты выполнением запроса. Теперь у нас есть файл базы данных SQLite, в котором имеется таблица . Попробуем наполнить эту базу данных сведениями о людях.

Реализуйте детали для второго экземпляра записи:

В SQLite есть таблица блокировок, позволяющая блокировать различные базы данных записи в последний момент для обеспечения максимального параллелизма.

Исходное состояние «UNLOCKED», и в этом состоянии соединение еще не имеет доступа к базе данных. Когда база данных подключена к базе данных и даже транзакция была запущена с BEGIN, соединение все еще находится в состоянии «UNLOCKED».

Следующее состояние разблокированного состояния — это состояние SHARED. Чтобы иметь возможность считывать (не записывать) данные из базы данных, соединение должно сначала войти в состояние SHARED, то есть сначала получить блокировку SHARED. Несколько соединений могут одновременно получать и поддерживать блокировки SHARED, то есть несколько соединений могут одновременно считывать данные из одной и той же базы данных. Но даже если только одна блокировка SHARED не была снята, она не позволяет никакому соединению записывать базу данных.

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

_
Одновременно может быть активна только одна зарезервированная блокировка, хотя несколько блокировок SHARED могут сосуществовать с одной зарезервированной блокировкой. RESERVED отличается от PENDING тем, что новые замки SHARED могут быть получены при наличии зарезервированного замка.
_

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

_ _
Блокировка PENDING означает, что процесс, удерживающий блокировку, хочет выполнить запись в базу данных как можно скорее и просто ожидает от всех текущих блокировок SHARED, чтобы снять ее, чтобы получить блокировку EXCLUSIVE. Новые блокировки SHARED не разрешены для базы данных, если активна блокировка PENDING, хотя существующие блокировки SHARED могут продолжаться.

ЭКСКЛЮЗИВНАЯ блокировка необходима для записи в файл базы данных. Только одна ИСКЛЮЧИТЕЛЬНАЯ блокировка разрешена для файла, и никакие другие блокировки любого вида не могут сосуществовать с ИСКЛЮЧИТЕЛЬНОЙ блокировкой. Чтобы максимизировать параллелизм, SQLite работает, чтобы минимизировать время, в течение которого удерживаются ИСКЛЮЧИТЕЛЬНЫЕ блокировки.
_ _

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

Отображение списка данных по запросу

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

Делается это через обычный адаптер. Особенно не буду перегружать объяснением.

В добавляем :

Создадим файл разметки , в котором опишем внешний вид одного элемента списка с таким содержанием:

Добавим, например, в метод onCreate главной активности код:

Запускаем приложение. Видим список наших клиентов:

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

Операторы сравнения SQLite

Предполагая, что переменная a = 10 и переменная b = 20, тогда:

Оператор	описание	Пример
==	Проверьте, равны ли значения двух операндов, если они равны, условие становится истинным.	(a == b) неверно.
=	Проверьте, равны ли значения двух операндов, если они равны, условие становится истинным.	(a = b) неверно.
!=	Проверьте, равны ли значения двух операндов, если не равны, то условие становится истинным.	(a! = b) верно.
<>	Проверьте, равны ли значения двух операндов, если не равны, то условие становится истинным.	(a <> b) верно.
>	Проверить, больше ли значение левого операнда, чем значение правого операнда, если да, условие становится истинным.	(a> b) неверно.
<	Проверьте, меньше ли значение левого операнда, чем значение правого операнда, если да, условие становится истинным.	(a <b) верно.
>=	Проверьте, больше ли значение левого операнда или равно значению правого операнда, если да, условие становится истинным.	(a> = b) неверно.
<=	Проверить, меньше ли значение левого операнда или равно значению правого операнда, если да, условие становится истинным.	(a <= b) верно.
!<	Проверить, не меньше ли значение левого операнда, чем значение правого операнда, если да, условие становится истинным.	(a! <b) ложно.
!>	Проверить, не превышает ли значение левого операнда значение правого операнда, если да, условие становится истинным.	(a!> b) верно.

likelihood(X,Y)

Функция likelihood(X,Y) возвращает аргумент X без изменений. Значение Y в likelihood(X, Y) должно быть константой с плавающей точкой между 0.0 и 1.0 включительно. Функция likelihood(X) — это функция no-op, которую генератор кода оптимизирует, чтобы она не потребляла циклов ЦП во время выполнения (то есть во время вызовов sqlite3_step()). likelihood(х,Y) функция должна давать подсказку планировщику, что аргумент x является логическим значением True с вероятностью приблизительно Y. unlikely(х) функция — краткая запись likelihood(х,0.0625). Функция likely(X) является краткой записью likelihood(X,0.9375).

Понимание SQLite-подключения в подробностях

Эта строка импортирует в программу модуль sqlite3. С помощью классов и методов из этого модуля можно взаимодействовать с базой данных SQLite.

С помощью метода выполняется подключение к базе данных. Этот метод возвращает объект подключения SQLite.
Объект не является потокобезопасным. Модуль sqlite3 не позволяет делиться подключением между потоками. Если попытаться сделать это, то можно получить исключение.
Метод принимает разные аргументы. В этом примере передается название базы данных.

С помощью объекта соединения создается объект , который позволяет выполнять SQLite-запросы из Python.
Для одного соединения можно создать неограниченное количество cursor. Он также не является потокобезопасным. Модуль не позволяет делиться объектами cursor между потоками. Если это сделать, то будет ошибка.

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

С помощью метода объекта можно выполнить запрос в базу данных из Python. Он принимает SQLite-запрос в качестве параметра и возвращает — то есть, строки базы данных
Получить результат запроса из можно с помощью методов, например,
В этом примере выполняется для получения версии базы данных SQLite.

Блок try-except-finally: весь код расположен в блоке try-except, что позволит перехватить исключения и ошибки базы данных, которые могут появиться в процессе.

С помощью класса можно обработать любую ошибку и исключение, которые могут появиться при работе с SQLite из Python.
Это позволит сделать приложение более отказоустойчивым. Класс позволит понять суть ошибки. Он возвращает сообщение и код ошибки.

Хорошей практикой считается закрывать объекты connection и curosor после завершения работы, чтобы избежать проблем с базой данных.

Более сложные запросы

Для выполнения других операций с данными можно использовать следующие методы.

INSERT — добавляет новый объект в базу данных.
Получить T > — пытается получить объект с помощью первичного ключа.
Таблица T > — возвращает все объекты в таблице.
Удалить — удаляет объект, используя его первичный ключ.
Запрос T > — выполнение SQL запроса, возвращающего несколько строк (в виде объектов).
Execute — используйте этот метод (а не ), если не предполагается, что строки возвращаются из SQL (например, инструкции INSERT, UPDATE и DELETE).

Выбор объекта с помощью LINQ

Методы, возвращающие поддержку коллекций, поэтому можно использовать LINQ для запроса или сортировки содержимого таблицы. В следующем коде показан пример использования LINQ для фильтрации всех записей, начинающихся с буквы «A»:

Выбор объекта с помощью SQL

Несмотря на то, что SQLite.Net может предоставлять доступ к данным на основе объектов, иногда может потребоваться более сложный запрос, чем разрешено LINQ (или может потребоваться более высокая производительность). с помощью команды SQL можно использовать метод Query, как показано ниже:

Примечание

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

Удаление объекта

Первичный ключ используется для удаления строки, как показано ниже:

Вы можете проверить, сколько строк было затронуто (удалено в этом случае).

Пример базового доступа к данным

Пример кода DataAccess_Basic для этого документа выглядит следующим образом при работе в Android. В коде показано, как выполнять простые операции SQLite.NET и выводить результаты в виде текста в главном окне приложения.

Android

для Android

В следующем примере кода показано взаимодействие базы данных с использованием библиотеки SQLite.NET для инкапсуляции базового доступа к базе данных.
Он показывает:

Создание файла базы данных
Вставка данных путем создания объектов и последующего их сохранения
Запрос данных

Необходимо включить следующие пространства имен:

Последний требует добавления SQLite в проект

Обратите внимание, что таблица базы данных SQLite определяется путем добавления атрибутов к классу ( классу), а не CREATE TABLE команде

Использование атрибута без указания параметра имени таблицы приведет к тому, что имя базовой таблицы базы данных будет совпадать с именем класса (в данном случае — «фондовой биржи»)

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

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

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

Python

import sqlite3

conn = sqlite3.connect(«mydatabase.db») # или :memory: чтобы сохранить в RAM
cursor = conn.cursor()

# Создание таблицы
cursor.execute(«»»CREATE TABLE albums
(title text, artist text, release_date text,
publisher text, media_type text)
«»»)

1
2
3
4
5
6
7
8
9
10

importsqlite3

conn=sqlite3.connect(«mydatabase.db»)# или :memory: чтобы сохранить в RAM

cursor=conn.cursor()

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

cursor.execute(«»»CREATE TABLE albums

(title text, artist text, release_date text,
publisher text, media_type text)

«»»)

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

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

Python

# Вставляем данные в таблицу
cursor.execute(«»»INSERT INTO albums
VALUES (‘Glow’, ‘Andy Hunter’, ‘7/24/2012’,
‘Xplore Records’, ‘MP3’)»»»
)

# Сохраняем изменения
conn.commit()

# Вставляем множество данных в таблицу используя безопасный метод «?»
albums = [(‘Exodus’, ‘Andy Hunter’, ‘7/9/2002’, ‘Sparrow Records’, ‘CD’),
(‘Until We Have Faces’, ‘Red’, ‘2/1/2011’, ‘Essential Records’, ‘CD’),
(‘The End is Where We Begin’, ‘Thousand Foot Krutch’, ‘4/17/2012’, ‘TFKmusic’, ‘CD’),
(‘The Good Life’, ‘Trip Lee’, ‘4/10/2012’, ‘Reach Records’, ‘CD’)]

cursor.executemany(«INSERT INTO albums VALUES (?,?,?,?,?)», albums)
conn.commit()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# Вставляем данные в таблицу

cursor.execute(«»»INSERT INTO albums

VALUES (‘Glow’, ‘Andy Hunter’, ‘7/24/2012’,
‘Xplore Records’, ‘MP3’)»»»

)

# Сохраняем изменения

conn.commit()

# Вставляем множество данных в таблицу используя безопасный метод «?»

albums=(‘Exodus’,’Andy Hunter’,’7/9/2002′,’Sparrow Records’,’CD’),

(‘Until We Have Faces’,’Red’,’2/1/2011′,’Essential Records’,’CD’),

(‘The End is Where We Begin’,’Thousand Foot Krutch’,’4/17/2012′,’TFKmusic’,’CD’),

(‘The Good Life’,’Trip Lee’,’4/10/2012′,’Reach Records’,’CD’)

cursor.executemany(«INSERT INTO albums VALUES (?,?,?,?,?)»,albums)

conn.commit()

Здесь мы использовали команду INSERT INTO SQL чтобы вставить запись в нашу базу данных

Обратите внимание на то, что каждый объект находится в одинарных кавычках. Это может усложнить работу, если вам нужно вставить строчки, которые содержат одинарные кавычки

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

6.3.Дополнительные соображения для пользовательских запросов

MATCH-оператор пользовательской функции R*Tree запроса должен быть И-связанным термином верхнего уровня пункта WHERE,иначе он не будет использоваться оптимизатором R*Tree запроса и запрос не будет запущен.Если,например,MATCH-оператор связан с другими терминами выражения «WHERE» через оператор «ИЛИ»,то запрос не будет выполнен с ошибкой.

Два или более Сопоставимых операторов разрешены в одном и том же пункте WHERE при условии,что они соединены операторами AND.Однако механизм запросов R*Tree содержит только одну приоритетную очередь.Приоритет,присвоенный каждому узлу при поиске,является наименьшим приоритетом,возвращаемым любым из MATCH-операторов.

7.Детали осуществления

В следующих разделах описываются некоторые низкоуровневые детали реализации R*Tree,которые могут быть полезны для поиска неисправностей или анализа производительности.