Home | Trees | Indices | Help |
|
---|
|
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)
|
|||
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 |
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
|||
MAXCHARLENGTH = 32768
|
|||
DEFAULTLENGTH =
|
|||
TIMINGSSIZE = 100
|
|||
CALLABLETYPES =
|
|||
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'^
|
|||
regex_dbname = re.compile(r'^
|
|||
regex_table_field = re.compile(r'^
|
|||
regex_content = re.compile(r'
|
|||
regex_cleanup_fn = re.compile(r'
|
|||
string_unpack = re.compile(r'
|
|||
regex_python_keywords = re.compile(r'^
|
|||
regex_select_as_parser = re.compile(r'\s
|
|||
drivers =
|
|||
PLURALIZE_RULES =
|
|||
is_jdbc = True
|
|||
INGRES_SEQNAME =
|
|||
ADAPTERS =
|
|||
regex_quotes = re.compile(r'\'
|
|
Field type validation, using web2py's validators mechanism. makes sure the content of a field is in line with the declared fieldtype |
>>> 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() |
|
DEFAULTLENGTH
|
CALLABLETYPES
|
regex_content
|
regex_python_keywords
|
PLURALIZE_RULES
|
ADAPTERS
|
Home | Trees | Indices | Help |
|
---|
Generated by Epydoc 3.0beta1 on Sun Mar 4 22:11:57 2012 | http://epydoc.sourceforge.net |