Quantcast

Some obscurity with paramstyle

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
34 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Some obscurity with paramstyle

Christoph Zwerschke
At the PyGreSQL mailing list we're currently wondering whether the
'format' and 'pyformat' paramstyles allow specifying parameters with
types other than '%s' - e.g. can I specify my parameter as '%.2f' or
'%(name).2f' if I want to round floats to 2 digits?

PEP 249 has only '%s' in the example, but does not exclude other types,
does this mean these are allowed?

Also, should we define a new paramstyle for the advanced string
formatting syntax available since Py 2.6?

-- Christoph
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

Michael Bayer

On Jul 16, 2011, at 3:20 PM, Christoph Zwerschke wrote:

> At the PyGreSQL mailing list we're currently wondering whether the 'format' and 'pyformat' paramstyles allow specifying parameters with types other than '%s' - e.g. can I specify my parameter as '%.2f' or '%(name).2f' if I want to round floats to 2 digits?
>
> PEP 249 has only '%s' in the example, but does not exclude other types,
> does this mean these are allowed?
>
> Also, should we define a new paramstyle for the advanced string formatting syntax available since Py 2.6?

Not all backends that support "format" or "pyformat" would be able to allow such behavior - often the client API of the database in use is passed, from the DBAPI, the SQL statement with placeholders and the parameters separately - no "string formatting" takes place.    A DBAPI that advertises "format" or "pyformat" then may or may not be able to handle the extended syntax.

IMHO I would prefer to see the DBAPI have exactly two paramstyles, named and qmark, and have all DBAPIs support both consistently.    The (py)format styles continuously introduce the mixing of Python's string formatting behavior with the presentation of bound parameters, which are two completely different things.


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

Carl Karsten
On Sat, Jul 16, 2011 at 3:27 PM, Michael Bayer <[hidden email]> wrote:
> IMHO I would prefer to see the DBAPI have exactly two paramstyles, named and qmark, and have all DBAPIs support both consistently.    The (py)format styles continuously introduce the mixing of Python's string formatting behavior with the presentation of bound parameters, which are two completely different things.

bold +1 /bold

--
Carl K
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

Vernon D. Cole
In reply to this post by Michael Bayer
On Sat, Jul 16, 2011 at 2:27 PM, Michael Bayer <[hidden email]> wrote:

On Jul 16, 2011, at 3:20 PM, Christoph Zwerschke wrote:

> At the PyGreSQL mailing list we're currently wondering whether the 'format' and 'pyformat' paramstyles allow specifying parameters with types other than '%s' - e.g. can I specify my parameter as '%.2f' or '%(name).2f' if I want to round floats to 2 digits?
>
> PEP 249 has only '%s' in the example, but does not exclude other types,
> does this mean these are allowed?
>
> Also, should we define a new paramstyle for the advanced string formatting syntax available since Py 2.6?

Not all backends that support "format" or "pyformat" would be able to allow such behavior - often the client API of the database in use is passed, from the DBAPI, the SQL statement with placeholders and the parameters separately - no "string formatting" takes place.    A DBAPI that advertises "format" or "pyformat" then may or may not be able to handle the extended syntax.

IMHO I would prefer to see the DBAPI have exactly two paramstyles, named and qmark, and have all DBAPIs support both consistently.    The (py)format styles continuously introduce the mixing of Python's string formatting behavior with the presentation of bound parameters, which are two completely different things.

Another advantage to supporting only those two paramstyles is that it would make the paramstyle attribute obsolete.
  If the bound parameters form a sequence, qmark is implied, if a mapping, then named is implied.

Disadvantages would include:
1) some database engines seem to use %s format internally, and efficiency would be lost in translation.
2) some major applications (e.g. django) assume %s format.

But I am +1 for the idea anyway.
--
Vernon Cole


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

Michael Bayer

On Jul 17, 2011, at 1:52 AM, Vernon Cole wrote:

> 2) some major applications (e.g. django) assume %s format.
>
> But I am +1 for the idea anyway.

