sqlobject-discuss — All things SQLObject-related

[SQLObject] .get(int) vs. .get(string)

[Hendrik M. <he...@ma...>]

Hello everyone,

---cut---
>>> from Crop.Data.User import User
>>> u1 = User.get(1)
>>> u2 = User.get('1')
>>> u1 == u2
False
---cut---

Is this by design? I was expecting SQLObject to either return the same
object instance *or* raise an exception when passing the string.

Background: I'm currently using SQLObject with CherryPy (a Python web
application framework, www.cherrypy.org). CherryPy receives GET/POST
parameters as strings, so when I want to use them to fetch an SQLObject, I
have to convert them to ints first:

  u = User.get(int(id))

It's not much of a problem, but it's really hard to miss, since SQLObject
simply returns a different object representing the same data.

The User class is a simple SQLObject derived class with no "funky"
features.

Currently using SQLObject from SVN, revision 79.

Thanks,
Hendrik

-- 
http://blog.mans.de

Re: [SQLObject] Requesting WIKI on website for SQLObject

[David M. <da...@re...>]

Ian Bicking wrote:
> On Mar 25, 2004, at 6:54 PM, David McNab wrote:
> 
>> How would the SQLObject project admins feel about putting up a wiki on 
>> the sqlobject.sf.net website?
> Sure, that'd be great.  Do you have any interest in setting it up?  I 
> can give you the access as well.

Guess this makes sense, since I'm building the pyweb.gui framework which 
makes extensive use of SQLObject, so I guess I'll be part of the 
SQLObject 'furniture' for some time to come.

I exist on sourceforge as 'davidmcnab'.

What I'd have in mind is installing the MoinMoin wiki system into the 
SQLObject htdocs dir (actually, 
/home/groups/s/sq/sqlobject/htdocs/wiki), and populating it with a few 
basic headings, and linking to it from the main SQLObject home page.

Would you be happy with this?

Cheers
David


