pyDAL – работа с любыми базам данных в Python

Пакет был запущен как часть веб-фреймворка web2py.

Используя pyDAL, ваше приложение сможет взаимодействовать без изменения кода со следующими движками баз данных:

• SQLite
• PostgreSQL
• MySQL
• Oracle
• Microsoft SQL Server
• Firebird
• DB2
• Informix
• Ingres
• Cubrid
• Sybase
• Teradata
• SAPDB
• MongoDB
• IMAP

Как вы можете видеть, пакет даже способен взаимодействовать с базами данных NoSQL, такими как MongoDB и Google NoSQL.

Эта статья поможет вам познакомиться с pyDAL, поняв его возможности и общие принципы.

Установка

Самый простой способ установки pyDAL – с помощью pip:

pip install pydal

В качестве альтернативы вы можете загрузить исходный код из проекта на GitHub и запустить его:

python setup.py install
Code language: CSS (css)

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

Первые шаги

Начнем со следующего кода.

from pydal import DAL, Field
db = DAL("sqlite://storage.db")
Code language: JavaScript (javascript)

Сначала мы импортируем необходимые классы (в большинстве случаев это не более двух). DAL – это основной класс, поэтому далее мы создадим его экземпляр. Field представляет собой поле внутри таблицы.

В pyDAL для подключения к базе данных мы используем так называемый URI (аналогично URL).

Первая часть строки, называемая схемой, указывает на движок базы данных.

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

Для остальных SQL-движков необходимо указать IP-адрес (или домен) сервера, имя базы данных, пользователя и пароль.

В частности, формат URI для каждого из них следующий (взято из документации web2py).

База данных	Формат URI
SQLite	sqlite://bdname.db
MySQL	mysql://user:password@localhost/bdname
PostgreSQL	postgres://user:password@localhost/bdname
MSSQL (legacy)	mssql://user:password@localhost/bdname
MSSQL (>=2005)	mssql3://user:password@localhost/bdname
MSSQL (>=2012)	mssql4://user:password@localhost/bdname
Firebird	firebird://user:password@localhost/bdname
Oracle	oracle://user/password@bdname
DB2	db2://user:password@bdname
Ingres	ingres://user:password@localhost/bdname
Sybase	sybase://user:password@localhost/bdname
Informix	informix://user:password@bdname
Teradata	teradata://DSN=dsn;UID=user;PWD=password;DATABASE=bdname
Cubrid	cubrid://user:password@localhost/bdname
SAPDB	sapdb://user:password@localhost/bdname
IMAP	imap://user:password@server:port
MongoDB	mongodb://user:password@localhost/bdname
Google/SQL	google:sql://project:instance/database
Google/NoSQL	google:datastore
Google/NoSQL/NDB	google:datastore+ndb

Обратите внимание, что pyDAL требует, чтобы базы данных были уже созданы, за исключением SQLite, который автоматически создаст файл, если он не существует.

Для подключения к конкретной базе данных pyDAL работает с установленным драйвером.

Например, для SQLite он использует модуль sqlite3, который является частью стандартной библиотеки.

Для MySQL он может работать с pymysql или MySQLdb.

Для PostgreSQL – psycopg2 или pg8000.

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

Если для одной и той же базы данных существует два модуля, мы можем принудить pyDAL использовать один из них.

Например, если установлены и psycopg2, и pg8000, мы можем явно указать pyDAL использовать второй.

db = DAL("postgres:pg8000://user:password@localhost/bdname")
Code language: JavaScript (javascript)

В противном случае pyDAL выберет один из них случайным образом. (По моему опыту, для конкретного случая PostgreSQL предпочтение отдается psycopg2).

Определение таблицы

Определим таблицу под названием person с двумя полями или атрибутами: name и age.

db.define_table(
    "person",
    Field("name"),
    Field("age", type="integer")
)
Code language: JavaScript (javascript)

Имя, не указывающее параметр типа, по умолчанию является строкой. А вот возраст – это целое число.

