Context Navigation

Changes between Version 35 and Version 36 of TracDev/DatabaseApi

Timestamp:: Jul 16, 2018, 7:53:40 PM (6 years ago)
Author:: Ryan J Ollos
Comment:: Minor revisions and expand Trac 1.0 db API usage examples (Refs th:comment:6:ticket:13380).

Legend:

: Unmodified
: Added
: Removed
: Modified

TracDev/DatabaseApi

-              v35
+              v36
 == Accessing the Database
 The Database API has evolved in the various versions of Trac, and its usage differs substantially across Trac 0.11, 0.12 and 1.x. The following subsections explain these differences chronologically.
+The Database API has evolved in the various versions of Trac, and its usage differs substantially across Trac 0.11, 0.12 and 1.x. The following subsections explain these differences chronologically. You can skip to the [#Trac1.0API last section] if you don't care about the history and only need to write code that works in Trac 1.0 and later.
 === API before Trac 0.12
 …
 === Trac 0.12 API
 // The 0.12 style can still be used in 1.0, but for new code we encourage you to focus on the Trac version 1.0 and to adopt the even simpler 1.0 style. See next sections for details. The 0.12 style was removed in [milestone:1.3.1].//
+//The 0.12 style can still be used in 1.0, but for new code we encourage you to focus on the Trac version 1.0 and to adopt the even simpler 1.0 style. See next sections for details. The 0.12 style was removed in [milestone:1.3.1].//
 As support for Python 2.3 has been dropped, we could make use of decorators to get rid of some of the boilerplate code:
 …
 In this example, when `myFunc1()` is called, it first executes the outer transaction (`do_outer_transaction()`) and then executes a nested transaction (`myFunc2()`) from within the outer transaction. The key observation is that '''nested transactions are atomic'''.
 This means that either the ''whole'' (outer) transaction is either committed or aborted. So even if the nested transaction succeeded but the outer transaction fails (an exception is raised at the line with the "commit or rollback" comment), the ''whole'' transaction will be rolled back, ie including the changes made by the nested transaction.
+This means that either the ''whole'' (outer) transaction is either committed or aborted. So even if the nested transaction succeeded but the outer transaction fails (an exception is raised at the line with the "commit or rollback" comment), the ''whole'' transaction will be rolled back, i.e. including the changes made by the nested transaction.
 '''Notes:'''
  - Do **not** call `commit()` yourself in a transaction, even though this is still possible in the API for backward compatibility reasons. Not only is a commit performed automatically at the proper time (as explained above), but if you call it yourself, you risk to commit from within a nested transaction, possibly leading to an inconsistent state of the database.
  - Using `env.get_read_db()` within a transaction reuses the same connection as used for the connection. So uncommitted changes made by the transaction will already be visible to the caller of `get_read_db()` (but not outside of the transaction - that is in another thread).
+ - Using `env.get_read_db()` within a transaction reuses the same connection. So uncommitted changes made by the transaction will already be visible to the caller of `get_read_db()` (but not outside of the transaction - that is in another thread).
  - Uncommited changes of a transaction are only visible to nested transactions in the same thread. Different threads use different database connections and therefore different transactions. To be more precise, the exact detail of what is visible to other threads is database specific and depends to the //isolation level// used.
 === Trac 1.0 API
+=== API after Trac 1.0 #Trac1.0API
 [=#Trac0.13API]
 // This style is supported for version 1.0.x, 1.1.x and will be supported beyond as well.
 Starting with 1.3.1, this is actually the only way, as the deprecated APIs were removed.//
+//This style is supported for version 1.0 and later.
+Starting with [milestone:1.3.1], this is actually the only way, as the deprecated APIs were removed.//
 As we dropped support for Python 2.4, we could simplify the code a bit more by using the `with` keyword and context managers:
 …
 }}}
+You can use a list comprehension or dict comprehension to generate a sequence from a query:
+{{{#!python
+from trac.env import Environment
+env = Environment('/path/to/projenv')
+def myFunc1():
+    """Returns a list of tuples."""
+    return [a, b, c for a, b, c in env.db_query("""
+            SELECT a, b, c
+            FROM ...
+            """, (param1, param2))]
+env = Environment('/path/to/projenv')
+def myFunc1():
+    """Equivalent to myFunc1, without tuple unpacking."""
+    return [row for row in env.db_query("""
+            SELECT a, b, c
+            FROM ...
+            """, (param1, param2))]
+def myFunc3():
+    """Equivalent to myFunc1."""
+    # Or even more simply, if the data is returned
+    # directly from the database without modification.
+    return env.db_query("""
+            SELECT a, b, c
+            FROM ...
+            """, (param1, param2))
+def myFunc4():
+    """Returns a dict."""
+    # This form of dict creation should be used in Python < 2.7
+    return [dict(a, b) for a, b in env.db_query("""
+            SELECT a, b
+            FROM ...
+            """, (param1, param2))]
+def myFunc5():
+    """Returns a dict."""
+    # Dict comprehensions available in Python 2.7
+    return {a: b for a, b in env.db_query("""
+            SELECT a, b
+            FROM ...
+            """, (param1, param2))}
+}}}
+Note that dict comprehensions are only available in Python 2.7
+and Trac < 1.3.1 supports Python 2.6.
 These short forms also present an additional safety measure as they're only allowing a "SELECT" query to be executed by a read-only connection. Indeed, the same short forms are possible on both `env.db_transaction` and `env.db_query`.
 === Configuration
 To determine which database has to be used, Trac looks at the value of the {{{database}}} configuration option in [wiki:TracIni trac.ini], which should contain a database connection URI. The default value for this option tells Trac to use an SQLite database inside the environment directory:
