Changes between Version 15 and Version 16 of TracDev/DatabaseApi

Timestamp:

Sep 25, 2010, 2:30:37 PM (14 years ago)

Author:

Christian Boos

Comment:

updating #Trac0.13API to latest status

TracDev/DatabaseApi

@@ -84 +84 @@
             """, (param1, param2))
         # Execute some SQL "SELECT" statements
+    # (continue)
 }}}
 
-This one enforces the notion of read-access, because the `db` connection bound to the context doesn't support the `commit()` or `rollback()` methods. As there's not `commit()` upon exit, one could question the usefulness of this syntactic construct. The main interest is in better locality of the  connection: in order to improve concurrency, the lifetime of a connection wrapper has to be as short as possible (see #3446). 
+This one enforces the notion of read-access, because the `db` connection bound to the context doesn't support the `commit()` or `rollback()` methods. As there's no `commit()` upon exit, one could question the usefulness of this syntactic construct. The main interest is in better locality of the  connection: in order to improve concurrency, the lifetime of a connection wrapper has to be as short as possible (see #3446). Therefore, we close the connection on exit, making it available to other threads. So even if Python gives you access to the `db` variable after the `with` block (at the //`# (continue)`// line in the above example), you should not (and can't) use it at that point.
 
-However, there's maybe an even better/shorter way to achieve this, with an execute helper function:
+Actually, if you don't need to do anything fancy with the cursor like calling `db.get_last_id(cursor, ...)`, then you can use a shorter form:
+{{{
+#!python
+from trac.env import Environment
+def myFunc():
+    env = Environment('/path/to/projenv')
+    with env.db_query as db:
+        for a, b, c in db("""
+                SELECT a, b, c 
+                FROM ...
+                """, (param1, param2)):
+            # do something with a, b, c
+}}}
+
+All the results are returned at once in a list, by calling `fetchall()` on the underlying cursor. This is fine most of the time, if you need to limit the number of returned results, you can still use "LIMIT" or "OFFSET" in the SQL query.
+
+In the same spirit, if you don't even need to use `db` itself for things like `db.cast()` or `db.like()`, you can simply do:
 
 {{{
@@ -95 +112 @@
 def myFunc():
     env = Environment('/path/to/projenv')
-    with env.db_query as db:
-        for a, b, c in db.execute("""
-            SELECT a, b, c FROM ...
+    for a, b, c in env.db_query("""
+            SELECT a, b, c
+            FROM ...
             """, (param1, param2)):
-            # do something with a, b, c
+        # do something with a, b, c
 }}}
 
-or:
-
-{{{
-#!python
-from trac.env import Environment
-def myFunc():
-    env = Environment('/path/to/projenv')
-    with env.db_query as db:
-        for a, b, c in db.select("""
-            a, b, c FROM ...
-            """, (param1, param2)):
-            # do something with a, b, c
-}}}
-
-or even:
-
-{{{
-#!python
-from trac.env import Environment
-def myFunc():
-    env = Environment('/path/to/projenv')
-    for a, b, c in env.db_query.select("""
-        a, b, c FROM ...
-        """, (param1, param2)):
-        # do something with a, b, c
-}}}
+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