Chapter 6: Слой абстракции базы данных

Слой абстракции базы данных

DAL

Зависимости

web2py поставляется с модулем слоя абстракции базы данных (DAL), который посредством API позволяет отобразить объекты Python в такие объекты базы данных, как запросы, таблицы и записи. DAL динамически генерирует SQL в режиме реального времени, используя указанный диалект для конечной базы данных, так что вам не придется писать код SQL или изучать различные диалекты SQL (термин SQL используется обобщенно), и приложение будет переносимым между различными типами баз данных. Частичный список поддерживаемых баз данных показан в таблице ниже. Пожалуйста, проверьте на веб-сайте web2py и список рассылки для получения более свежих адаптеров. Google NoSQL рассматривается как частный случай в главе 13.

Раздел Gotchas в конце этой главы содержит более подробную информацию о конкретных базах данных.

Бинарный дистрибутив Windows, работает из коробки с SQLite, MySQL, PostgreSQL и MySQL. Бинарный дистрибутив Mac работает из коробки с SQLite. Чтобы использовать любую другую базу данных со стороны сервера(back-end), из дистрибутива с исходным кодом запустите и установите соответствующий драйвер, требуемый со стороны сервера(back end).

database drivers

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

DAL
SQLite
MySQL
PostgresSQL
Oracle
MSSQL
FireBird
DB2
Informix
Sybase
Teradata
MongoDB
CouchDB
SAPDB
Cubrid

databasedrivers (source)
SQLitesqlite3 or pysqlite2 or zxJDBC [zxjdbc] (on Jython)
PostgreSQLpsycopg2 [psycopg2] or pg8000 [pg8000] or zxJDBC [zxjdbc] (on Jython)
MySQLpymysql [pymysql] or MySQLdb [mysqldb]
Oraclecx_Oracle [cxoracle]
MSSQLpyodbc [pyodbc] or pypyodbc[pypyodbc]
FireBirdkinterbasdb [kinterbasdb] or fdb or pyodbc
DB2pyodbc [pyodbc]
Informixinformixdb [informixdb]
Ingresingresdbi [ingresdbi]
Cubridcubriddb [cubridb] [cubridb]
SybaseSybase [Sybase]
Teradatapyodbc [Teradata]
SAPDBsapdb [SAPDB]
MongoDBpymongo [pymongo]
IMAPimaplib [IMAP]

sqlite3, pymysql, pg8000, и imaplib идут вместе с web2py. Поддержка MongoDB является экспериментальной. Опция IMAP позволяет использовать DAL для доступа по протоколу IMAP.

DAL: Быстрый тур

web2py определяет следующие классы, которые составляют DAL:

Объект DAL представляет собой соединение с базой данных. Например:

sqlite
db = DAL('sqlite://storage.sqlite')
define_table

Класс таблицы Table представляет собой таблицу базы данных. Вы не создаете экземпляр Table напрямую, за вас это делает DAL.define_table, который и создает экземпляр.

db.define_table('mytable', Field('myfield'))

Наиболее важными методами класса Table являются:

insert
truncate
drop
import_from_csv_file
count

.insert, .truncate, .drop, и .import_from_csv_file.

Field

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

Rows

DAL Rows

Row
это объект, возвращаемый методом select базы данных. Это можно представить в виде списка строк Row:

rows = db(db.mytable.myfield != None).select()
Row

Row содержит значения поля.

for row in rows:
    print row.myfield
Query

Query это объект, который представляет собой SQL условие "Где":

myquery = (db.mytable.myfield != None) | (db.mytable.myfield > 'A')
Set

Set это объект, который представляет собой набор записей. Его наиболее важные методы count, select, update, и delete. Например:

myset = db(myquery)
rows = myset.select()
myset.update(myfield='somevalue')
myset.delete()
Expression

Expression это что-то вроде выражения orderby или groupby. Класс Field происходит от Expression. Вот пример.

myorder = db.mytable.myfield.upper() | db.mytable.id
db().select(db.table.ALL, orderby=myorder)

Использование DAL "автономно"

Web2py DAL может использоваться вне среды web2py с помощью

from gluon import DAL, Field
# также рассмотреть: from gluon.validators import *

DAL конструктор

Основное использование:

>>> db = DAL('sqlite://storage.sqlite')

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

В любое время вы можете получить строку подключения.

_uri
>>> print db._uri
sqlite://storage.sqlite

и имя базы данных

_dbname
>>> print db._dbname
sqlite

Строка соединения называется _uri потому что это экземпляр унифицированного идентификатора ресурса (Uniform Resource Identifier).

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

DAL подпись

DAL(uri='sqlite://dummy.db',
    pool_size=0,
    folder=None,
    db_codec='UTF-8',
    check_reserved=None,
    migrate=True,
    fake_migrate=False,
    migrate_enabled=True,
    fake_migrate_all=False,
    decode_credentials=False,
    driver_args=None,
    adapter_args=None,
    attempts=5,
    auto_import=False,
    bigint_id=False,
    debug=False,
    lazy_tables=False,
    db_uid=None,
    do_connect=True,
    after_connection=None,
    tables=None,
    ignore_field_case=True,
    entity_quoting=False,
    table_hash=None)

Строки подключения (Значение URI параметров)

connection strings

Соединение с базой данных устанавливается путем создания экземпляра объекта DAL:

>>> db = DAL('sqlite://storage.sqlite', pool_size=0)

db не является ключевым словом; это локальная переменная, которая хранит объект подключения DAL. Вы можете дать ему другое имя. Конструктор DAL требует один аргумент, строку подключения. Строка соединения является единственным кодом web2py, который зависит от конкретной конечной базы данных. Ниже приведены примеры строк соединения для конкретных типов поддерживаемых конечных баз данных (во всех случаях, мы предполагаем, что база данных работает с локального хоста на его порту по умолчанию и называется "test"):

ndb
SQLitesqlite://storage.sqlite
MySQLmysql://username:password@localhost/test
PostgreSQLpostgres://username:password@localhost/test
MSSQL (legacy)mssql://username:password@localhost/test
MSSQL (>=2005)mssql3://username:password@localhost/test
MSSQL (>=2012)mssql4://username:password@localhost/test
FireBirdfirebird://username:password@localhost/test
Oracleoracle://username/password@test
DB2db2://username:password@test
Ingresingres://username:password@localhost/test
Sybasesybase://username:password@localhost/test
Informixinformix://username:password@test
Teradatateradata://DSN=dsn;UID=user;PWD=pass;DATABASE=test
Cubridcubrid://username:password@localhost/test
SAPDBsapdb://username:password@localhost/test
IMAPimap://user:password@server:port
MongoDBmongodb://username:password@localhost/test
Google/SQLgoogle:sql://project:instance/database
Google/NoSQLgoogle:datastore
Google/NoSQL/NDBgoogle:datastore+ndb

Обратите внимание на то, что база данных SQLite состоит из одного файла. Если он не существует, то он будет создан. Этот файл блокируется каждый раз при обращении к нему. В случае с MySQL, PostgreSQL, MSSQL, FireBird, Oracle, DB2, Ingres и Informix база данных "test" должна быть создана за пределами web2py. После того, как соединение установлено, web2py сможет создавать, изменять и удалять таблицы соответствующим образом.

В случае Google/NoSQL опция + ndb включает NDB. NDB использует Memcache буфер для чтения данных, доступ к которым часто используется. Это выполняется полностью автоматически и сделано на уровне хранилища данных, а не на уровне web2py.

Кроме того, можно установить строку подключения на None. В этом случае DAL не будет подключаться к любой конечной базе данных, но API все еще может быть доступен для тестирования. Примеры этого будут рассмотрены в главе 7.

Иногда вам может понадобиться генерировать SQL-запросы таким образом, как если бы у вас была связь, но без фактического подключения к базе данных. Это можно сделать с помощью

db = DAL('...', do_connect=False)

В этом случае вы сможете вызвать _select, _insert, _update, и _delete для генерации SQL, но не можете вызвать select, insert, update, и delete. В большинстве случаев вы можете использовать do_connect=False даже не имея необходимых драйверов баз данных.

Обратите внимание на то, что по умолчанию web2py использует кодировку utf8 для баз данных. Если вы работаете с существующими базами данных, которые ведут себя по-разному, вы должны изменить его с дополнительным параметром вроде db_codec.

db = DAL('...', db_codec='latin1')

В противном случае вы получите билеты UnicodeDecodeError.

Пулы соединений

connection pooling

Общепринятым аргументом конструктора DAL является pool_size; он по умолчанию равен нулю.

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

Когда запускается web2py, пул всегда пустой. Пул вырастает до минимума между значением pool_size и максимальным числом одновременных запросов. Это означает, что если pool_size = 10 и наш сервер никогда не получает более 5 одновременных запросов, то фактический размер пула будет расти только до 5. Если pool_size = 0 то пул соединений не используется.

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

Параметр pool_size игнорируется SQLite и Google App Engine. Пул соединений игнорируется для SQLite, так как это не дает никакой выгоды.

Неудачи подключения (число попыток)

Если web2py не удается подключиться к базе данных, то он ожидает 1 секунду и по умолчанию делает еще 5 попыток, прежде чем объявить неудачу. В случае пула соединений вполне возможно, что существует пулированное соединение, которое остается открытым, но не используется в течение некоторого времени, будет закрыто со стороны базы данных. Благодаря функции повторной попытки web2py пытается повторно установить данные разрывы подключений. Количество попыток устанавливается с помощью параметра числа попыток.

Ленивые Таблицы (Lazy Tables)

Настройка lazy_tables = True обеспечивает значительное повышение производительности. Смотри ниже: Ленивые Таблицы

Без-Модельные приложения

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

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

Это именуется как разработка "без-модели" ("model-less") посредством web2py сообщества. Это означает отсутствие использования автоматического выполнения python файлов в каталоге модели. Это не означает отказа от концепции модели, представления и контроллеры.

Web2py автоматически выполняет Python код внутри каталога модели:

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

Модели также делают полезными сессии интерактивной оболочки, когда web2py запускается из командной строки с опцией -M.

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

Используя подход "без модели", вы принимаете ответственность за выполнения этих двух задач по ведению домашнего хозяйства. Вы вызываете определения таблицы, когда вы нуждаетесь в них, и обеспечиваете необходимый доступ к глобальной области видимости с помощью current объекта. (как описано в главе 4 Использование объектом current глобальной области видимости)

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

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

Если функция, которая определяет набор таблиц, называется define_employee_tables() и содержится в модуле, который называется "table_setup.py", то ваш контроллер, который хочет обратиться к таблицам, связанными с записями сотрудников, с тем чтобы сделать SQLFORM должен вызвать функцию define_employee_tables() перед доступом к любым таблицам. Функция define_employee_tables() должна получить доступ к объекту подключения базы данных с целью определения таблиц. Именно поэтому вам нужно правильно использовать объект current в файле модуля, содержащего define_employee_tables()(как упоминалось выше).

Реплицируемые базы данных

Первый аргумент DAL(...) может быть списком URI-адресов. В этом случае web2py пытается подключиться к каждой из них. Основная цель этого заключается в том, чтобы иметь дело с несколькими серверами баз данных и распределить нагрузку между ними). Вот типичный случай использования:

db = DAL(['mysql://...1', 'mysql://...2', 'mysql://...3'])

В этом случае DAL пытается подключиться к первой и, в случае неудачи, он будет пытаться соединиться со второй и третьей. Это также может быть использовано для распределения нагрузки в конфигурации ведущей-ведомой базы данных. Мы поговорим об этом больше в главе 13 в контексте масштабируемости.

Зарезервированные ключевые слова

reserved Keywords

check_reserved говорит конструктору проверить имена таблиц и имена столбцов на наличие зарезервированных ключевых слов SQL в целевых серверных базах данных. check_reserved по умолчанию None.

Это список строк, которые содержат имена адаптеров серверных баз данных.

Имя адаптера является тем же самым, что и используемое в строке подключения DAL. Так что если вы хотите проверить в отношении PostgreSQL и MSSQL, то строка подключения будет выглядеть следующим образом:

db = DAL('sqlite://storage.sqlite',
         check_reserved=['postgres', 'mssql'])

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

Есть два дополнительных варианта "all" и "common". Если вы укажете все ("all"), то он будет проверять по всем известным ключевым словам SQL. Если вы укажете общие ("common"), он будет проверять только по общим ключевым словам, таких как SQL SELECT, INSERT, UPDATE и т.п.

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

check_reserved=['postgres', 'postgres_nonreserved']

Следующие серверные базы данных поддерживают проверку зарезервированных слов.

PostgreSQLpostgres(_nonreserved)
MySQLmysql
FireBirdfirebird(_nonreserved)
MSSQLmssql
Oracleoracle

Настройки квотирования и случаев базы данных (entity_quoting, ignore_field)

Вы также можете использовать явное квотирование сущностей SQL на уровне DAL. Это работает прозрачно, так что вы можете использовать одни и те же имена в Python и в схеме DB.

ignore_field_case = Trueentity_quoting = True

Вот пример:

db = DAL('postgres://...', ...,ignore_field_case=False, entity_quoting=True)

db.define_table('table1', Field('column'), Field('COLUMN'))

print db(db.table1.COLUMN != db.table1.column).select()

Другие параметры конструктора DAL

Расположение папки базы данных

folder – где будут созданы файлы .table. Автоматически устанавливается в пределах web2py. Используйте явный путь при использовании DAL вне web2py

Настройки миграции по умолчанию

Миграция подробно описана ниже в Tables миграции таблицы. Параметры DAL конструктора миграции являются булевыми, затрагивают значения по умолчанию и глобальное поведение.

migrate = True устанавливает поведение по умолчанию при миграции для всех таблиц

fake_migrate = False устанавливает поведение по умолчанию поддельной миграции fake_migrate для всех таблиц

migrate_enabled = True Если задано False, то отключает все миграции

fake_migrate_all = False Если задано True, то поддельные миграции во всех таблицах

Эксперимент с web2py оболочкой

Вы можете поэкспериментировать с DAL API используя web2py оболочку (-S опция командной строки).

Начните с создания соединения. Ради примера, вы можете использовать SQLite. Ничего в этой дискуссии не меняется при изменении серверного движка.

Конструктор таблиц

define_table
Field

Подпись define_table

Подпись для define_table:

Таблицы определяются в DAL с помощью define_table:

>>> db.define_table('person',
                    Field('name'),
                    id=id,
                    rname=None,
                    redefine=True
                    common_filter,
                    fake_migrate,
                    fields,
                    format,
                    migrate,
                    on_define,
                    plural,
                    polymodel,
                    primarykey,
                    redefine,
                    sequence_name,
                    singular,
                    table_class,
                    trigger_name)

Он определяет, сохраняет и возвращает Table объект под названием "person", содержащей поле (столбец) "name". Этот объект также может быть доступен через db.person, так что вам не нужно ловить возвращаемое значение.

Идентификатор id: Замечания по первичному ключу

Не объявляйте поле с именем "id", потому что оно все равно создается web2py. Каждая таблица имеет поле с названием "id" по умолчанию. Это целочисленное поле с автоматическим приращением (начиная с 1) используется для перекрестных ссылок и для создания каждой записи уникальным образом, поэтому "id" является первичным ключом. (Примечание: счетчик id начинается с 1 и задается серверной частью. Например, это не относится к the Google App Engine NoSQL.)

named id field

При желании вы можете определить поле type='id' и web2py будет использовать это поле как поле id с автоприращением. Это не рекомендуется, кроме случаев доступа к таблицам устаревших баз данных, которые имеют первичный ключ под другим именем. С некоторыми ограничениями, вы можете также использовать различные первичные ключи используя primarykey параметр. Первичный ключ будет объяснен ниже в ближайшее время.

Множественное plural и единственное singular числа

SmartGrid объектам возможно понадобится знать имя таблицы в единственном и множественном числе. Значения по умолчанию являются умными, но эти параметры вы можете конкретизировать. Смотрите SmartGrid для получения дополнительной информации.