> 
>> This IMO would be an invaluable resource not only for people to add 
>> tips'n'tricks, but also as a better place for various snippets of info 
>> don't appear in the official docs.
>>
>> For example - the doco briefly mentions .addJoin() for dynamically 
>> creating joins, but it doesn't mention the fact that if you create 
>> RelatedJoin()s, you have to call <tableobj>.createJoinTables() 
>> afterwards (or else the join tables won't exist).
> 
> 
> I'd also be happy to give you access so you can update the docs as 
> well.  Though there's still stuff that is best in a Wiki environment 
> (recipes, for instance).
> 
>> Thankfully the SQLObject source is reasonably clean and well 
>> commented, but it should still be possible to enjoy all the advertised 
>> functionality of a piece of software without referring to docs.
> 
> 
> -- 
> Ian Bicking | ia...@co... | http://blog.ianbicking.org
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 

-- 

Kind regards
David

--

leave this line intact so your email gets through my junk mail filter

Re: [SQLObject] Requesting WIKI on website for SQLObject

[Ian B. <ia...@co...>]

On Mar 25, 2004, at 6:54 PM, David McNab wrote:
> How would the SQLObject project admins feel about putting up a wiki on 
> the sqlobject.sf.net website?

Sure, that'd be great.  Do you have any interest in setting it up?  I 
can give you the access as well.

> This IMO would be an invaluable resource not only for people to add 
> tips'n'tricks, but also as a better place for various snippets of info 
> don't appear in the official docs.
>
> For example - the doco briefly mentions .addJoin() for dynamically 
> creating joins, but it doesn't mention the fact that if you create 
> RelatedJoin()s, you have to call <tableobj>.createJoinTables() 
> afterwards (or else the join tables won't exist).

I'd also be happy to give you access so you can update the docs as 
well.  Though there's still stuff that is best in a Wiki environment 
(recipes, for instance).

> Thankfully the SQLObject source is reasonably clean and well 
> commented, but it should still be possible to enjoy all the advertised 
> functionality of a piece of software without referring to docs.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

[SQLObject] Requesting WIKI on website for SQLObject

[David M. <da...@re...>]

Hi,

How would the SQLObject project admins feel about putting up a wiki on 
the sqlobject.sf.net website?

This IMO would be an invaluable resource not only for people to add 
tips'n'tricks, but also as a better place for various snippets of info 
don't appear in the official docs.

For example - the doco briefly mentions .addJoin() for dynamically 
creating joins, but it doesn't mention the fact that if you create 
RelatedJoin()s, you have to call <tableobj>.createJoinTables() 
afterwards (or else the join tables won't exist).

Thankfully the SQLObject source is reasonably clean and well commented, 
but it should still be possible to enjoy all the advertised 
functionality of a piece of software without referring to docs.

Thoughts?

Cheers
David

Re: [SQLObject] mapping DB to legacy python objects

[Ian B. <ia...@co...>]

On Mar 25, 2004, at 2:18 AM, Charles Brandt wrote:
>> You might still hit that assertion error, though I'm not quite sure 
>> why
>> -- maybe the leading underscores.  You could probably disable that
>> assertion if that's the case.  SQLObject should be a little smarter
>> about that, and probably put quotes of some sort around the name if it
>> isn't SQL-safe.
>
> Thanks for the responses.  I agree that the leading underscores are 
> causing
> the problems in this case.
>
> I checked in SQLBuilder's sqlIdentifier and found that the following 
> re was
> causing the Assertion:
> safeSQLRE = re.compile(r'^[a-zA-Z][a-zA-Z0-9_\.]*$')
>
> I changed that to
> safeSQLRE = re.compile(r'^[a-zA-Z0-9_\.]*$')
>
> Which worked, but gave me some pretty strange column names:
>  1/Query   :  CREATE TABLE sticky_sessions (
>     id INT PRIMARY KEY AUTO_INCREMENT,
>     _sticky_sessions__remote_address VARCHAR(32),
>     sid VARCHAR(255) NOT NULL UNIQUE,
>     user_id INT,
>     _sticky_sessions__access_time DATETIME,
>     _sticky_sessions__creation_time DATETIME
> )

The general rule if you get weird column names is to use the colName 
keyword argument.  The code that generates the names is in Style.py, I 
believe.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [sqlobject] [SQLObject] Table Joining to Itself

[Victor Ng <vn...@sy...>]

Similiarly - if you wanted to have Many to Many relationships, you can 
use:

parents = RelatedJoin('prodcats',
                         addRemoveName='Parent',
                         intermediateTable='prodcats_parent_child',
                         otherColumn = 'parent_id',
                         joinColumn='child_id')

children = RelatedJoin('prodcats',
                         addRemoveName='Child',
                         intermediateTable='prodcats_parent_child',
                         otherColumn= 'child_id',
                         joinColumn='parent_id')

Just remember to use ifNotExists when you create the table - since you 
run the risk of creating the intermediateTable twice.

prodcats.createTable(ifNotExists = True)

vic

On Mar 24, 2004, at 9:20 AM, David McNab wrote:

> Hi,
>
> I'm posting here in the hope that my problem, and its solution, might 
> save others from the battle I've had.
>
> Basically, I'm implementing a web shopping cart. While I was tempted 
> to have just a plain 2 or 3 level product hierarchy, this felt too 
> restrictive and I wanted a more general solution - the ability to have 
> an unrestricted n-level hierarchy of product categories.
>
> After some experimenting and reading SQLObject source, the following 
> solution came out:
>
>   class prodcats(SQLObject):
>       _connection = _conn
>       name=StringCol()
>       longname=StringCol()
>       parent=ForeignKey('prodcats', default=None)
>       children=MultipleJoin('prodcats', joinColumn='parent_id')
>
>   prodcats.createTable()
>
>   rPets = prodcats.new(
>     name='pets', longname='Pets')
>
>   rBirds = prodcats.new(
>     name='birds', longname='Birds', parent=rPets)
>
>   rLargeBirds = prodcats.new(
>     name='large', longname='Large Birds', parent=rBirds)
>
>   rSmallBirds = prodcats.new(
>     name='small', longname='Small Birds', parent=rBirds)
>
>   rDogs = prodcats.new(
>     name='dogs', longname='Dogs', parent=rPets)
>
>   rLargeDogs = prodcats.new(
>     name='large', longname='Large Dogs', parent=rDogs)
>
>   rSmallDogs = prodcats.new(
>     name='small', longname='Small Dogs', parent=rDogs)
>
> -- 
>
> Kind regards
> David
>
> --
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
>
---
"We are what we repeatedly do. Excellence then, is not an act. It is a 
habit."  - Aristotle

[SQLObject] example persistent dict/mapping object using SQLObject

[Charles B. <li...@st...>]

Not sure if anyone else would have a use for this, but I needed a persistent
mapping interface to substitute in existing python code (non-pickle).   I
imagine there might be a more elegant way to do this (maybe using
meta-classes?), and I'd love to hear any feedback on it.

-Charles

----------------------------> code below
<--------------------------------------
from SQLObject import *

class StickyMap (dict):
#StickyDict seemed inappropriate... ;)
    """
    This object will map a (simple) dictionary to SQLObjects.

    Both keys and values will be strings (or values configured in items)

    """
    def __init__(self, connection):
        # could pass connection in here in order to use on different threads
        self.conn = connection

        # maybe use dict for caching already initialized sqlobjects?
        # will not want this if more than one thread is updating the
database?
        # (get a fresh copy every time then)
        self._dict = {}

    def get (self, key, default=None):
        items = StickyMapItem.select(StickyMapItem.q.dkey == key,
                                      connection=self.conn)
        if items.count():
            val = items[0].value
        else:
            val = default
        return val

    def __getitem__(self, key):
        item = StickyMapItem.byDkey(key, connection=self.conn)
        return item.dvalue  # if no dvalue, exception raised

    def __setitem__(self, key, value):
        dict = {}
        items = StickyMapItem.select(StickyMapItem.q.dkey == key,
                                      connection=self.conn)
        if items.count():
            dict[key] = items[0]
            dict[key].set(dvalue=value)
        else:
            dict[key] = StickyMapItem.new(dkey=key, dvalue=value,
                                                 _connection=self.conn)

    def __delitem__(self, key):
        StickyMapItem.byDkey(key, connection=self.conn).destroySelf()

    def __len__(self):
        return StickyMapItem.select(connection=self.conn).count()

    def has_key (self, key):
        items = StickyMapItem.select(StickyMapItem.q.dkey == key,
                                      connection=self.conn)
        if items.count():
            return True
        else:
            return False

    ### these are implemented to be consistent with the mapping object
    ### interface, but may be resource intensive on large tables?
    def _get_all(self):
        dict = {}
        items =  StickyMapItem.select(connection=self.conn)
        for item in items:
            dict[item.dkey] = item.dvalue
        return dict

    def values(self):
        dict = self._get_all()
        return dict.values()

    def items(self):
        dict = self._get_all()
        return dict.items()

    def keys(self):
        dict = self._get_all()
        return dict.keys()


class StickyMapItem(SQLObject):
    """
    table to store persistent dictionary items

    the only catch is that the table will be named by this class
    seems to indicate the need for a meta class implementation
    (something to replace the customized item class in the StickyMap logic)

    NOTE:
    "key" is a sql keyword that doesn't get quoted properly in dbconnection
    """
    # for now just using a string
    # eventually a join sqlobject type to another object
    #
    dvalue = StringCol(alternateID=True, unique=True, length=255)

    dkey = StringCol(alternateID=True, unique=True, length=255)

Re: [SQLObject] mapping DB to legacy python objects

[Charles B. <li...@st...>]

> You might still hit that assertion error, though I'm not quite sure why
> -- maybe the leading underscores.  You could probably disable that
> assertion if that's the case.  SQLObject should be a little smarter
> about that, and probably put quotes of some sort around the name if it
> isn't SQL-safe.

Thanks for the responses.  I agree that the leading underscores are causing
the problems in this case.

I checked in SQLBuilder's sqlIdentifier and found that the following re was
causing the Assertion:
safeSQLRE = re.compile(r'^[a-zA-Z][a-zA-Z0-9_\.]*$')

I changed that to
safeSQLRE = re.compile(r'^[a-zA-Z0-9_\.]*$')

Which worked, but gave me some pretty strange column names:
 1/Query   :  CREATE TABLE sticky_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    _sticky_sessions__remote_address VARCHAR(32),
    sid VARCHAR(255) NOT NULL UNIQUE,
    user_id INT,
    _sticky_sessions__access_time DATETIME,
    _sticky_sessions__creation_time DATETIME
)

I imagine this has something to do with the way the newClass generates the
names, but I couldn't quite follow how that is done.  Maybe once I progress
in my familiarity w/ the code and metaclasses in general. :)

For now I think I'll just change the project code I'm trying to adapt to and
deal w/ consequences later.  Thanks again!

-Charles.

Re: [SQLObject] mapping DB to legacy python objects

[Ian B. <ia...@co...>]

On Mar 24, 2004, at 4:31 PM, Charles Brandt wrote:
> I'm in the process of trying to add persistence using SQLObject to some
> python objects in an existing project.  I'm hitting a snag since the 
> object
> has (private-ish) variables that begin with "__".  When I try simply
> creating corresponding columns in the database with SQLObject, ie:
> __remote_address = STringCol(length=32)
> __creation_time = DateTimeCol(default=SQLBuilder.func.NOW())
>
> I get:
> AssertionError: Name must be SQL-safe (letters, numbers, underscores):
> '_StickySessions__remote_address'
>
> I was hoping to avoid renaming them, since that would mean over riding 
> all
> methods that access that data too.  Seemed rather brittle, but is 
> looking
> like the best option at this point.
>
> I also tried to just create an alias pointing to the variable like 
> this:
> remote_address = STringCol(length=32)
> __remote_address = remote_address
> but that didn't work either.

No, that definitely won't work.  SQLObject uses the attribute name as a 
heuristic to determine the database column name, when you don't give a 
column name explicitly.  So renaming the attribute will only confuse 
things (and I think actually make SQLObject expect two columns in that 
case.  You can rename the column explicitly with the colName keyword 
argument to *Col, I think -- this is specifically meant to keep you 
from having to propagate bad legacy names.  Or if your attributes are 
the legacy names, you can keep those from propagating to your database.

You might still hit that assertion error, though I'm not quite sure why 
-- maybe the leading underscores.  You could probably disable that 
assertion if that's the case.  SQLObject should be a little smarter 
about that, and probably put quotes of some sort around the name if it 
isn't SQL-safe.

   Ian

Re: [SQLObject] mapping DB to legacy python objects

[David M. <da...@re...>]

Something I'm doing that works is to not try to save the SQLOBject 
object in the persistables (doesn't really make sense to me to try to 
persist something which itself facilitates persistence), but only save 
whatever is required to reconstruct the SQLObject object - for instance, 
the database name, username and password, in the case of {My|Postgre}SQL 
backends.

Yes, during each run cycle it's handy to have the odd SQLObject object 
ref lurking around within your persistables, but it's also easy to del 
those refs immediately prior to pickling, and reconstruct the SQLObject 
object and set it as an attribute in the persistables immediately after 
unpickling.

Cheers
David


Charles Brandt wrote:
> Hi,
> 
> I'm in the process of trying to add persistence using SQLObject to some
> python objects in an existing project.  I'm hitting a snag since the object
> has (private-ish) variables that begin with "__".  When I try simply
> creating corresponding columns in the database with SQLObject, ie:
> __remote_address = STringCol(length=32)
> __creation_time = DateTimeCol(default=SQLBuilder.func.NOW())
> 
> I get:
> AssertionError: Name must be SQL-safe (letters, numbers, underscores):
> '_StickySessions__remote_address'
> 
> I was hoping to avoid renaming them, since that would mean over riding all
> methods that access that data too.  Seemed rather brittle, but is looking
> like the best option at this point.
> 
> I also tried to just create an alias pointing to the variable like this:
> remote_address = STringCol(length=32)
> __remote_address = remote_address
> but that didn't work either.
> 
> Any other ideas on how to deal with this scenario?  Thanks in advance.
> 
> -Charles.
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 

-- 

Kind regards
David

--

leave this line intact so your email gets through my junk mail filter

[SQLObject] mapping DB to legacy python objects

[Charles B. <li...@st...>]

Hi,

I'm in the process of trying to add persistence using SQLObject to some
python objects in an existing project.  I'm hitting a snag since the object
has (private-ish) variables that begin with "__".  When I try simply
creating corresponding columns in the database with SQLObject, ie:
__remote_address = STringCol(length=32)
__creation_time = DateTimeCol(default=SQLBuilder.func.NOW())

I get:
AssertionError: Name must be SQL-safe (letters, numbers, underscores):
'_StickySessions__remote_address'

I was hoping to avoid renaming them, since that would mean over riding all
methods that access that data too.  Seemed rather brittle, but is looking
like the best option at this point.

I also tried to just create an alias pointing to the variable like this:
remote_address = STringCol(length=32)
__remote_address = remote_address
but that didn't work either.

Any other ideas on how to deal with this scenario?  Thanks in advance.

-Charles.

[SQLObject] Table Joining to Itself

[David M. <da...@re...>]

Hi,

I'm posting here in the hope that my problem, and its solution, might 
save others from the battle I've had.

Basically, I'm implementing a web shopping cart. While I was tempted to 
have just a plain 2 or 3 level product hierarchy, this felt too 
restrictive and I wanted a more general solution - the ability to have 
an unrestricted n-level hierarchy of product categories.

After some experimenting and reading SQLObject source, the following 
solution came out:

   class prodcats(SQLObject):
       _connection = _conn
       name=StringCol()
       longname=StringCol()
       parent=ForeignKey('prodcats', default=None)
       children=MultipleJoin('prodcats', joinColumn='parent_id')

   prodcats.createTable()

   rPets = prodcats.new(
     name='pets', longname='Pets')

   rBirds = prodcats.new(
     name='birds', longname='Birds', parent=rPets)

   rLargeBirds = prodcats.new(
     name='large', longname='Large Birds', parent=rBirds)

   rSmallBirds = prodcats.new(
     name='small', longname='Small Birds', parent=rBirds)

   rDogs = prodcats.new(
     name='dogs', longname='Dogs', parent=rPets)

   rLargeDogs = prodcats.new(
     name='large', longname='Large Dogs', parent=rDogs)

   rSmallDogs = prodcats.new(
     name='small', longname='Small Dogs', parent=rDogs)

-- 

Kind regards
David

--

[SQLObject] transaction support for MySQL/InnoDB

[John A. B. <ja...@os...>]

Hi,

I've attached a patch against 0.5.2 to add transaction support for
MySQL/InnoDB.  It works for me (using Python 2.2.3 and MySQL 4.0.18),
but there may be a better approach; any feedback would be appreciated. 

One issue I see is setting supportTransactions to True in the
MySQLConnection class, when it technically depends on which table type
you're using.  Though, MyISAM accepts transaction-related statements
just fine (they're just ignored), so it shouldn't break anything.

-jab

-- 
John A. Barbuto                                 ja...@os...
Senior System Administrator,
Open Source Development Network                 http://www.osdn.com/

Re: [SQLObject] Re: Concurrency issues...

[Ian B. <ia...@co...>]

On Mar 23, 2004, at 2:31 AM, Chris Gahan wrote:
> Can anybody suggest why MySQL would think I was executing an 
> "out-of-sync"
> command? Could it be that the cursor needs to be "closed" before being
> returned to the DB? Maybe the object is getting deleted mid-iteration, 
> and a
> connection containing partially-iterated cursor is being returned to 
> the
> pool?

Have you run the problem code with debugging on?  The output should 
list connection IDs along with the commands, so you should be able to 
see if there's something out of sync.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

[SQLObject] Re: Concurrency issues...

[Chris G. <ch...@il...>]

"Chris Gahan" <ch...@il...> wrote in message
news:c3k162$stb$1...@se......
> [...] If I hammer SQLObject
> with multiple threads all doing queries simultaneously, it'll either bomb
> out by giving me an exception, or permanently lock up one of the threads,
> and sometimes the entire python interpreter.

I've been experimenting with DBConnection.py a bit, and I still can't figure
out what's causing this thread synchronization error.

The code all looks kosher:
 + take and hold a connection from the pool
 + execute a query
 + iterate through the results using the connection's cursor
 + when the "sqlresult" object gets deleted, return the connection to the
pool

I'm still randomly getting a "Commands out of sync" error in MySQL, or an
"OperationalError" exception in Postgres.  The problem still seems to be
that another process is getting a hold of a connection which contains a
cursor that hasn't finished iterating through its results.

I really need somebody to help me brainstorm causes here, because this isn't
my area of expertise. :)

Can anybody suggest why MySQL would think I was executing an "out-of-sync"
command? Could it be that the cursor needs to be "closed" before being
returned to the DB? Maybe the object is getting deleted mid-iteration, and a
connection containing partially-iterated cursor is being returned to the
pool?

Any help at all is appreciated. :)

= Chris Gahan =============
  (ch...@il...)

[SQLObject] Pickling - Thanks

[Philippe N. <ph...@re...>]

Hi,

Just few lines to thank Ian, David and Charles for their explanations. Now I
won't try to pickle SQLObjects anymore, since objects are persistents in DB.
It's useless to try to do what DBM do better :-)

Cheers,

Philippe

Re: [SQLObject] Pickling

[Ian B. <ia...@co...>]

On Mar 21, 2004, at 5:34 PM, Philippe Normand wrote:
> I'm currently having the following issue when trying to pickle 
> SQLObjects
> (0.5.2) / python 2.2:

SQLObject doesn't currently support pickling, though I believe the 
changes necessary would not be particularly difficult -- adding a 
__getinitargs__ (or something like that, the pickle docs have more) 
would probably do it.  (Adding to both SQLObject and DBConnection 
objects)

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

[SQLObject] DBconnection SQL generation error?

[charles b. <li...@st...>]

I think there might be a problem with the way DBConnection generates some
SQL queries.  Specifically, when using SQLObject's createTable function on a
table with a column named "key", mysql will throw an exception since the
generated SQL does not back quote the names of items.

CREATE TABLE table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    value VARCHAR(255) NOT NULL UNIQUE,
    key VARCHAR(255) NOT NULL UNIQUE
)