Типы, которые мы указываем, являются общими и не зависят от используемого движка базы данных: сstring, boolean, integer, double и т.д. pyDAL отвечает за преобразование этих общих типов в типы, специфичные для базы данных, поэтому программисту не нужно заботиться об этом.

Во всех определениях pyDAL автоматически включает первое поле, называемое id

Полный список типов данных (тех, которые могут быть переданы в параметре type), автоматически поддерживаемых web2py, выглядит следующим образом.

• string
• text
• blob
• boolean
• integer
• double
• decimal(n, m)
• date
• time
• datetime
• password
• upload
• reference <tabla>
• list:string
• list:integer
• list:reference <tabla>
• json
• bigint
• big-id
• big-reference

Помимо обычных типов, pyDAL добавляет некоторые интересные типы, такие как даты и время (date, time, datetime), пароли (password, который автоматически шифрует данные), файлы (upload), списки (list), ссылки на другие таблицы (reference) и данные в формате JSON (json).

Даже тип decimal(n, m) (n – общее количество цифр, а m – цифры после десятичной точки) автоматически взаимодействует со стандартным десятичным модулем, предоставляя рациональные числа, в отличие от типа double, который работает с классическим float.

Если вы хотите взаимодействовать с таблицами, использующими специфические для базы данных типы данных (например, tsvector в PostgreSQL), pyDAL предоставляет простой метод включения этих типов данных.

from pydal import DAL, Field, SQLCustomType
#  Функции преобразования из типов Python
#  в определенные типы баз данных и наоборот..
def from_tsvector(data):
    return data
def to_tsvector(data):
    return data
tsvector = SQLCustomType(
    type="string",
    native="tsvector",
    encoder=to_tsvector,
    decoder=from_tsvector
)
db.define_table(
     "<name>",
     Field("my_vector", type=tsvector)
)
Code language: PHP (php)

Через SQLCustomType мы создаем новый тип данных, где type соответствует типу данных pyDAL (перечисленному выше), а native выражает имя типа, специфичного для движка базы данных.

Ссылка на уже существующую таблицу

pyDAL в основном предназначен для работы с самостоятельно созданными таблицами, но он также поддерживает привязку к уже существующим таблицам (с некоторыми ограничениями).

Оба варианта выполняются с помощью define_table().

Когда мы вызываем define_table(), pyDAL создаст таблицу, если она не существует.

Если она существует, то по умолчанию проверяется, что определяемые поля совпадают с уже существующими.

Если структура таблицы в базе данных отличается от указанной, пакет автоматически вносит соответствующие изменения (в SQL-движках с помощью команды ALTER TABLE).

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

Когда мы хотим связать pyDAL с уже существующей таблицей, рекомендуется отключить миграции с помощью migrate=False.

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

Если, например, структура нашей существующей таблицы выглядит следующим образом:

CREATE TABLE "person" (
    "person_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "name" CHAR(512),
    "age" INTEGER
)
Code language: JavaScript (javascript)

Ее определение в pyDAL будет выглядеть так::

# Ссылка на уже существующую таблицу.
db.define_table(
    "person",
    # Укажем pyDAL, что является первичным ключом.
    Field("person_id", type="id"),
    Field("name"),
    Field("age", type="integer"),
    # Отключение миграций.
    migrate=False
)
Code language: PHP (php)

Получение и добавление данных

Когда наша таблица определена, пришло время добавить в нее данные.

Для этого мы используем функцию insert:

id = db.person.insert(name="Иван", age=30)
Code language: JavaScript (javascript)

Каждое из полей указывается в качестве аргумента вместе с соответствующим значением. id автоматически генерируется базой данных и идентифицирует вставленный ряд.

Для получения данных (выполнения запроса) мы используем следующее.

db(condiciones)

Например, чтобы вернуть всех лиц старше 18 лет в нашу таблицу, мы выполняем:

s = db(db.person.age > 18)

s – это то, что в web2py называется набором (pydal.objects.Set), который представляет собой запрос к базе данных (то, что в SQL применялось бы с предложением WHERE).

Мы можем задать несколько условий для любого количества полей. Можно добавить, что они должны быть больше 18, но меньше или равны 65.

s = db((db.person.age > 18) & (db.person.age <= 65))

