date:20090311

[sqlalchemy] Re: Using sqlalchemy in twisted.

2009-03-11 Thread Jeff FW


Logging SA objects *after* the session is gone will always be a
problem, unless you make sure to detach all of them from the session.
I'd just log the original request, instead.  In my case, I have to
convert all of my SA objects to something Perspective Broker
understands, so I actually log those *after* that, as they're no
longer part of the session--but I'm not sure if you can do that in
your case.

As for the decorator--I got a little confused with your names--you
call it model in your decorator, but it's really an instance of
Database when it gets passed in as self.  One way to get rid of that
parameter would be to make sess a keyword argument, like so:

def wrapper(*args, **kw):
sess = model.Session()
try:
return f(sess=sess, *args, **kw)

and then change your method:

def _getObjectById(self, klass, id, sess=None):
return sess.query(klass).get(id)

That way, self will get passed in *args with no problem.

Are you planning to have multiple instances of your Database class?
If not, I'd suggest changing everything in it into class methods, so
that way you can call it *without* an instance at all, and don't have
to worry about connecting to the database multiple times by accident.
Just a thought.

-Jeff

On Mar 10, 10:38 am, 一首诗 newpt...@gmail.com wrote:
 Hi Jeff,

 Thanks for your kind suggestion.

 I first add some log decorators, but i found when it might cause to
 print sqalchemy objects which has not been bound to any session.

 And I am not quite sure about how to make the decorator mor genreal.

 Actually, I think I must use model as the first parameter because as a
 instance method, _getObjectById require the first parameter to be
 self.
 Can you write a few lines of code to show your suggestion?

 On Mar 8, 5:06 am, Jeff FW jeff...@gmail.com wrote:

  That's pretty similar to what I do, actually, if a bit simpler (but
  that's good!)  One suggestion would be to throw an except (maybe for
  the base SQLAlchemy exception class)  in your try block, otherwise you
  run the risk of things dying in an ugly way.  I'm not familiar with
  pyamf, so I don't know how it would handle errors, but twisted usually
  carries on as if nothing happens.

  Also, I'd make the decorator a bit more general--don't put the model
  argument in wrapper().  Put sess first, then take *args and **kwargs,
  and pass those right to the inner function f(). That way you can reuse
  it for anything that requires a DB session.

  Other things you could add (if so inclined) are decorators for logging
  and other types of error handling (like catching IntegrityErros thrown
  by duplicates.)  I do those things, but I might be a bit OCD :-)

  -Jeff

  On Mar 7, 1:41 am, 一首诗 newpt...@gmail.com wrote:

   Hi, Thanks for your reply.  I'm using it the way like you.  The only
   difference is that I am using pyamf instead of PB.

   On every request, I delegate required db operations to a class called
   Database, similar to these code below.

   I used to use scope_session instead of create and close session every
   time.   But as I said in my earlier mails, they don't work.

   These code below seems to work right now.  But if you have more
   suggestion,  I will be very thankful.

   #=

   def require_session(f):
   '''create and close session for each synchronous method'''
   def wrapper(model, *args, **kw):
   sess = model.Session()
   try:
   return f(model, sess, *args, **kw)
   finally:
   sess.close()
   return wrapper

   class Database()
   def __init__(self, conn_str):
   self.conn_str = conn_str
   self.engine = create_engine(self.conn_str, echo=False)
   self.Session = sessionmaker(bind = self.engine,
   expire_on_commit=False)

   def getObjectById(self, klass, id):
   return threads.deferToThread(self._getObjectById, klass, id)

   @require_session

   def _getObjectById(self, sess, klass, id):

   return sess.query(klass).get(id)
   #=

   On Mar 6, 5:44 am, Jeff FW jeff...@gmail.com wrote:

Don't use scoped_session--you'll run into problems no matter what you
do.  I'm using Perspective Broker from Twisted with SQLAlchemy.  I
make sure to create and commit/rollback a session for *every* PB
request.  It works perfectly, and that's the only way I was really
able to get it to work in all cases.

Assuming you're using Twisted in a similar way, you could write a
simple decorator to wrap any functions that need a database session in
the begin/commit stuff as necessary.

If you can give more details of how you're using Twisted, I might be
able to offer some more insight.

-Jeff

On Mar 5, 12:33 am, 一首诗 newpt...@gmail.com wrote:

 I'm not quite sure, but I think I'm pretty careful of sharing objects
 between threads.

 1st, I