Переопределение redefine

Таблицы могут быть определены только один раз, но вы можете заставить web2py переопределить существующую таблицу:

db.define_table('person', Field('name'))
db.define_table('person', Field('name'), redefine=True)

Переопределение может вызвать миграцию, если содержимое поля отличается.

Формат format: представление записи

Это не является обязательным, но рекомендуется указать представление формата для записей с помощью format параметра.

>>> db.define_table('person', Field('name'), format='%(name)s')

или

>>> db.define_table('person', Field('name'), format='%(name)s %(id)s')

или даже более сложные, с помощью функции:

>>> db.define_table('person',
                    Field('name'),
                    format=lambda r: r.name or 'anonymous')

Атрибут формата format будет использоваться для двух целей:

  • Для представления записей, имеющих ссылки при выборе/опций в раскрывающихся списках.
  • Установите атрибут db.othertable.person.represent для всех полей, ссылающихся на эту таблицу. Это значит, что SQLTABLE не будет показывать ссылки по id, а вместо этого будет использовать предпочтительный формат представления.

rname: Представление записи

rname задает имя конечной базы данных для таблицы. Это создает псевдоним имени таблицы web2py, а rname это реальное имя, используемое при создании запроса для конечной базы данных. Проиллюстрируем только одно использование, rname может быть использован для обеспечения MSSQL полностью квалифицированного по именам таблиц доступа к таблицам, принадлежащих к другим базам данных на сервере:

rname = 'db1.dbo.table1'

Первичный ключ primarykey: Поддержка традиционных таблиц

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

migrate, fake_migrate

migrate задает параметры миграции для таблицы. Смотреть Миграции Таблицы ниже

table_class

Если вы определили свой собственный класс Table в качестве подкласса gluon.dal.Table, вы можете предоставить его здесь; это позволяет вам расширить и переопределить методы. Например:

table_class=MyTable

sequence_name

(Необязательно) Имя последовательности пользовательской таблицы (если поддерживается базой данных). Можно создать SEQUENCE (начинающейся с 1 и увеличивающийся на 1) или использовать это для устаревших таблиц с пользовательскими последовательностями. Обратите внимание, что в случае необходимости, web2py будет создавать последовательности автоматически по умолчанию (начинающихся с 1).

trigger_name

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

polymodel

Для Google App Engine

on_define

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

Например:

db = DAL(lazy_tables=True)
db.define_table('person',
                Field('name'),
                Field('age', 'integer'))

on_define=lambda table: [table.name.set_attributes(requires=IS_NOT_EMPTY(), default=''),
                         table.age.set_attributes(requires=IS_INT_IN_RANGE(0, 120), default=30)]

Обратите внимание, этот пример показывает, как использовать on_define, но это фактически не нужно. Простые значения requires могут быть добавлены к определениям поля и таблица все еще будет ленивой. Тем не менее, requires, который принимает объект Set в качестве первого аргумента, такие как IS_IN_DB, будет делать запрос, как

db.sometable.somefield == some_value
, который бы вызвал sometable определенную в начале. Это является ситуацией, сохраняемой через on_define.

Ленивые таблицы, основной прирост производительности

lazy tables

web2py модели выполняются перед контроллерами, так что все таблицы определяются в каждом запросе. Не все таблицы необходимы для обработки каждого запроса, так что вполне возможно, что некоторое время тратится при определениях таблиц впустую. Условные модели (условные модели, глава 4) могут помочь, но web2py предлагает большой прирост производительности с помощью lazy_tables. Эта возможность означает, что создание таблиц откладывается, пока таблица не будет на самом деле ссылаемой. Включение ленивых таблиц производится при инициализации базы данных с помощью конструктора DAL. Он требует установки параметра DAL(...,lazy_tables=True). Это одно из наиболее существенных повышений производительности по времени отклика в web2py.

Добавление атрибутов к полям и таблицам

Если вам нужно добавить пользовательские атрибуты полей, то вы можете просто сделать:

db.table.field.extra = {}

"extra" не является ключевым словом; это пользовательские атрибуты ныне прикрепленные к объекту поля. Вы можете сделать это с таблицами также, но они должны предворяться знаком подчеркивания, чтобы избежать конфликта имен с полями:

db.table._extra = {} 

Конструктор Field

Field constructor

Вот значения по умолчанию конструктора Field:

Field(fieldname, type='string', length=None, default=None,
      required=False, requires='<default>',
      ondelete='CASCADE', notnull=False, unique=False,
      uploadfield=True, widget=None, label=None, comment=None,
      writable=True, readable=True, update=None, authorize=None,
      autodelete=False, represent=None, compute=None,
      uploadfolder=None, uploadseparate=None, uploadfs=None, rname=None)

Не все из них актуальны для каждого поля. "length" имеет значение только для полей типа "string". "uploadfield" и "authorize" имеют значения только для полей типа "upload". "ondelete" имеет значение только для полей типа "reference" и "upload".

  • length устанавливает максимальную длину поля типа "string", "password" или "upload". Если length не задано, то используется значение по умолчанию, но значение по умолчанию не гарантирует обратную совместимость. Чтобы избежать нежелательных миграции при обновлении, мы рекомендуем вам всегда указывать длину полей строк, пароля и загрузки.
  • default устанавливает значение по умолчанию для поля. Значение по умолчанию используется при выполнении вставки, если значение не указано явно. Он также используется для предварительного заполнения формы, построенных из таблицы с использованием SQLFORM. Обратите внимание, что скорее всего взамен фиксированного значения по умолчанию может быть функция (в том числе лямбда-функция), которая возвращает значение соответствующего типа для поля. В этом случае функция вызывается один раз для каждой вставляемой записи, даже при наличии нескольких вставляемых записей в одной транзакции.
  • required сообщает DAL, что вставка не должна быть разрешена в этой таблице, если значение для этого поля не указано явно.
  • requires является валидатором или списком валидаторов. Параметр не используется DAL, но он используется SQLFORM. Валидаторы по умолчанию для заданных типов полей приведены в следующем разделе.

Обратите внимание на то , что requires=... обеспечивается на уровне форм, required=True обеспечивается на уровне DAL (вставка), в то время как notnull, unique и ondelete применяются на уровне базы данных. Несмотря на то, что они иногда могут показаться излишними, важно поддерживать различие при программировании с помощью DAL.

  • uploadfolder пока по умолчанию используется None, большинство адаптеров баз данных будет по умолчанию загружать файлы в os.path.join(request.folder, 'uploads'). MongoAdapter кажется, не будет делать это в настоящее время.
  • rname обеспечивает чтобы поле имело "реальное имя" ("real name"), имя для поля, известного адаптера базы данных; когда поле используется, то значение rname это то значение, которое отправляется в базу данных. Имя для поля web2py является в действительности псевдонимом.
ondelete
  • ondelete переводится как выражение SQL "ON DELETE". По умолчанию он установлен на "CASCADE". Это сообщает базе данных, чтобы при удалении заданной записи также необходимо удалить все связанные записи. Чтобы отключить эту функцию, задайте параметру ondelete значение "NO ACTION" или "SET NULL".
  • notnull=True переводится как выражение SQL "NOT NULL". Он ограждает базу данных от вставки нулевых значений для поля.
  • unique=True переводится как выражение SQL "UNIQUE" и он гарантирует, что значения этого поля являются уникальными в пределах таблицы. Это обеспечивается на уровне базы данных.
  • uploadfield применяется только к полям типа "upload". Поле типа "upload" хранит имя файла, сохраненного в другом месте, по умолчанию в папке приложения "uploads/" файловой системы. Если uploadfield задан как True, то файл хранится в поле двоичных объектов в пределах одной таблицы и значение uploadfield это имя поля двоичных объектов. Этот вопрос будет обсуждаться более подробно позже в контексте SQLFORM.
  • uploadfolder по умолчанию является папкой приложения "uploads/". Если задать другой путь, то файлы будут загружены в другую папку.

Например поле,

Field(...,uploadfolder=os.path.join(request.folder,'static/temp'))

будет загружать файлы в папку "web2py/applications/myapp/static/temp".

  • uploadseparate Если задано значение True, поле будет загружать файлы в различные подпапки внутри папки uploadfolder. Предназначено для оптимизации во избежание слишком большого количества файлов в одной той же самой папке folder/subfolder. ВНИМАНИЕ: Вы не можете изменить значение uploadseparate с True на False без разрушения ссылок на существующие загрузки. web2py либо использует отдельные вложенные папки, либо нет. Изменение поведения после того, как файлы были загружены будет препятствовать web2py при получении этих файлов. Если это произойдет, то можно переместить файлы и исправить эту проблему, но этот способ не описывается здесь.
  • uploadfs позволяет указать другую файловую систему, куда загружать файлы, включая хранилище Amazon S3 или удаленное хранилище SFTP. Данный параметр требует установленный PyFileSystem. uploadfs должен указывать на PyFileSystem.
    PyFileSystem
    uploadfs
  • widget должен быть одним из доступных виджет объектов, в том числе одним из пользовательских виджетов, например: SQLFORM.widgets.string.widget. Список доступных виджетов будет обсуждаться позже. Каждый тип поля имеет виджет по умолчанию.
  • label является строкой (или помощником или чем-то, что может быть сериализовано в строку), содержащей метку и которая будет использоваться для этого поля в автоматически генерируемых формах.
  • comment является строкой (или помощником или чем-то, что может быть сериализовано в строку), содержащей связанный с этим полем комментарий и который будет отображаться справа от поля ввода в автоматически сгенерированных формах.
  • writable объявляет поле в формах, предназначенное для записи.
  • readable объявляет поле в формах, предназначенное для чтения. Если поле не предназначено ни для чтения, ни для записи, то оно не будет отображаться при создании и обновлении формы.
  • update содержит значение по умолчанию для этого поля, когда запись является обновленной.
  • compute является дополнительной функцией. Если запись вставлена или обновлена, то данная функция вычислений будет выполнена, и поле будет заполнено результатом действия функции. Запись передается в функцию вычисления в виде словаря dict, и словарь dict не будет включать в себя текущее значение этого, или любого другого вычисляемого поля.
  • authorize может быть использован при необходимости контроля доступа для соответствующего поля, только для полей загрузки "upload". Это будет обсуждаться более подробно в контексте аутентификации и авторизации.
  • autodelete задает автоматическое удаление соответствующего загруженного файла при удалении ссылки на файл. Только для полей загрузки "upload". Тем не менее, удаление записей самой базой данных при выполнении соответствующей операции CASCADE, не будет вызывать автоудаление web2py. В Web2py группе Google имеется обсуждение обходных путей.
  • represent может быть None или может указывать на функцию, которая принимает значение поля и возвращает альтернативное представление для значения поля.

Пример:

db.mytable.name.represent = lambda name, row: name.capitalize()
db.mytable.other_id.represent = lambda id, row: row.myfield
db.mytable.some_uploadfield.represent = lambda value, row: A('get it', _href=URL('download', args=value))

Типы полей

field types
Тип поляВалидаторы поля по умолчаниюОписание
stringIS_LENGTH(length)Строка (длина по умолчанию 512)
textIS_LENGTH(65536)Текст
blobNoneБольшой двоичный объект (big large object)
booleanNoneБулевое значение
integerIS_INT_IN_RANGE(-1e100, 1e100)Целое число
doubleIS_FLOAT_IN_RANGE(-1e100, 1e100)Действительное число
decimal(n,m)IS_DECIMAL_IN_RANGE(-1e100, 1e100)Десятичная дробь
dateIS_DATE()Дата
timeIS_TIME()Время
datetimeIS_DATETIME()Дата-время
passwordNoneПароль
uploadNoneПоле загрузки
reference <table>IS_IN_DB(db, table.field, format)Ссылка
list:stringNoneСписок строк
list:integerNoneСписок целых
list:reference <table>IS_IN_DB(db, table.field, format, multiple=True)список ссылок
jsonIS_JSON()Формат json
bigintNoneБольшое целое
big-idNoneДлинный идентификатор
big-referenceNoneДлинная ссылка

Десятичный тип поля требует и возвращает значения в виде объектов класса Decimal, как это определено в Python модуле decimal. SQLite не обрабатывает тип decimal, таким образом внутренне мы рассматриваем его как double. (n,m) это количество цифр в целой и в дробной части соответственно.

Типы big-id и big-reference поддерживаются только некоторыми движками баз данных и являются экспериментальными. Они обычно не используются в качестве типов полей, за исключением использования для устаревших таблиц, однако, конструктор DAL имеет аргумент bigint_id, который при установке на True преобразует тип полей id и reference в big-id и big-reference соответственно.

Поля с типом list:<тип> являются особенным, потому что они разработаны, чтобы воспользоваться преимуществами определенных функций денормализации на NoSQL (в случае с Google App Engine NoSQL, типы полей ListProperty и StringListProperty) и обратного-портирования (back-port) данных типов полей для всех других поддерживаемым реляционных баз данных. В реляционных базах данных списки хранятся в виде поля text. Элементы разделяются символом | и каждый символ |, находящийся в элементе строки должен экранироваться путем удваивания ||. Они обсуждаются в отдельном разделе.

Тип поля json не требует много пояснений. Он может хранить любой сериализованный объект в формате JSON. Он предназначен специально для работы с MongoDB и обратного портирования на другие адаптеры базы данных для портативности.

blob

Поля blob(big large object) также являются специальными. По умолчанию, двоичные данные кодируются в base64 перед сохранением в фактическое поле базы данных и декодируются при извлечении. Это оказывает негативное влияние, используя на 33% больше места для хранения, чем это необходимо в blob полях, но имеет преимущество в создании связи независимо от определенных соглашений по экранированию конечных баз данных.

Поле времени выполнения и таблица модификации

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

db.define_table('person',
                Field('name', default=''),
                format='%(name)s')

db.person._format = '%(name)s/%(id)s'
db.person.name.default = 'anonymous'

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

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

tables
>>> print db.tables
['person']

Вы также можете перечислить поля, которые были определены для данной таблицы:

fields
>>> print db.person.fields
['id', 'name']

Вы можете запросить тип таблицы:

Table
>>> print type(db.person)
<class 'pydal.objects.Table'>

и вы можете получить доступ к таблицы из DAL соединения используя:

>>> print type(db['person'])
<class 'pydal.objects.Table'>

Точно так же вы можете получить доступ к полям по их имени несколькими эквивалентными способами:

>>> print type(db.person.name)
<class 'pydal.objects.Field'>
>>> print type(db.person['name'])
<class 'pydal.objects.Field'>
>>> print type(db['person']['name'])
<class 'pydal.objects.Field'>

Получив поле, вы можете получить доступ к атрибутам, установленным в его определении:

>>> print db.person.name.type
string
>>> print db.person.name.unique
False
>>> print db.person.name.notnull
False
>>> print db.person.name.length
32

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

>>> db.person.name._table == db.person
True
>>> db.person.name._tablename == 'person'
True
>>> db.person.name._db == db
True

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

print db.person.name.validate('John')

возвращает кортеж (value, error). error равняется None если входные данные прошли проверку.

Миграции

migrations

Метод define_table проверяет, существует ли или нет соответствующая таблица. Если это не так, то он генерирует SQL-код для ее создания и выполняет его. Если таблица все же существует, но отличается от определяемой, то он генерирует SQL-код, чтобы изменить таблицу и выполняет его. Если изменяется тип поля, но не имя, то он попытается преобразовать данные (Если вы не хотите этого, то вам нужно переопределить таблицу дважды, в первый раз, позволяя web2py отбросить поле путем его удаления, и во второй раз добавляя вновь определенное поле таким образом, что web2py может создать его.). Если таблица существует и соответствует текущему определению, то он оставит его в покое. Во всех случаях он будет создавать объект db.person, который представляет таблицу.

Мы называем это поведение как "миграция" ("migration"). web2py регистрирует все миграции и попытки миграции в файле "databases/sql.log".