But not all DBAPIs support %s format, do you mean that Django assumes %s format for those DBAPIs that are known to do so ?   Shouldn't they be at least using .paramstyle to determine that ?

Is there a path to changes being made in the DBAPI?   i.e. would there be a DBAPI 3 ?  
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

Vernon D. Cole


On Sun, Jul 17, 2011 at 8:54 AM, Michael Bayer <[hidden email]> wrote:

On Jul 17, 2011, at 1:52 AM, Vernon Cole wrote:

> 2) some major applications (e.g. django) assume %s format.
>
> But I am +1 for the idea anyway.

But not all DBAPIs support %s format, do you mean that Django assumes %s format for those DBAPIs that are known to do so ?   Shouldn't they be at least using .paramstyle to determine that ?

Well, that's how the 2.0 spec assumes things should work: the DBAPI module tells you which paramstyle it uses, then you write your program five different ways to cover the five possibilities.  But django does not do that (does anyone??) -- it blindly assumes that all DBAPI's use %s.  That's why someone made a fork of adodbapi in order to support Microsoft SQL server in django.  ADO uses qmark, so the fork version had to convert %s to qmark before executing the SQL statements.   I pulled the format conversion code (and some other improvements) back into the release version of adodbapi, fixed it so that it does not break % signs in literals, and added a feature so that a programmer can request either qmark, format, or named paramstyle.  That's why I favor the suggestion. Having written a version which does format conversion, I know that it is not hard to do.

Is there a path to changes being made in the DBAPI?   i.e. would there be a DBAPI 3 ?  

That possibility has been discussed before, and is particularly timely given that it is impossible to write a PEP 249 compliant module in Python 3.  [For example, the PEP states that an Error exception "must be a subclass of the Python StandardError" -- which Python 3 does not support.] 

Marc-André Lemburg (the author of PEP 249) came out against an update -- mostly due to performance reasons.  This is understandable, because his company produces a very efficient db api module.  The features which have been suggested for a possible DBAPI 3.0 would add some significant overhead in some situations. The worry is real. Compare the mxDateTime module (also from Mark-Andre's company) with the (newer) standard datetime module, and you will see that datetime is a slow dog. (I use it anyway.)

On the other hand, GVR is in favor of an update. When MAL suggested that some ease-of-use improvements (such as named columns) would make implementation of a module more difficult, The BDFL replied that we should make things easier for the user, not the module author. [ I am relying on my memory -- my copy of the exchange got deleted and I am too lazy to look it up in the archive. ;) ]

So Yes, there are a TON of new language constructs which could be addressed in an updated API version.  The use of a cursor as an iterator is supported by most DB API modules, but not mentioned in PEP 249.  A cursor and/or a connection should probably be context managers, so that they will work in a "with" statement.  There should be a better definition of how BINARY fields work with byte() data.
--
Vernon


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

Michael Bayer

On Jul 17, 2011, at 9:10 PM, Vernon Cole wrote:



On Sun, Jul 17, 2011 at 8:54 AM, Michael Bayer <[hidden email]> wrote:

On Jul 17, 2011, at 1:52 AM, Vernon Cole wrote:

> 2) some major applications (e.g. django) assume %s format.
>
> But I am +1 for the idea anyway.

But not all DBAPIs support %s format, do you mean that Django assumes %s format for those DBAPIs that are known to do so ?   Shouldn't they be at least using .paramstyle to determine that ?

Well, that's how the 2.0 spec assumes things should work: the DBAPI module tells you which paramstyle it uses, then you write your program five different ways to cover the five possibilities.  But django does not do that (does anyone??)

If I understand correctly,  SQLAlchemy has done it this way since its first release six years ago.   It's quite simple to have the param behavior vary independently of a specific DBAPI implementation. 

-- it blindly assumes that all DBAPI's use %s.  That's why someone made a fork of adodbapi in order to support Microsoft SQL server in django. 
ADO uses qmark, so the fork version had to convert %s to qmark before executing the SQL statements.   I pulled the format conversion code (and some other improvements) back into the release version of adodbapi, fixed it so that it does not break % signs in literals, and added a feature so that a programmer can request either qmark, format, or named paramstyle. 