should probably be:

CREATE TABLE `table_name` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `value` VARCHAR(255) NOT NULL UNIQUE,
    `key` VARCHAR(255) NOT NULL UNIQUE
)

I haven't had a chance to investigate further, but I wanted to throw it out
there before I forgot.  Thanks!

-Charles

Re: [SQLObject] Pickling

[charles b. <li...@st...>]

Hi Philippe,

I'm pretty new to SQLObject, but I also just ran into this issue.  I was
using pickle(shelve) as a temporary way to add persistence to dict type
session data in quixote, and wanted to associate a SQLObject with the
session.

This is just a guess, but it makes sense on a conceptual level that pickle
should not allow storing data related to thread dependent objects (like
SQLObjects).  There is no guarantee that the process associated with an
object will still exist when the pickled data is unpickled (the whole point
of pickling anyway... persisting across processes).  For SQLObjects
specifically, there are database connections associated with the object.

I then realized that pickle and databases are both ways to add persistence
to data.  Therefore, whatever data you are trying to persist using pickle,
you may just want to persist using SQLObject.

Now if only I could figure out a way to easily map an existing dict type
object to a SQLObject I'd be set.  Anyone else have experience/advice on
doing that??

> PS: I'm not subscribed to the list, please reply to ph...@re...
Why not join?  Wouldn't want you to miss other responses... ;)