Первый аргумент define_table всегда является именем таблицы. Остальными безымянными аргументами являются поля (Field). Функция также принимает необязательный аргумент - ключевое слово с именем "migrate":

>>> db.define_table('person', Field('name'), migrate='person.table')

Значением migrate является имя файла (в папке "databases" приложения) где web2py хранит внутреннюю миграционную информацию для этой таблицы. Эти файлы очень важны и не должны удаляться до тех пор, пока соответствующие таблицы существуют. В тех случаях, когда таблица была отброшена и соответствующий файл по-прежнему существует, он может быть удален вручную. По умолчанию, миграция имеет значение True. Это заставляет web2py генерировать имя файла из хэш-строки подключения. Если миграция имеет значение False, то миграция не выполняется, и web2py предполагает, что таблица существует в хранилище данных и содержит (по крайней мере) поля, перечисленные в define_table. Передовая практика заключается в задании явного имени таблицы миграции.

Здесь не может быть две таблицы в одном и том же приложении с аналогичным именем файла миграции.

Класс DAL также принимает аргумент "migrate", который определяет значение migrate по умолчанию для вызовов в define_table. Например,

>>> db = DAL('sqlite://storage.sqlite', migrate=False)

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

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

Миграции могут быть отключены для всех таблиц одновременно:

db = DAL(..., migrate_enabled=False)

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

Фиксация нарушенных миграций

fake_migrate

Есть две общие проблемы, связанные с миграциями и есть способы восстановления после них.

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

Если web2py возвращает ошибку в функции gluon.sql.parse при выборе записей, это проблема: появляются поврежденные данные в столбце из-за указанной выше проблемы.

Решение состоит в обновлении всех записей таблицы и обновления значений в столбце на None.

Другая проблема является более общей, но типичной для MySQL. MySQL не позволяет более одного оператора ALTER TABLE в транзакции. Это означает, что web2py должен разбить сложные операции на более мелкие (один ALTER TABLE за раз) и зафиксировать один кусок в это же время. Поэтому возможно, что одна часть сложной транзакции получит фиксацию, а другая часть потерпит неудачу, оставляя web2py в испорченном состоянии. Почему части транзакции потерпит неудачу? Потому что, например, она может включать в себя изменение таблицы и преобразования строкового столбца в столбец даты и времени, web2py попытается преобразовать данные, но данные не могут быть преобразованы. Что произойдет с web2py? Он будет поставлен в тупик о том, какая именно структура таблицы на самом деле хранится в базе данных.

Решение состоит из отключение миграций для всех таблиц и включения поддельных миграций:

db.define_table(...., migrate=True, fake_migrate=True)

Это позволит web2py перестроить метаданные о таблице в соответствии с определением таблицы. Попробуйте несколько определений таблицы, чтобы увидеть, какая из них работает (одна перед неудачной миграцией, и одна после неудачной миграции). После успешного завершения удалите fake_migrate=True параметр.

Прежде чем пытаться исправить проблемы миграции благоразумно сделать копию файлов "applications/yourapp/databases/*.table".

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

db = DAL(..., fake_migrate_all=True)

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

Краткое описание миграционного контроля

Логика различных аргументов миграции обобщена в этом псевдокоде:

if DAL.migrate_enabled and table.migrate:
   if DAL.fake_migrate_all or table.fake_migrate:
       perform fake migration
   else:
       perform migration

Вставка insert

Получив таблицу, вы можете вставлять записи

insert
>>> db.person.insert(name="Alex")
1
>>> db.person.insert(name="Bob")
2

при вставке возвращаются уникальные значения "id" для каждой из вставленной записи.

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

truncate
>>> db.person.truncate()

Теперь, если вы вставите запись снова, то отсчет начнется с 1 (это зависит от конкретной базы данных и не относится к Google NoSQL):

>>> db.person.insert(name="Alex")
1

Обратите внимание, вы можете передать некоторые параметры truncate, например, чтобы сообщить SQLITE перезапустить счетчик id.

db.person.truncate('RESTART IDENTITY CASCADE')

Аргумент является необработанным SQL-кодом и поэтому зависит от конкретного движка базы данных.

bulk_insert

web2py также предоставляет метод массовой вставки bulk_insert

>>> db.person.bulk_insert([{'name': 'Alex'}, {'name': 'John'}, {'name': 'Tim'}])
[3, 4, 5]

Она принимает список из словарей с именами полей и их значениями, которые подлежат вставке, и выполняет несколько вставок одновременно. Он возвращает идентификаторы вставленных записей. На поддерживаемых реляционных базах данных не существует каких-либо преимуществ в использовании этой функции в отличие от цикла и выполнения отдельных вставок, а вот на Google App Engine NoSQL, это дает основное преимущество в скорости.

Фиксация commit и откат rollback изменений

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

commit
>>> db.commit()

Чтобы проверить это, давайте вставим новую запись:

>>> db.person.insert(name="Bob")
2

и откатим, т.е. проигнорируем все операции после последней фиксации:

rollback
>>> db.rollback()

Если вы теперь вставите снова, то счетчик вновь будет установлен на 2, так как предыдущая вставка откатывается.

>>> db.person.insert(name="Bob")
2

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

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

Таким образом, в моделях, представлениях и контроллерах web2py нет необходимости в явном вызове commit или rollback, если вам не требуется более детальный контроль. Тем не менее, в модулях вам нужно будет использовать commit().

Сырой SQL

Расчет времени запросов

Все запросы автоматически просчитываются по времени web2py. Переменная db._timings является списком кортежей. Каждый кортеж содержит необработанный запрос SQL, который передается драйверу базы данных и время выполнения запроса в секундах. Эта переменная может быть отображена в представлениях с помощью панели инструментов:

{{=response.toolbar()}}

Метод executesql

DAL позволяет явно выдавать SQL операторы.

executesql
>>> print db.executesql('SELECT * FROM person;')
[(1, u'Massimo'), (2, u'Massimo')]

В этом случае возвращаемые значения не разбираются или преобразуются через DAL, и формат зависит от конкретного драйвера базы данных. Такое использование с выборкой данных, как правило, не требуется, но чаще встречается с индексами. Метод executesql принимает четыре необязательных аргумента: placeholders, as_dict, fields и colnames. placeholders является необязательной последовательностью значений, которые будут замещены, или, если эта функция поддерживается драйвером базы данных, словарем с ключами, соответствующими именованным местозаполнителям в вашем SQL.

Если as_dict установлен на True, то результирующий курсор, возвращаемый драйвером базы данных будет преобразован в последовательность словарей, ключированных именами полей базы данных. Результаты возвращенные с as_dict = True являются теми же самыми, которые возвращается при применении . as_list() для нормального выбора.

[{field1: value1, field2: value2}, {field1: value1b, field2: value2b}]

аргумент fields является списком объектов DAL Field, которые соответствуют возвращаемым из базы данных полям. Объекты Field должны быть частью одного или нескольких объектов Table, определенных в объекте DAL. Список fields может включать в себя один или несколько объектов DAL Table в дополнение или взамен включения объектов Field, или он может быть только одной таблицей (нет в списке). В этом случае объекты Field будут извлечены из таблиц(ы).

Вместо уточнения аргумента fields, аргумент colnames может быть указан в виде списка имен полей в следующем формате tablename.fieldname. Опять же, они должны представлять таблицы и поля, определенные в объекте DAL.

Кроме того, можно указать оба fields и связанный с ним colnames. В этом случае, fields может также включать в себя объекты DAL Expression в дополнение к объектам Field. Для объектов Field в "полях", связанный с ним colnames должны еще быть в tablename.fieldname формате. Для Expression объектов в fields, связанные с ним colnames могут быть любыми произвольными метками.

Обратите внимание, объекты DAL Table ссылаемые через fields или colnames могут быть фиктивными таблицами и не должны представлять какие-либо реальные таблицы в базе данных. Кроме того, обратите внимание, что fields и colnames должны идти в том же порядке, что и поля в курсоре результатов, возвращаемых из базы данных.

Атрибут _lastsql

Если SQL был выполнен вручную с помощью executesql или SQL-код был сгенерирован через DAL, то вы всегда можете найти код SQL в атрибуте db._lastsql. Это полезно для целей отладки:

_lastdb
>>> rows = db().select(db.person.ALL)
>>> print db._lastsql
SELECT person.id, person.name FROM person;

web2py никогда не генерирует запросы, используя оператор "*". web2py всегда явно указывает при выборе полей.

Метод drop

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

drop
>>> db.person.drop()

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

Индексы

В настоящее время DAL API не предоставляет команду для создания индексов таблиц, но это можно сделать с помощью команды executesql. Это происходит потому, что существование индексов может сделать миграции сложным, и лучше иметь дело с ними в явном виде. Индексы могут быть необходимы для тех полей, которые используются в повторяющихся запросах.

Ниже приведен пример того, как создать индекс с помощью SQL в SQLite:

>>> db = DAL('sqlite://storage.sqlite')
>>> db.define_table('person', Field('name'))
>>> db.executesql('CREATE INDEX IF NOT EXISTS myidx ON person (name);')

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

Унаследованные базы данных и ключированные таблицы

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

Самый простой способ, когда будут выполнены эти условия:

  • Каждая таблица должна иметь уникальное автоприращаемое целочисленное поле, называемое "id"
  • Записи должны быть привязаны исключительно с помощью "id" поля.

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

Если унаследованная таблица имеет автоприращаемое целочисленное поле, но оно не называется "id", то web2py по-прежнему может получить доступ к нему, но определение таблицы должно содержать в явном виде Field('....','id'), где вместо точек ... подставляется имя автоприращаемого целочисленного поля.

keyed table

И, наконец, если унаследованная таблица использует первичный ключ, который не является автоприращаемым id полем, то существует возможность использовать "ключированную таблицу", например:

db.define_table('account',
                Field('accnum','integer'),
                Field('acctype'),
                Field('accdesc'),
                primarykey=['accnum','acctype'],
                migrate=False)
  • primarykey список имен полей, которые составляют первичный ключ.
  • Все первичные ключевые поля имеют установку NOT NULL, даже если это не указано.
  • Ключированные таблицы могут ссылаться только на другие ключированные таблицы.
  • Ссылочные поля должны использовать формат reference tablename.fieldname.
  • Функция update_record недоступна для строк ключированных таблиц.

В настоящее время ключированные таблицы поддерживаются только для DB2, MS-SQL, Ingres и Informix, но будут добавлены другие движки.

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

Распределенные транзакции

distributed transactions

На момент написания эта возможность поддерживается только PostgreSQL, MySQL и Firebird, так как они предоставляют API для двухфазных фиксаций.

Если у вас есть два (или более) подключений к отдельным базам данных PostgreSQL, например:

db_a = DAL('postgres://...')
db_b = DAL('postgres://...')

В ваших моделях или контроллерах, вы можете зафиксировать их одновременно:

DAL.distributed_transaction_commit(db_a, db_b)

В случае неудачи, эта функция выполняет откат и поднимает исключение (Exception).

В контроллерах, когда действие возвращает результат, если у вас есть два различных соединения и вы не вызывали вышеприведенную функцию, то web2py фиксирует их по отдельности. Это означает, что существует вероятность того, что одна из фиксаций может завершиться успешно, а другая потерпеть неудачу. Распределенная транзакция предотвращает от такого происшествия.

Дополнительная информация по загрузкам

Рассмотрим следующую модель:

>>> db.define_table('myfile',
                    Field('image', 'upload', default='path/'))

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

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

>>> stream = open(filename, 'rb')
>>> db.myfile.insert(image=db.myfile.image.store(stream, filename))

Кроме того, можно вставить файл более простым способом и иметь автоматически вызываемый хранилищем метод вставки:

>>> stream = open(filename, 'rb')
>>> db.myfile.insert(image=stream)

В этом случае имя файла получается из объекта stream, если таковой имеется.

Метод store объекта поля загрузки принимает stream файла и имя файла. Он использует имя файла, чтобы определить расширение (тип) файла, создает новое временное имя для файла (в соответствии с web2py механизмом загрузки) и загружает содержимое файла в этот новый временный файл (в папку uploads, если не указано иное). Он возвращает новое временное имя, которое затем хранится в поле image из таблицы db.myfile.

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

>>> db.define_table('myfile',
                    Field('image', 'upload', uploadfield='image_file'),
                    Field('image_file', 'blob'))
>>> stream = open(filename, 'rb')
>>> db.myfile.insert(image=db.myfile.image.store(stream, filename),
                     image_file=stream.read())

Противоположностью .store является .retrieve:

>>> row = db(db.myfile).select().first()
>>> (filename, stream) = db.myfile.image.retrieve(row.image)
>>> import shutil
>>> shutil.copyfileobj(stream, open(filename, 'wb'))

Объекты Query, Set, Rows

Давайте снова рассмотрим ранее определенные таблицы (и сброшенные) и вставим три записи:

>>> db.define_table('person', Field('name'))
>>> db.person.insert(name="Alex")
1
>>> db.person.insert(name="Bob")
2
>>> db.person.insert(name="Carl")
3

Вы можете сохранить таблицу в переменной. Например, в переменной person, вы могли бы сделать:

Table
>>> person = db.person

Вы также можете сохранить поля в такую переменную как name. Например, вы могли бы также сделать:

Field
>>> name = person.name

Вы даже можете построить запрос (используя операторы вроде ==, !=, <, >, <=, >=, like, belongs) и сохранить запрос в переменной q:

Query
>>> q = name == 'Alex'

Когда вы вызываете db с запросом, вы определяете набор (Set) записей. Вы можете сохранить его в переменной s и написать:

Set
>>> s = db(q)

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

Метод select

Допустим задан объект Set, в переменнойs, тогда вы можете получить записи с помощью команды select:

Rows
select

>>> rows = s.select()
Row

Она возвращает итерируемый объект класса pydal.objects.Rows элементы которого являются объектами Row. pydal.objects.Row объекты действуют как словари, но их элементы также могут быть доступны в качестве атрибутов, как в gluon.storage.Storage. Объекты класса Row отличаются от объектов класса Storage тем, что их значения предназначены только для чтения.

Объект Rows позволяет перебрать в цикле результат выборки и, например, напечатать выбранные значения полей для каждой строки:

>>> for row in rows:
        print row.id, row.name
1 Alex

Вы можете сделать все шаги в одном выражении:

>>> for row in db(db.person.name == 'Alex').select():
        print row.name
Alex
ALL

Команда select может принимать аргументы. Все безымянные аргументы интерпретируются как имена полей, которые вы хотите достать. Например, вы сможете явным образом сделать выборку поля "id" и поля "name":

>>> for row in db().select(db.person.id, db.person.name):
        print row.name
Alex
Bob
Carl

Атрибут таблицы ALL позволяет указать все поля:

>>> for row in db().select(db.person.ALL):
        print row.name
Alex
Bob
Carl

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

Эквивалентный альтернативный синтаксис выглядит следующим образом:

>>> for row in db(db.person.id > 0).select():
        print row.name
Alex
Bob
Carl

и web2py поймет, что если вы запрашиваете все записи таблицы person (id> 0) без дополнительной информации, то вам нужны все поля таблицы person.

Возьмем одну строку

row = rows[0]

вы можете извлечь её значения, используя несколько эквивалентных выражений:

>>> row.name
Alex
>>> row['name']
Alex
>>> row('person.name')
Alex

Последний синтаксис особенно удобен при выборе EN выражения вместо столбца. Покажем это позже.

Вы можете также сделать

rows.compact = False

и отключить компактную нотацию

row[i].name

и включить, взамен этого, менее компактную нотацию:

row[i].person.name

Да, это необычно и редко требуется.

Объекты Row также имеют два важных метода:

row.delete_record()

и

row.update_record(name="new value")

Использование итератор-подобного метода select для более низкого использования памяти

Python "итераторы" относятся к типу "ленивой оценки". Они 'скармливают' данные один шаг за раз; традиционные циклы Python создают весь набор данных в памяти перед перебором цикла.