[sqlalchemy] Re: Using sqlalchemy in twisted.

2009-03-11 Thread 一首诗


Hi Jeff,

In my project I use the *model* to indicate an instance of Database.
I don't really need multiple instances of Database.  But when I wrote
unit test, I always create an new one database in setup, which create
a new sqlite in memory database to avoid conflicts between test cases.

About the trick to make *sess* a keywork parameter,  that's really
clever!
Thanks a lot!

On Mar 11, 9:05 pm, Jeff FW jeff...@gmail.com wrote:
 Logging SA objects *after* the session is gone will always be a
 problem, unless you make sure to detach all of them from the session.
 I'd just log the original request, instead.  In my case, I have to
 convert all of my SA objects to something Perspective Broker
 understands, so I actually log those *after* that, as they're no
 longer part of the session--but I'm not sure if you can do that in
 your case.

 As for the decorator--I got a little confused with your names--you
 call it model in your decorator, but it's really an instance of
 Database when it gets passed in as self.  One way to get rid of that
 parameter would be to make sess a keyword argument, like so:

 def wrapper(*args, **kw):
 sess = model.Session()
 try:
 return f(sess=sess, *args, **kw)

 and then change your method:

 def _getObjectById(self, klass, id, sess=None):
 return sess.query(klass).get(id)

 That way, self will get passed in *args with no problem.

 Are you planning to have multiple instances of your Database class?
 If not, I'd suggest changing everything in it into class methods, so
 that way you can call it *without* an instance at all, and don't have
 to worry about connecting to the database multiple times by accident.
 Just a thought.

 -Jeff

 On Mar 10, 10:38 am, 一首诗 newpt...@gmail.com wrote:

  Hi Jeff,

  Thanks for your kind suggestion.

  I first add some log decorators, but i found when it might cause to
  print sqalchemy objects which has not been bound to any session.

  And I am not quite sure about how to make the decorator mor genreal.

  Actually, I think I must use model as the first parameter because as a
  instance method, _getObjectById require the first parameter to be
  self.
  Can you write a few lines of code to show your suggestion?

  On Mar 8, 5:06 am, Jeff FW jeff...@gmail.com wrote:

   That's pretty similar to what I do, actually, if a bit simpler (but
   that's good!)  One suggestion would be to throw an except (maybe for
   the base SQLAlchemy exception class)  in your try block, otherwise you
   run the risk of things dying in an ugly way.  I'm not familiar with
   pyamf, so I don't know how it would handle errors, but twisted usually
   carries on as if nothing happens.

   Also, I'd make the decorator a bit more general--don't put the model
   argument in wrapper().  Put sess first, then take *args and **kwargs,
   and pass those right to the inner function f(). That way you can reuse
   it for anything that requires a DB session.

   Other things you could add (if so inclined) are decorators for logging
   and other types of error handling (like catching IntegrityErros thrown
   by duplicates.)  I do those things, but I might be a bit OCD :-)

   -Jeff

   On Mar 7, 1:41 am, 一首诗 newpt...@gmail.com wrote:

Hi, Thanks for your reply.  I'm using it the way like you.  The only
difference is that I am using pyamf instead of PB.

On every request, I delegate required db operations to a class called
Database, similar to these code below.

I used to use scope_session instead of create and close session every
time.   But as I said in my earlier mails, they don't work.

These code below seems to work right now.  But if you have more
suggestion,  I will be very thankful.

#=

def require_session(f):
'''create and close session for each synchronous method'''
def wrapper(model, *args, **kw):
sess = model.Session()
try:
return f(model, sess, *args, **kw)
finally:
sess.close()
return wrapper

class Database()
def __init__(self, conn_str):
self.conn_str = conn_str
self.engine = create_engine(self.conn_str, echo=False)
self.Session = sessionmaker(bind = self.engine,
expire_on_commit=False)

def getObjectById(self, klass, id):
return threads.deferToThread(self._getObjectById, klass, id)

@require_session

def _getObjectById(self, sess, klass, id):

return sess.query(klass).get(id)
#=

On Mar 6, 5:44 am, Jeff FW jeff...@gmail.com wrote:

 Don't use scoped_session--you'll run into problems no matter what you
 do.  I'm using Perspective Broker from Twisted with SQLAlchemy.  I
 make sure to create and commit/rollback a session for *every* PB
 request.  It works perfectly, and that's the only way I was