-Charles.

[SQLObject] Pickling

[Philippe N. <ph...@re...>]

Hi,

I'm currently having the following issue when trying to pickle SQLObjects
(0.5.2) / python 2.2:

-------------------------------------------------------------------------

from cPickle import dump, load


class SessionTest:

    def __init__(self, val):
        self._dict = {'row': val}


# get an SQLObject with id == 1
a = Alinea_getArticleWithID(1)
test = SessionTest(a)

f = open('toto','w')
dump(test,f)
f.close()
print open('toto').read()
-------------------------------------------------------------------------

    dump(test,f)
cPickle.UnpickleableError: Cannot pickle <type 'thread.lock'> objects


Has anybody succeeded pickling SQLObjects ?

Cheers,

PS: I'm not subscribed to the list, please reply to ph...@re...

--
PN

[SQLObject] Concurrency issues...

[Chris G. <ch...@il...>]

Hey there. I'm having a big problem, and I need to find away around it,
otherwise I don't think I can use SQLObject. :)

It's a problem that was brought up a couple weeks ago. If I hammer SQLObject
with multiple threads all doing queries simultaneously, it'll either bomb
out by giving me an exception, or permanently lock up one of the threads,
and sometimes the entire python interpreter.

The problem was supposedly fixed with the new Iteration() class, but I don't
think it got to the root of the problem.

