doc/book/en/development/devcore/dbapi.rst
author Aurelien Campeas <aurelien.campeas@logilab.fr>
Thu, 08 Apr 2010 18:05:41 +0200
branchstable
changeset 5190 73bdc50d6af1
parent 5189 84d4587a92bc
child 5257 a31fbcfa8c3b
permissions -rw-r--r--
[doc/book] dbapi: talk about IN exception, simplify
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     1
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     2
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     3
API Python/RQL
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     4
~~~~~~~~~~~~~~
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     5
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     6
The Python API developped to interface with RQL is inspired from the standard db-api,
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     7
with a Connection object having the methods cursor, rollback and commit essentially.
5189
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
     8
The most important method is the `execute` method of a cursor.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     9
5189
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
    10
.. sourcecode:: python
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
    11
  execute(rqlstring, args=None, cachekey=None, build_descr=True)
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    12
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    13
:rqlstring: the RQL query to execute (unicode)
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    14
:args: if the query contains substitutions, a dictionary containing the values to use
4441
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    15
:cachekey:
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    16
   an implementation detail of the RQL cache implies that if a substitution
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    17
   is used to introduce an eid *susceptible to raise the ambiguities in the query
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    18
   type resolution*, then we have to specify the corresponding key in the dictionary
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    19
   through this argument
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    20
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    21
5189
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
    22
The `Connection` object owns the methods `commit` and `rollback`. You
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
    23
*should never need to use them* during the development of the web
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
    24
interface based on the *CubicWeb* framework as it determines the end
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
    25
of the transaction depending on the query execution success. They are
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
    26
however useful in other contexts such as tests.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    27
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    28
.. note::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    29
  While executing update queries (SET, INSERT, DELETE), if a query generates
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    30
  an error related to security, a rollback is automatically done on the current
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    31
  transaction.
4441
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    32
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    33
Executing RQL queries from a view or a hook
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    34
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
5189
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
    35
4441
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    36
When you're within code of the web interface, the db-api like connexion is
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    37
handled by the request object. You should not have to access it directly, but
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    38
use the `execute` method directly available on the request, eg:
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    39
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    40
   rset = self._cw.execute(rqlstring, kwargs)
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    41
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    42
Similarly, on the server side (eg in hooks), there is no db-api connexion (since
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    43
you're directly inside the data-server), so you'll have to use the execute method
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    44
of the session object.
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    45
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    46
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    47
Important note about proper usage of .execute
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    48
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    49
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    50
Let's say you want to get T which is in configuration C, this translates to:
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    51
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    52
.. sourcecode:: python
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    53
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    54
   self._cw.execute('Any T WHERE T in_conf C, C eid %s' % entity.eid)
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    55
5189
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
    56
But it must be written in a syntax that will benefit from the use
4441
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    57
of a cache on the RQL server side:
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    58
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    59
.. sourcecode:: python
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    60
5189
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
    61
   self._cw.execute('Any T WHERE T in_conf C, C eid %(x)s', {'x': entity.eid})
4441
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    62
5189
84d4587a92bc [doc/book] rql/dbapi cleanup, rip cachekey (prematurely ?)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4441
diff changeset
    63
The syntax tree is built once for the "generic" RQL and can be re-used
5190
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    64
with a number of different eids. There rql IN operator is an exception
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    65
to this rule.
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    66
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    67
.. sourcecode:: python
4441
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    68
5190
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    69
   self._cw.execute('Any T WHERE T in_conf C, C name IN (%s)' % ','.join(['foo', 'bar']))
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    70
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    71
Alternativelly, some of the common data related to an entity can be
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    72
obtained from the `entity.related()` method (which is used under the
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    73
hood by the orm when you use attribute access notation on an entity to
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    74
get a relation. The initial request would then be translated to:
4441
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    75
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    76
.. sourcecode:: python
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    77
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    78
   entity.related('in_conf', 'object')
550cf406dbc6 moved content to the dbapi section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2175
diff changeset
    79
5190
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    80
Additionnaly this benefits from the fetch_attrs policy eventually
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    81
defined on the class element, which says which attributes must be also
73bdc50d6af1 [doc/book] dbapi: talk about IN exception, simplify
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5189
diff changeset
    82
loaded when the entity is loaded through the orm.