[sqlalchemy] Re: Supporting fully-qualified table names and cross-database references in Sybase and SQL Server?

2009-03-11 Thread phrrn...@googlemail.com


I wasn't able to get it working so easily using the existing entry-
points so I created a new one, quote_schema, and use it explicitly in
a couple of places in compiler.py. The default implementation is the
same as the old one.

pjjH


+def quote_schema(self, schema, force):
+Quote a schema.
+
+Subclasses should override this to provide database-dependent
+quoting behavior.
+
+return self.quote(schema, force)
+


def quote_schema(self, schema, force=True):
Prepare a quoted table and schema name.
result = '.'.join([self.quote(x, force) for x in schema.split
('.')])
return result


On Mar 10, 5:30 pm, phrrn...@googlemail.com
phrrn...@googlemail.com wrote:
 As it happens, this works on the Sybase dialect without fixing the
 quoting at all!  Apparently SQL such as this is happily accepted by
 Sybase:

 SELECT [fdcommon.dbo].organization.org_id,
 [fdcommon.dbo].organization.abbrev
 FROM [fdcommon.dbo].organization JOIN [fdcommon.dbo].org_type
 ON [fdcommon.dbo].org_type.org_type_id =
 [fdcommon.dbo].organization.org_type

 I resorted to some brute-force list operations rather than regular
 expressions to parse out the component names (see diff below). I will
 fix the quoting shortly (within the next day or so) and submit a
 single diff.

 thanks,

 pjjH

 Index: schema.py

 ===

 --- schema.py   (revision 5816)

 +++ schema.py   (working copy)

 @@ -876,17 +876,22 @@

  raise exc.ArgumentError(
  Parent column '%s' does not descend from a 
  table-attached Column % str(self.parent))
 -m = re.match(r^(.+?)(?:\.(.+?))?(?:\.(.+?))?$,
 self._colspec,
 - re.UNICODE)
 +m = self._colspec.split('.')
  if m is None:
  raise exc.ArgumentError(
  Invalid foreign key column specification: %s %
  self._colspec)
 -if m.group(3) is None:
 -(tname, colname) = m.group(1, 2)
 +
 +m.reverse()
 +(colname, tname) = m[0:2]
 +
 +if m[2] is None:
  schema = None
  else:
 -(schema, tname, colname) = m.group(1, 2, 3)
 +m1 = m[2:]
 +m1.reverse()
 +schema = '.'.join(m1)
 +

 On Mar 5, 7:21 pm, phrrn...@googlemail.com phrrn...@googlemail.com
 wrote:

  OK. If it might be as easy as that, I will have a go and see how well
  it works.

  pjjH

  On Mar 5, 4:31 pm, Michael Bayer mike...@zzzcomputing.com wrote:

   phrrn...@googlemail.com wrote:

Sybase (and SQL Server) support cross-database JOINs (Sybase even
supports cross-database foreign-key constraints). There are four
components to an object identifier:

1 = Object name
2 = Schema name
3 = Database name
4 = Server name

the dataserver, database and schema are assumed for one-part
identifiers (e.g. 'foo'), dataserver and database assumed for two-part
identifiers (e.g. 'dbo.foo') and, finally, dataserver assumed for
three-part identifiers ('production.dbo.foo')

e.g. SELECT foo.* FROM BANANA.production.dbo.foo
   SELECT foo.* FROM production.dbo.foo
   SELECT foo.* FROM production..foo -- same as the previous query
if the callers default schema is dbo
   SELECT foo.* FROM dbo.foo
   SELECT foo.* FROM foo
   SELECT foo.* FROM ..foo
I am not so interested in supporting four-part identifiers in SA but I
would like to figure out how to support three-part identifiers as very
many of our databases have cross-database references.

One natural (to me!) way of doing this is to add a 'database' property
to the Table and ForeignKeyConstraint schema items and have the Sybase/
SQL Server dialects always emit fully-qualified three-part identifiers
for table names.

   we have an element on Table called schema.  I had in mind that schema
   should accept dotted names, so SQLA generally doesn't need to get
   involved.  The only change needed is to the IdentifierPreparer, such that
   when quote_schema is called, it separates the name along the dot first so
   that it can quote each token separately.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] How to set default join onclause if table has more than one FK to another table?

2009-03-11 Thread sector119


Hi All!

How to set default join condition if table has more than one FK to
another table?

It may be Transaction.user_id==User.id or
Transaction.rollback_user_id==User.id, I want first one to be default,
is it possible to do?