This weirdness happens with Postgres and MySQL drivers (the exceptions they
throw are slightly different, but their meanings are similar). It also
happens in SQLObject 0.5.2 and 0.6 (the latest SVN).

Here are the results of a program that hammers SQLObject (source attached).
It creates a simple database of 49+1 people, then spawns a bunch of
'whacker' threads which each repeatedly select all the records and iterate
over them.

---8<---8<---8<------S---N---I---P------>8--->8--->8---
$ python sqlobject_hammer.py
Making peeps...
--------------------------------------------------
49 peeps created...

WHACKING!!!
==================================================
whacker #0 | iteration: 1 (read 49 records)
whacker #0 | iteration: 2 (read 49 records)
whacker #1 | iteration: 1 (read 49 records)
whacker #0 | iteration: 3 (read 49 records)
whacker #0 | iteration: 4 (read 49 records)
whacker #0 | iteration: 5 (read 49 records)
whacker #0 | iteration: 6 (read 49 records)
whacker #1 | iteration: 2 (read 49 records)
whacker #0 | iteration: 7 (read 49 records)
whacker #1 | iteration: 3 (read 49 records)
whacker #0 | iteration: 8 (read 49 records)
whacker #1 | iteration: 4 (read 49 records)
whacker #1 | iteration: 5 (read 49 records)
whacker #1 | iteration: 6 (read 49 records)
whacker #2 | iteration: 1 (read 49 records)
whacker #0 | iteration: 9 (read 49 records)
Unhandled exception in thread started by <function whackit at 0x40459d84>
Traceback (most recent call last):
  File "sqlobject_hammer.py", line 33, in whackit
    del peep
