sqlrelay-discussion

[Sqlrelay-discussion] Python DB API inconsistencies

[Axel H. <axe...@gm...>]

Hi everyone,

I've recently set up sql relay to use for connection pooling and read/write
routing for two mysql databases.  I have a web application in python that
used to use MySQLdb for cursors and to connect.  I switched my code over to
use PySQLRDB, which also implements the python DB api.  PySQLRDB is acting
unexpectedly though.  I had to add a call to CSQLRelay by hand to get it to
return None values for NULL fields (which still doesn't work perfectly), and
fetchone() would return a false row with 0s when nothing was returned from a
SELECT (even though fetchall would return an empty array).  My biggest
problem though is getting unicode support, which MySQLdb cursors did very
well.  I've modified PySQLRDB's getRow to check if any of the columns are
strings and then convert them to unicode, but I'll have to be a bit more
sophisticated to deal with the actual column type (for example, binary data
should not be converted to ascii).

My question is, has anyone else dealt with these kinds of problems and is
the approach I'm taking (modifying PySQLRDB to fix/change its behavior) the
best solution?  I keep finding new kinds of problems where the difference in
behavior of the cursor introduces a new bug, and I don't want to keep
patching those with little hacks to the cursor.

Thanks!

Re: [Sqlrelay-discussion] Python DB API inconsistencies

[David M. <dav...@fi...>]

It's possible that there are quite a few odd bugs like that in the 
PySQLRDB module.  It was really just written to get SQLRelay working 
with Zope.  It was written in 1999 or so.  I maintain it, but other than 
update it to use CSQLRelay directly rather than PySQLRClient, I haven't 
done much work on it, so I'm not surprised there are unicode issues.  I 
do test it with Zope and in standalone mode before each release, but I 
haven't done much testing to verify that it acts the same as other 
Python DB modules.  There could be some quirks.

There is a getNullsAsNone function in the CSQLRelay API already though, 
I'm surprised you had to add one.  I would appreciate any patches you 
have for the DB module though.  I'd be happy to incorporate them into 
CVS, assuming they don't break Zope compatibility.

David Muse
dav...@fi...

On 11/12/2010 05:23 PM, Axel Hansen wrote:
> Hi everyone,
>
> I've recently set up sql relay to use for connection pooling and 
> read/write routing for two mysql databases.  I have a web application 
> in python that used to use MySQLdb for cursors and to connect.  I 
> switched my code over to use PySQLRDB, which also implements the 
> python DB api.  PySQLRDB is acting unexpectedly though.  I had to add 
> a call to CSQLRelay by hand to get it to return None values for NULL 
> fields (which still doesn't work perfectly), and fetchone() would 
> return a false row with 0s when nothing was returned from a SELECT 
> (even though fetchall would return an empty array).  My biggest 
> problem though is getting unicode support, which MySQLdb cursors did 
> very well.  I've modified PySQLRDB's getRow to check if any of the 
> columns are strings and then convert them to unicode, but I'll have to 
> be a bit more sophisticated to deal with the actual column type (for 
> example, binary data should not be converted to ascii).
>
> My question is, has anyone else dealt with these kinds of problems and 
> is the approach I'm taking (modifying PySQLRDB to fix/change its 
> behavior) the best solution?  I keep finding new kinds of problems 
> where the difference in behavior of the cursor introduces a new bug, 
> and I don't want to keep patching those with little hacks to the cursor.
>
> Thanks!
>
>
> ------------------------------------------------------------------------------
> Centralized Desktop Delivery: Dell and VMware Reference Architecture
> Simplifying enterprise desktop deployment and management using
> Dell EqualLogic storage and VMware View: A highly scalable, end-to-end
> client virtualization framework. Read more!
> http://p.sf.net/sfu/dell-eql-dev2dev
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com



_______________________________________________________
Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
              http://www.doteasy.com 

Re: [Sqlrelay-discussion] Python DB API inconsistencies

[Cameron L. <cla...@gm...>]

On Fri, Nov 12, 2010 at 4:23 PM, Axel Hansen <axe...@gm...>wrote:

> Hi everyone,
>
> I've recently set up sql relay to use for connection pooling and read/write
> routing for two mysql databases.  I have a web application in python that
> used to use MySQLdb for cursors and to connect.  I switched my code over to
> use PySQLRDB, which also implements the python DB api.  PySQLRDB is acting
> unexpectedly though.  I had to add a call to CSQLRelay by hand to get it to
> return None values for NULL fields (which still doesn't work perfectly), and
> fetchone() would return a false row with 0s when nothing was returned from a
> SELECT (even though fetchall would return an empty array).  My biggest
> problem though is getting unicode support, which MySQLdb cursors did very
> well.  I've modified PySQLRDB's getRow to check if any of the columns are
> strings and then convert them to unicode, but I'll have to be a bit more
> sophisticated to deal with the actual column type (for example, binary data
> should not be converted to ascii).
>
> My question is, has anyone else dealt with these kinds of problems and is
> the approach I'm taking (modifying PySQLRDB to fix/change its behavior) the
> best solution?  I keep
>
...

I've also seen inconsistencies with the Zope Adapter in regard to empty
result-sets.  I've had bigger problems to solve, and so haven't tracked down
my empty-vs-None symptoms.  My best guess, though, is that at least some of
them remain in the latest sources.
-- 
Cameron Laird
Phaseit, Inc.
+1 281 648 9889