Pysqlite doesn't support %s, how'd they work around that one ?  


Is there a path to changes being made in the DBAPI?   i.e. would there be a DBAPI 3 ?  

That possibility has been discussed before, and is particularly timely given that it is impossible to write a PEP 249 compliant module in Python 3.  [For example, the PEP states that an Error exception "must be a subclass of the Python StandardError" -- which Python 3 does not support.] 

yeah StandardError, had a funny thread about that one some weeks ago...


Marc-André Lemburg (the author of PEP 249) came out against an update -- mostly due to performance reasons.  This is understandable, because his company produces a very efficient db api module.  The features which have been suggested for a possible DBAPI 3.0 would add some significant overhead in some situations. The worry is real. Compare the mxDateTime module (also from Mark-Andre's company) with the (newer) standard datetime module, and you will see that datetime is a slow dog. (I use it anyway.)

The spec should provide that every DBAPI support the standard datetime module by default (though this is tricky for sqlite...);  hooks to use alternates like mxDateTime should be present as well as I would assume mxDateTime has a very similar interface to the datetime builtin.    

If datetime is slow, that's a Python bug - an efficient C implementation should be provided for such a critical piece.   Similarly, the cDecimal library should be part of Python too as Decimal is extremely slow (I would hope that DBAPI 3.0 references support for Decimal explicitly - many DBAPIs like pymssql are broken due to the assumption of float for precision numerics).   I would hope DBAPI3 can say something about supporting Decimal for precision numerics specifically without using lossful floats as a middle layer (we have a lot of workarounds for different DBAPIs to get this part to work, on some it's impossible).


On the other hand, GVR is in favor of an update.

Given the very bad state of the Unicode situation in DBAPI 3, as well as for precision numerics,  i think it's pretty necessary.    I would opt that all strings from a version 3 DBAPI can be  returned as u'' literals in Python 2 (including down to the names present in cursor.description - pyodbc + MSSQL for example decides to fix these as latin-1 despite other encoding settings).    It's been my observation that an encoding parameter is pretty much present in some form for every database client API, this should be an explicit part of the DBAPI, and the encoding present would be used for all bytestring/unicode coercions.

If performance is a concern here, a documented system of enabling "unicode returns" can be provided.      But in Python 3 these strings are all unicodes anyway, so this behavior will be needed in any case.   

Python unicodes should also always be accepted for all arguments, including bind parameter values, bind parameter names, and the SQL statement itself.  SQLAlchemy has to encode these for some backends, for others it has to leave it alone else things break, and for pyodbc it's dependent on if you're using FreeTDS or not [!].     While it might seem a little strange to suggest that unicode is explicitly allowed for all strings/identifiers/names, as well as cursor.description names, we have users in europe and asia that use non-ascii identifiers all the time (and they are accepted by most databases), and we've bent over backwards for years to support them all.   

Pysqlite is the only DBAPI that seems to have "all unicode everywhere" behavior, with unicode support to extremely varying degrees in all the others (with Psycopg2 probably doing the best job at coming close - even though you need to use non-standard extension calls to enable it fully).  

On the unicode issue, cx_oracle had a totally complicated progression in version 5.0 when they introduced this poorly-considered UNICODE mode, derived from their Python 3 implementation but made available as a static build option for Python 2, where even the connect() call wouldn't accept any incoming string that was *not* unicode !   Then some user insisted that he absolutely *needed* this ridiculous mode, more bending over backwards for SQLAlchemy to support it, then cx_oracle 5.1, realizing they should just be using NLS_LANG (i.e. the "encoding" parameter provided by OCI), they *removed* the whole thing entirely.   A long effort that caused wasted time for many.   Explicit, non-ambiguous guidance on unicode handling is sorely needed.


When MAL suggested that some ease-of-use improvements (such as named columns) would make implementation of a module more difficult, The BDFL replied that we should make things easier for the user, not the module author. [ I am relying on my memory -- my copy of the exchange got deleted and I am too lazy to look it up in the archive. ;) ]