Вместо and и or используются их двоичные эквиваленты: & и |, соответственно. (Обратите внимание, что важно группировать каждое выражение в круглых скобках из-за приоритета операторов сравнения над двоичными операторами).

Другие примеры:

# Те, чье имя начинается на "И".
s = db(db.person.name.startswith("И"))
# Те, чье имя заканчивается на "н" или кому исполнилось 20 лет.
s = db((db.person.name.endswith("n")) | (db.person.age == 20))
Code language: PHP (php)

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

s = db(db.person.age > 18)
people = s.select()

people – это итерируемый объект, экземпляр класса pydal.objects.Rows.

Для печати каждой строки мы используем:

for person in people:
    print(person)
Code language: PHP (php)

person является экземпляром класса pydal.objects.Row, поэтому для доступа к определенному столбцу или полю мы указываем его в качестве атрибута.

for person in people:
    print(person.id, person.name, person.age)
Code language: CSS (css)

Доступ к полям также можно получить в виде элементов.

for person in people:
    print(person["id"], person["name"], person["age"])
Code language: CSS (css)

Класс Row предоставляет ряд методов для возврата информации в виде словаря или в формате JSON или XML.

for person in people:
    print(person.as_dict())
    print(person.as_json())
    print(person.as_xml())
Code language: CSS (css)

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

Примеры:

# Выбираем только имя.
people = s.select(db.person.name)
# Выбираем только ID и возраст.
people = s.select(db.person.id, db.person.age)
# Выбираем все поля.
people = s.select(db.person.ALL)
Code language: PHP (php)

Чтобы получить количество строк, возвращенных из запроса, мы используем функцию count().

count = s.count()

Чтобы удалить все строки, содержащиеся в s, из базы данных, выполните delete().

# Возвращает количество удаленных строк.
deleted_rows = s.delete()
Code language: PHP (php)

Обратите внимание, что сам набор не влияет на базу данных, пока над ним не будет выполнена какая-либо операция (например, select() или delete()).

Для редактирования или обновления (то, что в SQL называется UPDATE) информации в таблице используется метод update.

Действует так же, как и insert, но на строки, содержащиеся в наборе.

Итак, если в нем содержатся все люди старше 18 лет, давайте снова сделаем их 5-летними.

# Возвращает количество измененных строк.
updated_rows = s.update(age=5)
Code language: PHP (php)

Чтобы изменить имя всех детей младше 18 лет на “Петр”, мы используем:

s = db(db.person.age > 18)
s.update(name="Петр")
Code language: JavaScript (javascript)

Или просто:

db(db.person.age > 18).update(name="Петр")
Code language: JavaScript (javascript)

По возможности используйте функцию iterselect() вместо select(). Она аналогична последней, но возвращает значения “на лету” для экономии памяти. (Строго говоря, это генератор).

for person in db(db.person.age > 18).iterselect():
    print(person.name)
Code language: CSS (css)

Чтобы сохранить изменения в базе данных:

db.commit()
Code language: CSS (css)

И отменить их в случае ошибки:

db.rollback()
Code language: CSS (css)

Если вам нужно построить достаточно сложный запрос, превышающий возможности pyDAL, вы можете использовать функцию executesql() для выполнения собственного SQL-кода.

people = db.executesql("SELECT name, age FROM person WHERE age > 18")
Code language: JavaScript (javascript)

Обратите внимание, что people в данном случае представляет собой список (поскольку он является прямым результатом используемого драйвера и не изменяется pyDAL), который содержит кортежи, элементы которых представляют каждый из выбранных столбцов, например:

[("Иван", 30), ("Петр", 26), ...]
Code language: JSON / JSON with Comments (json)

Для удаления таблицы:

db.person.drop()
Code language: CSS (css)

Подробнее об определениях

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

Например, мы можем указать значение по умолчанию для поля при вызове insert():

# Необязательное поле со значением по умолчанию.
Field("age", type="integer", default=18)
Code language: PHP (php)

Значение по умолчанию действует только при вставке информации. Для обновления через update() используйте параметр update.