transactions_table = sa.Table('transactions', meta.metadata,
sa.Column('id', sa.types.Integer, primary_key=True),
sa.Column('serial', sa.types.Integer, index=True, nullable=False),
sa.Column('person_id', sa.types.Integer, sa.ForeignKey
('people.id'), index=True, nullable=False),
sa.Column('user_id', sa.types.Integer, sa.ForeignKey(SYSTEM_SCHEMA
+'.users.id'), index=True, nullable=False),
sa.Column('service_id', sa.types.Integer, sa.ForeignKey
('services.id'), index=True, nullable=False),
sa.Column('sum', sa.types.Integer, nullable=False),
sa.Column('commit_date', sa.types.Date, index=True,
nullable=False),
sa.Column('commit_time', sa.types.Time, index=True,
nullable=False),
sa.Column('rollback_date', sa.types.Date, index=True,
nullable=True),
sa.Column('rollback_time', sa.types.Time, index=True,
nullable=True),
sa.Column('rollback_user_id', sa.types.Integer, sa.ForeignKey
(SYSTEM_SCHEMA+'.users.id'), index=True, nullable=True)
)

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

[sqlalchemy] How to map columns into a tuple using SQLAlchemy?

2009-03-11 Thread batraone


Hi,

I'm just starting to use SQLAlchemy and hit a roadblock.

I have a class Result which contains a tuple called _limits.
_limits = (Upper value, lower value, nominal)

I would like to map a the table's columns directly into this tuple.

From the documentation, all I can see is columns mapping directly to
python attributes (I.e. Result.upper_value, Result.lower_value,..).
Is there a way to map the three columns directly into the tuple?

I do not want to modify the Result class and therefore cannot create
it as composite column type.

I'm hoping there is a syntax that states map these 3 columns into
this tuple via the mapper.

Thanks,

Raj

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

[sqlalchemy] How to map columns into a tuple using SQLAlchemy?

2009-03-11 Thread batraone


Hi,

I'm just starting to use SQLAlchemy and hit a roadblock.

I have a class Result which contains a tuple called _limits.
_limits = (Upper value, lower value, nominal)

I would like to map a the table's columns directly into this tuple.

From the documentation, all I can see is columns mapping directly to
python attributes (I.e. Result.upper_value, Result.lower_value,..).
Is there a way to map the three columns directly into the tuple?

I do not want to modify the Result class and therefore cannot
create it as composite column type.

I'm hoping there is a syntax that states map these 3 columns into
this tuple via the mapper.

Thanks,

Raj

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

[sqlalchemy] Help! More Class-Mapper confusion!

2009-03-11 Thread batraone


Hi,

OK - I ran into another thing that I do not understand about
sqlalchemy. I am a newbie, so hopefully this will be
straightforward.

There are two issues here:

ISSUE1:
I create a class called foo w/ a method called name().
I map a table that has a column called 'name' but use
the column_prefix = '_' but I do not see _name get added
to the class! But other labels, such as _phone which do
not have a corresponding method name does get added.

Here's the code:

import sqlalchemy as sa
from sqlalchemy import orm
from sqlite3 import dbapi2 as sqlite

class Foo(object):
def __init__(self):
self._myname = 'Bar'

def name(self):
return(self._myname)

class FooStore:
def __init__(self):
self.metadata = sa.MetaData()

# table to map class to
self.t_foo = sa.Table('table_foo', self.metadata,
sa.Column('id', sa.types.Integer, primary_key=True),
sa.Column('name', sa.types.String(100)),
sa.Column('phone', sa.types.String(100))
)

def map(self, myfoo):
'Creates the map. '

orm.mapper(Foo, self.t_foo, column_prefix = '_')

# Add foo to the database
print dir(myfoo) # where did _name go?, _phone is there!

mf2 = Foo() # ok, let's create a new one.
print dir(mf2) # same problem

if __name__ == '__main__':
f = Foo()
fs = FooStore()
fs.map(f)


ISSUE2: I have an object that will be given to me that I want to
store into a database. The class definition is located in a package.
When I map this class to a table and set the attribute - I get
an exception:

AttributeError: 'NoneType' object has no attribute 'set'

This can be seen by modifying the above example - where I put
Foo into a package called 'foo':

import sqlalchemy as sa
from sqlalchemy import orm
from sqlite3 import dbapi2 as sqlite

import sys