Ease of use things like named columns, connection.execute(), context manager, these are all fairly trivial adds for all DBAPIs.    They are of course all fine.   But IMHO this is not the critical piece.   It's about behaviors that everyone needs to deal with, like can I send u'select * from table' to execute() or must it be 'select * from table', can the "sequence" sent to execute() be a tuple *or* a list (yes this varies, *incompatibly*, between DBAPIs, some accepting only tuple, others accepting only list), that each DBAPI is currently doing in an arbitrary and different fashion with no guidance establishing non-compliant behaviors as bugs.    If the spec were just more decisive about things, I could just go to all these DBAPI authors that all have their own opinions about things and say "no, this is it".   Right now, DBAPIs are out there with all kinds of behaviors, I can walk you through a large number of them that we've had to normalize.   None of them are bugs.  Once the spec comes out and takes a stand, they all become bugs, and until fixed the DBAPi is not spec-compliant.



So Yes, there are a TON of new language constructs which could be addressed in an updated API version.  The use of a cursor as an iterator is supported by most DB API modules, but not mentioned in PEP 249.  A cursor and/or a connection should probably be context managers, so that they will work in a "with" statement.  There should be a better definition of how BINARY fields work with byte() data.
--
Vernon



_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

M.-A. Lemburg
In reply to this post by Christoph Zwerschke
Christoph Zwerschke wrote:
> At the PyGreSQL mailing list we're currently wondering whether the
> 'format' and 'pyformat' paramstyles allow specifying parameters with
> types other than '%s' - e.g. can I specify my parameter as '%.2f' or
> '%(name).2f' if I want to round floats to 2 digits?
>
> PEP 249 has only '%s' in the example, but does not exclude other types,
> does this mean these are allowed?

Not really. The DB-API 2.0 allows for a certain number of binding
parameter formats, but you shouldn't really invent more.

For the next version, we're likely going to reduce the number
of allowed formats to just two (%s and ?, IIRC), since having
lots of different formats has resulted in much confusion.

> Also, should we define a new paramstyle for the advanced string
> formatting syntax available since Py 2.6?

No. See above.

The problem with %s or any other format that resembles a
Python string formatting character is that people (rightly)
expect the full functionality of those to be available, which
is not the case.

There's a significant difference between string formatting
characters and binding parameter characters: the latter just
let the database module know where a certain parameter
value should be added, but don't literally include the
parameters in the SQL string, nor define how the parameters
are passed to the database.

Binding parameters just define an association between the
SQL and the parameter list, nothing more.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jul 18 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

M.-A. Lemburg
In reply to this post by Michael Bayer
Michael Bayer wrote:

>
> On Jul 16, 2011, at 3:20 PM, Christoph Zwerschke wrote:
>
>> At the PyGreSQL mailing list we're currently wondering whether the 'format' and 'pyformat' paramstyles allow specifying parameters with types other than '%s' - e.g. can I specify my parameter as '%.2f' or '%(name).2f' if I want to round floats to 2 digits?
>>
>> PEP 249 has only '%s' in the example, but does not exclude other types,
>> does this mean these are allowed?
>>
>> Also, should we define a new paramstyle for the advanced string formatting syntax available since Py 2.6?
>
> Not all backends that support "format" or "pyformat" would be able to allow such behavior - often the client API of the database in use is passed, from the DBAPI, the SQL statement with placeholders and the parameters separately - no "string formatting" takes place.    A DBAPI that advertises "format" or "pyformat" then may or may not be able to handle the extended syntax.
>
> IMHO I would prefer to see the DBAPI have exactly two paramstyles, named and qmark, and have all DBAPIs support both consistently.    The (py)format styles continuously introduce the mixing of Python's string formatting behavior with the presentation of bound parameters, which are two completely different things.

+1

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jul 18 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

M.-A. Lemburg
In reply to this post by Michael Bayer
Michael Bayer wrote:

>
> On Jul 17, 2011, at 1:52 AM, Vernon Cole wrote:
>
>> 2) some major applications (e.g. django) assume %s format.
>>
>> But I am +1 for the idea anyway.
>
> But not all DBAPIs support %s format, do you mean that Django assumes %s format for those DBAPIs that are known to do so ?   Shouldn't they be at least using .paramstyle to determine that ?
>
> Is there a path to changes being made in the DBAPI?   i.e. would there be a DBAPI 3 ?  

Yes, such changes would have to go into a DB API 3.

See http://ep2011.europython.eu/conference/talks/introduction-to-python-database-programming
for some other things I'd like to see in DBAPI 3.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jul 18 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

M.-A. Lemburg
In reply to this post by Vernon D. Cole
Vernon Cole wrote:

> On Sun, Jul 17, 2011 at 8:54 AM, Michael Bayer <[hidden email]>wrote:
> >Is there a path to changes being made in the DBAPI?   i.e. would there be a
>> DBAPI 3 ?
>
>
> That possibility has been discussed before, and is particularly timely given
> that it is impossible to write a PEP 249 compliant module in Python 3.  [For
> example, the PEP states that an Error exception "must be a subclass of the
> Python StandardError" -- which Python 3 does not support.]
>
> Marc-André Lemburg (the author of PEP 249) came out against an update --
> mostly due to performance reasons.

Not sure, where you read that :-)

I'm not opposed to a DB API 3, but since there are only a few
things on the table for DB API 3 and the adoption of Python 3.x
has just started, so it's not all that urgent.

> This is understandable, because his
> company produces a very efficient db api module.  The features which have
> been suggested for a possible DBAPI 3.0 would add some significant overhead
> in some situations. The worry is real. Compare the mxDateTime module (also
> from Mark-Andre's company) with the (newer) standard datetime module, and
> you will see that datetime is a slow dog. (I use it anyway.)
>
> On the other hand, GVR is in favor of an update. When MAL suggested that
> some ease-of-use improvements (such as named columns) would make
> implementation of a module more difficult, The BDFL replied that we should
> make things easier for the user, not the module author. [ I am relying on my
> memory -- my copy of the exchange got deleted and I am too lazy to look it
> up in the archive. ;) ]
>
> So Yes, there are a TON of new language constructs which could be addressed
> in an updated API version.  The use of a cursor as an iterator is supported
> by most DB API modules, but not mentioned in PEP 249.

Please have a look again.

Note that we have added quite a few (optional) extensions to the
DB API 2 in recent years, so even though the version number and
the base set of required features hasn't changed, there are many
feature already addresses in the DB API.

> A cursor and/or a
> connection should probably be context managers, so that they will work in a
> "with" statement.

This is was discussed before and it should probably go into
the DB API in some form.

I'm no particular fan of hiding transactions in context
managers, though. In practice this often causes problems, since
you usually want to apply error handling logic in case of
problems, other than simply issuing a .rollback().

Another problem is that the connections used in a with
statement will usually have already started a transaction
before entering the context, so the .rollback() would
remove more than just the things added in the with
context.

There's also the problem of intuitive use: some users will
likely expect the connection to also be closed when leaving
the context - much like what happens with files.

The case for having cursors automatically closed by
is not problematic

> There should be a better definition of how BINARY fields
> work with byte() data.

Right.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jul 18 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

James Henstridge
On Mon, Jul 18, 2011 at 3:52 PM, M.-A. Lemburg <[hidden email]> wrote:

> Vernon Cole wrote:
>> A cursor and/or a
>> connection should probably be context managers, so that they will work in a
>> "with" statement.
>
> This is was discussed before and it should probably go into
> the DB API in some form.
>
> I'm no particular fan of hiding transactions in context
> managers, though. In practice this often causes problems, since
> you usually want to apply error handling logic in case of
> problems, other than simply issuing a .rollback().