# Значение по умолчанию для insert() и update().
Field("age", type="integer", default=18, update=18)
Code language: PHP (php)

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

# Обязательное поле.
Field("age", type="integer", required=True)
Code language: PHP (php)

Чтобы:

>>> db.person.insert(name="Павел")
Traceback (most recent call last):
   ...
RuntimeError: Table: missing required field: age
Code language: JavaScript (javascript)

Для полей типа “string”, “password” и “upload” рекомендуется указать параметр length, который указывает на его максимальную длину.

# Максимум 50 символов.
Field("name", length=50)
Code language: PHP (php)

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

Параметры filter_in и filter_out принимают функции для обработки данных до их поступления и после их возвращения из базы данных, соответственно.

Например, в следующем коде первая буква имени пишется заглавной.

def capitalize(name):
    return name.capitalize()
db.define_table(
    "person",
    Field("name", length=50, filter_in=capitalize),
    Field("age", type="integer")
)
Code language: JavaScript (javascript)

Так что:

# Вставляет "Павел".
db.person.insert(name="павел")
Code language: PHP (php)

filter_out работает аналогичным образом:

def upper(name):
    return name.upper()
db.define_table(
    "person",
    Field("name", length=50, filter_in=capitalize, filter_out=upper),
    Field("age", type="integer")
)
# Получим первый ряд, имя которого "Василий".
row = db(db.person.name == "Василий").select().first()
print(row.name)  # Imprime 'ВАСИЛИЙ'
Code language: PHP (php)

Доступ к коду SQL

Большинство поддерживаемых баз данных являются реляционными, поэтому pyDAL работает с внутренним построением и выполнением SQL-запросов в реальном времени.

Атрибут db._lastsql содержит последний выполненный запрос, например:

>>> people = db(db.person.age > 18).select()
>>> db._lastsql
('SELECT "person"."id", "person"."name", "person"."age" FROM "person" WHERE ("person"."age" > 18);', 0.0)
Code language: JavaScript (javascript)

Возвращает кортеж, содержащий запрос и время, затраченное на его выполнение.

Чтобы узнать SQL-код, генерируемый какой-то конкретной операцией, не выполняя ее сначала, pyDAL предоставляет функции _select, _insert, _update и _delete.

>>> db(db.person.age > 18)._select()
'SELECT "person"."id", "person"."name", "person"."age" FROM "person" WHERE ("person"."age" > 18);'
Code language: JavaScript (javascript)

Эти методы можно даже вызывать без необходимости устанавливать реальное соединение с базой данных, используя do_connect=False.

db = DAL("sqlite://storage.db", do_connect=False)
Code language: PHP (php)

Миграции

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

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

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

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

Эти файлы содержат информацию о структуре таблицы, чтобы в случае изменений pyDAL выполнял соответствующие миграции.

По этой причине важно, чтобы они не были изменены или удалены.

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

db.define_table(
    "person",
    Field("name"),
    Field("age", type="integer"),
    migrate="person.table"
)
Code language: JavaScript (javascript)

При связывании pyDAL с уже существующей таблицей, если вы не хотите, чтобы pyDAL вносил в нее изменения, отключите миграции через:

# Отключение миграций для определенной таблицы.
db.define_table(
    ...,
    migrate=False
)
Code language: PHP (php)

Это также относится к случаям, когда два приложения, использующие pyDAL, обращаются к одной и той же таблице (таблицам). Только одно из них должно включать миграции.

Чтобы отключить миграции для всех таблиц, мы указываем migrate_enabled=False при установлении соединения.

# Отключение миграций для всех таблиц.
db = DAL("sqlite://storage.db", migrate_enabled=False)
Code language: PHP (php)

Заключение

Как я хотел показать в этом кратком введении, pyDAL обладает уникальными возможностями, которых нет в других библиотеках Python.

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

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

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

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

Наконец, pyDAL имеет очень активное сообщество (фактически то же сообщество, что и web2py), которое не только быстро отвечает на запросы, но и вовремя обнаруживает ошибки, чтобы исправить их как можно быстрее и добавить новые возможности, как того требует мир ИТ.