declarative versus classes mapped to multiple engines

Hi Again,

I'm wondering if the use case I have is one that is supported...

So, the situation is that I have a bunch of classes that I need to map 
to a bunch of tables, and I'd prefer to do that declaratively. The 
spicey bit is that I need to connect to several environments a lot of 
the time and not all of these tables are available in all environments.

So, my plan is to have one engine per database I connect to.
But what to do about tables?

Am I right in thinking that I should have one MetaData object per 
engine, and that MetaData object should only have the tables in it that 
are actually present in that database?

If so, what's the recommended pattern for doing that?

Now, what about declarative? Declarative seems to like each class to 
have a MetaData object, but how do I tie that in with multiple engines 
and some tables not being present in some engines?

What about the declarative registry? I guess having only one of those is 
fine since it just maps a string name to a class object, right?

cheers,

Chris

--

-- 

Versioning of Many-to-Many relationships

PDF documentation 0.5

Hi there.

I just tried to download the PDF documentation for 0.5 from the
documentation page[0], and the link seems to be broken (404).

Sorry if it has been brought to the list already, first time post
here, I checked the archive and couldn't find anything related.

[0] http://docs.sqlalchemy.org/en/rel_0_5/index.html

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

reflected declarative versus single table inheritance

Hi,

I'm hitting some issues with single table inheritance and the reflected 
declarative example interacting badly...

I'm not sure I have my head around this 100% and certainly no idea what 
to do about it, so thought I'd see what others (Mike  ) think...

IIUC, the following breaks:

class GeneralType(Reflected):
     __tablename__ = 'the_table'
     __mapper_args__ = dict(polymorphic_on='col')

class TypeOne(GeneralType):
     __mapper_args__ = dict(polymorphic_identity='one')

...firstly because we don't have a column object to pass to 
polymorphic_on (shame that and primary_key don't take a string(s)), but 
in my interpretation of the recipe I've worked around that.

However, the one that's stumped me is an InvalidRequestError saying 
'the_table' is already defined for this MetaData instance.

I believe that this because there's some dancing around using 
_is_mapped_class that gets confused when declarative reflection is used, 
because the actual mapping is deferred until later.

Any suggestions on this front?

My thinking was leaning towards actually abandoning the full declarative 
base and coming up with a light weight one (or maybe even class 
decorator) that basically added recorded the class in a sequence (much 
like the declarative reflection does) and then calls 
instrument_declarative with each class at the point of relfection.

Does that sound sane?

cheers,

Chris

-- 
Simplistix - Content Management, Batch Processing & Python Consulting
             - http://www.simplistix.co.uk

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

How do I change enum values after previously creating a table?

When creating many-to-many relationship I get: NoReferencedTableError

Hi,

I am trying to create a simple many-to-many relationship using
PostgreSQL in a specific schema ('cards'). I do it like this:

class BaseObject(object):
    id =  Column(Integer, primary_key=True)
    date_created = Column(DateTime)
    date_modified = Column(DateTime)

collection_cards = Table(
    'collection_cards', Base.metadata,
    Column('card_id', Integer, ForeignKey('cards.id')),
    Column('collection_id', Integer, ForeignKey('collections.id'))
    )

class Card(Base, BaseObject):
    __tablename__ = 'cards'

    content = Column(Text)

    def __init__(self):
        super(Card, self).__init__()

class Collection(BaseObject, Base):
    __tablename__ = 'collections'

    name = Column(String)

    def __init__(self):
        super(Collection, self).__init__()

    cards = relationship(Card, secondary=collection_cards)

...

Base.metadata.schema = "cards"
Base.metadata.create_all(engine)

and I get:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with
column 'collection_cards.card_id' could not find table 'cards' with
which to generate a foreign key to target column 'id'

Without the relationsip, the two tables are created just fine in the
specified schema. What I am doing wrong?

Thanks,

Stefan

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

using sqlalchemy with Google app engine

I'm trying to deploy an sqlalchemy+ bottle.py web app to google app
engine. I saw they also have an sql like backend so I guess sqlalchemy
could work with Gae but I'm not sure
would It support sqlalchemy "from the box"? or how should I the
application it if needed?
I know this was asked before, but the last time a couple of month ago,
there wasn't a clear answer about that

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

user defined type

Hi,

I am using some custom types which "just about" work, i.e. I just have 
an issue when I do this type of query.

result = result.filter(db.Drinkinfo.namesandvar.startswith('cloudy'))

It throws the following exception, which is due to my custom type not 
being correctly setup.

sqlalchemy.exc.ProgrammingError: (ProgrammingError) (-902, 
'isc_dsql_prepare: \n  Dynamic SQL Error\n  expression evaluation not 
supported\n  Strings cannot be added or subtracted in dialect 3') "SELECT
...
cellarbook.updated_by AS cellarbook_updated_by \nFROM cellarbook JOIN 
vintage ON vintage.id = cellarbook.fk_vintage_id JOIN drinkinfo ON 
drinkinfo.id = vintage.fk_drinkinfo_id \nWHERE vintage.avgscore BETWEEN 
? AND ? AND drinkinfo.name + ? + drinkinfo.name2 + (? || 
drinkinfo.variety) LIKE ? || '%%'" (80, 95, ', ', ', ', 'cloudy')
File "c:\dev\twcbv4\twcbsrc\test3to4\saTest.py", line 57, in <module>
   for item in result:
File 
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\orm\query.py", 
line 1839, in __iter__
   return self._execute_and_instances(context)
File 
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\orm\query.py", 
line 1854, in _execute_and_instances
   result = conn.execute(querycontext.statement, self._params)
File 
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py", 
line 1399, in execute
   params)
File 
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py", 
line 1532, in _execute_clauseelement
   compiled_sql, distilled_params
File 
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py", 
line 1640, in _execute_context
   context)