Традиционным использованием Select является:

for row in db(db.table.id > 0).select():
    rtn = row

но для большого количества строк, использование итератор-основанной альтернативы имеет драматически меньшее использование памяти:

for row in db(db.table.id > 0).iterselect():
    rtn = row

Тестирование показывает, что это примерно на 10% быстрее, даже на машинах с большой оперативной памятью.

Визуализация строк с использованием представления

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

rows = db(query).select()  
repr_row = rows.render(0)

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

for row in rows.render():
    print row.myfield

Можно также применить к срезам:

for row in rows[0:10].render():
    print row.myfield

Если вы хотите преобразовать выбранные поля через их "represent" атрибут, то вы можете перечислить их в "fields" аргументе:

repr_row = row.render(0, fields=[db.mytable.myfield])

Обратите внимание, он возвращает преобразованную копию исходного Row, так что здесь нет update_record (которые вы не хотели бы в любом случае) или delete_record.

Сокращения (shortcuts)

DAL shortcuts

DAL поддерживает различные упрощающие код сокращения. В частности:

myrecord = db.mytable[id]

возвращает запись с заданным id если он существует. Если id не существует, то он возвращает None. Вышеуказанный оператор эквивалентен

myrecord = db(db.mytable.id == id).select().first()

Вы можете удалить записи с помощью id:

del db.mytable[id]

и это эквивалентно

db(db.mytable.id == id).delete()

и удаляет запись с заданным id, если он существует.

Примечание: Этот сокращенный синтаксис удаления на данный момент не работает, если активирован контроль версий.

Вы можете вставлять записи:

db.mytable[0] = dict(myfield='somevalue')

Это эквивалентно

db.mytable.insert(myfield='somevalue')

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

Вы можете обновить записи:

db.mytable[id] = dict(myfield='somevalue')

что эквивалентно

db(db.mytable.id == id).update(myfield='somevalue')

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

Извлечение (Fetching ) Row

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

record = db.mytable(id)
record = db.mytable(db.mytable.id == id)
record = db.mytable(id, myfield='somevalue')

Очевидно, что он похож на db.mytable[id], но вышеуказанный синтаксис является более гибким и более безопасным. Прежде всего, он проверяет, является ли id целым числом (или str(id) является целым) и возвращает None в противном случае (он никогда не вызывает исключение). Он также позволяет указать несколько условий, которым запись должна удовлетворять. Если они не будут выполнены, он также возвращает None.

Рекурсивные выборки (select)

recursive selects

Рассмотрим предыдущую таблицу person и новую таблицу "thing", ссылающуюся на "person":

>>> db.define_table('thing',
                    Field('name'),
                    Field('owner_id', 'reference person'))

и простую выборку из этой таблицы:

>>> things = db(db.thing).select()

что эквивалентно

>>> things = db(db.thing._id > 0).select()

где ._id является ссылкой на первичный ключ таблицы. Обычно db.thing._id такой же как db.thing.id и мы будем так считать в большей части этой книги.

_id

Для каждой Row из things есть возможность выборки не только полей из выбранной таблицы (thing), но и из связанных таблиц (рекурсивно):

>>> for thing in things: print thing.name, thing.owner_id.name

Здесь выражение thing.owner_id.name требует единичного выбора из базы данных для каждой thing в перечне things и поэтому выражение неэффективно. Мы рекомендуем использовать сцепки (joins), когда это возможно, вместо рекурсивных выборок, тем не менее, это удобно и практично при доступе к отдельным записям.

Вы также можете сделать это в обратном направлении, выбирая вещи (things), на которые ссылается person:

person =  db.person(id)
for thing in person.thing.select(orderby=db.thing.name):
    print person.name, 'owns', thing.name

В этих последних выражениях person.thing это сокращение для

db(db.thing.owner_id == person.id)

т.е. Набор (Set) вещей thing, на который ссылается текущая персона person. Этот синтаксис ломается, если таблица ссылок содержит несколько ссылок на указанную таблицу. В этом случае нужно быть более конкретным и использовать полный запрос (Query).

Сериализация Rows в представлениях

Давайте рассмотрим следующее действие, содержащее запрос

SQLTABLE
def index():
    return dict(rows = db(query).select())

Результат выборки select может отображаться в представлении со следующим синтаксисом:

{{extend 'layout.html'}}
<h1>Records</h1>
{{=rows}}

Что эквивалентно:

{{extend 'layout.html'}}
<h1>Records</h1>
{{=SQLTABLE(rows)}}

SQLTABLE преобразует строки в HTML-таблицу с заголовком, содержащим имена столбцов и одну строку для каждой записи. Строки через одну помечаются классом "even" (четные) и классом "odd" (нечетные). Под капотом, строки (Rows) сначала преобразуется в SQLTABLE объект (не следует путать с классом Table), а затем сериализуются. Значения, извлеченные из базы данных форматируются с помощью валидаторов, ассоциированных с полем, а затем экранируются.

Тем не менее, возможно и иногда удобно вызывать SQLTABLE в явном виде.

Конструктор SQLTABLE принимает следующие необязательные аргументы:

  • linkto лямбда-функция или действие, которое должно использоваться для ссылки на связанные поля (по умолчанию None).

Если присвоить ему строку с именем какого-либо действия, он будет генерировать ссылку на эту функцию передавая ей, как аргументы args, имя таблицы и идентификатор каждой записи (в таком порядке). Например:

linkto = 'pointed_function' # генерирует что-то вроде <а href="pointed_function/table_name/id_value">

Если вы хотите, чтобы была сгенерирована другая ссылка, то вы можете указать лямбда, которая получит в качестве параметров, значение идентификатора, тип объекта (например, таблицы), а также имя объекта. Например, если вы хотите получить аргументы args в обратном порядке:

linkto = lambda id, type, name: URL(f='pointed_function', args=[id, name])
  • upload URL-адрес или действие загрузки для обеспечения загрузки закачиваемых файлов (по умолчанию None)
  • headers словарь, сопоставляющий имена полей с их надписями, которые будут использоваться в качестве заголовков (по умолчанию {}). Он также может быть инструкцией. В настоящее время мы поддерживаем headers='fieldname:capitalize'.
  • truncate количество символов для усечения длинных значений в таблице (по умолчанию 16)
  • columns список имен полей, которые будут показаны в виде столбцов (в формате tablename.fieldname). Те, что вне списка не отображается (по умолчанию все).
  • **attributes общие вспомогательные атрибуты, передаваемые в основном внешнему объекту TABLE.

Вот пример:

{{extend 'layout.html'}}
<h1>Records</h1>
{{=SQLTABLE(rows,
            headers='fieldname:capitalize',
            truncate=100,
            upload=URL('download'))
}}

SQLFORM.grid
SQLFORM.smartgrid

SQLTABLE полезен, но бывают времена, когда требуется большего. SQLFORM.grid является расширением SQLTABLE, который создает таблицу с поисковыми функциями и пагинацией, а также возможность открывать детальные записи, создавать, редактировать и удалять записи. SQLFORM.smartgrid является дальнейшим обобщением, которое позволяет все вышесказанное, а также создает кнопки для доступа к ссылающимся записям.

Ниже приведен пример использования SQLFORM.grid:

def index():
    return dict(grid=SQLFORM.grid(query))

и соответствующее представление:

{{extend 'layout.html'}}
{{=grid}}

Для работы с несколькими строками, использование SQLFORM.grid и SQLFORM.smartgrid являются более предпочтительным, чем SQLTABLE, потому что они являются более мощными. Пожалуйста, смотрите Главу 7.

orderby, groupby, limitby, distinct, having,orderby_on_limitby,left,cache

Команда select принимает ряд дополнительных аргументов.

Аргумент orderby

Вы можете добыть записи, отсортированные по имени:

orderby
groupby
having

>>> for row in db().select(
        db.person.ALL, orderby=db.person.name):
        print row.name
Alex
Bob
Carl

Вы можете добыть записи, отсортированные по имени в обратном порядке (обратите внимание на тильду "~"):

>>> for row in db().select(db.person.ALL, orderby=~db.person.name):
        print row.name
Carl
Bob
Alex

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

>>> for row in db().select(db.person.ALL, orderby='<random>'):
        print row.name
Carl
Alex
Bob

Использование orderby='<random>' не поддерживается на Google NoSQL. Тем не менее, в этой ситуации и также во многих других, где встроенных возможностей недостаточно, может быть использован импорт:

import random
rows=db(...).select().sort(lambda row: random.random())

Вы можете отсортировать записи по нескольким полям петем конкатенации их с "|":

>>> for row in db().select(db.person.ALL, orderby=db.person.name|db.person.id):
        print row.name
Carl
Bob
Alex
Аргументы groupby, having

Используя groupby вместе с orderby, вы можете сгруппировать записи по одинаковому значению для указанного поля (это специфика конечной базы данных, и не распространяется на Google NoSQL):

>>> for row in db().select(db.person.ALL,
                           orderby=db.person.name,
                           groupby=db.person.name):
        print row.name
Alex
Bob
Carl

Вы можете использовать having в сочетании с groupby для группировки по условию (то есть группировка тех записей, которые удовлетворяют having условию).

>>> print db(query1).select(db.person.ALL, groupby=db.person.name, having=query2)

Обратите внимание на то, что query1 фильтрует записи, которые будут отображаться, query2 фильтрует записи, которые будут сгруппированы.

Аргумент distinct
distinct

С аргументом distinct=True, вы можете указать на выбор только отдельных записей. Это имеет тот же эффект, что и группировка с использованием всех заданных полей за исключением того, что она не требует сортировки. При использовании различий (distinct), важно не выбрать ВСЕ поля, и, в частности, чтобы не выбрать "id" поле, в противном случае все записи всегда будут различными (distinct).

Вот пример:

>>> for row in db().select(db.person.name, distinct=True):
        print row.name
Alex
Bob
Carl

Заметить, что distinct также может быть выражением, например:

>>> for row in db().select(db.person.name, distinct=db.person.name):
        print row.name
Alex
Bob
Carl
Аргумент limitby

С аргументом limitby=(min, max), вы можете выбрать подмножество записей из offset=min, но не включая offset=max (В этом случае, первые два начинаются с нуля):

limitby
>>> for row in db().select(db.person.ALL, limitby=(0, 2)):
        print row.name
Alex
Bob
Аргумент orderby_on_limitby
orderby_on_limitby

Обратите внимание, что DAL по умолчанию неявно добавляет orderby при использовании limitby. Это обеспечивает, чтобы один и тот же самый запрос каждый раз возвращал те же самые результаты, важно для пагинации. Но это может привести к проблемам производительности. Используйте orderby_on_limitby = False для изменения (по умолчанию True).

Аргумент left

Обсуждается ниже в разделе, посвященном сцепкам

Аргумент cache, cacheable

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

rows = db(query).select(cache=(cache.ram, 3600), cacheable=True)

Смотрите обсуждение по вопросам "кэширования выборок", ниже, для понять, какие существуют компромиссы.

Логические операторы

Запросы могут быть объединены с помощью логического оператора И (AND ) "&":

and
or
not

>>> rows = db((db.person.name=='Alex') & (db.person.id>3)).select()
>>> for row in rows: print row.id, row.name
4 Alex

и логического оператора ИЛИ (OR) "|":

>>> rows = db((db.person.name == 'Alex') | (db.person.id > 3)).select()
>>> for row in rows:
        print row.id, row.name
1 Alex

Вы можете отвергнуть запрос (или подзапрос) с логическим оператором НЕ РАВНО "!=":

>>> rows = db((db.person.name != 'Alex') | (db.person.id > 3)).select()
>>> for row in rows:
        print row.id, row.name
2 Bob
3 Carl

или путем явного отрицания с унарным оператором "~":

>>> rows = db(~(db.person.name == 'Alex') | (db.person.id > 3)).select()
>>> for row in rows:
        print row.id, row.name
2 Bob
3 Carl

Из-за ограничений Python, связанных с перегрузкой "and" и "or" операторов, они не могут быть использованы при формировании запросов. Вместо этого необходимо использовать бинарные операторы "&" и "|". Обратите внимание, что эти операторы (в отличие от "and" и "or") имеют более высокий приоритет, чем операторы сравнения, так что "лишние" скобки в приведенных выше примерах, являются обязательными. Аналогичным образом, унарный оператор "~" имеет более высокий приоритет, чем операторы сравнения, поэтому ~-отрицающие сравнения также должны быть в круглых скобках.

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

>>> query = db.person.name != 'Alex'
>>> query &= db.person.id > 3
>>> query |= db.person.name == 'John'

Методы count, isempty, delete, update

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

count
isempty

>>> print db(db.person.id > 0).count()
3

Заметить, что count принимает необязательный аргумент distinct, который по умолчанию False, и его работа очень напоминает работу того же самого аргумента для select. countимеет также cache Аргумент, работа которого очень напоминает работу эквивалентного аргумента метода select.

Иногда вам может понадобиться проверка наличия пустой таблицы. Более эффективным способом, чем подсчет, является использование метода isempty:

>>> print db(db.person.id > 0).isempty()
False

или, что эквивалентно:

>>> print db(db.person).isempty()
False

Вы можете удалить записи в наборе:

delete
>>> db(db.person.id > 3).delete()

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

update
>>> db(db.person.id > 3).update(name='Ken')

Выражения

Значение, присвоенное оператору обновления может быть выражением. Например, рассмотрим эту модель

>>> db.define_table('person',
                    Field('name'),
                    Field('visits', 'integer', default=0))

>>> db(db.person.name == 'Massimo').update(visits = db.person.visits + 1)

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

>>> db.define_table('person',
                    Field('name'),
                    Field('visits', 'integer', default=0),
                    Field('clicks', 'integer', default=0))

>>> db(db.person.visits == db.person.clicks + 1).delete()

Выражение case
case

Выражение может содержать условие для случая case, например:

>>> db.define_table('person', Field('name'))
>>> condition = db.person.name.startswith('M')
>>> yes_or_no = condition.case('Yes', 'No')
>>> for row in db().select(db.person.name, yes_or_no):
...     print row.person.name,  row(yes_or_no)
Max Yes
John No

Метод update_record

update_record

web2py также позволяет обновлять отдельную запись, которая уже находится в памяти с помощью update_record

>>> row = db(db.person.id == 2).select().first()
>>> row.update_record(name='Curt')

Метод update_record не следует путать с методом

>>> row.update(name='Curt')

потому что для одной строки, метод update обновляет объект строки, но не записи базы данных, в отличии от update_record.

Кроме того, можно изменить атрибуты строки (по одной за раз), а затем вызвать update_record() без аргументов для сохранения изменений:

>>> row = db(db.person.id > 2).select().first()
>>> row.name = 'Curt'
>>> row.update_record() # сохранение вышеуказанного изменения

Метод update_record доступен только если поле таблицы id включается в выборку select и cacheable не задано значение True.

Вставка и обновление из словаря

Общая проблема состоит из необходимости вставки или обновления записей в таблицу, когда все параметры - имя таблицы, обновляемое поле, и значение поля, хранятся в переменных. Например: tablename, fieldname, и value.

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

db[tablename].insert(**{fieldname:value})

Обновление записи с заданным идентификатором id может быть сделано:

_id

db(db[tablename]._id == id).update(**{fieldname:value})

Обратите внимание, мы использовали table._id вместо table.id. Таким образом, запрос работает даже для таблиц с полем типа "id" которое имеет имя, отличное от "id".

Методы first и last

first
last

Возьмем Rows объект, содержащий записи:

>>> rows = db(query).select()
>>> first_row = rows.first()
>>> last_row = rows.last()

что эквивалентно

>>> first_row = rows[0] if len(rows)>0 else None
>>> last_row = rows[-1] if len(rows)>0 else None

