Sqlite �������� ��� ������

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

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

Модуль 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 прекрасно подойдет для проектов у которых мало операций записи, не нужна система прав доступа к БД и ограниченны ресурсы сервера.

Источник

DB Browser for SQLite

The Official home of the DB Browser for SQLite

Screenshot

What it is

DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.

DB4S is for users and developers who want to create, search, and edit databases. DB4S uses a familiar spreadsheet-like interface, and complicated SQL commands do not have to be learned.

Controls and wizards are available for users to:

• Create and compact database files
• Create, define, modify and delete tables
• Create, define, and delete indexes
• Browse, edit, add, and delete records
• Search records
• Import and export records as text
• Import and export tables from/to CSV files
• Import and export databases from/to SQL dump files
• Issue SQL queries and inspect the results
• Examine a log of all SQL commands issued by the application
• Plot simple graphs based on table or query data

What it is not

This program is not a visual shell for the sqlite command line tool, and does not require familiarity with SQL commands. It is a tool to be used by both developers and end users, and must remain as simple to use as possible in order to achieve these goals.

Источник

Национальная библиотека им. Н. Э. Баумана
Bauman National Library

Персональные инструменты

SQLite

SQLite

Разработчики:	D. Richard Hipp
Выпущена:	August 2000 ( 2000-08 )
Написана на:	C
Операционная система:	Кроссплатформенная
Размер дистрибутива:	699 KiB
Тип ПО:	RDBMS (embedded)
Лицензия:	Public domain [1]
Веб-сайт	www .sqlite .org

SQLite — это встраиваемая кроссплатформенная БД, которая поддерживает достаточно полный набор команд SQL и доступна в исходных кодах (на языке C).

Содержание

Общее

SQLite – это встраиваемая библиотека в которой реализовано многое из стандарта SQL 92. Её притязанием на известность является как собственно сам движок базы, так и её интерфейс (точнее его движок) в пределах одной библиотеки, а также возможность хранить все данные в одном файле. Позиция функциональности SQLite где-то между MySQL и PostgreSQL. Однако, на практике, SQLite нередко оказывается в 2-3 раза (и даже больше) быстрее. Такое возможно благодаря высокоупорядоченной внутренней архитектуре и устранению необходимости в соединениях типа «сервер-клиент» и «клиент-сервер».

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

Преимущества

1. Очень популярна
2. Очень надежна
3. Консольная утилита для работы с базами
4. Открытые исходные коды

Недостатки

1. Нет хранимых процедур.
2. Нет встроенной поддержки UNICODE
3. Не подходит для приложений, которые часто обращаются в базу

SQLite поддерживает динамическое типизирование данных.

Возможные типы полей

Архитектура

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

Несколько процессов или потоков могут одновременно без каких-либо проблем читать данные из одной базы. Запись в базу можно осуществить только в том случае, если никаких других запросов в данный момент не обслуживается; в противном случае попытка записи оканчивается неудачей, и в программу возвращается код ошибки. Другим вариантом развития событий является автоматическое повторение попыток записи в течение заданного интервала времени.

Особенности

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

Использование SQLite в многопоточных приложениях

SQLite может быть собран в однопоточном варианте (параметр компиляции SQLITE_THREADSAFE = 0 ). В этом варианте его нельзя одновременно использовать из нескольких потоков, поскольку полностью отсутствует код синхронизации. Проверить, есть ли многопоточность можно через вызов sqlite3_threadsafe(): если вернула 0, то это однопоточный SQLite. По умолчанию, SQLite собран с поддержкой потоков (sqlite3.dll). Есть два способа использования многопоточного SQLite: serialized и multi-thread.

Serialized (надо указать флаг SQLITE_OPEN_FULLMUTEX при открытии соединения). В этом режиме потоки могут как угодно дергать вызовы SQLite, никаких ограничений. Но все вызовы блокируют друг друга и обрабатываются строго последовательно.

Multi-thread ( SQLITE_OPEN_NOMUTEX ). В этом режиме нельзя использовать одно и то же соединение одновременно из нескольких потоков (но допускается одновременное использование разных соединений разными потоками). Обычно используется именно этот режим.

Формат данных

База данных SQLite может хранить (текстовые) данные в UTF-8 или UTF-16. Набор вызовов API состоит из вызовов, которые получают UTF-8 (sqlite3_XXX) и вызовов, которые получают UTF-16 (sqlite3_XXX16). Если тип данных интерфейса и соединения не совпадает, то выполняется конвертация «на лету».

Поддержка UNICODE

