Menu
▾
▴
[SQL-CVS] r875 - trunk/SQLObject/docs
|
From: <sub...@co...> - 2005-08-01 05:55:57
|
Author: ianb
Date: 2005-08-01 05:55:51 +0000 (Mon, 01 Aug 2005)
New Revision: 875
Modified:
trunk/SQLObject/docs/SQLObject.txt
Log:
Finished transition back to single document
Modified: trunk/SQLObject/docs/SQLObject.txt
===================================================================
--- trunk/SQLObject/docs/SQLObject.txt 2005-08-01 05:55:31 UTC (rev 874)
+++ trunk/SQLObject/docs/SQLObject.txt 2005-08-01 05:55:51 UTC (rev 875)
@@ -624,7 +624,7 @@
that's name starts with ``_set_``, ``_get_``, ``_del_``, or ``_doc_``,
it will be used to create a property. So, for instance, say you have
images stored under the ID of the person in the ``/var/people/images``
-directory:
+directory::
class Person(SQLObject):
# ...
@@ -683,7 +683,7 @@
To deal with this, SQLObject creates two methods for each getter and
setter, for example: ``_set_lastName`` and ``_SO_set_lastName``. So
-to intercept all changes to ``lastName``:
+to intercept all changes to ``lastName``::
class Person(SQLObject):
lastName = StringCol()
@@ -694,11 +694,31 @@
self._SO_set_lastName(value)
Or perhaps you want to constrain a phone numbers to be actual
-digits, and of proper length, and make the formatting nice:
+digits, and of proper length, and make the formatting nice::
-.. raw:: html
- :file: ../examples/snippets/phonenumber_magicoverride.html
+ import re
+ class PhoneNumber(SQLObject):
+ phoneNumber = StringCol(length=30)
+
+ _garbageCharactersRE = re.compile(r'[\-\.\(\) ]')
+ _phoneNumberRE = re.compile(r'^[0-9]+$')
+ def _set_phoneNumber(self, value):
+ value = self._garbageCharactersRE.sub('', value)
+ if not len(value) >= 10:
+ raise ValueError(
+ 'Phone numbers must be at least 10 digits long')
+ if not self._phoneNumberRE.match(value):
+ raise ValueError, 'Phone numbers can contain only digits'
+ self._SO_set_phoneNumber(value)
+
+ def _get_phoneNumber(self):
+ value = self._SO_get_phoneNumber()
+ number = '(%s) %s-%s' % (value[0:3], value[3:6], value[6:10])
+ if len(value) > 10:
+ number += ' ext.%s' % value[10:]
+ return number
+
.. note::
You should be a little cautious when modifying data that gets set
@@ -709,9 +729,6 @@
out. One advantage of methods (as opposed to attribute access) is
that the programmer is more likely to expect this disconnect.
-
-.. include:: SQLObjectCustomization.txt
-
Reference
=========
@@ -719,18 +736,752 @@
be useful for many situations. Now we'll show how to specify the
class more completely.
-.. include:: SQLObjectCol.txt
-.. include:: SQLObjectClasses.txt
-.. include:: SQLObjectRelationships.txt
-.. include:: SQLObjectTransactions.txt
-.. include:: SQLObjectGeneration.txt
-.. include:: SQLObjectDynamic.txt
-.. include:: SQLObjectLegacy.txt
-.. include:: SQLObjectDBConnection.txt
-.. include:: SQLObjectExported.txt
-.. include:: SQLObjectJoins.txt
-.. include:: SQLObjectSubqueries.txt
+Col Class: Specifying Columns
+-----------------------------
+The list of columns is a list of `Col` objects. These objects don't
+have functionality in themselves, but give you a way to specify the
+column.
+
+`dbName`:
+ This is the name of the column in the database. If you don't
+ give a name, your Pythonic name will be converted from
+ mixed-case to underscore-separated.
+`default`:
+ The default value for this column. Used when creating a new row.
+ If you give a callable object or function, the function will be
+ called, and the return value will be used. So you can give
+ ``DateTime.now`` to make the default value be the current time.
+ Or you can use ``SQLBuilder.func.NOW()`` to have the database use
+ the ``NOW()`` function internally. If you don't give a default
+ there will be an exception if this column isn't specified in the
+ call to `new`.
+`alternateID`:
+ This boolean (default False) indicates if the column can be used
+ as an ID for the field (for instance, a username), though it is
+ not a primary key. If so a class method will be added, like
+ ``byUsername`` which will return that object. Use
+ `alternateMethodName` if you don't like the ``by*`` name
+ (e.g. ``alternateMethodName="username"``).
+
+ The column should be declared ``UNIQUE`` in your table schema.
+`unique`:
+ If true, when SQLObject creates a table it will declare this
+ column to be ``UNIQUE``.
+`notNone`:
+ If true, None/``NULL`` is not allowed for this column. Useful if
+ you are using SQLObject to create your tables.
+`sqlType`:
+ The SQL type for this column (like ``INT``, ``BOOLEAN``, etc).
+ You can use classes (defined below) for this, but if those don't
+ work it's sometimes easiest just to use `sqlType`. Only necessary
+ if SQLObject is creating your tables.
+
+Subclasses of Col
+~~~~~~~~~~~~~~~~~
+
+The `ForeignKey` class should be used instead of `Col` when the column
+is a reference to another table/class. It is generally used like
+``ForeignKey('Role')``, in this instance to create a reference to a
+table `Role`. This is largely equivalent to ``Col(foreignKey='Role',
+sqlType='INT')``. Two attributes will generally be created, ``role``,
+which returns a `Role` instance, and ``roleID``, which returns an
+integer ID for the related role.
+
+There are some other subclasses of `Col`. These are used to indicate
+different types of columns, when SQLObject creates your tables.
+
+`BoolCol`:
+ Will create a ``BOOLEAN`` column in Postgres, or ``INT`` in other
+ databses. It will also convert values to ``"t"/"f"`` or ``0/1``
+ according to the database backend.
+
+`CurrencyCol`:
+ Equivalent to ``DecimalCol(size=10, precision=2)``.
+
+`DateTimeCol`:
+ A date and time (usually returned as an mxDateTime object).
+
+`DecimalCol`:
+ Base-10, precise number. Uses the keyword arguments `size` for
+ number of digits stored, and `precision` for the number of digits
+ after the decimal point.
+
+`EnumCol`:
+ One of several string values -- give the possible strings as a
+ list, with the `enumValues` keyword argument. MySQL has a native
+ ``ENUM`` type, but will work with other databases too (storage
+ just won't be as efficient).
+
+`FloatCol`:
+ Floats.
+
+`ForeignKey`:
+ A key to another table/class. Use like ``user =
+ ForeignKey('User')``.
+
+`IntCol`:
+ Integers.
+
+`StringCol`:
+ A string (character) column. Extra keywords:
+
+ `length`:
+ If given, the type will be something like ``VARCHAR(length)``.
+ If not given, then ``TEXT`` is assumed (i.e., lengthless).
+ `varchar`:
+ A boolean; if you have a length, differentiates between
+ ``CHAR`` and ``VARCHAR``, default True, i.e., use
+ ``VARCHAR``.
+
+`UnicodeCol`:
+ A subclass of `StringCol`. Also accepts a dbEncoding keyword
+ argument, which defaults to ``"UTF-8"``. Values coming in and
+ out from the database will be encoded and decoded. **Note**:
+ parameters in queries will not be automatically encoded, so if
+ you do a query matching a UnicodeCol column you must apply the
+ encoding yourself.
+
+
+SQLObject Class: Specifying Your Class
+--------------------------------------
+
+In addition to the columns, there are a number of other special
+attributes you can set in your class.
+
+`_connection`:
+ The connection object to use, from `DBConnection`. You can also
+ set the variable `__connection__` in the enclosing module and it
+ will be picked up (be sure to define `__connection__` before you
+ class). You can also pass a connection object in at instance
+ creation time, as described in transactions_.
+
+`_table`:
+ The database name of the table for this class. If you don't give
+ a name, then the standard ``MixedCase`` to ``mixed_case``
+ translation is performed.
+
+`_joins`:
+ A list of `Join` objects. This is covered below.
+
+`_cacheValues`:
+ If set to ``False`` then values for attributes from the database
+ won't be cached. So everytime you access an attribute in the
+ object the database will be queried for a value. If you want to
+ handle concurrent access to the database from multiple processes
+ then this is probably the way to do so. You should also use
+ it with transactions_ (it is not implied).
+
+.. _idName:
+
+`_idName`:
+ The name of the primary key column (default ``id``).
+
+`_style`:
+ A style object -- this object allows you to use other algorithms
+ for translating between Python attribute and class names, and the
+ database's column and table names. See `Changing the Naming
+ Style`_ for more.
+
+.. Relationships_:
+
+Relationships Between Classes/Tables
+------------------------------------
+
+You can use the `ForeignKey` to handle foreign references in a table,
+but for back references and many-to-many relationships you'll use
+joins.
+
+MultipleJoin: One-to-Many
+~~~~~~~~~~~~~~~~~~~~~~~~~
+
+See `One-to-Many Relationships`_ for an example of one-to-many
+relationships.
+
+Several keyword arguments are allowed to the `MultipleJoin` constructor:
+
+.. _`Multiple Join Keywords`:
+
+`joinColumn`:
+ The column name of the key that points to this table. So, if you have
+ a table ``Product``, and another table has a column ``ProductNo`` that
+ points to this table, then you'd use ``joinColumn="ProductNo"``.
+`orderBy`:
+ Like the `orderBy`_ argument to `select()`, you can specify
+ the order that the joined objects should be returned in. `_defaultOrder`
+ will be used if not specified; ``None`` forces unordered results.
+`joinMethodName`:
+ When adding joins dynamically (using the class method `addJoin`_),
+ you can give the name of the accessor for the join. It can also be
+ created automatically, and is normally implied (i.e., ``addresses =
+ MultipleJoin(...)`` implies ``joinMethodName="addresses"``).
+
+RelatedJoin: Many-to-Many
+~~~~~~~~~~~~~~~~~~~~~~~~~
+
+See `Many-to-Many Relationships`_ for examples of using many-to-many joins.
+
+`RelatedJoin` has all the keyword arguments of `MultipleJoin`__, plus:
+
+__ `Multiple Join Keywords`_
+
+`otherColumn`:
+ Similar to `joinColumn`, but referring to the joined class.
+`intermediateTable`:
+ The name of the intermediate table which references both classes.
+`addRemoveName`:
+ In the `user/role example`__, the methods `addRole(role)` and
+ `removeRole(role)` are created. The ``Role`` portion of these
+ method names can be changed by giving a string value here.
+
+__ `Many-to-Many Relationships`_
+
+An example schema that requires the use of `joinColumn`, `otherColumn`,
+and `intermediateTable`::
+
+ CREATE TABLE person (
+ id SERIAL,
+ username VARCHAR(100) NOT NULL UNIQUE
+ );
+
+ CREATE TABLE role (
+ id SERIAL,
+ name VARCHAR(50) NOT NULL UNIQUE
+ );
+
+ CREATE TABLE assigned_roles (
+ person INT NOT NULL,
+ role INT NOT NULL
+ );
+
+Then the usage in a class::
+
+ class Person(SQLObject):
+ username = StringCol(length=100, alternateID=True)
+ roles = RelatedJoin('Role', joinColumn='person', otherColumn='role',
+ intermediateTable='assigned_roles')
+ class Role(SQLObject):
+ name = StringCol(length=50, alternateID=True)
+ roles = RelatedJoin('Person', joinColumn='role', otherColumn='person',
+ intermediateTable='assigned_roles')
+
+SingleJoin: One-to-One
+~~~~~~~~~~~~~~~~~~~~~~~~~
+
+Similar to `MultipleJoin`, but returns just one object, not a list.
+
+
+Transactions
+------------
+
+Transaction support in SQLObject is left to the database.
+Transactions can be used like::
+
+ conn = DBConnection.PostgresConnection('yada')
+ trans = conn.transaction()
+ p = Person.get(1, trans)
+ p.firstName = 'Bob'
+ trans.commit()
+ p.firstName = 'Billy'
+ trans.rollback()
+
+The ``trans`` object here is essentially a wrapper around a single
+database connection, and `commit` and `rollback` just pass that
+message to the `psycopg` connection.
+
+If you want to use transactions you should also turn `_cacheValues`
+off, like:
+
+ class Person(SQLObject):
+ _cacheValue = False
+ # ...
+
+Automatic Schema Generation
+---------------------------
+
+All the connections support creating and droping tables based on the
+class definition. First you have to prepare your class definition,
+which means including type information in your columns.
+
+Columns Types
+~~~~~~~~~~~~~
+
+A column type is indicated by using a subclass of `Col`:
+
+`StringCol`:
+ StringCol represents ``CHAR``, ``VARCHAR``, and ``TEXT``. The
+ `length` keyword argument indicates the ``CHAR`` or ``VARCHAR``
+ length -- if not given, then ``TEXT`` is assumed. If you use
+ ``varchar=False`` then ``CHAR`` will be used, otherwise
+ ``VARCHAR`` is the default.
+`IntCol`:
+ The ``INT`` type.
+`FloatCol`:
+ The ``FLOAT`` type.
+`DecimalCol`:
+ The ``DECIMAL`` SQL type, i.e., base 10 number. The keyword
+ arguments `size` and `precision` indicate the scope. So
+ ``DecimalCol(size=5, precision=2)`` is a number like ###.##,
+ i.e., 5 digits, two of them past the decimal point.
+`CurrencyCol`:
+ Like ``DecimalCol(size=10, precision=2)``.
+`EnumCol`:
+ A MySQL ``ENUM``, i.e., one of a finite number of strings.
+ For other databases this will be a ``VARCHAR``.
+`DateTimeCol`:
+ A moment in time. ``TIMESTAMP`` in Postgres, and ``DATETIME``
+ in MySQL. Note the names of these columns match the *Python*
+ type names, not the SQL names.
+`ForeignKey`:
+ This is a reference to another table. You typically need to
+ only give the name of the foreign class that is referenced.
+ `ForeignKey` implies an ``INT`` column.
+
+Indexes
+~~~~~~~
+
+You can also define indexes for your tables, which is only meaningful
+when creating your tables through SQLObject (SQLObject relies on the
+database to implement the indexes). You do this again with attribute
+assignment, like::
+
+ firstLastIndex = DatabaseIndex('firstName', 'lastName')
+
+This creates an index on two columns, useful if you are selecting a
+particular name. Of course, you can give a single column, and you can
+give the column object (``firstName``) instead of the string name.
+Note that if you use ``unique`` or ``alternateID`` (which implies
+``unique``) the database may make an index for you, and primary keys
+are always indexed.
+
+If you give the keyword argument ``unique`` to `DatabaseIndex` you'll
+create a unique index -- the combination of columns must be unique.
+
+You can also use dictionaries in place of the column names, to add
+extra options. E.g.::
+
+ lastNameIndex = DatabaseIndex({'expression': 'lower(last_name)'})
+
+In that case, the index will be on the lower-case version of the
+column. It seems that only PostgreSQL supports this. You can also
+do::
+
+ lastNameIndex = DatabaseIndex({'column': lastName, 'length': 10})
+
+Which asks the database to only pay attention to the first ten
+characters. Only MySQL supports this, but it is ignored in other
+databases.
+
+Creating and Dropping Tables
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+To create a table call `createTable`. It takes two arguments:
+
+`ifNotExists`:
+ If the table already exists, then don't try to create it. Default
+ False.
+`createJoinTables`:
+ If you used `Many-to-Many relationships`_, then the intermediate tables
+ will be created (but only for one of the two involved classes).
+ Default True.
+
+`dropTable` takes arguments `ifExists` and `dropJoinTables`,
+self-explanatory.
+
+Dynamic Classes
+===============
+
+SQLObject classes can be manipulated dynamically. This leaves open
+the possibility of constructing SQLObject classes from an XML file,
+from database introspection, or from a graphical interface.
+
+Automatic Class Generation
+---------------------------
+
+SQLObject can read the table description from the database, and fill
+in the class columns (as would normally be described in the `_columns`
+attribute). Do this like::
+
+ class Person(SQLObject):
+
+ _fromDatabase = True
+
+You can still specify columns (in `_columns`), and only missing
+columns will be added.
+
+*This is not supported in SQLite*
+
+Runtime Column Changes
+----------------------
+
+*SQLite does not support this feature*
+
+You can add and remove columns to your class at runtime. Such changes
+will effect all instances, since changes are made inplace to the
+class. There are two methods, `addColumn` and `delColumn`, both of
+which take a `Col` object (or subclass) as an argument. There's also
+an option argument `changeSchema` which, if True, will add or drop the
+column from the database (typically with an ``ALTER`` command).
+
+When adding columns, you must pass the name as part of the column
+constructor, like ``StringCol("username", length=20)``. When removing
+columns, you can either use the Col object (as found in `_columns`, or
+which you used in `addColumn`), or you can use the column name (like
+``MyClass.delColumn("username")``).
+
+.. _addJoin:
+
+You can also add Joins__, like
+``MyClass.addJoin(MultipleJoin("MyOtherClass"))``, and remove joins with
+`delJoin`. `delJoin` does not take strings, you have to get the join
+object out of the `_joins` attribute.
+
+__ Relationships_:
+
+Legacy Database Schemas
+=======================
+
+Often you will have a database that already exists, and does not use
+the naming conventions that SQLObject expects, or does not use any
+naming convention at all.
+
+SQLObject requirements
+----------------------
+
+While SQLObject tries not to make too many requirements on your
+schema, some assumptions are made. Some of these may be relaxed in
+the future.
+
+All tables that you want to turn into a class need to have an integer
+primary key. That key should be defined like:
+
+MySQL:
+ ``INT PRIMARY KEY AUTO_INCREMENT``
+Postgres:
+ ``SERIAL PRIMARY KEY``
+SQLite:
+ ``INTEGER PRIMARY KEY``
+
+SQLObject does not support non-integer keys (that may change). It
+does not support sequences in Postgres (that will change -- ``SERIAL``
+uses an implicit sequence). It does not support primary keys made up
+of multiple columns (that probably won't change). It does not
+generally support tables with primary keys with business meaning --
+i.e., primary keys are assumed to be immutable (that won't change).
+
+At the moment foreign key column names must end in ``"ID"``
+(case-insensitive). This restriction will probably be removed in the
+next release.
+
+Changing the Naming Style
+-------------------------
+
+By default names in SQLObject are expected to be mixed case in Python
+(like ``mixedCase``), and underscore-separated in SQL (like
+``mixed_case``). This applies to table and column names. The primary
+key is assumed to be simply ``id``.
+
+Other styles exist. A typical one is mixed case column names, and a
+primary key that includes the table name, like ``ProductID``. You can
+use a different "Style" object to indicate a different naming
+convention. For instance::
+
+ class Person(SQLObject):
+ _style = MixedCaseStyle(longID=True)
+
+ firstName = StringCol()
+ lastName = StringCol()
+
+If you use ``Person.createTable()``, you'll get::
+
+ CREATE TABLE Person (
+ PersonID INT PRIMARY KEY,
+ FirstName Text,
+ LastName Text
+ )
+
+The `MixedCaseStyle` object handles the initial capitalization of
+words, but otherwise leaves them be. By using ``longID=True``, we
+indicate that the primary key should look like a normal reference
+(``PersonID`` for `MixedCaseStyle`, or ``person_id`` for the default
+style).
+
+If you wish to change the style globally, assign the style to the
+connection, like::
+
+ __connection__.style = MixedCaseStyle(longID=True)
+
+Irregular Naming
+----------------
+
+While naming conventions are nice, they are not always present. You
+can control most of the names that SQLObject uses, independent of the
+Python names (so at least you don't have to propagate the
+irregularity to your brand-spanking new Python code).
+
+Here's a simple example::
+
+ class User(SQLObject):
+ _table = "user_table"
+ _idName = "userid"
+
+ username = StringCol(length=20, dbName='name')
+
+The attribute `_table` overrides the table name. `_idName` provides
+an alternative to ``id``. The ``dbName`` keyword argument gives the
+column name.
+
+Non-Integer Keys
+----------------
+
+While not strictly a legacy database issue, this fits into the
+category of "irregularities". If you use non-integer keys, all
+primary key management is up to you. You must create the table
+yourself, and when you create instances you must pass a ``id`` keyword
+argument into constructor (like ``Person(id='555-55-5555', ...)``).
+
+DBConnection: Database Connections
+==================================
+
+The `DBConnection` module currently has four external classes,
+`MySQLConnection`, `PostgresConnection`, `SQLiteConnection`,
+`SybaseConnection`, and `MaxdbConnection`.
+
+You can pass the keyword argument `debug` to any connector. If set to
+true, then any SQL sent to the database will also be printed to the
+console.
+
+MySQL
+-----
+
+`MySQLConnection` takes the keyword arguments `host`, `db`, `user`,
+and `passwd`, just like `MySQLdb.connect` does.
+
+MySQLConnection supports all the features, though MySQL only supports
+transactions_ when using the InnoDB backend, and SQLObject currently
+does not have support for explicitly defining the backend when using
+``createTable``.
+
+Postgres
+--------
+
+`PostgresConnection` takes a single connection string, like
+``"dbname=something user=some_user"``, just like `psycopg.connect`.
+You can also use the same keyword arguments as for `MySQLConnection`,
+and a dsn string will be constructed.
+
+PostgresConnection supports transactions and all other features.
+
+SQLite
+------
+
+`SQLiteConnection` takes the a single string, which is the path to the
+database file.
+
+SQLite puts all data into one file, with a journal file that is opened
+in the same directory during operation (the file is deleted when the
+program quits). SQLite does not restrict the types you can put in a
+column -- strings can go in integer columns, dates in integers, etc.
+
+SQLiteConnection doesn't support `automatic class generation`_ and
+SQLite does not support `runtime column changes`_.
+
+SQLite may have concurrency issues, depending on your usage in a
+multi-threaded environment.
+
+Firebird
+--------
+
+`FirebirdConnection` takes the arguments `host`, `db`, `user` (default
+``"sysdba"``), `passwd` (default ``"masterkey"``).
+
+Firebird supports all the features. Support is still young, so there
+may be some issues, especially with concurrent access, and especially
+using lazy selects. Try ``list(MyClass.select())`` to avoid
+concurrent cursors if you have problems (using ``list()`` will
+pre-fetch all the results of a select).
+
+Firebird support uses the kinterbasdb_ Python library.
+
+.. _kinterbasdb: http://kinterbasdb.sourceforge.net/
+
+If you are using indexes and get an error like *key size exceeds
+implementation restriction for index*, see `this page`_ to understand
+the restrictions on your indexing.
+
+.. _this page: http://www.volny.cz/iprenosil/interbase/ip_ib_indexcalculator.htm
+
+SybaseConnection
+----------------
+
+`SybaseConnection` takes the arguments `host`, `db`, `user`, and
+`passwd`. It also takes the extra boolean argument `locking` (default
+True), which is passed through when performing a connection. You may
+use a False value for `locking` if you are not using multiple threads,
+for a slight performance boost.
+
+It uses the Sybase_ module.
+
+.. _Sybase: http://www.object-craft.com.au/projects/sybase/
+
+MAX DB
+------
+
+MAX DB, also known as SAP DB, is available from a partnership of SAP
+and MySQL. It takes the typical arguments: `host`, `database`,
+`user`, `password`. It also takes the arguments `sqlmode` (default
+``"internal"``), `isolation`, and `timeout`, which are passed through
+when creating the connection to the database.
+
+It uses the sapdb_ module.
+
+.. _sapdb: http://www.sapdb.org/sapdbPython.html
+
+Exported Symbols
+================
+
+You can use ``from sqlobject import *``, though you don't have to. It
+exports a minimal number of symbols. The symbols exported:
+
+From `sqlobject.main`:
+
+* `NoDefault`
+* `SQLObject`
+* `getID`
+* `getObject`
+
+From `sqlobject.col`:
+* `Col`
+* `StringCol`
+* `IntCol`
+* `FloatCol`
+* `KeyCol`
+* `ForeignKey`
+* `EnumCol`
+* `DateTimeCol`
+* `DecimalCol`
+* `CurrencyCol`
+
+From `sqlobject.joins`:
+* `MultipleJoin`
+* `RelatedJoin`
+
+From `sqlobject.styles`:
+* `Style`
+* `MixedCaseUnderscoreStyle`
+* `DefaultStyle`
+* `MixedCaseStyle`
+
+From `sqlobject.sqlbuilder`:
+
+* `AND`
+* `OR`
+* `NOT`
+* `IN`
+* `LIKE`
+* `DESC`
+* `CONTAINSSTRING`
+* `const`
+* `func`
+
+LEFT JOIN and other JOINs
+-------------------------
+
+First look in the FAQ_, question "How can I do a LEFT JOIN?"
+
+Still here? Well. To perform a JOIN use one of the JOIN helpers from
+sqlobject.sqlbuilder. Pass an instance of the helper to .select()
+method. For example::
+
+ from sqlobject.sqlbuilder import LEFTJOINOn
+ MyTable.select(
+ join=LEFTJOINOn(Table1, Table2,
+ Table1.q.name == Table2.q.value))
+
+will generate the query::
+
+ SELECT * FROM my_table, table1
+ LEFT JOIN table2 ON table1.name = table2.value;
+
+.. _FAQ: FAQ.html#how-can-i-do-a-left-join
+
+If you want to join with the primary table - leave the first table
+None::
+
+ MyTable.select(
+ join=LEFTJOINOn(None, Table1,
+ MyTable.q.name == Table1.q.value))
+
+will generate the query::
+
+ SELECT * FROM my_table
+ LEFT JOIN table2 ON my_table.name = table1.value;
+
+The join argument for .select() can be a JOIN() or a list/tuples of
+JOIN()s.
+
+
+How can I join a table with itself?
+-----------------------------------
+
+Use Alias from sqlobject.sqlbuilder. Example::
+
+ from sqlobject.sqlbuilder import Alias
+ alias = Alias(MyTable, "my_table_alias")
+ MyTable.select(MyTable.q.name == alias.q.value)
+
+will generate the query::
+
+ SELECT * FROM my_table, my_table AS my_table_alias
+ WHERE my_table.name = my_table_alias.value;
+
+Can I use LEFTJOIN() with aliases?
+----------------------------------
+
+Sure! That's a situation the JOINs and aliases were primary developed
+for. Code::
+
+ from sqlobject.sqlbuilder import LEFTJOINOn, Alias
+ alias = Alias(OtherTable, "other_table_alias")
+ MyTable.select(MyTable.q.name == OtherTable.q.value,
+ join=LEFTJOINOn(MyTable, alias, MyTable.col1 == alias.q.col2))
+
+will result in the query::
+
+ SELECT * FROM other_table,
+ my_table LEFT JOIN other_table AS other_table_alias
+ WHERE my_table.name == other_table.value AND
+ my_table.col1 = other_table_alias.col2.
+
+Subqueries (subselects)
+-----------------------
+
+You can run queries with subqueries (subselects) on those DBMS that can do
+subqueries (MySQL supports subqueries from version 4.1).
+
+Use corresponding classess and functions from sqlbuilder::
+
+ from sqlobject.sqlbuilder import EXISTS, Select
+ select = Test1.select(EXISTS(Select(Test2.q.col2, where=(Outer(Test1).q.col1 == Test2.q.col2))))
+
+generates the query::
+
+ SELECT test1.id, test1.col1 FROM test1 WHERE
+ EXISTS (SELECT test2.col2 FROM test2 WHERE (test1.col1 = test2.col2))
+
+Note the usage of Outer - this is the helper to allow refering to a table
+in the outer query.
+
+Select() is used instead of .select() because you need to control what
+columns and in what order the inner query returns.
+
+Avalable queries are IN(), NOTIN(), EXISTS(), NOTEXISTS(), SOME(), ANY()
+and ALL(). The last 3 are used with comparison operators, like this:
+"somevalue = ANY(Select(...))".
+
+SQLBuilder
+----------
+
For more information on SQLBuilder, read the `SQLBuilder
Documentation`_.
|