Обратите внимание, first() и last() позволяют получить, очевидно, первую и последнюю запись, присутствующую в запросе, но это не будет означать, что эти записи будут первой или последней из вставленных записей. В случае, если вы хотите получить первую или последнюю запись из введенных в данную таблицу, то не забудьте использовать orderby=db.table_name.id. Если вы забыли, то вы получите только первую и последнюю запись возвращенного ответа на ваш запрос, который оптимизатором запросов конечной базы данных зачастую составляется в случайном порядке.

Методы as_dict и as_list

as_list
as_dict

Объект Row может быть сериализован в обычный словарь, используя метод as_dict() и объект Rows может быть сериализовать в список словарей с помощью метода as_list(). Вот несколько примеров:

>>> rows = db(query).select()
>>> rows_list = rows.as_list()
>>> first_row_dict = rows.first().as_dict()

Эти методы удобны для передачи Rows в общие представления и/или для сохранения Rows в сессиях (так как Rows объекты сами по себе не могут быть сериализованы, поскольку содержать ссылку на открытое соединение базы данных):

>>> rows = db(query).select()
>>> session.rows = rows  # не допускается!
>>> session.rows = rows.as_list()  # допускается!

Комбинирование строк

Объекты Row могут быть скомбинированы на уровне Python. Здесь мы предполагаем:

>>> print rows1
person.name
Max
Tim
>>> print rows2
person.name
John
Tim

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

>>> rows3 = rows1 & rows2
>>> print rows3
name
Tim

Вы можете сделать объединение записей с удалением дубликатов:

>>> rows3 = rows1 | rows2
>>> print rows3
name
Max
Tim
John

Методы find, exclude, sort

find
exclude
sort

Иногда вам необходимо выполнить две выборки и одна содержит подмножество предыдущей выборки. В данном случае бессмысленно получать доступ к базе данных снова. Объекты find, exclude и sort позволяют манипулировать объектом Rows и генерировать другой без доступа к базе данных. Более конкретно:

  • find возвращает новый набор строк, отфильтрованный по условию, и оставляет оригинал без изменений.
  • exclude возвращает новый набор строк Rows, отфильтрованный по условию, и удаляет их из исходных строк Rows.
  • sort возвращает новый набор строк, отсортированный по условию и оставляет оригинал без изменений.

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

Вот пример использования:

>>> db.define_table('person', Field('name'))
>>> db.person.insert(name='John')
>>> db.person.insert(name='Max')
>>> db.person.insert(name='Alex')
>>> rows = db(db.person).select()
>>> for row in rows.find(lambda row: row.name[0]=='M'):
        print row.name
Max
>>> print len(rows)
3
>>> for row in rows.exclude(lambda row: row.name[0]=='M'):
        print row.name
Max
>>> print len(rows)
2
>>> for row in rows.sort(lambda row: row.name):
        print row.name
Alex
John

Они могут быть скомбинированы:

>>> rows = db(db.person).select()
>>> rows = rows.find(lambda row: 'x' in row.name
                     ).sort(lambda row: row.name)
>>> for row in rows:
        print row.name
Alex
Max

Sort принимает необязательный аргумент reverse=True с очевидным значением.

Метод find имеет необязательный аргумент limitby с тем же синтаксисом и функциональностью как и у метода select объекта Set.

Другие методы

Метод update_or_insert

update_or_insert

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

db.define_table('person',
                Field('name'),
                Field('birthplace'))

db.person.update_or_insert(name='John', birthplace='Chicago')

Запись будет вставлена только если нет другого пользователя по имени Джон родом из Чикаго.

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

db.person.update_or_insert(db.person.name == 'John',
                           name='John',
                           birthplace='Chicago')

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

Критерий отбора в приведенном выше примере это одно поле. Он также может быть запросом, таким как

db.person.update_or_insert((db.person.name == 'John') & (db.person.birthplace == 'Chicago'),
                           name='John',
                           birthplace='Chicago',
                           pet='Rover')

Методы validate_and_insert, validate_and_update

validate_and_insert
validate_and_update

Функция

ret = db.mytable.validate_and_insert(field='value')

работает очень похоже

id = db.mytable.insert(field='value')

за исключением того, что она вызывает валидаторы для полей перед выполнением вставки и выручает, если проверка не проходит. Если проверка не проходит ошибки можно найти в ret.errors. Переменная ret.errors удерживает сопоставление ключей и значений, где каждый ключ является именем поля, проверка которого не удалась, и значением ключа является результат ошибочной проверки (сильно напоминает form.errors). Если она проходит, то идентификатор новой записи содержится в ret.id. Имейте в виду, что обычно проверка выполняется с помощью логики обработки форм поэтому эта функция редко требуется.

По аналогии

ret = db(query).validate_and_update(field='value')

работает очень похоже, как

num = db(query).update(field='value')

за исключением того, что она вызывает валидаторы для полей перед выполнением обновления. Обратите внимание на то, что он работает только если запрос содержит одну таблицу. Количество обновленных записей можно найти в ret.updated и ошибки будут в ret.errors.

Метод smart_query (экспериментальный)

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

name contain m and age greater than 18 #(Имя содержит букву m и возраст больше 18)

DAL предоставляет способ для синтаксического анализа этого типа запросов:

search = 'name contain m and age greater than 18'
rows = db.smart_query([db.person], search).select()

Первым аргументом должен быть список таблиц или полей, которые должны быть разрешены в поиске. Он поднимает RuntimeError, если строка поиска является недопустимой. Эта функция может быть использована для построения RESTful интерфейсов (смотри Главу 10), и она используется внутри SQLFORM.grid и SQLFORM.smartgrid.

В строке поиска smartquery, поле может быть идентифицировано только с помощью fieldname и/или с помощью tablename.fieldname. Строки могут быть заключены в двойные кавычки, если они содержат пробелы.

Вычисляемые поля

compute

DAL поля могут иметь compute атрибут. Это должно быть функцией (или лямбда), которая принимает объект Row и возвращает значение для поля. Когда новая запись будет модифицирована, включая методы вставки и обновлений, и если значение поля не предусмотрено, то web2py попытается вычислить на основании других значений полей с помощью функции compute. Вот пример:

>>> db.define_table('item',
                    Field('unit_price','double'),
                    Field('quantity','integer'),
                    Field('total_price',
                          compute=lambda r: r['unit_price'] * r['quantity']))

>>> r = db.item.insert(unit_price=1.99, quantity=5)
>>> print r.total_price
9.95

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

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

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

Виртуальные поля

virtual fields

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

Новый стиль виртуальных полей

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

Здесь мы рассмотрим тот же пример, что и в предыдущем пункте. В частности, мы рассмотрим следующую модель:

>>> db.define_table('item',
                    Field('unit_price', 'double'),
                    Field('quantity', 'integer'))

Можно определить виртуальное поле total_price

>>> db.item.total_price =         Field.Virtual('total_price',
                      lambda row: row.item.unit_price * row.item.quantity)

т.е. просто сказать новому поле total_price быть Field.Virtual. Единственным аргументом конструктора является функция, которая принимает строку и возвращает вычисленные значения.

Виртуальное поле, определенное как описано выше, автоматически вычисляется для всех записей при выборе записи:

>>> for row in db(db.item).select():
        print row.total_price

Кроме того, можно определить метод полей, которые вычисляются по требованию, при вызове. Например:

>>> db.item.discounted_total =         Field.Method(lambda row, discount=0.0:
                     row.item.unit_price * row.item.quantity * (1.0 - discount / 100))

В этом случае row.discounted_total является не значением, а функцией. Функция принимает те же аргументы, что и функция, переданная конструктору Method для row за исключением того, что они являются неявными (думайте что это self для объектов rows).

Ленивое поле в приведенном выше примере, позволяет вычислить общую цену для каждого элемента item:

>>> for row in db(db.item).select(): print row.discounted_total()

И оно также позволяет передавать дополнительный discount процент (15%):

>>> for row in db(db.item).select():
        print row.discounted_total(15)

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

>>> db.define_table('item',
                    Field('unit_price', 'double'),
                    Field('quantity', 'integer'),
                    Field.Virtual('total_price',
                                  lambda row: ...),
                    Field.Method('discounted_total',
                                 lambda row, discount=0.0: ...))

Имейте в виду, что виртуальные поля не имеют тех же самых атрибутов, что и другие поля (default, readable, requires, и т.д). В старых версиях web2py они не появляются в списке db.table.fields и они требуют особого подхода для отображения в SQLFORM.grid и SQLFORM.smartgrid. Смотрите обсуждение grids и виртуальных полей в главе Формы.

Старый стиль виртуальных полей

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

>>> db.define_table('item',
                    Field('unit_price', 'double'),
                    Field('quantity', 'integer'))

Можно определить виртуальное поле total_price как

>>> class MyVirtualFields(object):
        def total_price(self):
            return self.item.unit_price*self.item.quantity
>>> db.item.virtualfields.append(MyVirtualFields())

Обратите внимание на то, что каждый метод класса, который принимает один аргумент (self) является новым виртуальным полем. Аргумент self относится к каждой отдельной строке row выборки. Значения полей получаются по полному пути, как в выражении self.item.unit_price. Таблица связывается с виртуальными полями путем добавления экземпляра созданного класса виртуального поля в атрибут таблицы virtualfields.

Виртуальные поля также могут получить доступ к рекурсивным полям, как в

>>> db.define_table('item',
                    Field('unit_price', 'double'))

>>> db.define_table('order_item',
                    Field('item', 'reference item'),
                    Field('quantity', 'integer'))
>>> class MyVirtualFields(object):
        def total_price(self):
            return self.order_item.item.unit_price * self.order_item.quantity

>>> db.order_item.virtualfields.append(MyVirtualFields())

Обратите внимание на доступ к рекурсивному полю self.order_item.item.unit_price, где self это петляющая запись.

Они также могут применяться к результату СЦЕПКИ (JOIN)

>>> db.define_table('item',
                    Field('unit_price', 'double'))

>>> db.define_table('order_item',
                    Field('item', 'reference item'),
                    Field('quantity', 'integer'))

>>> rows = db(db.order_item.item == db.item.id).select()
>>> class MyVirtualFields(object):
        def total_price(self):
            return self.item.unit_price * self.order_item.quantity

>>> rows.setvirtualfields(order_item=MyVirtualFields())

>>> for row in rows:
        print row.order_item.total_price

Заметьте, как в этом случае отличается синтаксис. Виртуальное поле получает доступ к обоим полям self.item.unit_price и self.order_item.quantity, которые принадлежат к сцепке выборки. Виртуальное поле прикрепляется к строкам таблицы с помощью метода setvirtualfields объекта rows. Этот метод принимает произвольное число именованных аргументов и может быть использован для установки нескольких виртуальных полей, определенных в нескольких классах, и прикрепления их к нескольким таблицам:

>>> class MyVirtualFields1(object):
        def discounted_unit_price(self):
            return self.item.unit_price * 0.90
>>> class MyVirtualFields2(object):
        def total_price(self):
            return self.item.unit_price * self.order_item.quantity

        def discounted_total_price(self):
            return self.item.discounted_unit_price * self.order_item.quantity

>>> rows.setvirtualfields(item=MyVirtualFields1(),
                          order_item=MyVirtualFields2())

>>> for row in rows:
        print row.order_item.discounted_total_price

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

>>> db.define_table('item',
                    Field('unit_price', 'double'),
                    Field('quantity', 'integer'))

>>> class MyVirtualFields(object):
        def lazy_total_price(self):
            def lazy(self=self):
                return self.item.unit_price * self.item.quantity
            return lazy

>>> db.item.virtualfields.append(MyVirtualFields())

>>> for item in db(db.item).select():
        print item.lazy_total_price()

или короче, с помощью лямбда-функции:

>>> class MyVirtualFields(object):
        def lazy_total_price(self):
            return lambda self=self: self.item.unit_price * self.item.quantity

Отношения один ко многим

one to many

Чтобы проиллюстрировать, как реализовать отношения один ко многим с помощью web2py DAL, давайте определим другую таблицу "thing", которая обращается к таблице "person", которую мы переопределяем здесь:

>>> db.define_table('person',
                    Field('name'),
                    format='%(name)s')

>>> db.define_table('thing',
                    Field('name'),
                    Field('owner_id', 'reference person'),
                    format='%(name)s')

Таблица "thing" имеет два поля, имя вещи и владельца вещи. Поле "owner_id" является ссылочным полем. Поле ссылочного типа может быть указано двумя эквивалентными способами:

Field('owner_id', 'reference person')
Field('owner_id', db.person)

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

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

>>> print db.thing.owner_id.type
reference person

Теперь вставьте три вещи, две принадлежат Алекс и одна Бобу:

>>> db.thing.insert(name='Boat', owner_id=1)
1
>>> db.thing.insert(name='Chair', owner_id=1)
2
>>> db.thing.insert(name='Shoes', owner_id=2)
3

Вы можете сделать выборку, как если бы вы это сделали для любой другой таблицы:

>>> for row in db(db.thing.owner_id == 1).select():
        print row.name
Boat
Chair

Поскольку вещь (thing) содержит ссылку на человека (person), то человек может иметь много вещей, поэтому запись в таблице person в настоящее время приобретает новый атрибут thing, который является набором Set и определяет вещи этого человека. Это позволяет с легкостью в цикле пройтись по всем лицам и добыть их вещи:

referencing
>>> for person in db().select(db.person.ALL):
        print person.name
        for thing in person.thing.select():
            print '    ', thing.name
Alex
     Boat
     Chair
Bob
     Shoes
Carl

Внутренние сцепки

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

Rows
inner join
join

>>> rows = db(db.person.id == db.thing.owner_id).select()

>>> for row in rows:
        print row.person.name, 'has', row.thing.name

Alex has Boat
Alex has Chair
Bob has Shoes

Заметим, что web2py сделал сцепку, поэтому теперь строки содержат две записи, по одной из каждой таблицы, связанные друг с другом. Поскольку две записи могут иметь поля с конфликтующими именами, то вам необходимо указать таблицу, из строки которой извлекается значение поля. Это означает, что перед тем как сделать:

row.name

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

row.person.name

или:

row.thing.name

Существует альтернативный синтаксис для ВНУТРЕННИХ СЦЕПОК (INNER JOINS):

>>> rows = db(db.person).select(join=db.thing.on(db.person.id == db.thing.owner_id))

>>> for row in rows:
    print row.person.name, 'has', row.thing.name

Alex has Boat
Alex has Chair
Bob has Shoes

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

>>> db.define_table('thing',
                    Field('name'),
                    Field('owner_id1', 'reference person'),
                    Field('owner_id2', 'reference person'))

>>> rows =         db(db.person).select(join=[db.person.with_alias('owner_id1').on(db.person.id == db.thing.owner_id1),
                                   db.person.with_alias('owner_id2').on(db.person.id == db.thing.owner_id2)])

Значение join может быть списком db.table.on(...) для сцепки.

Левая внешняя сцепка

Обратите внимание на то, что Карл не появлялся в приведенном выше списке, потому что он не имеет вещей. Если вы собираетесь сделать выборку людей (независимо есть ли у них вещи или нет) и их вещей (если они имеются вообще), тогда вам необходимо выполнить ЛЕВУЮ ВНЕШНЮЮ СЦЕПКУ (LEFT OUTER JOIN). Это делается с помощью аргумента "left" в команде на выборку. Вот пример:

Rows
left outer join
outer join

>>> rows = db().select(db.person.ALL, db.thing.ALL,
                       left=db.thing.on(db.person.id == db.thing.owner_id))

>>> for row in rows:
        print row.person.name, 'has', row.thing.name

Alex has Boat
Alex has Chair
Bob has Shoes
Carl has None

где:

left = db.thing.on(...)

делает запрос на левую сцепку. При этом аргумент db.thing.on это требуемое условие для сцепки (точно такое же как и использованное выше для внутренней сцепки). В случае левой сцепки, необходимо явно указать какие поля выбираются.

Множественные левые сцепки могут быть скомбинированы путем передачи списка или кортежа из db.mytable.on(...) в left атрибут.

Группировка и подсчет

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