File 
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py", 
line 1633, in _execute_context
   context)
File

"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\dialects\firebird\base.py", 
line 692, in do_execute
   cursor.execute(statement, parameters or [])

The columns are defined like:

     name = sa.Column(cts.NAMES40(), default=u'', index=True)
     name2 = sa.Column(cts.NAMES40(), default=u'', index=True)
     variety = sa.Column(sa.Unicode(length=40), default=u'')

     @hybrid_property
     def namesandvar(self):
         nVar = self.name
         if self.name2:
             nVar += ', ' + self.name2
         if self.variety:
             nVar += ', ' + self.variety
         return nVar

I must be doing something wrong when defining my "NAMES40" custom type 
as when I change it to use Unicode the query works fine.

I used custom types but maybe there are even easier/better ways to do 
what I like to do (standard length and collation, would be nice to also 
have index=True), anyhow this is how I define it.

In Firebird SQL I define a domain:

CREATE DOMAIN NAMES40 AS
VARCHAR(40) CHARACTER SET UTF8
COLLATE UNICODE_CI_AI;

and then have this as the custom type:

class NAMES40(sa.types.UserDefinedType):

     impl = sa.Unicode

     def get_col_spec(self):
         return "NAMES40"

     def bind_processor(self, dialect):
         def process(value):
             return value
         return process

     def result_processor(self, dialect, coltype):
         def process(value):
             return value
         return process

Can anyone point me to more samples using UserDefinedType and 
TypeDecorator, looked at the UsageRecipes and the documentation but 
obviously can't quit put it together for my use.

Werner

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

A long-lived ORM object

I have an ORM object that gets loaded once during program run and never changes.

   sess = Session()
   unchanging = sess.query(Unchanging).get(1)
   sess.close()  # it is now detached

I then need to tell other objects about about it, having a many-to-one 
relationship to 'unchanging':

   sess1 = Session()
   thing1 = Thing()
   thing1.unchanging = unchanging
   sess1.add(thing1)
   sess1.commit()

   sess2 = Session()
   thing2 = Thing()
   thing2.unchanging = unchanging
   sess2.add(thing2)  # fails InvalidRequestError
   sess2.commit()

This doesn't work, of course, because 'unchanging' becomes attached to sess1.

Is there some way I can keep 'unchanging' detached and use it over and over 
again? Or make a copy of it before attaching it to 'thing'? Or must I load a 
fresh copy every time I need to use it somewhere?

Thanks,
Michael

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

multiple FK constraints and self referential

I aam having an issue defining a 2nd foreign key constraint on 2
derived classes of a base class that uses a self referential join.

Base class:ContentBase

class ContentBase(Base):
    __tablename__ = 'content'
    __mapper_args__ = {'polymorphic_on': ctype,
'polymorphic_identity':'contentbase'}
    id = Column(types.Integer, primary_key=True, autoincrement=True)
    children = relationship("ContentBase", backref=backref('parent',
remote_side=id))
    ....

Derived class CdRoot

class CdRoot(ContentBase):
    __tablename__ = 'cdroot'
    __mapper_args__ = {'polymorphic_identity' : 'cdroot'}
    contentType = 'CdRoot'
    id = Column(types.Integer, ForeignKey(ContentBase.id),
primary_key=True)
    clubs = relationship('CdClub', primaryjoin="cdroot.id ==
cdclub.cdroot_id")
    ...

Derived class CdClub

class CdClub(ContentBase):
    __tablename__ = 'cdclub'
    __mapper_args__ = {'polymorphic_identity' : 'cdclub'}
    id = Column(types.Integer, ForeignKey(ContentBase.id),
primary_key=True)
    cdroot_id = Column(types.Integer, ForeignKey(CdRoot.id))
    ...

The 2nd join between CdRoot and CdClub fails with the message : Can't
determine join between content and
cdclub, tables have more than one FK constraint.

Apparently the primaryjoin condition is not sufficient to get rid of
the error.  Any clue how to solve this

Ruben

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

RE: String matching

Ah, I missed a closing ) in my code sample there. There should be a ) before the ], to close the if statement.