import foo.Foo as Foo

class FooStore:
def __init__(self):
self.metadata = sa.MetaData()

# table to map class to
self.t_foo = sa.Table('table_foo', self.metadata,
sa.Column('id', sa.types.Integer, primary_key=True),
sa.Column('name', sa.types.String(100)),
sa.Column('phone', sa.types.String(100))
)

def map(self, myfoo):
'Creates the map. te is the test engine'

orm.mapper(Foo.Foo, self.t_foo, column_prefix = '_')

# Add foo to the database

try:
myfoo._phone = '555-1212' # exception is thrown!

except:
#AttributeError: 'NoneType' object has no attribute 'set'
print sys.exc_info()

mf2 = Foo.Foo() # ok, let's create a new one.
# AttributeError: 'Foo' object has no attribute
'_sa_instance_state'
myfoo._phone = '555-1212' #

if __name__ == '__main__':
orm.clear_mappers()
f = Foo.Foo()
fs = FooStore()
fs.map(f)



What's the right way to adapt this class to a table?

Thanks!

-Raj


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

[sqlalchemy] Re: Help! More Class-Mapper confusion!

2009-03-11 Thread Michael Bayer


batraone wrote:
 def map(self, myfoo):
 'Creates the map. '

 orm.mapper(Foo, self.t_foo, column_prefix = '_')

 # Add foo to the database
 print dir(myfoo) # where did _name go?, _phone is there!

 mf2 = Foo() # ok, let's create a new one.
 print dir(mf2) # same problem

 if __name__ == '__main__':
 f = Foo()
 fs = FooStore()
 fs.map(f)

The column_prefix wasn't being honored when the mapper checked for
existing names, this is fixed in trunk r5839.

But also, don't create instances of the object before the class is mapped.
 In particular, it's bad form to create tables and mappers inside of class
methods.  Create class-level constructs like tables and mappers at the
module level, in the same scope in which you create your classes.



 mf2 = Foo.Foo() # ok, let's create a new one.
 # AttributeError: 'Foo' object has no attribute
 '_sa_instance_state'
 myfoo._phone = '555-1212' #

 if __name__ == '__main__':
 orm.clear_mappers()
 f = Foo.Foo()
 fs = FooStore()
 fs.map(f)

don't create instances of the object before the class is mapped.


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

[sqlalchemy] Re: Help! More Class-Mapper confusion!

2009-03-11 Thread batraone


Thanks. I don't have control over the instance creation. These pre-
created objects are handed down to me. I could create an adapter that
maps the original class to the one that has been mapped but this is
quite a bit of work as I have to manually copy over each of the source
class attributes to my mapped class. I could be clever and perhaps
look for common attribute names via the internal dictionary but this
too seems clunky.

I would think this is a common problem (augmenting a class to dump
it's contents to a db but keep the original class untouched (sort of
like shelve - but w/o the
restore capability)). Is there a better way to handle this?

Thanks,

Raj

On Mar 11, 2:48 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 batraone wrote:
      def map(self, myfoo):
          'Creates the map. '

          orm.mapper(Foo, self.t_foo, column_prefix = '_')

          # Add foo to the database
          print dir(myfoo) # where did _name go?, _phone is there!

          mf2 = Foo() # ok, let's create a new one.
          print dir(mf2) # same problem

  if __name__ == '__main__':
      f = Foo()
      fs = FooStore()
      fs.map(f)

 The column_prefix wasn't being honored when the mapper checked for
 existing names, this is fixed in trunk r5839.

 But also, don't create instances of the object before the class is mapped.
  In particular, it's bad form to create tables and mappers inside of class
 methods.  Create class-level constructs like tables and mappers at the
 module level, in the same scope in which you create your classes.



          mf2 = Foo.Foo() # ok, let's create a new one.
          # AttributeError: 'Foo' object has no attribute
  '_sa_instance_state'
          myfoo._phone = '555-1212' #

  if __name__ == '__main__':
      orm.clear_mappers()
      f = Foo.Foo()
      fs = FooStore()
      fs.map(f)

 don't create instances of the object before the class is mapped.- Hide quoted 
 text -

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

[sqlalchemy] Re: Using order_by in an association many-to-many relationship with columns from the association object

2009-03-11 Thread Scott


When I do this...

cpt_codes = ManyToMany(
  ...
  order_by = procedure_cpt_codes.c.cpt_codes_idx
)

