Package gluon :: Module sql
[hide private]
[frames] | no frames]

Module sql

source code

This file is part of web2py Web Framework (Copyrighted, 2007) Developed by Massimo Di Pierro <mdipierro@cs.depaul.edu> License: GPL v2

Classes [hide private]
  SQLStorage
a dictionary that let you do d['a'] as well as d.a
  SQLCallableList
  SQLDB
an instance of this class represents a database connection
  SQLALL
  SQLTable
an instance of this class represents a database table Example:
  SQLXorable
  SQLField
an instance of this class represents a database field
  SQLQuery
a query object necessary to define a set.
  SQLSet
sn SQLSet represents a set of records in the database, the records are identified by the where=SQLQuery(...) object.
  SQLRows
A wrapper for the retun value of a select.
Functions [hide private]
 
hash5(txt) source code
 
sqlhtml_validators(field_type, length) source code
 
sql_represent(object, fieldtype, dbname) source code
 
cleanup(text) source code
 
sqlite3_web2py_extract(lookup, s) source code
 
parse_tablenames(text) source code
 
update_record(t, s, a) source code
 
test_all()
Create a table with all possible field types 'sqlite://test.db' 'mysql://root:none@localhost/test' 'postgres://mdipierro:none@localhost/test'
source code
Variables [hide private]
  sql_locker = thread.allocate_lock()
notes on concurrency....
  SQL_DIALECTS = {'mysql': {'blob': 'BLOB', 'boolean': 'CHAR(1)'...
  regex_tables = re.compile(r'(?P<table>[a-zA-Z]\w*)\.')
Function Details [hide private]

test_all()

source code 


 Create a table with all possible field types
 'sqlite://test.db'
 'mysql://root:none@localhost/test'
 'postgres://mdipierro:none@localhost/test'

 >>> if len(sys.argv)<2: db=SQLDB("sqlite://test.db")
 >>> if len(sys.argv)>1: db=SQLDB(sys.argv[1])
 >>> tmp=db.define_table('users',              SQLField('stringf','string',length=32,required=True),              SQLField('booleanf','boolean',default=False),              SQLField('passwordf','password',notnull=True),              SQLField('blobf','blob'),              SQLField('uploadf','upload'),              SQLField('integerf','integer',unique=True),              SQLField('doublef','double',unique=True,notnull=True),              SQLField('datef','date',default=datetime.date.today()),              SQLField('timef','time'),              SQLField('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',              SQLField('name'),               SQLField('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')
 >>> len(db().select(db.person.ALL))
 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
 >>> db(db.person.name=='Marco').delete() # test delete

 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 consitions 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',               SQLField('name'),               SQLField('birth','date'),               SQLField('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

 Drop tables

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

 Example of many 2 many relation and SQLSet

 >>> tmp=db.define_table('author',SQLField('name'),                            migrate='test_author.table')
 >>> tmp=db.define_table('paper',SQLField('title'),                            migrate='test_paper.table')
 >>> tmp=db.define_table('authorship',            SQLField('author_id',db.author),            SQLField('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 SQLSet

 >>> 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

 Output in csv

 >>> str(authored_papers.select(db.author.name,db.paper.title))
 'author.name,paper.title\r\nMassimo,QCD\r\n'

 Delete all leftover tables

 # >>> SQLDB.distributed_transaction_commit(db)

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


Variables Details [hide private]

sql_locker

notes on concurrency.... Accessing SQLDB._folders, SQLDB._instances and SQLDB .table files is not thread safe so they are locked with sql_locker Moreover .table files are locked with portalocker to account for multiple parallel processes.

Value:
thread.allocate_lock()

SQL_DIALECTS

Value:
{'mysql': {'blob': 'BLOB',
           'boolean': 'CHAR(1)',
           'date': 'DATE',
           'datetime': 'TIMESTAMP',
           'double': 'DOUBLE',
           'extract': 'EXTRACT(%(name)s FROM %(field)s)',
           'id': 'INT AUTO_INCREMENT NOT NULL',
           'integer': 'INT',
...