> Hello. I just started to document SQLBuilder, the most underdocumented part > of SQLObject: http://svn.colorstudy.com/SQLObject/trunk/docs/SQLBuilder.txt > I plan to document it fully (though, perhaps, not in the greatest > details) before releasing the trunk as the version 1.0 in the beginning of > next year. > Any help will be appreciated. A few paragraphs of text, or just a > sentence, a wording or grammar suggestion - anything, however small, would > be helpful.
This is really great Oleg! Here is a patch (coming from "diff -u SQLBuilder.new.txt SQLBuilder.txt") with grammatical suggestions. I also attached the new file along with the patch. Cheers, Daniel -- Psss, psss, put it down! - http://www.cafepress.com/putitdown
``````````
SQLBuilder
``````````
A number of variables from SQLBuilder are included with ``from
sqlobject import *`` -- see the `relevant SQLObject documentation`_
for more. Its functionality is also available through the special
``q`` attribute of `SQLObject` classes.
.. _`relevant SQLObject documentation`: SQLObject.html#exported-symbols
SQLExpression
=============
SQLExpression uses clever overriding of operators to make Python
expressions build SQL expressions -- so long as you start with a Magic
Object that knows how to fake it.
With SQLObject, you get a Magic Object by accessing the ``q`` attribute
of a table class -- this gives you an object that represents the
field. All of this is probably easier to grasp in an example::
>>> from sqlobject.sqlbuilder import *
>>> person = table.person
# person is now equivalent to the Person.q object from the SQLObject
# documentation
>>> person
person
>>> person.first_name
person.first_name
>>> person.first_name == 'John'
person.first_name = 'John'
>>> name = 'John'
>>> person.first_name != name
person.first_name <> 'John'
>>> AND(person.first_name == 'John', person.last_name == 'Doe')
(person.first_name = 'John' AND person.last_name = 'Doe')
Most of the operators work properly: <, >, <=, >=, !=, ==, +, -, /,
\*, \*\*, %. However, ``and``, ``or``, and ``not`` **do not work**.
You can use &, \|, and ~ instead -- but be aware that these have
the same precedence as multiplication. So::
# This isn't what you want:
>> person.first_name == 'John' & person.last_name == 'Doe'
(person.first_name = ('John' AND person.last_name)) = 'Doe')
# This is:
>> (person.first_name == 'John') & (person.last_name == 'Doe')
((person.first_name = 'John') AND (person.last_name == 'Doe'))
SQLBuilder also contains the functions ``AND``, ``OR``, and ``NOT`` which
also work -- I find these easier to work with. ``AND`` and ``OR`` can
take any number of arguments.
You can also use ``.startswith()`` and ``.endswith()`` on an SQL
expression -- these will translate to appropriate ``LIKE`` statements
and all ``%`` quoting is handled for you, so you can ignore that
implementation detail. There is also a ``LIKE`` function, where you
can pass your string, with ``%`` for the wildcard, as usual.
If you want to access an SQL function, use the ``func`` variable,
like::
>> person.created < func.NOW()
To pass a constant, use the ``const`` variable which is actually an
alias for func.
SQL statements
==============
SQLBuilder implements objects that execute SQL statements. SQLObject
uses them internally in its `higher-level API`_, but users can use this
mid-level API to execute SQL queries that are not supported by the
high-level API. To use these objects first construct an instance of a
statement object, then ask the connection to convert the instance to an
SQL query and finally ask the connection to execute the query and return
the results. For example, for Select class::
>>> from sqlobject.sqlbuilder import *
>> select = Select(['name', 'AVG(salary)'], staticTables=['employees'],
>> groupBy='name') # create an instance
>> query = connection.sqlrepr(select) # Convert to SQL string:
>> # SELECT name, AVG(salary) FROM employees GROUP BY name
>> rows = connection.queryAll(query) # Execute the query
>> # and get back the reuslt as a list of rows
>> # where every row is a sequence of length 2 (name and average salary)
.. _`higher-level API`: SQLObject.html
Select
~~~~~~
A class to build SELECT queries. Accepts a number of parameters, all
parameters are optional.
`items`:
A string, an SQLExpression or a sequence of strings or
SQLExpression's, represents the list of columns. If there are
SQLExpression's Select derives a list of tables for SELECT query.
`where`:
A string or an SQLExpression, represents the WHERE clause.
`groupBy`:
A string or an SQLExpression, represents the GROUP BY clause.
`having`:
A string or an SQLExpression, represents the HAVING part of the GROUP
BY clause.
`orderBy`:
A string or an SQLExpression, represents the ORDER BY clause.
`limit`:
A string or an SQLExpression, represents the LIMIT clause.
`join`:
A (list of) JOINs (LEFT JOIN, etc.)
`distinct`:
A bool flag to turn on DISTINCT query.
`start`, `end`:
Integers. Alternative ways to calculate LIMIT. `limit`, if passed,
overrides `end`.
`reversed`:
A bool flag to do sorting (ORDER BY) in the reverse direction.
`forUpdate`:
A bool flag to turn on SELECT FOR UPDATE query.
`staticTables`:
A sequence of strings or SQLExpression's that name tables for FROM.
This parameter must be used if `items` is a list of strings from
which Select cannot derive a list of tables.
.. image:: http://sflogo.sourceforge.net/sflogo.php?group_id=74338&type=10
:target: http://sourceforge.net/projects/sqlobject
:class: noborder
:align: center
:height: 15
:width: 80
:alt: Get SQLObject at SourceForge.net. Fast, secure and Free Open Source
software downloads
SQLBuilder.txt.patch
Description: Binary data
------------------------------------------------------------------------------ Lotusphere 2011 Register now for Lotusphere 2011 and learn how to connect the dots, take your collaborative environment to the next level, and enter the era of Social Business. http://p.sf.net/sfu/lotusphere-d2d
_______________________________________________ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