# procedure_cpt_codes is the JOIN table in between the parent
(procedure) --- children (cpt_codes)
# procedure_cpt_codes has 3 columns...procedure_id (foreign key),
cpt_code_id (foreign key) and cpt_codes_idx that's sorted

...I get the following error:

TypeError: 'Column' object is not iterable

I had tried passing order_by several configurations including the
column object as you suggested to no avail. It seemed when I looked
through the documentation and source (which was a few days ago now so
my memory may be fuzzy) for ManyToMany order_by was expecting a string
that was the name of a column on the secondary table (CptCode in my
example).

I've since started to try to shoe horn the relationship with an
association object representing the join table which seemed the only
way to access a non-foreign key column on the join table to order the
collection by. If there's a better way to do this with the order_by
parameter I'd love to figure it out b/c association object syntax with
ORM gets REALLY messy.

Thanks, Scott

On Mar 10, 12:03 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 order_by accepts a Column object, i.e. table.c.whatever, so pass that in.



 Scott wrote:

  Is there a way with the current iteration of SQLAlchemy to add a
  column to the association table in a many-to-many relationship with
  that column used to order the join? I looked at the order_by attribute
  of the ManyToMany() relationship definition, but it seems that this is
  expecting a string naming the column in the related entity. I'm using
  Elixir on top of alchemy, but here are my relevant class and table
  definitions:

  procedure_cpt_codes = Table('procedure_cpt_codes', metadata,
  autoload=True)

  class CptCode(Entity):
     using_options(tablename='cpt_codes', autosetup=True)

     name = Field(Unicode)
     code = Field(Unicode)
     description= Field(Unicode)

  class Procedure(Entity):
     using_options(tablename='procedures', autosetup=True)

     complications = OneToMany('Complication')
     cpt_codes = ManyToMany(
             'CptCode',
             table = procedure_cpt_codes, lazy=False,
             foreign_keys = lambda: [ procedure_cpt_codes.c.procedure_id,
  procedure_cpt_codes.c.cpt_code_id ],
             primaryjoin = lambda: Procedure.id ==
  procedure_cpt_codes.c.procedure_id,
             secondaryjoin = lambda: CptCode.id ==
  procedure_cpt_codes.c.cpt_code_id,
             order_by = procedure_cpt_codes.c.cpt_codes_idx
     )
     procedure_date = Field(Date)

  I get the following exception when run as listed:

  Traceback (most recent call last):
    File /System/Library/Frameworks/Python.framework/Versions/Current/
  Extras/lib/python/PyObjC/PyObjCTools/AppHelper.py, line 235, in
  runEventLoop
      main(argv)
    File /Users/tswall/Documents/workspace/Cocoa/python/Epdb/build/
  Debug/Epdb.app/Contents/Resources/MyController.py, line 15, in
  buttonPushed_
      for instance in Patient.query.all():
    File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
  elixir/entity.py, line 641, in __get__
      elixir.setup_all()
    File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
  elixir/__init__.py, line 145, in setup_all
    File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
  elixir/entity.py, line 816, in setup_entities
      method()
    File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
  elixir/entity.py, line 421, in setup_properties
      self.call_builders('create_properties')
    File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
  elixir/entity.py, line 433, in call_builders
      getattr(builder, what)()
    File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
  elixir/relationships.py, line 417, in create_properties
      self.target._descriptor.translate_order_by(kwargs['order_by'])
    File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
  elixir/entity.py, line 322, in translate_order_by
      for colname in order_by:
  TypeError: 'Column' object is not iterable

  When I change the order_by above to
    order_by = 'procedure_cpt_codes.c.cpt_codes_idx' #or 'cpt_codes_idx'
  I get an error that it can't find column 'cpt_codes_idx' on relation
  table 'CptCode'.

  Any advice would be appreciated!
  Scott
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] SQLAlchemy based syndication feed framework?

2009-03-11 Thread Noah Gift

Does anyone on this list know of a simple WSGI compatible, SQLAlchemy based
feed framework, like the one offered with Django:
http://docs.djangoproject.com/en/dev/ref/contrib/syndication/?from=olddocs



-- 
Cheers,

Noah

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

[sqlalchemy] Re: SQLAlchemy based syndication feed framework?

2009-03-11 Thread Noah Gift

On Thu, Mar 12, 2009 at 11:25 AM, Noah Gift noah.g...@gmail.com wrote:

 Does anyone on this list know of a simple WSGI compatible, SQLAlchemy based
 feed framework, like the one offered with Django:
 http://docs.djangoproject.com/en/dev/ref/contrib/syndication/?from=olddocs

 To answer my own question, I found this:

http://www.dalkescientific.com/Python/PyRSS2Gen.html

Which looks fairly easy to combine with SQLAlchemy + something like this:
http://pypi.python.org/pypi/urlrelay/0.6

Hmm, maybe I will make this into a library.



 --
 Cheers,

 Noah




-- 
Cheers,

Noah

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

[sqlalchemy] Re: SQLAlchemy based syndication feed framework?

2009-03-11 Thread Walter Cruz

webhelpers, from Pylons, has a handy rss generator.

I use it with SQLAlchemy, works fine.

On Wed, Mar 11, 2009 at 8:00 PM, Noah Gift noah.g...@gmail.com wrote:



 On Thu, Mar 12, 2009 at 11:25 AM, Noah Gift noah.g...@gmail.com wrote:

 Does anyone on this list know of a simple WSGI compatible, SQLAlchemy
 based feed framework, like the one offered with Django:
 http://docs.djangoproject.com/en/dev/ref/contrib/syndication/?from=olddocs

 To answer my own question, I found this:

 http://www.dalkescientific.com/Python/PyRSS2Gen.html

 Which looks fairly easy to combine with SQLAlchemy + something like this:
 http://pypi.python.org/pypi/urlrelay/0.6

 Hmm, maybe I will make this into a library.



 --
 Cheers,

 Noah




 --
 Cheers,

 Noah

 



-- 
[]'
- Walter
waltercruz.com

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

[sqlalchemy] Re: SQLAlchemy based syndication feed framework?

2009-03-11 Thread Noah Gift

-- Forwarded message --
From: Walter Cruz walter@gmail.com
Date: Thu, Mar 12, 2009 at 12:19 PM
Subject: [sqlalchemy] Re: SQLAlchemy based syndication feed framework?
To: sqlalchemy@googlegroups.com


webhelpers, from Pylons, has a handy rss generator.

I use it with SQLAlchemy, works fine.


Hmm, this app will talk to a few other Pylons apps at some point, so I will
have a look, thanks for the tip!




On Wed, Mar 11, 2009 at 8:00 PM, Noah Gift noah.g...@gmail.com wrote:



 On Thu, Mar 12, 2009 at 11:25 AM, Noah Gift noah.g...@gmail.com wrote:

 Does anyone on this list know of a simple WSGI compatible, SQLAlchemy
 based feed framework, like the one offered with Django:
 http://docs.djangoproject.com/en/dev/ref/contrib/syndication/?from=olddocs

 To answer my own question, I found this:

 http://www.dalkescientific.com/Python/PyRSS2Gen.html

 Which looks fairly easy to combine with SQLAlchemy + something like this:
 http://pypi.python.org/pypi/urlrelay/0.6

 Hmm, maybe I will make this into a library.



 --
 Cheers,

 Noah




 --
 Cheers,

 Noah





-- 
[]'
- Walter
waltercruz.com





-- 
Cheers,

Noah

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

[sqlalchemy] Re: Help! More Class-Mapper confusion!

2009-03-11 Thread Michael Bayer



On Mar 11, 2009, at 6:05 PM, batraone wrote:


 Thanks. I don't have control over the instance creation. These pre-
 created objects are handed down to me. I could create an adapter that
 maps the original class to the one that has been mapped but this is
 quite a bit of work as I have to manually copy over each of the source
 class attributes to my mapped class. I could be clever and perhaps
 look for common attribute names via the internal dictionary but this
 too seems clunky.

 I would think this is a common problem (augmenting a class to dump
 it's contents to a db but keep the original class untouched (sort of
 like shelve - but w/o the
 restore capability)). Is there a better way to handle this?

Unfortunately the usage of an object relational mapper assumes that  
some degree of convention can be applied to the classes being mapped.   
For example, you can't map old style classes nor can you map most  
natively-backed classes (like cElementTree structures),  
instrumentation will fail if the class relies upon direct __dict__  
access, and there's probably many other examples of limitations  
here.   Applying instrumentation to a class to which you don't have  
control over is generally a bad idea no matter what persistence  
approach you're using, as even if you do get it working, changes to  
the implementation of those objects will break your application.   So  
in this case you definitely should be marshalling these external  
objects immediately into an object structure which you have control  
over.You also should most certainly be using standard Python  
idioms like dir(object), getattr() and possibly __dict__ access in  
order to marshall the state of the incoming objects into your own  
model using a reflective approach rather than hardcoding every  
attribute.



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