UnboundLocalError: local variable 'peep' referenced before assignment
Unhandled exception in thread started by <function whackit at 0x40459d84>
Traceback (most recent call last):
  File "sqlobject_hammer.py", line 29, in whackit
    for count, peep in enumerate(peeps):
  File "/usr/lib/python2.3/site-packages/sqlobject/main.py", line 1192, in
__iter__
    return conn.iterSelect(self)
  File "/usr/lib/python2.3/site-packages/sqlobject/dbconnection.py", line
199, in iterSelect
    select, keepConnection=False)
  File "/usr/lib/python2.3/site-packages/sqlobject/dbconnection.py", line
393, in __init__
    self.cursor.execute(self.query)
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in
execute
    return self._execute(query, args)
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in
_execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33,
in defaulterrorhandler
    raise errorclass, errorvalue
ValueError: invalid literal for long(): w1
whacker #2 | iteration: 2 (read 49 records)
Unhandled exception in thread started by <function whackit at 0x40459d84>
Traceback (most recent call last):
  File "sqlobject_hammer.py", line 33, in whackit
    del peep
UnboundLocalError: local variable 'peep' referenced before assignment
whacker #1 | iteration: 7 (read 49 records)
Unhandled exception in thread started by <function whackit at 0x40459d84>
Traceback (most recent call last):
  File "sqlobject_hammer.py", line 29, in whackit
    for count, peep in enumerate(peeps):
  File "/usr/lib/python2.3/site-packages/sqlobject/main.py", line 1192, in