grouping
>>> count = db.person.id.count()
>>> for row in db(db.person.id == db.thing.owner_id
                  ).select(db.person.name, count, groupby=db.person.name):
        print row.person.name, row[count]

Alex 2
Bob 1

Обратите внимание, что оператор count (который является встроенным) используется в качестве поля. Единственная проблема здесь в том, как получить информацию. Каждая строка содержит очевидно человека и количество, но количество (count) не является полем таблицы person и не является таблицей. Так куда же они направляются? Они направляются в объект storage, представляющий запись с ключом, равным самому выражении запроса. Метод count объекта Field имеет необязательный distinct аргумент. При установке в True он указывает, что следует учитывать только отдельные значения поля в вопросе.

Многие ко многим

many-to-many

В предыдущих примерах, мы допускали вещам иметь одного владельца, но один человек может иметь много вещей. Что делать, если лодка принадлежит и Алексу и Курту? Это требует отношение многие-ко-многим, и оно реализуется через промежуточную таблицу, которая связывает человека с вещью через отношения собственности.

Вот как это сделать:

>>> db.define_table('person',
                    Field('name'))

>>> db.define_table('thing',
                    Field('name'))

>>> db.define_table('ownership',
                    Field('person', 'reference person'),
                    Field('thing', 'reference thing'))

существующие отношения собственности теперь можно переписать в виде:

>>> db.ownership.insert(person=1, thing=1)  # Алекс владеет Лодкой
>>> db.ownership.insert(person=1, thing=2)  # Алекс владеет Креслом
>>> db.ownership.insert(person=2, thing=3)  # Боб владеет Обувью

Теперь вы можете добавить новое отношение, что Курт является совладельцем Лодки:

>>> db.ownership.insert(person=3, thing=1)  # Курт тоже владеет Лодкой

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

>>> persons_and_things = db((db.person.id == db.ownership.person) &
                            (db.thing.id == db.ownership.thing))

Теперь вам будет легче выбирать всех людей и их вещи из нового набора Set:

>>> for row in persons_and_things.select():
        print row.person.name, row.thing.name

Alex Boat
Alex Chair
Bob Shoes
Curt Boat

Кроме того, вы можете поискать все вещи, принадлежащие Алексу:

>>> for row in persons_and_things(db.person.name == 'Alex').select():
        print row.thing.name

Boat
Chair

и всех владельцев Лодки:

>>> for row in persons_and_things(db.thing.name == 'Boat').select():
        print row.person.name

Alex
Curt

Более легкой альтернативой Многие ко Многим отношений является пометка. Пометка обсуждается в контексте IS_IN_DB валидатора. Пометка работает даже на таких движках баз данных, которые не поддерживают СЦЕПКИ вроде Google App Engine NoSQL.

Поля типа list:<type> и метод contains

list:string
list:integer
list:reference
contains
multiple
tags

web2py предоставляет следующие специальные типы полей:

list:string
list:integer
list:reference <table>

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

На Google App Engine NoSQL поле типа list:string сопоставляется с типом StringListProperty, два других сопоставляются с ListProperty(int). В реляционных базах данных они сопоставляются с текстовыми полями, которые содержат список элементов, разделенных |. Например [1,2,3] является сопоставлением |1|2|3|.

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

Вы можете использовать list:string, например, следующим образом:

>>> db.define_table('product',
                    Field('name'),
                    Field('colors', 'list:string'))

>>> db.product.colors.requires=IS_IN_SET(('red', 'blue', 'green'))

>>> db.product.insert(name='Toy Car', colors=['red', 'green'])

>>> products = db(db.product.colors.contains('red')).select()

>>> for item in products:
        print item.name, item.colors

Toy Car ['red', 'green']

list:integer работает таким же образом, но элементы должны быть целыми числами.

Как обычно, требования навязываются на уровне форм, а не на уровне insert.

Для полей типа list:<type> оператор contains(value) переводит в нетривиальный запрос, который проверяет наличие списков, содержащих value. Оператор contains также работает для регулярного string и text полей и он сопоставляется с LIKE '%value%'.

Типа list:reference и оператор contains(value) особенно полезны для денормализации отношений многие-ко-многим. Вот пример:

>>> db.define_table('tag',
                    Field('name'),
                    format='%(name)s')

>>> db.define_table('product',
                    Field('name'),
                    Field('tags', 'list:reference tag'))

>>> a = db.tag.insert(name='red')

>>> b = db.tag.insert(name='green')

>>> c = db.tag.insert(name='blue')

>>> db.product.insert(name='Toy Car', tags=[a, b, c])

>>> products = db(db.product.tags.contains(b)).select()

>>> for item in products:
        print item.name, item.tags

Toy Car [1, 2, 3]

>>> for item in products:
        print item.name, db.product.tags.represent(item.tags)

Toy Car red, green, blue

Обратите внимание на то, что поле list:reference tag получает ограничение по умолчанию

requires = IS_IN_DB(db, 'tag.id', db.tag._format, multiple=True)

что производит множественный SELECT/OPTION сброс-ящик (drop-box) в формах.

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

В то время как тип list:reference имеет валидатор по умолчанию и представление по умолчанию, поля типа list:integer и list:string не имеют такого. Таким образом, эти два нуждаются в IS_IN_SET или IS_IN_DB валидаторе, если вы хотите использовать их в формах.

Другие операторы

web2py имеет другие операторы, которые предоставляют API для доступа, которые эквивалентны операторам SQL.

Давайте определим еще одну таблицу "log" для хранения событий безопасности, их времени события (event_time) и серьезности (severity), где серьезность является целым числом.

date
datetime
time

>>> db.define_table('log', Field('event'),
                           Field('event_time', 'datetime'),
                           Field('severity', 'integer'))

Как и прежде, вставим несколько событий, "сканирование портов", "XSS инъекции" и "несанкционированный вход". Ради примера, вы можете регистрировать события с тем же самым event_time, но с различными степенями серьезности (1, 2, и 3 соответственно).

>>> import datetime
>>> now = datetime.datetime.now()
>>> print db.log.insert(
        event='сканирование портов', event_time=now, severity=1)
1
>>> print db.log.insert(
        event='XSS инъекции', event_time=now, severity=2)
2
>>> print db.log.insert(
        event='несанкционированный вход', event_time=now, severity=3)
3

Методы like, ilike, regexp, startswith, endswith, contains, upper, lower

like
ilike
startswith
endswith
regexp
contains
upper
lower

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

>>> for row in db(db.log.event.like('скан%')).select():
        print row.event
сканирование портов

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

Оператор like соответствует с LIKE слову в ANSI-SQL. LIKE чувствителен к регистру в большинстве баз данных, и зависит от параметров сортировки самой базы данных. Следовательно, метод like чувствителен к регистру, но это может быть сделано и без учета регистра

db.mytable.myfield.like('value', case_sensitive=False)

web2py также предоставляет некоторые сокращения:

db.mytable.myfield.startswith('value')
db.mytable.myfield.endswith('value')
db.mytable.myfield.contains('value')

которые примерно равны, соответственно

db.mytable.myfield.like('value%')
db.mytable.myfield.like('%value')
db.mytable.myfield.like('%value%')

Заметьте, что contains имеет особое значение для list:<type> полей и это обсуждалось в предыдущем разделе.

Методу containsтакже может быть передан список значений и необязательный логический аргумент all для поиска записей, которые содержат все искомые значения:

db.mytable.myfield.contains(['value1', 'value2'], all=True)

или любое значение из списка

db.mytable.myfield.contains(['value1', 'value2'], all=False)

Существует также метод regexp, который работает как и like метод, но допускает синтаксис регулярных выражений для просматриваемого выражения. Это поддерживается только PostgreSQL, MySQL, Oracle и SQLite (с различной степенью поддержки).

методы upper и lower позволяют преобразовать значение поля в верхний или нижний регистр, и вы также можете скомбинировать их с like оператором:

upper
lower

>>> for row in db(db.log.event.upper().like('СКАН%')).select():
        print row.event

сканирование портов

Методы year, month, day, hour, minutes, seconds

hour
minutes
seconds
day
month
year

Поля date и datetime имеют методы day, month и year. Поля datetime и time имеют методы hour, minutes и seconds. Вот пример:

>>> for row in db(db.log.event_time.year() == 2013).select():
        print row.event

сканирование портов
XSS инъекции
несанкционированный вход

Метод belongs

Оператор SQL IN реализуется посредством метода belongs, который возвращает истину, если значение поля принадлежит к указанному набору (списку или кортежам):

belongs
>>> for row in db(db.log.severity.belongs((1, 2))).select():
        print row.event

сканирование портов
XSS инъекции

DAL также позволяет вложить select в качестве аргумента оператора belongs. Единственное ограничение в том, что вложенный select должен быть _select, а не select, и только одно поле должно быть выбрано явным образом, а именно то, которое определяет набор.

nested select
>>> bad_days = db(db.log.severity == 3)._select(db.log.event_time)

>>> for row in db(db.log.event_time.belongs(bad_days)).select():
        print row.event

сканирование портов
XSS инъекции
несанкционированный вход

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

db.define_table('person', Field('name'))
db.define_table('thing',
                Field('name'),
                Field('owner_id', 'reference thing'))

db(db.thing.owner_id.belongs(db.person.name == 'Jonathan')).select()

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

nested_select

Вложенный select также может быть использован в качестве вставки/обновления значения, но в этом случае синтаксис отличается:

lazy = db(db.person.name == 'Jonathan').nested_select(db.person.id)

db(db.thing.id == 1).update(owner_id = lazy)

В этом случае lazy является вложенным выражением, которое вычисляет id личности "Джонатан". Две линии приводят к одному единственному запросу SQL.

Функции sum, avg, min, max and len

sum
avg
min
max

Ранее вы использовали оператор count, чтобы подсчитать количество записей. Точно так же, вы можете использовать оператор sum для добавления (суммирования) значений конкретного поля из группы записей. Как и в случае с count, результат суммирования извлекается с помощью объекта хранилища:

>>> sum = db.log.severity.sum()
>>> print db().select(sum).first()[sum]
6

Вы можете также использовать avg, min и max для вычисления среднего, минимального и максимального значения соответственно для выбранных записей. Например:

>>> max = db.log.severity.max()
>>> print db().select(max).first()[max]
3

.len() вычисляет длину строкового, текстового или логического полей.

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

>>> sum = (db.log.severity.len() + 1).sum()
>>> print db().select(sum).first()[sum]

Подстроки

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

db(db.thing).select(distinct = db.thing.name[:3])

Значения по умолчанию с coalesce и coalesce_zero

Есть моменты, когда вам необходимо вытянуть значение из базы данных, но также необходимо значения по умолчанию, если значение записи равняется NULL. В SQL для этого есть ключевое слово, COALESCE. web2py имеет эквивалентный метод coalesce:

>>> db.define_table('sysuser', Field('username'), Field('fullname'))
>>> db.sysuser.insert(username='max', fullname='Max Power')
>>> db.sysuser.insert(username='tim', fullname=None)
print db(db.sysuser).select(db.sysuser.fullname.coalesce(db.sysuser.username))
"COALESCE(sysuser.fullname, sysuser.username)"
Max Power
tim

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

>>> db.define_table('sysuser', Field('username'), Field('points'))
>>> db.sysuser.insert(username='max', points=10)
>>> db.sysuser.insert(username='tim', points=None)
>>> print db(db.sysuser).select(db.sysuser.points.coalesce_zero().sum())
"SUM(COALESCE(sysuser.points,0))"
10

Генерация сырого SQL

raw SQL

Иногда вам нужно сгенерировать SQL, но не выполнить его. Это легко сделать с помощью web2py поскольку каждая команда, которая выполняет ввода-вывода базы данных, имеет эквивалентную команду, которая не делает, а просто возвращает SQL, который был бы выполнен. Эти команды имеют одинаковые имена и синтаксис как и функциональные единицы, но они начинаются с подчеркивания:

Вот _insert

_insert

>>> print db.person._insert(name='Alex')
INSERT INTO person(name) VALUES ('Alex');

Вот _count

_count

>>> print db(db.person.name == 'Alex')._count()
SELECT count(*) FROM person WHERE person.name='Alex';

Вот _select

_select

>>> print db(db.person.name == 'Alex')._select()
SELECT person.id, person.name FROM person WHERE person.name='Alex';

Вот _delete

_delete

>>> print db(db.person.name == 'Alex')._delete()
DELETE FROM person WHERE person.name='Alex';

И наконец, вот _update

_update

>>> print db(db.person.name == 'Alex')._update()
UPDATE person SET  WHERE person.name='Alex';

Кроме того, вы всегда можете использовать db._lastsql для возврата самого последнего кода SQL, независимо от того, был ли он выполнен вручную с помощью ExecuteSQL или SQL был сгенерирован через DAL.

Экспорт и импорт данных

export
import

CSV (одна таблица за раз)

Когда Rows объект преобразуется в строку, то он автоматически сериализуется в CSV:

csv
>>> rows = db(db.person.id == db.thing.owner_id).select()
>>> print rows

person.id, person.name, thing.id, thing.name, thing.owner_id
1, Alex, 1, Boat, 1
1, Alex, 2, Chair, 1
2, Bob, 3, Shoes, 2

You can serialize a single table in CSV and store it in a file "test.csv":

>>> open('test.csv', 'wb').write(str(db(db.person.id).select()))

Это эквивалентно

>>> rows = db(db.person.id).select()
>>> rows.export_to_csv_file(open('test.csv', 'wb'))

Вы можете прочитать CSV файл обратно:

>>> db.person.import_from_csv_file(open('test.csv', 'r'))

При импорте, web2py ищет имена полей в заголовке CSV. В этом примере, он находит два столбца: "person.id" и "person.name". Он игнорирует префикс "person.", и он игнорирует "id" поля. Тогда все записи будут добавлены и им будут назначены новые идентификаторы. Обе эти операции могут быть выполнены через веб-интерфейс appadmin.

CSV (все таблицы разом)

В web2py, вы можете выполнить резервное копирование/восстановление всей базы данных с двумя командами:

Экспортировать:

>>> db.export_to_csv_file(open('somefile.csv', 'wb'))

Импортировать:

>>> db.import_from_csv_file(open('somefile.csv', 'rb'))

Этот механизм может быть использован даже если выполняется импорт в базу данных другого типа, чем экспортированная база данных. Данные хранятся в "somefile.csv" как CSV файл где каждая таблица начинается с одной строки, которая указывает на имя таблицы, а другие строки начинаются с имен полей:

TABLE tablename
field1, field2, field3, ...

Две таблицы разделены \r\n\r\n. Файл заканчивается строкой

END

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

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

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

CSV и удаленная синхронизация базы данных

Рассмотрим следующую модель:

db = DAL('sqlite:memory:')
db.define_table('person',
                Field('name'),
                format='%(name)s')
db.define_table('thing',
                Field('owner_id', 'reference person'),
                Field('name'),
                format='%(name)s')

if not db(db.person).count():
    id = db.person.insert(name="Massimo")
    db.thing.insert(owner_id=id, name="Chair")

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

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

  • имеют уникальный id (UUID),
  • иметь event_time (чтобы выяснить, какая из них более поздняя, если имеется несколько копий),
  • ссылаться по UUID вместо id.

Это может быть достигнуто без изменения web2py. Вот что нужно делать:

Измените вышеупомянутую модель в:

db.define_table('person',
                Field('uuid', length=64, default=lambda:str(uuid.uuid4())),
                Field('modified_on', 'datetime', default=request.now),
                Field('name'),
                format='%(name)s')

db.define_table('thing',
                Field('uuid', length=64, default=lambda:str(uuid.uuid4())),
                Field('modified_on', 'datetime', default=request.now),
                Field('owner_id', length=64),
                Field('name'),
                format='%(name)s')

db.thing.owner_id.requires = IS_IN_DB(db,'person.uuid','%(name)s')

if not db(db.person.id).count():
    id = uuid.uuid4()
    db.person.insert(name="Massimo", uuid=id)
    db.thing.insert(owner_id=id, name="Chair")

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