+To determine which database has to be used, Trac looks at the value of the `database` configuration option in [TracIni#trac-database-option trac.ini], which should contain a database connection URI. The default value for this option tells Trac to use an SQLite database inside the environment directory:
 {{{#!ini
 …
 }}}
+ ''The connection URI syntax has been designed to be compatible with that provided by [http://sqlobject.org/SQLObject.html#declaring-the-class SQLObject]. See also the section on SQLObject [http://sqlobject.org/SQLObject.html#declaring-a-connection connections]. The only supported URI schemes at this point are `sqlite`, `postgres` and `mysql`.''
+The connection URI syntax has been designed to be compatible with that provided by [http://sqlobject.org/SQLObject.html#declaring-the-class SQLObject]. See also the section on SQLObject [http://sqlobject.org/SQLObject.html#declaring-a-connection connections]. The only supported URI schemes at this point are `sqlite`, `postgres` and `mysql`.
 === Pooled Connections
 Prior to version 1.0, Trac used to operate the following way:
  The {{{Environment}}} method {{{get_db_cnx()}}} returns a connection from the pool of connections. This connection needs to be returned, and Trac is written so that the return will happen automatically by the garbage collector if the code is written to follow a simple rule. When the garbage collector determines the pooled database connection is no longer being used, its `__del__` method will return the pooled connection to the pool for reuse. If you have set a lexical variable in the function's body to the pooled connection, this typically occurs when the function is returning. In the example above of {{{myFunc}}} it occurs at the return statement since {{{db}}} is a variable local to {{{myFunc}}}
+ The `Environment` method `get_db_cnx()` returns a connection from the pool of connections. This connection needs to be returned, and Trac is written so that the return will happen automatically by the garbage collector if the code is written to follow a simple rule. When the garbage collector determines the pooled database connection is no longer being used, its `__del__` method will return the pooled connection to the pool for reuse. If you have set a lexical variable in the function's body to the pooled connection, this typically occurs when the function is returning. In the example above of `myFunc` it occurs at the return statement since `db` is a variable local to `myFunc`.
 With the context managers introduced in Trac 1.0, we're able to return this Connection to the pool in a much more robust and direct way:
 …
 == Rules for DB API Usage
 Different DB API modules have different ways to pass parameters to the cursors' {{{execute}}} method, and different ways to access query results. To keep the database API as thin as possible, Trac uses a relatively common subset in all database code.
+Different DB API modules have different ways to pass parameters to the cursors' `execute` method, and different ways to access query results. To keep the database API as thin as possible, Trac uses a relatively common subset in all database code.
 === Parameter passing
 Always use the "format" parameter style, and always with {{{%s}}}, because that's the only type that pyPgSQL supports. Statement parameters always need to be passed into execute as an actual sequence (list or tuple).
+Always use the "format" parameter style, and always with `%s`. Statement parameters always need to be passed into execute as an actual sequence (list or tuple).
 So the following statements are okay:
 …
 }}}
 At any cost, avoid [http://docs.python.org/library/stdtypes.html#string-formatting string formatting] to get values into the SQL statement. The database automatically escapes values you pass using {{{execute()}}} arguments, but the same is not true if you use string formatting, opening your code up to [wikipedia:SQL_injection SQL injection] attacks.
 On the other hand, you '''must''' use string formatting to dynamically specify names of tables or columns, ie anything that is not a value as such:
+At any cost, avoid [http://docs.python.org/library/stdtypes.html#string-formatting string formatting] to get values into the SQL statement. The database automatically escapes values you pass using `execute()` arguments, but the same is not true if you use string formatting, opening your code up to [wikipedia:SQL_injection SQL injection] attacks.
+On the other hand, you '''must''' use string formatting to dynamically specify names of tables or columns, i.e. anything that is not a value as such:
 {{{#!python