[sqlalchemy] Re: Using order_by in an association many-to-many relationship with columns from the association object

2009-03-11 Thread Michael Bayer



On Mar 11, 2009, at 6:17 PM, Scott wrote:


 When I do this...

 cpt_codes = ManyToMany(
  ...
  order_by = procedure_cpt_codes.c.cpt_codes_idx
 )

 # procedure_cpt_codes is the JOIN table in between the parent
 (procedure) --- children (cpt_codes)
 # procedure_cpt_codes has 3 columns...procedure_id (foreign key),
 cpt_code_id (foreign key) and cpt_codes_idx that's sorted

 ...I get the following error:

 TypeError: 'Column' object is not iterable

this would suggest the order_by argument on Elixir's ManyToMany  
function is expecting a list.   Try asking on the Elixir mailing list  
about this issue since this is not a SQLAlchemy issue.


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

[sqlalchemy] Late and ugly error when mixing timezone-savvy and timezone-naive datetimes

2009-03-11 Thread Gunnlaugur Briem


Hi,

I get away with stuffing datetime.datetime.now() into a DateTime
(timezone=True) column, despite the former being timezone-naive
(.utcoffset() is None, .tzinfo is None, etc.).

It is stored in the table with UTC offset +00, which is arguably
incorrect (states information that was not present in the input).

But even if you call it correct, you get in trouble when you read the
value back as an attribute of a mapped class in a session, set the
attribute again to datetime.datetime.now() (again timezone-naive), and
then try to query the session for the same object again. This retches
up a TypeError: “can't compare offset-naive and offset-aware
datetimes”.

Code to reproduce:

from sqlalchemy import Table, MetaData, Column, Integer, DateTime,
create_engine
from sqlalchemy.orm import sessionmaker, mapper
from datetime import datetime, timedelta
from pytz import utc
t = Table('foo', MetaData(), Column('id', Integer, primary_key=True,),
Column('dt', DateTime(timezone=True)))
class T(object):
pass

mapper(T, t)
e = create_engine('postgres://localhost/satest')
t.create(bind=e, checkfirst=True)
e.execute(t.delete()) # in case we are re-running this test
Session = sessionmaker(bind=e)
inst = T()
inst.dt = datetime.now()
assert inst.dt.utcoffset() is None
session = Session()
session.add(inst)
session.commit()
session.close()
session = Session()
inst = session.query(T).first()
assert inst.dt.utcoffset() == timedelta(0)
inst.dt = datetime.now()
assert inst.dt.utcoffset() is None
# next line raises TypeError: can't compare offset-naive and offset-
aware datetimes
inst = session.query(T).first()

SQLAlchemy should either reject the timezone-naive datetime value
right away when it is bound to a timezone-savvy DateTime column, or
tolerate me setting a timezone-naive datetime value again. I root for
the former.

Regards,

- Gulli

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

[sqlalchemy] Re: Using sqlalchemy in twisted.

[sqlalchemy] Re: Using sqlalchemy in twisted.

[sqlalchemy] Re: Supporting fully-qualified table names and cross-database references in Sybase and SQL Server?

[sqlalchemy] How to set default join onclause if table has more than one FK to another table?

[sqlalchemy] How to map columns into a tuple using SQLAlchemy?

[sqlalchemy] How to map columns into a tuple using SQLAlchemy?

[sqlalchemy] Help! More Class-Mapper confusion!

[sqlalchemy] Re: Help! More Class-Mapper confusion!

[sqlalchemy] Re: Help! More Class-Mapper confusion!

[sqlalchemy] Re: Using order_by in an association many-to-many relationship with columns from the association object

[sqlalchemy] SQLAlchemy based syndication feed framework?

[sqlalchemy] Re: SQLAlchemy based syndication feed framework?

[sqlalchemy] Re: SQLAlchemy based syndication feed framework?

[sqlalchemy] Re: SQLAlchemy based syndication feed framework?

[sqlalchemy] Re: Help! More Class-Mapper confusion!

[sqlalchemy] Re: Using order_by in an association many-to-many relationship with columns from the association object

[sqlalchemy] Late and ugly error when mixing timezone-savvy and timezone-naive datetimes

17 matches

Site Navigation

Mail list logo

Footer information