По умолчанию — поддержки нет. Нужно создать свой collation (способ сравнения) через sqlite3_create_collation . И определить свои встроенные функции like(), upper(), lower() через www.sqlite.org/c3ref/create_function.html. Есть проект «International Components for Unicode», ICU и некоторые собирают SQLite DLL уже с ним.

Немного про работу ICU и SQLite.

Значение внутри БД может принадлежать к одному из следующих типов хранения (storage class): NULL , INTEGER (занимает 1,2,3,4,6 или 8 байт), REAL (число с плавающей точкой, 8 байт в формате IEEE), TEXT (строка в формате данных базы, обычно UTF-8), BLOB (двоичные данные, хранятся «как есть»).

Порядок сортировки значений разных типов:

• NULL меньше всего (включая другой NULL );
• INTEGER и REAL меньше любого TEXT и BLOB , между собой сравниваются арифметически;
• TEXT меньше любого BLOB , между собой сравниваются на базе своих collation;
• BLOB сравниваются между собой через memcmp() .

SQLite выполняет неявные преобразования типов «на лету» в нескольких местах:

• при занесении значения в столбец (тип столбца задает рекомендацию по преобразованию);
• при сравнении значений между собой.

Столбец может иметь следующие рекомендации приведения типа: TEXT, NUMERIC, INTEGER, REAL, NONE .

Значения BLOB и NULL всегда заносятся в любой столбец «как есть».

В столбец TEXT значения TEXT заносятся «как есть», значения INTEGER и REAL становятся строками. В столбец NUMERIC , INTEGER числа записываются «как есть», а строки становятся числами, если могут (то есть допустимо обратное преобразование «без потерь»). Для столбца REAL правила похожи на INTEGER(NUMERIC) ; отличие в том, что все числа представлены в формате с плавающей запятой. В столбец NONE значения заносятся «как есть» (этот тип используется по умолчанию, если не задан другой).

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

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

В SQLite в уникальном индексе может быть сколько угодно NULL значений (с этим согласен Oracle и не согласен MS SQL).

Если в вызове sqlite3_open() передать имя файла как «:memory:», то SQLite создаст соединение к новой (чистой) БД в памяти. Это соединение абсолютно неотличимо от соединения к БД в файле по логике использования: доступен тот же набор SQL команд. Сейчас это исправлено и можно открыть два соединения к одной БД в памяти.

Чтобы открыть соединение к БД используется вызов sqlite3_open(). В любой момент времени мы можем к открытому соединению присоединить еще до 10 баз данных через SQL команду ATTACH DATABASE .

Теперь все таблицы БД в файле db1.sqlite3 стали прозрачно доступны в нашем соединении. Для разрешения конфликтов имен следует использовать имя присоединения (основная база называется «main»):

Ничего не мешает присоединить к БД новую базу в памяти и использовать ее для кэширования и пр.

Передайте пустую строку вместо имени файла в sqlite3_open() и будет создана временная БД в файле на диске. Причем, после закрытия соединения к БД, она будет удалена с диска.

SQL команда PRAGMA служит для задания всевозможных настроек у соединения или у самой БД:

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

Журнал и фиксация транзакций

SQLite тщательно блюдет целостность данных в БД (ACID), реализуя механизм изменения данных через транзакции. Кратко о транзакциях: транзакция либо полностью накатывается, либо полностью откатывается. Промежуточных состояний быть не может. Если вы не используете транзакции явно ( BEGIN; . ; COMMIT; ), то всегда создается неявная транзакция. Она стартует перед выполнением команды и коммитится сразу после. Отсюда жалобы на «медленность» SQLite. SQLite может вставлять и до 50 тыс записей в секунду, но фиксировать транзакций он не может больше, чем

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

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

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

Если SQLite открывает соединение к БД и видит, что журнал уже есть, он соображает, что БД находится в незавершенном состоянии и автоматически откатывает последнюю транзакцию. То есть механизм восстановления БД после сбоев, фактически, встроен в SQLite и работает незаметно для пользователя. По умолчанию журнал ведется в режиме DELETE .

PRAGMA journal_mode = DELETE

Это означает, что файл журнала удаляется после завершения транзакции. Сам факт наличия файла с журналом в этом режиме означает для SQLite, что транзакция не была завершена, база нуждается в восстановлении. Файл журнала имеет имя файла БД, к которому добавлено «-journal».

В режиме TRUNCATE файл журнала обрезается до нуля (на некоторых системах это работает быстрее, чем удаление файла).

В режиме PERSIST начало файла журнала забивается нулями (при этом его размер не меняется и он может занимать кучу места).

В режиме MEMORY файл журнала ведется в памяти и это работает быстро, но не гарантирует восстановление базы при сбоях (копии данных-то нету на диске).

