Инструменты пользователя

Инструменты сайта


python_i_sqlite

Различия

Здесь показаны различия между двумя версиями данной страницы.

Ссылка на это сравнение

python_i_sqlite [2018/11/08 15:24] (текущий)
Строка 1: Строка 1:
 +====== Python и SQLite ======
  
 +{{htmlmetatags>​
 +metatag-description=(Создание базы данных SQLite для Python и работа с ней.)
 +}}
 +
 +Python имеет встроенную поддержку SQLite базы данных,​ для этого вам не надо ничего дополнительно устанавливать,​ достаточно в скрипте указать импорт стандартной библиотеки import sqlite3
 +
 +
 +  * **Установка SQLite для Python Linux Ubuntu**
 +<​code>​
 +# aptitude install sqlite3-doc sqlite3 python-pysqlite2
 +</​code>​
 +Проверяем:​
 +<​code>​
 +# sqlite3 -version
 +3.6.10
 +</​code>​
 +  * **Как узнать версию движка sqlite в котором эта база была создана?​**
 +Имеется база данных (файл) sqlite (например файл БД называется sqlitest). Как узнать версию движка sqlite в котором эта база была создана?​
 +
 +<​code>​
 +# cat sqlitest | less
 +SQLite format 3^@^D^@^A^A^@@
 +...
 +</​code>​
 +В первых 16 байтах написана версия БД. В этом случае версии 3.
 +
 +P.S. Младшие номера версий и уж тем более номера билдов на структуру файла базы не влияют.
 +
 +[[http://​www.sqlite.org/​c3ref/​libversion.html|SQLite C/C++ Interface. Run-Time Library Version Numbers]]
 +  * **Значение констант БД SQlite**
 +<​code>​
 +~$ ipython
 +/​var/​lib/​python-support/​python2.6/​IPython/​Magic.py:​38:​ DeprecationWarning:​ the sets module is deprecated
 +  from sets import Set
 +Python 2.6.2 (release26-maint,​ Apr 19 2009, 01:56:41)
 +Type "​copyright",​ "​credits"​ or "​license"​ for more information.
 +
 +IPython 0.9.1 -- An enhanced Interactive Python.
 +?         -> Introduction and overview of IPython'​s features.
 +%quickref -> Quick reference.
 +help      -> Python'​s own help system.
 +object? ​  -> Details about '​object'​. ?object also works, ?? prints more.
 +In [7]: import sqlite3
 +</​code>​
 +**apilevel** = 2.0 - sqlite поддерживает DB-API 2.0
 +<​code>​
 +In [10]: sqlite3.apilevel
 +Out[10]: '​2.0'​
 +</​code>​
 +**paramstyle** - тип используемых пометок при подстановке параметров. Возможны следующие значения этой константы:​
 +  * **format** форматирование в стиле языка ANSI C (например,​ '​%s',​ '​%i'​)
 +  * **pyformat** использование именованных спецификаторов формата в стиле Python ('​%(item)s'​)
 +  * **qmark** использование знаков '?'​ для пометки мест подстановки параметров
 +  * **numeric** использование номеров позиций (':​1'​)
 +  * **named** использование имен подставляемых параметров (':​name'​)
 +<​code>​
 +In [11]: sqlite3.paramstyle
 +Out[11]: '​qmark'​
 +</​code>​
 +**threadsafety** - целочисленная константа,​ описывающая возможности модуля при использовании потоков управления:​
 +<​code>​
 +   0 Модуль не поддерживает потоки.
 +   1 Потоки могут совместно использовать модуль,​ но не соединения.
 +   2 Потоки могут совместно использовать модуль и соединения.
 +   3 Потоки могут совместно использовать модуль,​ соединения и курсоры. (Под совместным использованием здесь
 +     ​понимается возможность использования упомянутых ресурсов без применения семафоров).
 +</​code>​
 +<​code>​
 +In [12]: sqlite3.threadsafety
 +Out[12]: 1
 +</​code>​
 +
 +====== Работа с базой данных SQLite ======
 +В общем случае последовательность работы с БД выглядит так:
 +
 +| №^ Метод ​ ^ Note         ^
 +^1 |Подключение к базе данных (вызов **connect()** с получением объекта-соединения) ​  ​| ​         |
 +^2 |Создание одного или нескольких курсоров (вызов метода объекта-соединения **cursor()** с получением объекта-курсора) |   |
 +^3 |Исполнение команды или запроса (вызов метода **execute()** или его вариантов) ​ |   |
 +^4 |Получение результатов запроса (вызов метода **fetchone()** или его вариантов) ​ |   |
 +^5 |Завершение транзакции или ее откат (вызов метода объекта-соединения **commit()** или **rollback()**) ​ |**commit()** автоматически происходит посде закрытия соединения (после метода **close()**) ​  |
 +^6 |Когда все необходимые транзакции произведены,​ подключение закрывается вызовом метода **close()** объекта-соединения |   |
 +
 +===== DB-API =====
 +Аббревиатура **DB-API** объединяет два понятия:​ DB (Database, база данных) и API (Application Program Interface, интерфейс прикладной программы).
 +DB-API определяет интерфейс прикладной программы с базой данных. Этот интерфейс должен реализовывать все модули расширения,​ которые ​
 +служат для связи Python-программ с базами данных. Единый API позволяет абстрагироваться от марки используемой базы данных,​ при
 +необходимости довольно легко менять одну СУБД на другую,​ изучив всего один набор функций и методов.
 +  * Ссылка на описание текущей версии DB-API: [[http://​www.python.org/​dev/​peps/​pep-0249/​|Python Database API Specification v2.0]]
 +  * Ссылка на список поддерживаемых БД, через модули расширения [[http://​wiki.python.org/​moin/​DatabaseInterfaces|DatabaseInterfaces]]
 +  * Модуль расширения **pysqlite** [[http://​wiki.python.org/​moin/​SQLite |pysqlite DB API 2.0 Drivers for SQLite]]
 +
 +==== Объект-курсор ====
 +<​code>​
 +Курсор (от англ. cursor - CURrrent Set Of Records, текущий набор записей) служит для
 +работы с результатом запроса. Результатом запроса обычно является одна или несколько
 +прямоугольных таблиц со столбцами-полями и строками-записями. Приложение может
 +читать и обрабатывать полученные таблицы и записи в таблице по одной, поэтому в
 +курсоре хранится информация о текущей таблице и записи. Конкретный курсор в любой
 +момент времени связан с выполнением одной SQL-инструкции.
 +Атрибуты объекта-курсора тоже определены DB-API:
 +   ​• ​ arraysize Атрибут,​ равный количеству записей,​ возвращаемых методом
 +      fetchmany(). По умолчанию равен 1.
 +   ​• ​ callproc(procname[,​ params]) Вызывает хранимую процедуру procname с
 +      параметрами из изменчивой последовательности params. Хранимая процедура может
 +      изменить значения некоторых параметров последовательности. Метод может
 +      возвратить результат,​ доступ к которому осуществляется через fetch-методы.
 +   ​• ​ close() Закрывает объект-курсор.
 +   ​• ​ description Этот доступный только для чтения атрибут является
 +      последовательностью из семиэлементных последовательностей. Каждая из этих
 +      последовательностей содержит информацию,​ описывающую один столбец
 +      результата:​
 +   ​• ​ (name, type_code, display_size,​ internal_size,​ precision, scale, null_ok)
 +      Первые два элемента (имя и тип) обязательны,​ а вместо остальных (размер для
 +      вывода,​ внутренний размер,​ точность,​ масштаб,​ возможность задания пустого
 +      значения) может быть значение None. Этот атрибут может быть равным None для
 +      операций,​ не возвращающих значения.
 +   ​• ​ execute(operation[,​ parameters]) Исполняет запрос к базе данных или команду
 +      СУБД. Параметры (parameters) могут быть представлены в принятой в базе данных
 +      нотации в соответствии с атрибутом paramstyle, описанным выше.
 +   ​• ​ executemany(operation,​ seq_of_parameters) Выполняет серию запросов или
 +      команд,​ подставляя параметры в заданный шаблон. Параметр seq_of_parameters
 +      задает последовательность наборов параметров.
 +   ​• ​ fetchall() Возвращает все (или все оставшиеся) записи результата запроса.
 +   ​• ​ fetchmany([size]) Возвращает следующие несколько записей из результатов
 +      запроса в виде последовательности последовательностей. Пустая
 +      последовательность означает отсутствие данных. Необязательный параметр size
 +      указывает количество возвращаемых записей (реально возвращаемых записей
 +      может быть меньше). По умолчанию size равен атрибуту arraysize объекта-
 +      курсора.
 +   ​• ​ fetchone() Возвращает следующую запись (в виде последовательности) из
 +      результата запроса или None при отсутствии данных.
 +   ​• ​ nextset() Переводит курсор к началу следующего набора данных,​ полученного в
 +      результате запроса (при этом часть записей в предыдущем наборе может остаться
 +      непрочитанной). Если наборов больше нет, возвращает None. Не все базы данных
 +      поддерживают возврат нескольких наборов результатов за одну операцию.
 +   ​• ​ rowcount Количество записей,​ полученных или затронутых в результате выполнения
 +      последнего запроса. В случае отсутствия execute-запросов или невозможности
 +      указать количество записей равен -1.
 +   ​• ​ setinputsizes(sizes) Предопределяет области памяти для параметров,​
 +      используемых в операциях. Аргумент sizes задает последовательность,​ где каждый
 +      элемент соответствует одному входному параметру. Элемент может быть объектом-
 +      типом соответствующего параметра или целым числом,​ задающим длину строки. Он
 +      также может иметь значение None, если о размере входного параметра ничего
 +      нельзя сказать заранее или он предполагается очень большим. Метод должен быть
 +      вызван до execute-методов.
 +   ​• ​ setoutputsize(size[,​ column]) Устанавливает размер буфера для выходного
 +      параметра из столбца с номером column. Если column не задан, метод устанавливает
 +размер для всех больших выходных параметров. Может использоваться,​ например,​
 +для получения больших бинарных объектов (Binary Large Object, BLOB).
 +</​code>​
 +==== Типы данных в SQLite version 3.0====
 +**Типы данных в DB-API:**
 +
 +Спецификация [[http://​www.python.org/​dev/​peps/​pep-0249/​|Python Database API Specification v2.0]] предусматривает названия для объектов-типов,​ используемых для описания полей базы данных:​
 +
 +^Объект ^Тип ​ ^
 +^STRING |Строка и символ ​   |
 +^BINARY |Бинарный объект ​   |
 +^NUMBER |Число ​   |
 +^DATETIME |Дата и время ​   |
 +^ROWID |Идентификатор записи |
 +^None |NULL-значение (отсутствующее значение) |
 +
 +С каждым типом данных (в реальности это - классы) связан конструктор. Совместимый с DB-API модуль должен определять следующие конструкторы:​
 +  * Date(год,​ месяц, день) Дата.
 +  * Time(час,​ минута,​ секунда) Время.
 +  * Timestamp(год,​ месяц, день, час, минута,​ секунда) Дата-время.
 +  * DateFromTicks(secs) Дата в виде числа секунд secs от начала эпохи (1 января 1970 года).
 +  * TimeFromTicks(secs) Время, то же.
 +  * TimestampFromTicks(secs) Дата-время,​ то же.
 +  * Binary(string) Большой бинарный объект на основании строки string.
 +
 +**Типы данных в SQLite version 3.0:**
 +  * [[http://​www.sqlite.org/​datatype3.html|Datatypes In SQLite Version 3]]
 +  * [[http://​www.sqlite.org/​version3.html|SQLite Version 3 Overview]]
 +
 +Типы данных полей таблиц в SQLite -**декларативные**,​ то есть независимо от того какой тип данных для поля был указан при создании таблицы в этом поле могут сохраняться значения разных типов, при этом SQLite сам определяет тип данных для сохранения значения поля. Задание типа данных поля таблицы при создании указывает SQLite на **предпочтительный тип** данных для сохранения значений.
 +
 +Чтобы избежать путаницы для типов данных полей таблиц лучше использовать базовые типы данных SQLite:
 +^Объект ^Тип ​ ^Описание ^ Description ^
 +^NULL |базовый ​   |NULL-значение (отсутствующее значение) |The value is a NULL value. |
 +^INTEGER |базовый ​   |Числовой тип данных (целые положительные или отрицательные числа). Данный тип данных имеет переменный размер 1,2,3,4,6 или 8 байтов. Максимальный размер для данных данного типа состовляет 8 байтов и может хранить числовые значения в диапазоне [-9223372036854775808,​-1,​0,​1,​-9223372036854775807]. SQLite автоматически изменяет размер данного типа данных в зависимости от значения. |The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. |
 +^REAL|базовый ​   |Числовой тип данных имеющий размер 8 байтов и может хранить любые (в том числе и не целые) числа. |The value is a floating point value, stored as an 8-byte IEEE floating point number.|
 +^TEXT |базовый ​   |Текстовый тип данных который может хранить текстовые строки произвольной длины в кодировке UTF-8 или UTF-16. Максимальная длина строки для данного типа данных не лимитирована. **Этот же тип данных используется для хранения даты и времени**. |The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE). |
 +^BLOB |базовый ​   |Тип данных для хранения двоичных объектов. Максимальный размер данных данного типа не лимитирован. |The value is a blob of data, stored exactly as it was input. |
 +^ |базовый ​   | | |
 +
 +Значительные отличия между SQLite и другими базами данных находятся в самом движке. В отличие от других БД в SQLite нет привязки к типам; все данные сохраняются как строки оканчивающиеся символом NULL, что лучше, чем двоичное представление данных в столбцах специального типа. По причине совместимости SQLite поддерживает тип спецификации в конструкциях CREATE TABLE, например,​ такой как INT, CHAR, FLOAT, TEXT и тому подобные,​ но реально их не использует. Внутри БД, SQLite только делает различие между строковыми и целочисленными данными во время сортировки. Поэтому,​ если вы не собираетесь сортировать данные,​ вы можете обойтись без указания специального типа полей при создании таблиц в SQLite.
 +
 +"​Безтиповая природа"​ SQLite делает сортировку и сопоставление данных в некотором роде медленнее,​ так как каждый раз SQLite будет вынуждена определять тип данных и применять либо строковый механизм сортировки/​сравнения либо числовой. SQL таблицы часто требуют автоматически присваиваемый ключ для быстрого доступа к ячейкам,​ подразумевая возврат ссылки на последнюю добавленную ячейку. Для SQLite этот синтаксис,​ мягко говоря,​ бесполезен. Чтобы создать такую таблицу,​ вам понадобится объявить поле как INTEGER PRIMARY KEY, что более удобно,​ чем указание специализированного типа или присваивание дополнительных свойств,​ которые указывают на то, что поле является автоинкрементным.
 +
 +**Примеры**:​
 +<​code>​
 +$ sqlite3
 +SQLite version 3.6.10
 +Enter "​.help"​ for instructions
 +Enter SQL statements terminated with a ";"​
 +sqlite>
 +</​code>​
 +<​code>​
 +sqlite> select typeof(314),​ typeof(3.14),​ typeof('​3.14'​),​ typeof(x'​3142'​);​
 +integer|real|text|blob
 +</​code>​
 +<​code>​
 +sqlite> select CURRENT_DATE,​ CURRENT_TIME,​ CURRENT_TIMESTAMP;​
 +2009-09-20|11:​39:​19|2009-09-20 11:39:19
 +</​code>​
 +<​code>​
 +sqlite> select typeof(CURRENT_DATE),​ typeof(CURRENT_TIME),​ typeof(CURRENT_TIMESTAMP);​
 +text|text|text
 +</​code>​
 +<​code>​
 +sqlite> create table test (i integer, r real, t text, b blob);
 +sqlite> insert into test values (3142, 3142, 3142, 3142);
 +sqlite> insert into test values (3.142, 3.142, 3.142, 3.142);
 +sqlite> insert into test values ('​3.142',​ '​3.142',​ '​3.142',​ '​3.142'​);​
 +sqlite> insert into test values (x'​3142',​ x'​3142',​ x'​3142',​ x'​3142'​);​
 +sqlite> insert into test values (null, null, null, null);
 +
 +sqlite> select rowid, i, r, t, b from test;
 +1|3142|3142.0|3142|3142
 +2|3.142|3.142|3.142|3.142
 +3|3.142|3.142|3.142|3.142
 +4|1B|1B|1B|1B
 +5||||
 +
 +sqlite> select rowid, typeof(i), typeof(r), typeof(t), typeof(b) from test;
 +1|integer|real|text|integer
 +2|real|real|text|real
 +3|real|real|text|text
 +4|blob|blob|blob|blob
 +5|null|null|null|null
 +
 +sqlite> .quit
 +</​code>​
 +
 +===== Пример:​ создание БД SQLite =====
 +В случае с SQLite заботиться о создании базы данных не нужно, файл будет создан автоматически. Для других баз данных необходимо перед этим создать базу данных,​ например,​ SQL-инструкцией CREATE DATABASE).
 +  - Создание из командной строки. Пусть у нас есть файл dbcreate164.sql,​ содержащий текст:<​code>​$ cat dbcreate164.sql
 +BEGIN TRANSACTION;​
 +CREATE TABLE testqualuty (code NUMERIC, countryall TEXT, date NUMERIC, maxe164 NUMERIC, mine164 NUMERIC, rprice NUMERIC);
 +COMMIT;</​code>​Тогда БД testbdsqlite.db можно создать из командной строки,​ такой командой<​code>​$ sqlite3 testbdsqlite.db < dbcreate164.sql</​code>​
 +  - Создание БД test.db через [[CLI]] SQLite<​code>​$ sqlite3 test.db
 +SQLite version 3.6.10
 +Enter "​.help"​ for instructions
 +Enter SQL statements terminated with a ";"​
 +sqlite> CREATE TABLE testqualuty (code NUMERIC, countryall TEXT, date NUMERIC, maxe164 NUMERIC, mine164 NUMERIC, rprice NUMERIC);
 +sqlite> .quit</​code>​
 +  - Создание из Python <file python>
 +# Импортируем библиотеку,​ соответствующую типу нашей базы данных ​
 +import sqlite3
 +
 +# Создаем соединение с нашей базой данных
 +# В нашем примере у нас это просто файл базы
 +conn = sqlite3.connect('​Chinook_Sqlite.sqlite'​)
 +
 +# Создаем курсор - это специальный объект который делает запросы и получает их результаты
 +cursor = conn.cursor()
 +
 +# ТУТ БУДЕТ НАШ КОД РАБОТЫ С БАЗОЙ ДАННЫХ
 +# КОД ДАЛЬНЕЙШИХ ПРИМЕРОВ ВСТАВЛЯТЬ В ЭТО МЕСТО
 +
 +# Не забываем закрыть соединение с базой данных
 +conn.close()
 +</​file>​
 +
 +====== Ссылки ======
 +  * [[SQLite, Python - способы хранения,​ обработки данных о датах и времени]]
 +  * [[http://​www.python.org/​dev/​peps/​pep-0249/​|Python Database API Specification v2.0]]
python_i_sqlite.txt · Последние изменения: 2018/11/08 15:24 (внешнее изменение)