For most of the applications I work with, the transaction handling has
been delegated to function decorators, or have it hidden in the
framework (e.g. Django's TransactionMiddleware).  If there are clean
up tasks that need to happen on transaction commit or roll back (e.g.
deleting a file on a failed transaction), then using a global
transaction manager like Zope's transaction module seems to be a good
fit.

For transaction retry or error reporting, I haven't seen much benefit
in leaving the transaction in an "open by broken" state over cleaning
up with a rollback.

> Another problem is that the connections used in a with
> statement will usually have already started a transaction
> before entering the context, so the .rollback() would
> remove more than just the things added in the with
> context.

None of the databases I've worked with fit the Python DB API's
"implicit begin" behaviour for transactions, so their adapters have
all needed to run a simple state machine to determine when it is
necessary to start a transaction.  So it would be pretty easy for them
to raise an error if an attempt was made to use the context manager
when a transaction was already in progress.

If all adapters that support transactions function like that, then I
think it would be sensible to require them to raise an error in that
case.  If they don't raise an error, you know that someone somewhere
is going to write code like the following:

    with transaction:
        # do stuff
        with transaction:
            # do more stuff

... and wonder why things break.


> There's also the problem of intuitive use: some users will
> likely expect the connection to also be closed when leaving
> the context - much like what happens with files.

If that is a concern, you could require a method call to get the
context manager rather than making the connection itself a context
manager.  For example:

    with connection.begin_transaction():
        ...


> The case for having cursors automatically closed by
> is not problematic

I don't think it makes sense to make cursors behave as context
managers for transaction handling.  Transactions are a connection
level concept affecting all the cursors for the connection, so this
would just confuse matters and lead to coding errors.


James.
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

Federico Di Gregorio-5
On 18/07/11 11:25, James Henstridge wrote:

> If all adapters that support transactions function like that, then I
> think it would be sensible to require them to raise an error in that
> case.  If they don't raise an error, you know that someone somewhere
> is going to write code like the following:
>
>     with transaction:
>         # do stuff
>         with transaction:
>             # do more stuff
>
> ... and wonder why things break.

This can make sense for backends that support check points
(subtransactions). PostgreSQL is an example (even if we don't yet
support checkpoints in psycopg).

federico

--
Federico Di Gregorio                         [hidden email]
Studio Associato Di Nunzio e Di Gregorio                  http://dndg.it
             Quis custodiet ipsos custodes? -- Juvenal, Satires, VI, 347
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

M.-A. Lemburg
In reply to this post by James Henstridge
James Henstridge wrote:
> On Mon, Jul 18, 2011 at 3:52 PM, M.-A. Lemburg <[hidden email]> wrote:
>> The case for having cursors automatically closed by
>> is not problematic
>
> I don't think it makes sense to make cursors behave as context
> managers for transaction handling.  Transactions are a connection
> level concept affecting all the cursors for the connection, so this
> would just confuse matters and lead to coding errors.

Just to clarify:

The idea is to open a cursor and have the context manager close
it automatically. This doesn't have anything to do with the
transaction on the connection.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jul 18 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

Christoph Zwerschke
In reply to this post by M.-A. Lemburg
Am 18.07.2011 09:33 schrieb M.-A. Lemburg:
 > Not really. The DB-API 2.0 allows for a certain number of binding
 > parameter formats, but you shouldn't really invent more.

My main problem was that the specs do not even properly explain what
these formats are. E.g. they say 'format' means ANSI C printf format
codes, and ANSI C means more than just '%s'. So when writing a driver,
do I need to support '%f' or not? A new version of the PEP should be
more specific here. I agree wth you it makes sense to reduce the number
of formats instead of adding more.

Am 18.07.2011 09:52 schrieb M.-A. Lemburg:
 > I'm not opposed to a DB API 3, but since there are only a few
 > things on the table for DB API 3 and the adoption of Python 3.x
 > has just started, so it's not all that urgent.

But you can also see it the other way round. Adoption of Python 3.x is
so slow because important standards like DB-API or WSGI have not been
rewritten early enough so nobody knows how to properly implement these.
Therefore if we want to promote Py 3 it is urgent to do something.

 > Note that we have added quite a few (optional) extensions to the
 > DB API 2 in recent years, so even though the version number and
 > the base set of required features hasn't changed, there are many
 > feature already addresses in the DB API.

But if these are optional, you have the same problem as with the many
parameter formats. People must catch the warning saying the extension is
not supported and must write code for both case. At least some of the
extensions like iterator support should become mandatory in the next
version of the specs.

-- Christoph
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

M.-A. Lemburg
Christoph Zwerschke wrote:
> Am 18.07.2011 09:33 schrieb M.-A. Lemburg:
>> Not really. The DB-API 2.0 allows for a certain number of binding
>> parameter formats, but you shouldn't really invent more.
>
> My main problem was that the specs do not even properly explain what
> these formats are. E.g. they say 'format' means ANSI C printf format
> codes, and ANSI C means more than just '%s'.

True. Some modules are also using %i and %f to bind
integers and floats.

> So when writing a driver,
> do I need to support '%f' or not?

The question is not whether you need to support them. You can
choose among the available formats. You don't need to support
all of them. One will do just fine.

However, it's good practice to look around a bit at what other
modules are using.

> A new version of the PEP should be
> more specific here. I agree wth you it makes sense to reduce the number
> of formats instead of adding more.
>
> Am 18.07.2011 09:52 schrieb M.-A. Lemburg:
>> I'm not opposed to a DB API 3, but since there are only a few
>> things on the table for DB API 3 and the adoption of Python 3.x
>> has just started, so it's not all that urgent.
>
> But you can also see it the other way round. Adoption of Python 3.x is
> so slow because important standards like DB-API or WSGI have not been
> rewritten early enough so nobody knows how to properly implement these.
> Therefore if we want to promote Py 3 it is urgent to do something.

There are not a lot of Python 3 changes necessary and most of
the ones that are (like the exception base class StandardError
which then reads Exception) are obvious fixes.

>> Note that we have added quite a few (optional) extensions to the
>> DB API 2 in recent years, so even though the version number and
>> the base set of required features hasn't changed, there are many
>> feature already addresses in the DB API.
>
> But if these are optional, you have the same problem as with the many
> parameter formats. People must catch the warning saying the extension is
> not supported and must write code for both case. At least some of the
> extensions like iterator support should become mandatory in the next
> version of the specs.

That will be up for discussion, yes.

We will likely make extensions that are easy to implement for
a lot of database backends, mandatory in DB API 3.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jul 18 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

Christoph Zwerschke
Am 18.07.2011 13:20 schrieb M.-A. Lemburg:
 > True. Some modules are also using %i and %f to bind
 > integers and floats.
 >
 >> So when writing a driver,
 >> do I need to support '%f' or not?
 >
 > The question is not whether you need to support them. You can
 > choose among the available formats. You don't need to support
 > all of them. One will do just fine.

Sorry, I was unclear. I meant *if* my driver advertizes itself with
paramstyle='format', does it need to support '%f' or does it suffice to
support '%s'? Same for 'pyformat'? From your answer above, it would need
to support '%f' as well. And does it also need to support flags, width,
precision, length modifiers such as '%.2f'?

My concrete problem was that I wanted to support Infinity and NaN where
Postgres has a different spelling from Python and needs quotes. This is
more difficult to implement if '%f' is allowed.

 > There are not a lot of Python 3 changes necessary and most of
 > the ones that are (like the exception base class StandardError
 > which then reads Exception) are obvious fixes.

But as Michael already pointed out, at least the unicode related issues
are not so obvious. That was also the reason why WSGI needed a new PEP.

-- Christoph
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

Federico Di Gregorio
On 18/07/11 14:25, Christoph Zwerschke wrote:

> Am 18.07.2011 13:20 schrieb M.-A. Lemburg:
>> True. Some modules are also using %i and %f to bind
>> integers and floats.
>>
>>> So when writing a driver,
>>> do I need to support '%f' or not?
>>
>> The question is not whether you need to support them. You can
>> choose among the available formats. You don't need to support
>> all of them. One will do just fine.
>
> Sorry, I was unclear. I meant *if* my driver advertizes itself with
> paramstyle='format', does it need to support '%f' or does it suffice to
> support '%s'? Same for 'pyformat'? From your answer above, it would need
> to support '%f' as well. And does it also need to support flags, width,
> precision, length modifiers such as '%.2f'?
>
> My concrete problem was that I wanted to support Infinity and NaN where
> Postgres has a different spelling from Python and needs quotes. This is
> more difficult to implement if '%f' is allowed.
IMO, nothing except %s is allowed. Letting the user choose a format it
is very much like letting the user do the escaping: Bad Thing (TM).

federico

--
Federico Di Gregorio                                       [hidden email]
  Qu'est ce que la folie? Juste un sentiment de liberté si
   fort qu'on en oublie ce qui nous rattache au monde... -- J. de Loctra


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig

signature.asc (270 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

M.-A. Lemburg
In reply to this post by Christoph Zwerschke
Christoph Zwerschke wrote:

> Am 18.07.2011 13:20 schrieb M.-A. Lemburg:
>> True. Some modules are also using %i and %f to bind
>> integers and floats.
>>
>>> So when writing a driver,
>>> do I need to support '%f' or not?
>>
>> The question is not whether you need to support them. You can
>> choose among the available formats. You don't need to support
>> all of them. One will do just fine.
>
> Sorry, I was unclear. I meant *if* my driver advertizes itself with
> paramstyle='format', does it need to support '%f' or does it suffice to
> support '%s'? Same for 'pyformat'? From your answer above, it would need
> to support '%f' as well. And does it also need to support flags, width,
> precision, length modifiers such as '%.2f'?
>
> My concrete problem was that I wanted to support Infinity and NaN where
> Postgres has a different spelling from Python and needs quotes. This is
> more difficult to implement if '%f' is allowed.

The paramstyle only advertises the supported formatting style.

With "format" the supported formatting variants are module
dependent, so it's up to you what to support.

>> There are not a lot of Python 3 changes necessary and most of
>> the ones that are (like the exception base class StandardError
>> which then reads Exception) are obvious fixes.
>
> But as Michael already pointed out, at least the unicode related issues
> are not so obvious. That was also the reason why WSGI needed a new PEP.

True, the DB-API should provide some guidance here as well.
Note that the DB-API spec will have to be valid for Python 2
and 3, so special care has to be taken.

There are two things to consider:

1. parameters
2. data input and output

For parameters, I think the best way to handle this is by allowing
both bytes and Unicode for parameters. Perhaps we can add a
module global which then signals the supported variants.

For data input/output, the situation is difficult, since not
all backends support Unicode, some only configuration dependent
character sets and some others are basically ASCII/binary data
only.

We will likely have to introduce a new TEXT() constructor
which maps data objects to text data and then takes care
of the database specific encoding issues.

There are similar issues with float/decimal and date/time
values.

It would be great if we could resolve all of these using
a data type mapping facility that defines input and output
mappings in an efficient and flexible way.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jul 18 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Some obscurity with paramstyle

Federico Di Gregorio-5
On 18/07/11 14:54, M.-A. Lemburg wrote:
[snip]

> We will likely have to introduce a new TEXT() constructor
> which maps data objects to text data and then takes care
> of the database specific encoding issues.
>
> There are similar issues with float/decimal and date/time
> values.
>
> It would be great if we could resolve all of these using
> a data type mapping facility that defines input and output
> mappings in an efficient and flexible way.

At least 2 drivers (psycopg and pysqlite) provide a Python->backend
mechanism based on PEP 246, "Object Adaptation". If other implementors
are interested I can write a short explanation about how it works and
why it was chosen only for the Python->backend path and not for the reverse.

federico

--
Federico Di Gregorio                         [hidden email]
Studio Associato Di Nunzio e Di Gregorio                  http://dndg.it
       Se sai che hai un ***** di file così, lo manovri subito. -- vodka
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
12
Loading...