[DB-SIG] Optional DB API Extensions

Stuart Bishop zen@shangri-la.dropbear.id.au
Thu, 25 Oct 2001 22:02:58 +1000

On Thursday, October 25, 2001, at 09:11  PM, Federico Di Gregorio wrote:
> this is, imo, wrong. you can expect quote() to be able to cope with
> *any* object. lets stick to the dbapi official types:
> 	>>> print con.quote(module.TimestampFromTicks(time.time()))
>         TO_DATE('01-Jan-2001 12:01','DD-MMM-YYYY H24:MI')

Yup. My mistake. int, float, string, date for sure. Possibly others 
on the driver, such as raw.

> but we need dictionaries able to cope with multiple equal keys (some db
> allow multiple columns with the same name). also, should the keys be
> compared ci or cs?

I would keep it simple - dictionary keys are as taken from 
and behavior undefined if your SQL manages to return columns with the 
name. I havn't used a DB that actually lets me do this that I know of.

> i am working on this for psycopg. it is somewhat simplier that your
> proposal, just a way to ask the db for a given isolation level. or are
> isolation levels defined at the SQL standard level?

The transaction isolation levels are defined by SQL99 (I think - I 
and used the JDBC docs rather than wade through the official specs. The
proposal was pretty much identical to what JDBC offers in fact).

What I had proposed was fairly simple when summarised.
     lev is one of:

     con.set_transaction_level sets the level as requested, or a higher 
level if it can't.

I think most drivers would simply stay in TRANSACTION_READ_UNCOMMITTED 
mode and
raise an exception if an attempt is made to set it higher.

Here are the relevant definitions and such if anyone is interested...

     Transaction Isolation Levels

         Dirty reads occur when transactions are allowed to see 
         changes to the data. In other words, changes made inside a 
transaction are
         visible outside the transaction before they are committed. If 
the changes
         are rolled back instead of being committed, it is possible for 
         transactions to have done work based on incorrect, transient 

         Nonrepeatable reads occur when:
           a. Transaction A reads a row
           b. Transaction B changes the row
           c. Transaction A reads the same row a second time and gets
              different results

         Phantom reads occur hen:
           a. Transaction A reads all rows that satisfy a WHERE condition
           b. Transaction B inserts an additional row that satisfies the
              same condition.
           c. Transaction A reevaluates the WHERE condition and picks up 
              additional 'phantom' row.

         The following symbolic constants are defined to describe the
         four transaction isolation levels defined by SQL99. Note that
         they are in numeric order so comparison operators can be
         safely used to test for restrictivness. Note that these 
         are taken from the JDBC API 3.0 documentation by Sun.


             No transaction isolation is guarenteed.


             Allows transactions to see uncommitted changes to the data.
             This means that dirty reads, nonrepeatable reads, and
             phantom reads are possible.


             Means that any changes made inside a transaction are not 
             outside the transaction until the transaction is committed. 
             prevents dirty reads, but nonrepeatable reads and phantom
             reads are still possible.


             Disallows dirty reads and nonrepeatable reads. Phantom read 
             still possible.


             Specifies that dirty reads, nonrepeatable reads, and phantom
             reads are prevented.

Stuart Bishop <zen@shangri-la.dropbear.id.au>