sqlobject-discuss

[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] 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.

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

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

[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 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

[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] [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] pythonClassToDBTable and dbTableToPythonClass

[Minh L. <mi...@to...>]

The pythonClassToDBTable and dbTableToPythonClass in Style.py are written as
below:

 def pythonClassToDBTable(self, className):
        return className[0].lower() \
               + mixedToUnder(className[1:])

    def dbTableToPythonClass(self, table):
        return table[0].upper() \
               + underToMixed(table[1:])

They produces some unexpected results, for example "TOrder" is converted to
"torder" rather than "t_order".

Could this be changed to:

 def pythonClassToDBTable(self, className):
     return mixedToUnder(className)

 def dbTableToPythonClass(self, table):
      m = underToMixed(table)
      return m[0].upper() + m[1:]

Thanks