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

Example of usage:

>>> # from dal import DAL, Field

### create DAL connection (and create DB if not exists)
>>> db=DAL(('mysql://a:b@locahost/x','sqlite://storage.sqlite'),folder=None)

### define a table 'person' (create/aster 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 aggrgates
>>> 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:none@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' # 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
  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...
  Field
an instance of this class represents a database 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.
  Rows
A wrapper for the return value of a select.
  SQLField
an instance of this class represents a database field
  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
 
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
 
Reference_unpickler(data) source code
 
Reference_pickler(data) source code
 
xorify(orderby) source code
 
update_record(pack, a={}) 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 = 512
  INFINITY = 32768
  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_dbname = re.compile(r'^(\w+)(:\w+)*')
  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|...
  drivers = ['SQLite3', 'pymysql', 'PostgreSQL']
  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]

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)$')

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'>\
,
...