Создайте действие контроллера для экспорта базы данных:

def export():
    s = StringIO.StringIO()
    db.export_to_csv_file(s)
    response.headers['Content-Type'] = 'text/csv'
    return s.getvalue()

Создайте действие контроллера для импорта сохраненной копии другой базы данных и синхронизации записей:

def import_and_sync():
    form = FORM(INPUT(_type='file', _name='data'), INPUT(_type='submit'))
    if form.process().accepted:
        db.import_from_csv_file(form.vars.data.file,unique=False)
        # для каждой таблицы
        for table in db.tables:
            # для каждого UUID, удалив все, кроме последней
            items = db(db[table]).select(db[table].id,
                                         db[table].uuid,
                                         orderby=db[table].modified_on,
                                         groupby=db[table].uuid)
            for item in items:
                db((db[table].uuid==item.uuid) &
                   (db[table].id!=item.id)).delete()
    return dict(form=form)

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

XML-RPC

Кроме того, вы можете использовать XML-RPC для экспорта/импорта файла.

Если записи ссылаются на загруженные файлы, то вам также нужно экспортировать/импортировать содержимое папки uploads. Обратите внимание на то, что файлы в ней уже помечены через UUID, так что вам не нужно беспокоиться о конфликте имен и ссылок.

HTML и XML (одна таблица за раз)

Rows objects

Rows объекты также имеют метод xml (вроде помощников), который сериализует их в XML/HTML:

HTML
>>> rows = db(db.person.id > 0).select()
>>> print rows.xml()
<table>
  <thead>
    <tr>
      <th>person.id</th>
      <th>person.name</th>
      <th>thing.id</th>
      <th>thing.name</th>
      <th>thing.owner_id</th>
    </tr>
  </thead>
  <tbody>
    <tr class="even">
      <td>1</td>
      <td>Alex</td>
      <td>1</td>
      <td>Boat</td>
      <td>1</td>
    </tr>
    ...
  </tbody>
</table>
Rows custom tags

Если вам необходимо сериализовать строки в любом другом формате XML с пользовательскими тегами, то вы можете легко сделать это с помощью универсального помощника TAG и * нотации:

XML
>>> rows = db(db.person.id > 0).select()
>>> print TAG.result(*[TAG.row(*[TAG.field(r[f], _name=f) for f in db.person.fields]) for r in rows])

<result>
  <row>
    <field name="id">1</field>
    <field name="name">Alex</field>
  </row>
  ...
</result>

Представление данных

export_to_csv_file

Функция export_to_csv_file принимает ключевое слово - аргумент с именем represent. Когда он установлен на True, то он будет использовать функцию represent для столбцов при экспорте данных вместо необработанных данных.

colnames

Функция также принимает ключевое слово - аргумент с именем colnames, который должен содержать список имен столбцов, которые вы желаете экспортировать. По умолчанию это все столбцы.

Оба метода export_to_csv_file и import_from_csv_file принимают ключевые слова - аргументы, которые сообщают CSV-анализатору формат для сохранения/загрузки файлов:

  • delimiter: разделитель для разделения значений (по умолчанию ',')
  • quotechar: символ, используемый для квотирования строковых значений (по умолчанию двойные кавычки)
  • quoting: система квот (по умолчанию csv.QUOTE_MINIMAL)

Вот некоторые примеры использования:

>>> import csv
>>> rows = db(query).select()
>>> rows.export_to_csv_file(open('/tmp/test.txt', 'w'),
                            delimiter='|',
                            quotechar='"',
                            quoting=csv.QUOTE_NONNUMERIC)

Что сделает нечто похожее на

"hello"|35|"this is the text description"|"2013-03-03"

Для получения дополнительной информации обратитесь к официальной документации Python [quoteall]

Кэширование выборок

Метод select также принимает аргумент cache, который по умолчанию None. Для целей кэширования, ему должен быть задан кортеж, где первым элементом является модель кэша (cache.ram, cache.disk и т.д.), а вторым элементом является время истечения в секундах.

В следующем примере, вы видите контроллер, который кэширует select для ранее определенной db.log таблице. Фактический select извлекает данные из серверной базы данных не чаще, чем раз в 60 секунд, и сохраняет результат в cache.ram. Если следующий вызов этого контроллера происходит менее чем за 60 секунд с момента последнего ввода-вывода базы данных, то он просто извлекает предыдущие данные из cache.ram.

cache select
def cache_db_select():
    logs = db().select(db.log.ALL, cache=(cache.ram, 60))
    return dict(logs=logs)
cacheable

Метод select имеет необязательный cacheable аргумент, который обычно устанавливается на False. Когда cacheable=True, то результирующий объект Rows является сериализуемым, но тогда у объектов Row будут отсутствовать методы update_record и delete_record.

Если вам не нужны эти методы, вы можете ускорить выборку еще больше, установив атрибут cacheable:

rows = db(query).select(cacheable=True)

Когда cache аргумент задан, но cacheable =False (по умолчанию), кэшируются только результаты базы данных, а не фактический объект Rows. Когда cache аргумент используется в сочетании с cacheable = True, то объект Rows кэшируются полностью и это приводит к значительному ускорению кэширования:

rows = db(query).select(cache=(cache.ram,3600),cacheable=True)

Самоссылки и псевдонимы

self reference
alias

Можно определить таблицы с полями, которые ссылаются на себя, вот пример:

reference table
db.define_table('person',
                Field('name'),
                Field('father_id', 'reference person'),
                Field('mother_id', 'reference person'))

Обратите внимание на то, что использовать альтернативную нотацию объекта table при обозначении типа поля в этом случае не получится, так как он использует переменную db.person до ее определения:

db.define_table('person',
                Field('name'),
                Field('father_id', db.person),  # неправильно!
                Field('mother_id', db.person))  # неправильно!

В общем db.tablename и "reference tablename" являются эквивалентными типами полей, но последний является единственным разрешенным для self.references.

with_alias

Если таблица ссылается на себя, то не возможно выполнить JOIN, чтобы выбрать человека и его родителей без использования SQL ключевого слова "AS". Это достигается в web2py с помощью псевдонима with_alias. Вот пример:

>>> Father = db.person.with_alias('father')
>>> Mother = db.person.with_alias('mother')
>>> db.person.insert(name='Massimo')
1
>>> db.person.insert(name='Claudia')
2
>>> db.person.insert(name='Marco', father_id=1, mother_id=2)
3
>>> rows = db().select(db.person.name, Father.name, Mother.name,
                       left=(Father.on(Father.id == db.person.father_id),
                             Mother.on(Mother.id == db.person.mother_id)))

>>> for row in rows:
        print row.person.name, row.father.name, row.mother.name

Massimo None None
Claudia None None
Marco Massimo Claudia

Обратите внимание, что мы решили провести различие между:

  • "father_id": имя поля используется в таблице "person";
  • "father": псевдоним для таблицы, на которую ссылается поле выше; это сообщено в базу данных;
  • "Father": переменная, используемые web2py для ссылки на этот псевдоним.

Различие является тонким, и нет ничего плохого в использовании одного и того же имени для трех из них:

db.define_table('person',
                Field('name'),
                Field('father', 'reference person'),
                Field('mother', 'reference person'))

>>> father = db.person.with_alias('father')
>>> mother = db.person.with_alias('mother')
>>> db.person.insert(name='Massimo')
1
>>> db.person.insert(name='Claudia')
2
>>> db.person.insert(name='Marco', father=1, mother=2)
3
>>> rows = db().select(db.person.name, father.name, mother.name,
                       left=(father.on(father.id==db.person.father),
                             mother.on(mother.id==db.person.mother)))

>>> for row in rows:
        print row.person.name, row.father.name, row.mother.name

Massimo None None
Claudia None None
Marco Massimo Claudia

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

Расширенные возможности

Наследование таблиц

inheritance

Можно создать таблицу, содержащую все поля из другой таблицы. Достаточно передать другую таблицу вместо поля к define_table. Например

db.define_table('person', Field('name'))
db.define_table('doctor', db.person, Field('specialization'))
dummy table

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

signature = db.Table(db, 'signature',
                     Field('created_on', 'datetime', default=request.now),
                     Field('created_by', db.auth_user, default=auth.user_id),
                     Field('updated_on', 'datetime', update=request.now),
                     Field('updated_by', db.auth_user, update=auth.user_id))

db.define_table('payment', Field('amount', 'double'), signature)

В этом примере предполагается, что стандартная аутентификация web2py включена.

Обратите внимание, что если вы используете Auth, то web2py уже создал одну такую таблицу для вас:

auth = Auth(db)
db.define_table('payment', Field('amount', 'double'), auth.signature)

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

filter_in and filter_out

filter_in
filter_out

Можно для каждого поля определить фильтр, который будет вызываться перед вставкой значения этого поля в базу данных и после извлечения значения из базы данных.

Представьте себе, например, что вы хотите сохранить в поле сериализованную структуру данных Python в формате JSON. Вот как это может быть достигнуто:

>>> from simplejson import loads, dumps
>>> db.define_table('anyobj',
                    Field('name'),
                    Field('data', 'text'))

>>> db.anyobj.data.filter_in = lambda obj, dumps=dumps: dumps(obj)
>>> db.anyobj.data.filter_out = lambda txt, loads=loads: loads(txt)
>>> myobj = ['hello', 'world', 1, {2: 3}]
>>> id = db.anyobj.insert(name='myobjname', data=myobj)
>>> row = db.anyobj(id)
>>> row.data
['hello', 'world', 1, {2: 3}]

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

Обратные вызовы на вставку, удаление и обновление записи

_before_insert
_after_insert
_before_update
_after_update
_before_delete
_after_delete

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

Каждая таблица хранит шесть списков обратных вызовов:

db.mytable._before_insert
db.mytable._after_insert
db.mytable._before_update
db.mytable._after_update
db.mytable._before_delete
db.mytable._after_delete

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

Это лучше всего объяснить с помощью нескольких примеров.

>>> db.define_table('person', Field('name'))
>>> def pprint(*args): print args
>>> db.person._before_insert.append(lambda f: pprint(f))
>>> db.person._after_insert.append(lambda f, id: pprint(f, id))
>>> db.person._before_update.append(lambda s, f: pprint(s, f))
>>> db.person._after_update.append(lambda s, f: pprint(s, f))
>>> db.person._before_delete.append(lambda s: pprint(s))
>>> db.person._after_delete.append(lambda s: pprint(s))

Здесь f словарь из полей, переданный для вставки или обновления, id это id вновь вставленной записи, s это Set объект, используемый для обновления или удаления.

>>> db.person.insert(name='John')
({'name': 'John'},)
({'name': 'John'}, 1)
>>> db(db.person.id==1).update(name='Tim')
(<Set (person.id = 1)>, {'name': 'Tim'})
(<Set (person.id = 1)>, {'name': 'Tim'})
>>> db(db.person.id==1).delete()
(<Set (person.id = 1)>,)
(<Set (person.id = 1)>,)

Возвращаемые значения обратного вызова должны быть None или False. Если любой из обратных вызовов _before_ * возвратит значение True, то он прервет действующую операцию вставки/обновления/удаления.

update_naive

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

Для этого есть объекты Set, обладающие методом update_naive, который работает как точно также как и метод update, но игнорирует обратные вызовы до и после.

Каскады базы данных

Схема базы данных может определять взаимосвязи, которые запускают вычеркивания (deletions) связанных записей, известных как каскадирование (cascading). DAL не информируется, когда запись будет удалена из-за каскада. Поэтому переключатель on_delete не будет вызываться в связи с каскадным вычеркиванием.

Версионность записи

_enable_record_versioning

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

auth.enable_record_versioning(db)

это требует Auth и обсуждается в главе об аутентификации. Версионность также может быть сделана для каждой отдельной таблицы, как описано ниже.

Рассмотрим следующую таблицу:

db.define_table('stored_item',
                Field('name'),
                Field('quantity', 'integer'),
                Field('is_active', 'boolean',
                      writable=False, readable=False, default=True))

Обратите внимание на скрытое булевое поле, называемое is_active и установленное по умолчанию на True.

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

Это делается следующим образом:

db.stored_item._enable_record_versioning()

или в более развернутом синтаксисе:

db.stored_item._enable_record_versioning(archive_db=db,
                                         archive_name='stored_item_archive',
                                         current_record='current_record',
                                         is_active='is_active')

Аргумент archive_db=db говорит web2py хранить архивную таблицу в этой же базе данных как таблицу stored_item. Аргумент archive_name задает имя для архивной таблицы. Архивная таблица имеет те же поля, что и исходная таблица stored_item за исключением того, что уникальные поля уже не уникальны (поскольку им необходимо хранить несколько версий) и имеет дополнительное поле, имя которого указанно через аргумент current_record и которое является ссылкой на текущую запись в таблице stored_item.

Когда записи удаляются, то они на самом деле не удаляются. Удаленная запись копируется в stored_item_archive таблицу (также как, и когда она изменяется) и is_active полю задается значение False. Включив версионность записи web2py устанавливает custom_filter фильтр для этой таблицы, который скрывает все записи в таблице stored_item, где is_active поле содержит значение False. Параметр is_active метода _enable_record_versioning позволяет указать имя поля, используемого фильтром custom_filter для определения удалено ли было поле или нет.

Фильтр custom_filter игнорируются интерфейсом appadmin.

Общие поля и мульти-владение

common fields
multi tenancy

db._common_fields это список полей, которые должны принадлежать всем таблицам. Этот список может также содержать таблицы и они подразумеваются, как все поля из таблицы. Например, изредка вы оказываетесь в необходимости добавить подпись ко всем вашим таблицам, используя таблицу auth. В этом случае, после вашего db.define_tables(), но перед определением любой другой таблицы, вставьте

db._common_fields.append(auth.signature)

Одно поле является специальным: "request_tenant". Это поле не существует, но вы можете создать и добавить его в любую из таблиц (или во все из них):

db._common_fields.append(Field('request_tenant',
                               default=request.env.http_host,
                               writable=False))

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

db.table.request_tenant == db.table.request_tenant.default

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

default = request.env.http_host

т.е. мы решили попросить наше приложение фильтровать все таблицы во всех запросах с

db.table.request_tenant == request.env.http_host

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

Вы можете отключить мульти-владельческие фильтры используя:

ignore_common_filters

rows = db(query, ignore_common_filters=True).select()

Общие фильтры

Общий фильтр представляет собой обобщение вышеуказанной мульти-владельческой идеи. Он предоставляет простой способ предотвратить повторение того же самого запроса. Рассмотрим для примера следующую таблицу:

db.define_table('blog_post',
                Field('subject'),
                Field('post_text', 'text'),
                Field('is_public', 'boolean'),
                common_filter = lambda query: db.blog_post.is_public==True)

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

db.blog_post._common_filter = lambda query: db.blog_post.is_public == True

Он подается также как способ избежать повторения "db.blog_post.is_public==True" фразы в каждом сообщении блога поиска, и также как способ повышения безопасности, который ограждает вас от забывания установить запрет на просмотр непубличных сообщений.

В случае, если вы фактически хотите пропускать элементы вне общего фильтра (например, позволяя администратору видеть непубличные сообщения), то вы можете либо удалить фильтр:

db.blog_post._common_filter = None

или игнорировать его:

db(query, ignore_common_filters=True).select(...)

Пользовательские типы Field(экспериментальный)

SQLCustomType

Помимо использования filter_in и filter_out, можно определить новые/пользовательские типы полей. Например, мы рассмотрим здесь поле, содержащее двоичные данные в сжатой форме:

from gluon.dal import SQLCustomType
import zlib

compressed = SQLCustomType(type ='text',
                           native='text',
                           encoder=(lambda x: zlib.compress(x or '')),
                           decoder=(lambda x: zlib.decompress(x)))

db.define_table('example', Field('data', type=compressed))

