Package web2py :: Package gluon :: Module dal

[hide private]
[frames] | no frames]

# Module dal

source code

    This file is part of the web2py Web Framework
    Copyrighted by Massimo Di Pierro <mdipierro@cs.depaul.edu>
    License: LGPLv3 (http://www.gnu.org/licenses/lgpl.html)

    Thanks to
        * Niall Sweeny <niall.sweeny@fonjax.com> for MS SQL support
        * Marcel Leuthi <mluethi@mlsystems.ch> for Oracle support
        * Denes
        * Chris Clark
        * clach05
        * Denes Lengyel
        * and many others who have contributed to current and previous versions

    This file contains the DAL support for many relational databases,
    including:
    - SQLite
    - MySQL
    - Postgres
    - Oracle
    - MS SQL
    - DB2
    - Interbase
    - Ingres
    - SapDB (experimental)
    - Cubrid (experimental)
    - CouchDB (experimental)
    - MongoDB (in progress)
    - Google:nosql
    - Google:sql
    - Teradata
    - IMAP (experimental)

    Example of usage:

    >>> # from dal import DAL, Field

    ### create DAL connection (and create DB if it doesn't exist)
    >>> db = DAL(('mysql://a:b@localhost/x', 'sqlite://storage.sqlite'), folder=None)

    ### define a table 'person' (create/alter as necessary)
    >>> person = db.define_table('person',Field('name','string'))

    ### insert a record
    >>> id = person.insert(name='James')

    ### retrieve it by id
    >>> james = person(id)

    ### retrieve it by name
    >>> james = person(name='James')

    ### retrieve it by arbitrary query
    >>> query = (person.name=='James') & (person.name.startswith('J'))
    >>> james = db(query).select(person.ALL)[0]

    ### update one record
    >>> james.update_record(name='Jim')

    ### update multiple records by query
    >>> db(person.name.like('J%')).update(name='James')
    1

    ### delete records by query
    >>> db(person.name.lower() == 'jim').delete()
    0

    ### retrieve multiple records (rows)
    >>> people = db(person).select(orderby=person.name, groupby=person.name, limitby=(0,100))

    ### further filter them
    >>> james = people.find(lambda row: row.name == 'James').first()
    >>> print james.id, james.name
    1 James

    ### check aggregates
    >>> counter = person.id.count()
    >>> print db(person).select(counter).first()(counter)
    1

    ### delete one record
    >>> james.delete_record()
    1

    ### delete (drop) entire database table
    >>> person.drop()

    Supported field types:
    id string text boolean integer double decimal password upload blob time date datetime

    Supported DAL URI strings:
    'sqlite://test.db'
    'sqlite:memory'
    'jdbc:sqlite://test.db'
    'mysql://root:none@localhost/test'
    'postgres://mdipierro:password@localhost/test'
    'postgres:psycopg2://mdipierro:password@localhost/test'
    'postgres:pg8000://mdipierro:password@localhost/test'
    'jdbc:postgres://mdipierro:none@localhost/test'
    'mssql://web2py:none@A64X2/web2py_test'
    'mssql2://web2py:none@A64X2/web2py_test' # alternate mappings
    'oracle://username:password@database'
    'firebird://user:password@server:3050/database'
    'db2://DSN=dsn;UID=user;PWD=pass'
    'firebird://username:password@hostname/database'
    'firebird_embedded://username:password@c://path'
    'informix://user:password@server:3050/database'
    'informixu://user:password@server:3050/database' # unicode informix
    'google:datastore' # for google app engine datastore
    'google:sql' # for google app engine with sql (mysql compatible)
    'teradata://DSN=dsn;UID=user;PWD=pass; DATABASE=database' # experimental
    'imap://user:password@server:port' # experimental

    For more info:
    help(DAL)
    help(Field)

Classes | [hide private]

| GAEDecimalProperty GAE decimal implementation
| ConnectionPool
| BaseAdapter
| SQLiteAdapter
| JDBCSQLiteAdapter
| MySQLAdapter
| PostgreSQLAdapter
| JDBCPostgreSQLAdapter
| OracleAdapter
| MSSQLAdapter
| MSSQL2Adapter
| FireBirdAdapter
| FireBirdEmbeddedAdapter
| InformixAdapter
| DB2Adapter
| TeradataAdapter
| IngresAdapter
| IngresUnicodeAdapter
| SAPDBAdapter
| CubridAdapter
| DatabaseStoredFile
| UseDatabaseStoredFile
| GoogleSQLAdapter
| NoSQLAdapter
| GAEF
| GoogleDatastoreAdapter
| CouchDBAdapter
| MongoDBAdapter
| IMAPAdapter IMAP server adapter This class is intended as an interface with email IMAP servers to perform simple queries in the web2py DAL query syntax, so email read, search and other related IMAP mail services (as those implemented by brands like Google(r), and Yahoo!(r) can be managed from web2py applications.
| Row a dictionary that lets you do d['a'] as well as d.a this is only used to store a Row
| SQLCallableList
| DAL an instance of this class represents a database connection
| SQLALL Helper class providing a comma-separated string having all the field names (prefixed by table name and '.')
| Reference
| Table an instance of this class represents a database table
| Expression
| SQLCustomType allows defining of custom SQL types...
| FieldVirtual
| FieldLazy
| Field
| Query a query object necessary to define a set.
| Set a Set represents a set of records in the database, the records are identified by the query=Query(...) object.
| VirtualCommand
| Rows A wrapper for the return value of a select.
| SQLField
| SQLTable an instance of this class represents a database table
| SQLXorable
| SQLQuery a query object necessary to define a set.
| SQLSet a Set represents a set of records in the database, the records are identified by the query=Query(...) object.
| SQLRows A wrapper for the return value of a select.
| SQLStorage a dictionary that lets you do d['a'] as well as d.a this is only used to store a Row
| SQLDB an instance of this class represents a database connection
| GQLDB an instance of this class represents a database connection

Functions | [hide private]

DEFAULT() | source code

pluralize(singular, rules=`[``(`re.compile(r'child$')`,` re.compile(r'child$')`,` `'``children``'``)``,` `(``...`) | source code

OR(a, b) | source code

AND(a, b) | source code

uuid2int(uuidv) | source code

int2uuid(n) | source code

cleanup(text) validates that the given text is clean: only contains [0-9a-zA-Z_] | source code

sqlhtml_validators(field) Field type validation, using web2py's validators mechanism. | source code

bar_escape(item) | source code

bar_encode(items) | source code

bar_decode_integer(value) | source code

bar_decode_string(value) | source code

Row_unpickler(data) | source code

Row_pickler(data) | source code

smart_query(fields, text) | source code

Reference_unpickler(data) | source code

Reference_pickler(data) | source code

raw(s) | source code

xorify(orderby) | source code

use_common_filters(query) | source code

update_record(pack, a=global_settings.applications_parent) | source code

lazy_virtualfield(f) | source code

Rows_unpickler(data) | source code

Rows_pickler(data) | source code

test_all() >>> if len(sys.argv)<2: db = DAL("sqlite://test.db")... | source code

Variables | [hide private]

| MAXCHARLENGTH = `32768`
| DEFAULTLENGTH = ``{``'``blob``'``:` 2147483648`,` `'``password``'``:` 512`,` `'``string``'``...``
| TIMINGSSIZE = `100`
| CALLABLETYPES = ``(`<type 'function'>`,` <type 'function'>`,` <type '`...``
| have_portalocker = `True`
| have_serializers = `True`
| have_validators = `True`
| logger = `logging.getLogger("web2py.dal")`
| sql_locker = `<_RLock(None, 0)>`
| thread = `threading.local()`
| regex_type = `re.compile(r'^`(``[`\w_:`]``+``)`')`
| regex_dbname = `re.compile(r'^`(`\w`+``)``(`:\w`+``)``*`')`
| regex_table_field = `re.compile(r'^`(``[`\w_`]``+``)`\.`(``[`\w_`]``+``)`$')`
| regex_content = `re.compile(r'`(?P<``table``>``[`\w-`]``+``)`\.`(?P<``field``>``[`\w-`...``
| regex_cleanup_fn = `re.compile(r'`[`\'"\s;`]``+`')`
| string_unpack = `re.compile(r'`(?<!`\|`)`\|`(?!`\|`)`')`
| regex_python_keywords = `re.compile(r'^`(`and`|`del`|`from`|`not`|`while`|``...``
| regex_select_as_parser = `re.compile(r'\s`+`AS\s`+``(`\S`+``)`')`
| drivers = ``[``'``SQLite3``'``,` `'``pymysql``'``,` `'``psycopg2``'``,` `'``pg8000``'``,` `'``IMAP``'``]``
| PLURALIZE_RULES = ``[``(`re.compile(r'child$')`,` re.compile(r'child$`...``
| is_jdbc = `True`
| INGRES_SEQNAME = ``'``ii***lineitemsequence``'``
| ADAPTERS = ``{``'``couchdb``'``:` <class 'web2py.gluon.dal.CouchDBAdapter`...``
| regex_quotes = `re.compile(r'\'`[^`\'`]``*`\'')`

Function Details | [hide private]

### sqlhtml_validators(field)

source code

Field type validation, using web2py's validators mechanism.

makes sure the content of a field is in line with the declared fieldtype

### test_all()

source code

     >>> if len(sys.argv)<2: db = DAL("sqlite://test.db")
     >>> if len(sys.argv)>1: db = DAL(sys.argv[1])
     >>> tmp = db.define_table('users',              Field('stringf', 'string', length=32, required=True),              Field('booleanf', 'boolean', default=False),              Field('passwordf', 'password', notnull=True),              Field('uploadf', 'upload'),              Field('blobf', 'blob'),              Field('integerf', 'integer', unique=True),              Field('doublef', 'double', unique=True,notnull=True),              Field('datef', 'date', default=datetime.date.today()),              Field('timef', 'time'),              Field('datetimef', 'datetime'),              migrate='test_user.table')

    Insert a field

     >>> db.users.insert(stringf='a', booleanf=True, passwordf='p', blobf='0A',                       uploadf=None, integerf=5, doublef=3.14,                       datef=datetime.date(2001, 1, 1),                       timef=datetime.time(12, 30, 15),                       datetimef=datetime.datetime(2002, 2, 2, 12, 30, 15))
     1

     Drop the table

     >>> db.users.drop()

     Examples of insert, select, update, delete

     >>> tmp = db.define_table('person',              Field('name'),              Field('birth','date'),              migrate='test_person.table')
     >>> person_id = db.person.insert(name="Marco",birth='2005-06-22')
     >>> person_id = db.person.insert(name="Massimo",birth='1971-12-21')

     commented len(db().select(db.person.ALL))
     commented 2

     >>> me = db(db.person.id==person_id).select()[0] # test select
     >>> me.name
     'Massimo'
     >>> db(db.person.name=='Massimo').update(name='massimo') # test update
     1
     >>> db(db.person.name=='Marco').select().first().delete_record() # test delete
     1

     Update a single record

     >>> me.update_record(name="Max")
     >>> me.name
     'Max'

     Examples of complex search conditions

     >>> len(db((db.person.name=='Max')&(db.person.birth<'2003-01-01')).select())
     1
     >>> len(db((db.person.name=='Max')&(db.person.birth<datetime.date(2003,01,01))).select())
     1
     >>> len(db((db.person.name=='Max')|(db.person.birth<'2003-01-01')).select())
     1
     >>> me = db(db.person.id==person_id).select(db.person.name)[0]
     >>> me.name
     'Max'

     Examples of search conditions using extract from date/datetime/time

     >>> len(db(db.person.birth.month()==12).select())
     1
     >>> len(db(db.person.birth.year()>1900).select())
     1

     Example of usage of NULL

     >>> len(db(db.person.birth==None).select()) ### test NULL
     0
     >>> len(db(db.person.birth!=None).select()) ### test NULL
     1

     Examples of search conditions using lower, upper, and like

     >>> len(db(db.person.name.upper()=='MAX').select())
     1
     >>> len(db(db.person.name.like('%ax')).select())
     1
     >>> len(db(db.person.name.upper().like('%AX')).select())
     1
     >>> len(db(~db.person.name.upper().like('%AX')).select())
     0

     orderby, groupby and limitby

     >>> people = db().select(db.person.name, orderby=db.person.name)
     >>> order = db.person.name|~db.person.birth
     >>> people = db().select(db.person.name, orderby=order)

     >>> people = db().select(db.person.name, orderby=db.person.name, groupby=db.person.name)

     >>> people = db().select(db.person.name, orderby=order, limitby=(0,100))

     Example of one 2 many relation

     >>> tmp = db.define_table('dog',               Field('name'),               Field('birth','date'),               Field('owner',db.person),               migrate='test_dog.table')
     >>> db.dog.insert(name='Snoopy', birth=None, owner=person_id)
     1

     A simple JOIN

     >>> len(db(db.dog.owner==db.person.id).select())
     1

     >>> len(db().select(db.person.ALL, db.dog.name,left=db.dog.on(db.dog.owner==db.person.id)))
     1

     Drop tables

     >>> db.dog.drop()
     >>> db.person.drop()

     Example of many 2 many relation and Set

     >>> tmp = db.define_table('author', Field('name'),                            migrate='test_author.table')
     >>> tmp = db.define_table('paper', Field('title'),                            migrate='test_paper.table')
     >>> tmp = db.define_table('authorship',            Field('author_id', db.author),            Field('paper_id', db.paper),            migrate='test_authorship.table')
     >>> aid = db.author.insert(name='Massimo')
     >>> pid = db.paper.insert(title='QCD')
     >>> tmp = db.authorship.insert(author_id=aid, paper_id=pid)

     Define a Set

     >>> authored_papers = db((db.author.id==db.authorship.author_id)&(db.paper.id==db.authorship.paper_id))
     >>> rows = authored_papers.select(db.author.name, db.paper.title)
     >>> for row in rows: print row.author.name, row.paper.title
     Massimo QCD

     Example of search condition using  belongs

     >>> set = (1, 2, 3)
     >>> rows = db(db.paper.id.belongs(set)).select(db.paper.ALL)
     >>> print rows[0].title
     QCD

     Example of search condition using nested select

     >>> nested_select = db()._select(db.authorship.paper_id)
     >>> rows = db(db.paper.id.belongs(nested_select)).select(db.paper.ALL)
     >>> print rows[0].title
     QCD

     Example of expressions

     >>> mynumber = db.define_table('mynumber', Field('x', 'integer'))
     >>> db(mynumber.id>0).delete()
     0
     >>> for i in range(10): tmp = mynumber.insert(x=i)
     >>> db(mynumber.id>0).select(mynumber.x.sum())[0](mynumber.x.sum())
     45

     >>> db(mynumber.x+2==5).select(mynumber.x + 2)[0](mynumber.x + 2)
     5

     Output in csv

     >>> print str(authored_papers.select(db.author.name, db.paper.title)).strip()
     author.name,paper.title
     Massimo,QCD

     Delete all leftover tables

     >>> DAL.distributed_transaction_commit(db)

     >>> db.mynumber.drop()
     >>> db.authorship.drop()
     >>> db.author.drop()
     >>> db.paper.drop()

Variables Details | [hide private]

### DEFAULTLENGTH

Value:

      {'blob': 2147483648,
       'password': 512,
       'string': 512,
       'text': 32768,
       'upload': 512}

### CALLABLETYPES

Value:

      (<type 'function'>,
       <type 'function'>,
       <type 'builtin_function_or_method'>,
       <type 'instancemethod'>,
       <type 'builtin_function_or_method'>)

### regex_content

Value:

      re.compile(r'(?P<table>[\w-]+)\.(?P<field>[\w-]+)\.(?P<uuidkey>[\w-]+)
      \.(?P<name>\w+)\.\w+$')

### regex_python_keywords

Value:

      re.compile(r'^(and|del|from|not|while|as|elif|global|or|with|assert|el
      se|if|pass|yield|break|except|import|print|class|exec|in|raise|continu
      e|finally|is|return|def|for|lambda|try)$')

### PLURALIZE_RULES

Value:

      [(re.compile(r'child$'), re.compile(r'child$'), 'children'),
       (re.compile(r'oot$'), re.compile(r'oot$'), 'eet'),
       (re.compile(r'ooth$'), re.compile(r'ooth$'), 'eeth'),
       (re.compile(r'l[eo]af$'), re.compile(r'l([eo])af$'), 'l\\1aves'),
       (re.compile(r'sis$'), re.compile(r'sis$'), 'ses'),
       (re.compile(r'man$'), re.compile(r'man$'), 'men'),
       (re.compile(r'ife$'), re.compile(r'ife$'), 'ives'),
       (re.compile(r'eau$'), re.compile(r'eau$'), 'eaux'),
      ...

### ADAPTERS

Value:

      {'couchdb': <class 'web2py.gluon.dal.CouchDBAdapter'>,
       'cubrid': <class 'web2py.gluon.dal.CubridAdapter'>,
       'db2': <class 'web2py.gluon.dal.DB2Adapter'>,
       'firebird': <class 'web2py.gluon.dal.FireBirdAdapter'>,
       'firebird_embedded': <class 'web2py.gluon.dal.FireBirdAdapter'>,
       'gae': <class 'web2py.gluon.dal.GoogleDatastoreAdapter'>,
       'google:datastore': <class 'web2py.gluon.dal.GoogleDatastoreAdapter'>
      ,
      ...