-----Original Message-----
From: sqlalchemy <at> googlegroups.com [mailto:sqlalchemy <at> googlegroups.com] On Behalf Of Jackson, Cameron
Sent: Wednesday, 8 February 2012 10:01 AM
To: sqlalchemy <at> googlegroups.com
Subject: RE: [sqlalchemy] String matching

I'm not sure how to do exactly what you're asking, but I have an alternative suggestion: You could just query
everything and do the filter at the application level (i.e. in Python), rather than in the query.

Because Python is awesome, this is now easy to write:

    custom_string = 'foobar'
    parcours = self.session.query(Parcours).all()
    parcours = [parcour for parcour in parcours if (
                        custom_string.lower() in parcour.parcours_name.lower() or
                        parcour.parcours_name.lower() in custom_string.lower()]

I've done this in a few places in my current application where I needed string.startswith() logic.

Of course, if you're going to have a lot of data in the table, this might not be a good idea. Querying the entire
table will increase your bandwidth (although SQLAlchemy's lazy loading will help here), and filtering
the list in Python will probably be slower than letting the database use it. Although, if there are a lot of
clients connecting to the database, moving the filter from the DB to the clients might help to reduce the
load on the database server.

It's hard to say without knowing your situation. If in doubt, try both and profile.

Cheers,
Cam

-----Original Message-----
From: sqlalchemy <at> googlegroups.com [mailto:sqlalchemy <at> googlegroups.com] On Behalf Of yannack
Sent: Wednesday, 8 February 2012 4:35 AM
To: sqlalchemy
Subject: [sqlalchemy] String matching

Hello list,
I have the following problem. I wish to query my database to look for
columns with names included in, or including, a given string.
is there a way to do the opposite of "contains", ie, a "contained"
method for string columns? Right now, I am doing the following:

session.query(Parcours).filter(
                             or_(

Parcours.parcours_name.ilike('%'+custom_string+'%'),
                                 literal_column("'"+custom_string
+"'").ilike('%'+Parcours.parcours_name+'%'))

this works "ok" for most stuff, but breaks when custom_strings has
quotes and such. Therefore, I need to improve on this:
- either by escaping the custom_string ?
- or by taking another approach than the "ilike" route (I use ilike
instead of contains simply because I don't know how to do case-
insensitive "contains" in PGSQL and my code will have to run both on
sqlite and PGSQL)

also, is there a case-insensitive version of the "contains" method
(which would generate ilike statements instead of like in postgresql
while properly escaping quotes and other stuff) ?

Finally, I am also open to any thoughts and pointers people may have
concerning partial/approaching string matching. At the moment, I just
do this contains/contained, case-insensitive search, and finish with
some manual matching, but if anyone has opinions on this, I am of
course very interested!

Thanks for your time,
Best regards,
Yannick

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

-------------------------------------------------------------------------
DISCLAIMER: This e-mail transmission and any documents, files and 
previous e-mail messages attached to it are private and confidential.  
They may contain proprietary or copyright material or information that 
is subject to legal professional privilege.  They are for the use of 
the intended recipient only.  Any unauthorised viewing, use, disclosure, 
copying, alteration, storage or distribution of, or reliance on, this 
message is strictly prohibited.  No part may be reproduced, adapted or 
transmitted without the written permission of the owner.  If you have 
received this transmission in error, or are not an authorised recipient, 
please immediately notify the sender by return email, delete this 
message and all copies from your e-mail system, and destroy any printed 
copies.  Receipt by anyone other than the intended recipient should not 
be deemed a waiver of any privilege or protection.  Thales Australia 
does not warrant or represent that this e-mail or any documents, files 
and previous e-mail messages attached are error or virus free.  

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

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

-------------------------------------------------------------------------
DISCLAIMER: This e-mail transmission and any documents, files and 
previous e-mail messages attached to it are private and confidential.  
They may contain proprietary or copyright material or information that 
is subject to legal professional privilege.  They are for the use of 
the intended recipient only.  Any unauthorised viewing, use, disclosure, 
copying, alteration, storage or distribution of, or reliance on, this 
message is strictly prohibited.  No part may be reproduced, adapted or 
transmitted without the written permission of the owner.  If you have 
received this transmission in error, or are not an authorised recipient, 
please immediately notify the sender by return email, delete this 
message and all copies from your e-mail system, and destroy any printed 
copies.  Receipt by anyone other than the intended recipient should not 
be deemed a waiver of any privilege or protection.  Thales Australia 
does not warrant or represent that this e-mail or any documents, files 
and previous e-mail messages attached are error or virus free.  

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

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.