А можно и совсем отключить журнал ( PRAGMA journal_mode = OFF ). В этой ситуации перестает работать откат транзакций (команда ROLLBACK ) и база, скорее всего, испортится, если программа будет завершена аварийно. Для базы данных в памяти режим журнала может быть только либо MEMORY , либо OFF .

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

PRAGMA synchronous задает степень «паранойи» SQLite на это счет.

Режим OFF (или 0) означает: SQLite считает, что данные фиксированы на диске сразу после того как он передал их ОС (то есть сразу после вызова соот-го API ОС). Это означает, что целостность гарантирована при аварии приложения (поскольку ОС продолжает работать), но не при аварии ОС или отключении питания.

Режим синхронизации NORMAL (или 1) гарантирует целостность при авариях ОС и почти при всех отключениях питания. Существует ненулевой шанс, что при потере питания в самый неподходящий момент база испортится. Это некий средний, компромисный режим по производительности и надежности.

Режим FULL гарантирует целостность всегда и везде и при любых авариях. Но работает, разумеется, медленнее, поскольку в определенных местах делаются паузы ожидания. И это режим по умолчанию.

Режим журнала WAL

По умолчанию, режим журнала БД всегда «возвращается» в DELETE . Допустим, мы открыли соединение к БД и установили режим PERSIST . Изменили данные, закрыли соединение. На диске остался файл журнала (начало которого забито нулями). Открываем соединение к БД снова. Если не задать режим журнала в этом соединении, он опять будет работать в DELETE . Как только мы обновим данные, механизм фиксации транзакций сотрет файл журнала.

Режим журнала WAL работает иначе — он «постоянный». Как только мы перевели базу в режим WAL, она останется в этом режиме, пока ей явно не поменяют режим журнала на другой.

Изначально SQLite проектировалась как встроенная БД. Архитектура разделения одновременного доступа к данным была устроена примитивно: одновременно несколько соединений могут читать БД, а вот записывать в данный момент времени может только одно соединение. Это, как минимум, означает, что пишущее соединение ждет «освобождения» БД от читающих. При попытке записать в «занятую» БД приложение получает ошибку SQLITE_BUSY (не путать с SQLITE_LOCKED!). Достигается этот механизм разделения доступа через API блокировки файлов (которые плохо работают на сетевых дисках, поэтому там не рекомендуется использовать SQLite; узнать больше )

В режиме WAL (Write-Ahead Logging) «читатели» БД и «писатели» в БД уже не мешают друг другу, то есть допускается модификация данных при одновременном чтении. Короче говоря, это шаг в сторону больших и серьезных СУБД, в которых все так и есть. Утверждается также, что SQLite в WAL работает быстрее.

Но есть и недостатки: — требуется некоторые дополнительные ништяки от ОС (unix и Windows имеют эти ништяки); — БД занимает несколько файлов (файлы «XXX-wal» и «XXX-shm»); — плохо работает на больших транзакциях (условно, если транзакция больше 50 Мбайт); — нельзя открыть такую БД в режиме «только чтение»; — возникает дополнительная операция checkpoint.

Фактически, в режиме WAL данные БД разделяются между БД и файлом журнала. Операция checkpoint переносит данные в БД. По умолчанию, это делается автоматически, если журнал занял 1000 страниц БД. То есть, идут быстрые COMMIT-ы и вдруг какой-то COMMIT задумался и начал делать checkpoint. Если такое поведение нежелательно, можно делать checkpoint вручную (когда все спокойно), можно это делать и в отдельном процессе.

Пределы

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

По умолчанию, BLOB или строкое значение могут занимать 1 Гбайт и это же ограничение размера одной записи (можно поднять до 2^31 — 1, параметр SQLITE_MAX_LENGTH ).

Количество столбцов: 2000 (можно поднять до 32767, SQLITE_MAX_COLUMN ).

Размер SQL оператора: 1 МБайт (1073741824 байт, SQLITE_MAX_SQL_LENGTH ).

Одновременный join: 64 таблицы.

Присоединить баз к соединению: 10 (до 62, SQLITE_MAX_ATTACHED )

Максимальное количество страниц в БД: 1073741823 (до 2147483646, SQLITE_MAX_PAGE_COUNT ).

Если задать размер страницы 65636 байт, то максимальный размер БД будет примерно 14 Терабайт.

Максимальное число записей в таблице: 2^64 — 1, но на практике, конечно, ограничение размера вступит раньше.

Пример

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

Процедурный интерфейс к SQLite почти такой же, как у MySQL и других расширений БД. По большей части переход к SQLite потребует только изменить mysql/pq/etc. префикс функции на sqlite [2] .

Автоматическое резервирование

Пример скрипта, которые раз в день создает резервную копию бд.

Источник