__iter__
    return conn.iterSelect(self)
  File "/usr/lib/python2.3/site-packages/sqlobject/dbconnection.py", line
199, in iterSelect
    select, keepConnection=False)
  File "/usr/lib/python2.3/site-packages/sqlobject/dbconnection.py", line
393, in __init__
    self.cursor.execute(self.query)
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in
execute
    return self._execute(query, args)
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in
_execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33,
in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (2014, "Commands out of sync;  You can't
run this command now")
Traceback (most recent call last):
  File "sqlobject_hammer.py", line 49, in ?
    main()
  File "sqlobject_hammer.py", line 46, in main
    pass
--->8--->8--->8------S---N---I---P------8<---8<---8<---

It just locked up after that.

I'm not really sure how to start debugging this. I'm a little scared of
DBConnection.py. :)

= Chris Gahan =============
  (ch...@il...)


begin 666 sqlobject_hammer.py
M9G)O;2!S<6QO8FIE8W0@:6UP;W)T("H*7U]C;VYN96-T:6]N7U\@/2 G;7ES
M<6PZ+R]T97-T.G1E<W1 ;&]C86QH;W-T+W1E<W0G"FEM<&]R="!R86YD;VT*
M"FYU;7!E97!S(#T@-3 *;G5M=VAA8VME<G,@/2 T"@IC;&%S<R!0965P<RA3
M44Q/8FIE8W0I.@H@(&YA;64@/2!3=')I;F=#;VPH9&5F875L=#TB2&%N:S @
M3R=-86QL97DB*0H@(&%G92 ]($EN=$-O;"AD969A=6QT/3$P*0H*9&5F(&UA
M:V5P965P<R@I.@H@('!R:6YT(")-86MI;F<@<&5E<',N+BXB"B @<')I;G0@
M)RTG*C4P"B @4&5E<',N9')O<%1A8FQE*&EF17AI<W1S/51R=64I"B @4&5E
M<',N8W)E871E5&%B;&4H*0H@(&9O<B!I(&EN(')A;F=E*# L;G5M<&5E<',I
M.@H@(" @<&5E<" ](%!E97!S*&YA;64](DAA;FLE9"!/)TUA;&QE>2(E<F%N
M9&]M+G)A;F1I;G0H,2PY.3DI+ H@(" @(" @(" @(" @(" @(&%G93UR86YD
M;VTN<F%N9&EN="@Q+#4P,"DI"B @"B @<')I;G0@(B5D('!E97!S(&-R96%T
M960N+BXB("4@:0H*"F1E9B!W:&%C:VET*&ED*3H*(" @('0@/2 P"B @("!W
M:&EL92!T(#P@-3 P.@H@(" @(" @('0@*ST@,0H@(" @(" @('!E97!S(#T@
M4&5E<',N<V5L96-T*"D*(" @(" @("!F;W(@8V]U;G0L('!E97 @:6X@96YU
M;65R871E*'!E97!S*3H*(" @(" @(" @(" @<&%S<PH@(" @(" @('!R:6YT
M(")W:&%C:V5R(",E9"!\(&ET97)A=&EO;CH@)60@*')E860@)60@<F5C;W)D
M<RDB("4@*&ED+"!T+"!C;W5N="D*(" @(" @("!D96P@<&5E<',*(" @(" @
M("!D96P@<&5E< H@(" *9&5F(&UA:6XH*3H*(" @(&EM<&]R="!T:')E860*
M(" @(&UA:V5P965P<R@I"@H@(" @<')I;G0*(" @('!R:6YT(")72$%#2TE.
M1R$A(2(*(" @('!R:6YT("(](BHU, H*(" @(&9O<B!T(&EN(')A;F=E*&YU
M;7=H86-K97)S*3H*(" @(" @("!T:')E860N<W1A<G1?;F5W7W1H<F5A9"AW
M:&%C:VET+" H="PI*0H@(" @=VAI;&4@+3$Z"B @(" @(" @<&%S<PH@(" @
H"FEF(%]?;F%M95]?(#T]("=?7VUA:6Y?7R<Z"B @("!M86EN*"D*"@``
`
end

Re: [SQLObject] mysql connection losses

[Scott R. <sc...@to...>]

On Thu, 2004-03-18 at 11:34, Ian Bicking wrote: 
> That looks like the right thing to use.  Doing it in a database-specific
> way is no problem.  It just requires an additional method call in 
> getConnection (say, pingConnection), which by default is a no-op, and we 
> override it for the various databases.

+1 - yes, please.  The DBA who keeps bouncing the SQL server without the
app server is my biggest headache, right above the other DBA who keeps
changing data from under SO and not refreshing the app server...

It would be nice if there was a baked-in mechanism to turn the
connection test off if it effects performance during heavy tasks,
though. I've never benchmarked MySQL's ping, so this may be a pointless
point...

Does the java autocommit test affect performance at all?

Re: [SQLObject] mysql connection losses

[David W. <da...@su...>]

Ian,

> That seems like it might be problematic.  For instance, what if rollback 
> is a no-op or something?  It shouldn't be, but I wouldn't feel confident 
> that there's not a hidden corner case in the semantics.

Agreed. It is one of those times when the db api spec compared to the 
jdbc spec is a bit frustrating.

>> Otherwise it will need to do it in the db specific classes eg for 
>> mysql there is conn.ping() for mysql see 
>> http://www.esrf.fr/computing/bliss/python2/MySQL/MySQLdb-2.html
> 
> That looks like the right thing to use.  Doing it in a database-specific 
> way is no problem.  It just requires an additional method call in 
> getConnection (say, pingConnection), which by default is a no-op, and we 
> override it for the various databases.

I like this.

This also allows for another option in the future which is a background 
thread that slowly loops through the pool testing and refreshing the 
connections. It is more code but can help responsiveness in situations 
where you have long periods with little activity and then a burst. With 
the test only when you ask for a connection you could find that in a 
burst of activity all connections are failing the tests and so you have 
not benefited at all from the pool.

Regards

Dave
-- 
David Warnock, Sundayta Ltd. http://www.sundayta.com
iDocSys for Document Management. VisibleResults for Fundraising.
Development and Hosting of Web Applications and Sites.

Re: [SQLObject] mysql connection losses

[Ian B. <ia...@co...>]

David Warnock wrote:
> Looking at the db api spec and DBAPIConnection I wonder what would 
> happen if we called rollback again in the getConnection.
> 
> In theory there cannot be a transaction in progress as release 
> connection will have already done a commit or rollback.
> 
> So what is the effect of calling rollback, rollback on a connection? I 
> guess we might get an exception which is easy to cope with, but will it 
> do enough for us to know if the connection is valid?

That seems like it might be problematic.  For instance, what if rollback 
is a no-op or something?  It shouldn't be, but I wouldn't feel confident 
that there's not a hidden corner case in the semantics.

> Otherwise it will need to do it in the db specific classes eg for mysql 
> there is conn.ping() for mysql see 
> http://www.esrf.fr/computing/bliss/python2/MySQL/MySQLdb-2.html

That looks like the right thing to use.  Doing it in a database-specific 
way is no problem.  It just requires an additional method call in 
getConnection (say, pingConnection), which by default is a no-op, and we 
override it for the various databases.

   Ian

Re: [SQLObject] mysql connection losses

[David W. <da...@su...>]

Ian,

> It would be easy to add *if* there's an interface to do this with the 
> database driver.  If there was, DBAPIConnection.getConnection() could 
> ping the database before returning the connection.

Looking at the db api spec and DBAPIConnection I wonder what would 
happen if we called rollback again in the getConnection.

In theory there cannot be a transaction in progress as release 
connection will have already done a commit or rollback.

So what is the effect of calling rollback, rollback on a connection? I 
guess we might get an exception which is easy to cope with, but will it 
do enough for us to know if the connection is valid?

Otherwise it will need to do it in the db specific classes eg for mysql 
there is conn.ping() for mysql see 
http://www.esrf.fr/computing/bliss/python2/MySQL/MySQLdb-2.html

Regards

Dave
-- 
David Warnock, Sundayta Ltd. http://www.sundayta.com
iDocSys for Document Management. VisibleResults for Fundraising.
Development and Hosting of Web Applications and Sites.