SQLCustomType это фабрика типа поля. Его type аргумент должен быть одним из стандартных типов web2py. Это сообщает web2py как трактовать значения полей на уровне web2py. native является типом поля, насколько база данных обеспокоена. Допустимые имена зависят от движка базы данных. encoder является дополнительной функцией преобразования, применяемой при сохранении данных, и decoder является необязательно обратной функцией преобразования.

Эта возможность отмечена как экспериментальная. На практике она была опробована в web2py в течение длительного времени, и она работает, но она может сделать код не переносимым, например, когда родной тип поля определяется конкретной базой данных. Она не работает на Google App Engine NoSQL.

Использование DAL без определения таблиц

DAL можно использовать из любой программы Python просто делая это:

from gluon import DAL, Field
db = DAL('sqlite://storage.sqlite',folder='path/to/app/databases')

то есть импортировать DAL, Field, подключиться и указать папку, содержащую .table файлы (папка app/databases).

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

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

from gluon import DAL, Field
db = DAL('sqlite://storage.sqlite', folder='path/to/app/databases', auto_import=True))

Это позволяет получить доступ к любой db.table без необходимости повторного определения ее.

PostGIS, SpatiaLite и MS Geo (экспериментальный)

PostGIS
StatiaLite
Geo Extensions
geometry
geoPoint
geoLine
geoPolygon

DAL поддерживает географические API-интерфейсы с использованием PostGIS (для PostgreSQL), SpatiaLite (для SQLite), и MSSQL и Spatial Extensions. Это функция, которая была спонсирована в рамках проекта Сахана и реализована Денес Лендьел.

DAL предоставляет поля геометрического и географического типа и следующие функции:

st_asgeojson
st_astext
st_contains
st_distance
st_equals
st_intersects
st_overlaps
st_simplify
st_touches
st_within

st_asgeojson (PostGIS only)
st_astext
st_contains
st_distance
st_equals
st_intersects
st_overlaps
st_simplify (PostGIS only)
st_touches
st_within
st_x
st_y

Вот несколько примеров:

from gluon.dal import DAL, Field, geoPoint, geoLine, geoPolygon
db = DAL("mssql://user:pass@host:db")
sp = db.define_table('spatial', Field('loc', 'geometry()'))

Ниже мы вставим точку, линию, и многоугольник:

sp.insert(loc=geoPoint(1, 1))
sp.insert(loc=geoLine((100, 100), (20, 180), (180, 180)))
sp.insert(loc=geoPolygon((0, 0), (150, 0), (150, 150), (0, 150), (0, 0)))

Заметьте, что

rows = db(sp.id > 0).select()

Всегда возвращает сериализованные в виде текста геометрические данные. Вы можете сделать то же самое более явно с помощью st_astext():

print db(sp.id>0).select(sp.id, sp.loc.st_astext())
spatial.id,spatial.loc.STAsText()
1, "POINT (1 2)"
2, "LINESTRING (100 100, 20 180, 180 180)"
3, "POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))"

Вы можете запросить родное представление путем использования st_asgeojson() (в PostGIS только):

print db(sp.id>0).select(sp.id, sp.loc.st_asgeojson().with_alias('loc'))
spatial.id,loc
1, [1, 2]
2, [[100, 100], [20 180], [180, 180]]
3, [[[0, 0], [150, 0], [150, 150], [0, 150], [0, 0]]]

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

Вот пример того, как использовать географические функции:

query = sp.loc.st_intersects(geoLine((20, 120), (60, 160)))
query = sp.loc.st_overlaps(geoPolygon((1, 1), (11, 1), (11, 11), (11, 1), (1, 1)))
query = sp.loc.st_contains(geoPoint(1, 1))
print db(query).select(sp.id, sp.loc)
spatial.id, spatial.loc
3,"POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))"

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

dist = sp.loc.st_distance(geoPoint(-1,2)).with_alias('dist')
print db(sp.id>0).select(sp.id, dist)
spatial.id, dist
1 2.0
2 140.714249456
3 1.0

Копирование данных из одной базы данных в другую

Рассмотрим ситуацию, в которой вы используете следующую базу данных:

db = DAL('sqlite://storage.sqlite')

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

db = DAL('postgres://username:password@localhost/mydb')

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

Web2py предоставляет скрипт, который делает эту работу за вас:

cd web2py
python scripts/cpdb.py \
   -f applications/app/databases \
   -y 'sqlite://storage.sqlite' \
   -Y 'postgres://username:password@localhost/mydb' \
   -d ../gluon

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

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

python scripts/cpdb.py -h

Заметки по новому DAL и адаптерам

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

Файл "gluon/dal.py" определяются, среди прочего, следующие классы.

ConnectionPool
BaseAdapter extends ConnectionPool
Row
DAL
Reference
Table
Expression
Field
Query
Set
Rows

Их использование было объяснено в предыдущих разделах, исключение для BaseAdapter. Когда методам Table или Set объекта необходимо взаимодействовать с базой данных, то они делегируют задачи к методам адаптера для генерации SQL и/или вызова функции.

Например:

db.mytable.insert(myfield='myvalue')

вызывает

Table.insert(myfield='myvalue')

который делегирует адаптер, возвращая:

db._adapter.insert(db.mytable, db.mytable._listify(dict(myfield='myvalue')))

Здесь db.mytable._listify преобразует словарь из аргументов в список из (field,value) и вызывает insert метод адаптера adapter. db._adapter делает более или менее следующее:

query = db._adapter._insert(db.mytable, list_of_fields)
db._adapter.execute(query)

где первая строка создает запрос, а вторая выполняет его.

BaseAdapter определяет интерфейс для всех адаптеров.

"gluon/dal.py" на момент написания этой книги, содержит следующие адаптеры:

SQLiteAdapter extends BaseAdapter
JDBCSQLiteAdapter extends SQLiteAdapter
MySQLAdapter extends BaseAdapter
PostgreSQLAdapter extends BaseAdapter
JDBCPostgreSQLAdapter extends PostgreSQLAdapter
OracleAdapter extends BaseAdapter
MSSQLAdapter extends BaseAdapter
MSSQL2Adapter extends MSSQLAdapter
MSSQL3Adapter extends MSSQLAdapter
MSSQL4Adapter extends MSSQLAdapter
FireBirdAdapter extends BaseAdapter
FireBirdEmbeddedAdapter extends FireBirdAdapter
InformixAdapter extends BaseAdapter
DB2Adapter extends BaseAdapter
IngresAdapter extends BaseAdapter
IngresUnicodeAdapter extends IngresAdapter
GoogleSQLAdapter extends MySQLAdapter
NoSQLAdapter extends BaseAdapter
GoogleDatastoreAdapter extends NoSQLAdapter
CubridAdapter extends MySQLAdapter (experimental)
TeradataAdapter extends DB2Adapter (experimental)
SAPDBAdapter extends BaseAdapter (experimental)
CouchDBAdapter extends NoSQLAdapter (experimental)
IMAPAdapter extends NoSQLAdapter (experimental)
MongoDBAdapter extends NoSQLAdapter (experimental)
VerticaAdapter extends MSSQLAdapter (experimental)
SybaseAdapter extends MSSQLAdapter (experimental)

которые переопределяют поведение BaseAdapter.

Каждый адаптер имеет более или менее эту структуру:

class MySQLAdapter(BaseAdapter):

    # Укажем драйвер для использования
    driver = globals().get('pymysql', None)

    # Сопоставим типы web2py с типами базы данных
    types = {
        'boolean': 'CHAR(1)',
        'string': 'VARCHAR(%(length)s)',
        'text': 'LONGTEXT',
        ...
        }

    # подключимся к базе данных с помощью драйвера
    def __init__(self, db, uri, pool_size=0, folder=None, db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                 adapter_args={}):
        # разберем строку uri  и сохраним параметры в driver_args
        ...
        # Определим функцию подключения
        def connect(driver_args=driver_args):
            return self.driver.connect(**driver_args)
        # поместим её в пул
        self.pool_connection(connect)
        # установим необязательные параметры (после подключения)
        self.execute('SET FOREIGN_KEY_CHECKS=1;')
        self.execute("SET sql_mode='NO_BACKSLASH_ESCAPES';")

   # переопределим методы BaseAdapter по мере необходимости
   def lastrowid(self, table):
        self.execute('select last_insert_id();')
        return int(self.cursor.fetchone()[0])

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

Когда создается экземпляр db:

db = DAL('mysql://...')

префикс в uri строке определяет адаптер. Сопоставление также определяется в следующем словаре из файла "gluon/dal.py":

ADAPTERS = {
    'sqlite': SQLiteAdapter,
    'spatialite': SpatiaLiteAdapter,
    'sqlite:memory': SQLiteAdapter,
    'spatialite:memory': SpatiaLiteAdapter,
    'mysql': MySQLAdapter,
    'postgres': PostgreSQLAdapter,
    'postgres:psycopg2': PostgreSQLAdapter,
    'postgres:pg8000': PostgreSQLAdapter,
    'postgres2:psycopg2': NewPostgreSQLAdapter,
    'postgres2:pg8000': NewPostgreSQLAdapter,
    'oracle': OracleAdapter,
    'mssql': MSSQLAdapter,
    'mssql2': MSSQL2Adapter,
    'mssql3': MSSQL3Adapter,
    'mssql4' : MSSQL4Adapter,
    'vertica': VerticaAdapter,
    'sybase': SybaseAdapter,
    'db2': DB2Adapter,
    'teradata': TeradataAdapter,
    'informix': InformixAdapter,
    'informix-se': InformixSEAdapter,
    'firebird': FireBirdAdapter,
    'firebird_embedded': FireBirdAdapter,
    'ingres': IngresAdapter,
    'ingresu': IngresUnicodeAdapter,
    'sapdb': SAPDBAdapter,
    'cubrid': CubridAdapter,
    'jdbc:sqlite': JDBCSQLiteAdapter,
    'jdbc:sqlite:memory': JDBCSQLiteAdapter,
    'jdbc:postgres': JDBCPostgreSQLAdapter,
    'gae': GoogleDatastoreAdapter, # discouraged, for backward compatibility
    'google:datastore': GoogleDatastoreAdapter,
    'google:datastore+ndb': GoogleDatastoreAdapter,
    'google:sql': GoogleSQLAdapter,
    'couchdb': CouchDBAdapter,
    'mongodb': MongoDBAdapter,
    'imap': IMAPAdapter
}

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

Для любого адаптера вы можете заменить драйвер на другой:

import MySQLdb as mysqldb
from gluon.dal import MySQLAdapter
MySQLAdapter.driver = mysqldb

то есть mysqldb должен быть как модуль с .connect() методом. Можно указать необязательные аргументы драйвера и аргументы адаптера:

db =DAL(..., driver_args={}, adapter_args={})

Существующие ограничения

SQLite

SQLite не поддерживает отбрасывание и изменение столбцов. Это означает, что web2py миграции будут работать до точки. При удалении поля из таблицы, столбец будет оставаться в базе данных, но будет невидимым для web2py. Если вы решите восстановить столбец, то web2py попробует заново создать его и потерпит неудачу. В этом случае вы должны установить fake_migrate=True с тем, чтобы метаданные перестраивались без попытки повторного добавления столбца. Кроме того, по той же причине, SQLite не известно о любом изменении типа столбца. Если вставить число в строковое поле, то оно будет храниться в виде строки. Если позже изменить модель и заменить тип "string" на тип "integer", то SQLite будет продолжать держать число в виде строки, и это может вызвать проблемы при попытке извлечь данные.

SQLite не имеет полей булевого типа. web2py внутренне сопоставляет булевый тип со строкой из 1 символа, с 'T' и 'F', представляющие True и False. DAL обрабатывает это полностью; абстракция истинного логического значения хорошо работает. Но если вы обновляете таблицу SQLite с помощью SQL непосредственно, то учитывайте реализацию web2py и избегайте использования значений 0 и 1.

MySQL

MySQL не поддерживает множественные изменения таблицы (ALTER TABLE) в рамках одной транзакции. Это означает, что любой процесс миграции разбивается на несколько фиксаций. Если так случится, что произойдет сбой, то можно нарушить миграцию (метаданные web2py не будут синхронизированы с фактической структурой таблицы в базе данных). Это невезение, но его можно предотвратить (мигрировать одну таблицу за раз) или оно может быть зафиксировано в последнюю очередь(вернуть модель web2py к тому, что соответствует структуре таблицы в базе данных, установить fake_migrate=True и после того, как метаданные будут перестроены, установить fake_migrate=False и мигрировать таблицу снова).

Google SQL

Google SQL имеет те же самые проблемы, как MySQL и многие другие. В частности метаданные таблицы сами по себе должны храниться в базе данных в таблице, которая не мигрирует через web2py. Это потому, что Google App Engine имеет файловую систему только для чтения. Web2py миграции в Google: SQL в сочетании с вышеописанной проблемой MySQL может привести к повреждению метаданных. Опять же, это может быть предотвращено (за счет миграции таблицы за один раз и последующей установкой migrate=False так, чтобы больше не обращаться к таблице метаданных) или она может фиксироваться в последнюю очередь (путем доступа к базе данных с помощью панели управления Google, и удалением любой поврежденной записи из таблицы под названием web2py_filesystem.

MSSQL (Microsoft SQL Server)

limitby

MSSQL < 2012 не поддерживает SQL ключевое слово OFFSET. Поэтому база данных не может сделать разбиение на страницы (пагинацию). При выполнении limitby=(a,b) web2py получит первые b строк и отбросит первые a. Это может привести к значительным накладным расходам по сравнению с другими движками баз данных. Если вы используете MSSQL >= 2005, то рекомендуемым префиксом для использования является mssql3://, который предоставляет метод избежать проблем при извлечения всего не разбитого на страницы набора результатов. Если у вас MSSQL >= 2012, используйте mssql4://, который уже использует OFFSET ... ROWS ... FETCH NEXT ... ROWS ONLY конструкцию для поддержки родной пагинации без ударов по производительности подобно другим движкам баз данных. mssql:// uri также навязывает (по историческим причинам) использование text столбцов, которые вытесняются в более поздних версиях (начиная с 2005 года) через varchar(max). mssql3:// и mssql4:// следует использовать, если вы не хотите столкнуться с некоторыми ограничениями официально нерекомендуемых text столбцов.

MSSQL имеет проблемы с циклическими ссылками в таблицах, которые имеют ONDELETE CASCADE. Это ошибка MSSQL и вы можете обойти ее, установив ondelete атрибут для всех ссылочных полей на "NO ACTION".

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

db = DAL('mssql://....')
for key in ['reference', 'reference FK']:
    db._adapter.types[key]=db._adapter.types[key].replace('%(on_delete_action)s', 'NO ACTION')

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

db(query).select(distinct=True)

то это выражение уже не будет работать

db(query).select(distinct=db.mytable.myfield)

Oracle

Oracle также не поддерживает пагинацию. Он не поддерживает ни OFFSET, ни LIMIT ключевых слов. Web2py достигает пагинации путем перевода db(...).select(limitby=(a,b)) в комплексную трехходовую вложенную выборку (three-way nested select) (как это было предложено официальной документацией Oracle).

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

Google NoSQL (Datastore)

Google NoSQL (Datastore) не допускает сцепки, левые сцепки, агрегацию, выражения, OR  с участием более чем одной таблицы, ‘like’ оператор поиска в "text" полях.

Транзакции ограничены и не предоставляются автоматически web2py (вам нужно использовать Google API run_in_transaction, которые вы можете посмотреть в документации Google App Engine, в Интернете).

Google также ограничивает количество записей, которое вы можете получить в каждом одном запросе (1000 на момент написания). Идентификаторы Google Datastore являются целыми числами, но они не являются последовательными.

В то время как на SQL "list:string" тип преобразуется в тип "text", на Google Datastore он сопоставляется в ListStringProperty. Аналогично "list:integer" и "list:reference" сопоставляются в "ListProperty". Это делает поиск контента внутри данных типов полей более эффективным на Google NoSQL, чем в базах данных SQL.

 top