[sqlalchemy] mixing metaclass with redefining the primary key of an autoloaded table

2010-05-10 Thread GHZ 
Hi,

Using plain Declarative, I am able to redefine a primary key column
that has been autoloaded, so that I can link it to an oracle sequence
and give it a new name:

Id = Column('id', Integer, Sequence('table_sq'), primary_key=True)


However, if I then try to add some methods to the class using a
metaclass, the foreign key relationships pointing to this column, seem
to go missing.

Apologies for not being able to track down the exact cause of this,
but it seems to be something I am doing wrong with the combination of
autloading, redefining the primary key, and adding to the class
through a metaclass.

The following example should work against an empty oracle schema.


I get: sqlalchemy.exc.ArgumentError: Could not determine join
condition between parent/child tables on relationship
Customer.addresses.  Specify a 'primaryjoin' expression.  If this is a
many-to-many relationship, 'secondaryjoin' is needed as well.


http://python.pastebin.com/7V8MEfH3


from sqlalchemy import MetaData, Column, Integer, Sequence,
ForeignKey, create_engine
from sqlalchemy.orm import relationship, sessionmaker, scoped_session
from sqlalchemy.ext.declarative import DeclarativeMeta,
declarative_base

engine = create_engine('oracle://fred:f...@mig01')

ddls = [
drop table customer,
drop table address,
drop table country,
drop table customer_type,
create table customer_type (
id number primary key,
name varchar2(10)),
create table country (
id number primary key,
name varchar2(10)),
create table customer (
id number primary key,
name varchar2(10),
customer_type_id number references customer_type),
create table address (
id number primary key,
name varchar2(10),
country_id number references country,
customer_id number references customer),
]

for ddl in ddls:
try:
print ddl,
engine.execute(ddl)
print 'ok'

except:
print 'fail'
pass


metadata = MetaData(bind=engine)

Session = scoped_session(sessionmaker())


class RelationNameAttribute(object):

def __init__(self, relationname, childclass):
self.relationname = relationname
self.query = Session.query(childclass)

def __get__(self, obj, objtype):
return getattr(getattr(obj, self.relationname), 'name')

def __set__(self, obj, val):
child = self.query.filter_by(name=val).one()
setattr(obj, self.relationname, child)


class DataMeta(DeclarativeMeta):

def __init__(cls, classname, bases, dict_):

classvalues = {}

for name, obj in vars(cls).items():

#print name
if name in ('entity',
'entity_id',
'event_id',
'attributes',
'__tablename__',
'history_table',
'id_column',
'relations'):

classvalues[name] = obj


if 'attributes' in classvalues:   # could have checked for any
variable names

#
# Id attribute
#

sequence_name = classvalues['__tablename__'] + '_sq'


cls.Id = Column('id', Integer, Sequence(sequence_name),
primary_key=True)


#
# Other attributes
#

for aname, nname, rname, childclass in
classvalues['attributes']:



#
# A relationship attribute
#


# The relationship

setattr(cls, rname, relationship(childclass,
uselist=False))

# The Name attribute

setattr(cls, nname, RelationNameAttribute(rname,
childclass))

#
# Table arguments
#

cls.__table_args__ = {'autoload': True,
  'useexisting' : True}



return DeclarativeMeta.__init__(cls, classname, bases, dict_)





BaseForConfig = declarative_base(metadata=metadata)
BaseForData = declarative_base(metaclass=DataMeta,  metadata=metadata)



class Country(BaseForConfig):
__tablename__  = 'country'
__table_args__ = {'autoload' : True}

class CustomerType(BaseForConfig):
__tablename__  = 'customer_type'
__table_args__ = {'autoload' : True}


class Address(BaseForData):
__tablename__  = 'address'
history_table  = 'address_history'
id_column  = 'addrnr'

entity = 'Address'
entity_id  = 2
event_id   = 103


attributes = [
('CountryKey', 'CountryName', 'country', Country),
]


class Customer(BaseForData):
__tablename__ = 'customer'


attributes = [
('TypeKey', 'TypeName', 'type', CustomerType)
]


addresses = relationship( Address, backref='customer')


c = Customer()
print dir(c)
print c.addresses


-- 
You received this message because you are subscribed to the Google Groups

Re: [sqlalchemy] mixing metaclass with redefining the primary key of an autoloaded table

2010-05-10 Thread Michael Bayer 
the clue here is that you're needing to call useexisting.   this means a 
Table that is already reflected will be pulled from the metadata. By adding 
your own id column, that blows away the id that was already reflected, 
which is what the addresses primary key is pointing to:

pdb at line 895 of properties.py

(Pdb) self.target
Table('address', MetaData(Engine(oracle://scott:ti...@localhost/xe)), 
Column('id', Integer(), table=address, primary_key=True, nullable=False, 
default=Sequence('address_sq', start=None, increment=None, optional=False)), 
Column(u'name', VARCHAR(length=10, convert_unicode=False, assert_unicode=None, 
unicode_error=None, _warn_on_bytestring=False), table=address), 
Column(u'country_id', NUMBER(precision=None, scale=None, asdecimal=False), 
ForeignKey(u'country.id'), table=address), Column(u'customer_id', 
NUMBER(precision=None, scale=None, asdecimal=False), 
ForeignKey(u'customer.id'), table=address), schema=None)

(Pdb) self.parent.mapped_table
Table(u'customer', MetaData(Engine(oracle://scott:ti...@localhost/xe)), 
Column('id', Integer(), table=customer, primary_key=True, nullable=False, 
default=Sequence('customer_sq', start=None, increment=None, optional=False)), 
Column(u'name', VARCHAR(length=10, convert_unicode=False, assert_unicode=None, 
unicode_error=None, _warn_on_bytestring=False), table=customer), 
Column(u'customer_type_id', NUMBER(precision=None, scale=None, 
asdecimal=False), ForeignKey(u'customer_type.id'), table=customer), 
schema=None)

(Pdb) self.target.c.customer_id
Column(u'customer_id', NUMBER(precision=None, scale=None, asdecimal=False), 
ForeignKey(u'customer.id'), table=address)

(Pdb) self.target.c.customer_id.references(self.parent.mapped_table.c.id)
False

(Pdb) self.target.c.customer_id.foreign_keys 
OrderedSet([ForeignKey(u'customer.id')])

So the first part of the answer is to reverse the order in which you declare 
Customer and Address, and don't use the 'useexisting' flag - leaving it off 
instead ensures you are reflecting tables in their order of dependency.   

Then the next issue is that you're defining relationships using class objects 
in your metaclass - this triggers a premature mapper compilation issue and you 
really should be using strings to reference classes at that level (i.e. 
relationship (classobj.__name__)) .   The RelationNameAttribute *way* oversteps 
its bounds by actually constructing a full blown Query() object before all 
classes are compiled - all of this has to occur using methods and descriptors 
so that nothing is asked of the mapper setup until all classes are defined.

You then have to ensure your two declarative bases are sharing the same 
_decl_class_registry.   I'd move the two bases to use one declarative base and 
have your metaclass check for attributes that indicate it should do its thing.

Also, trying to make declarative, which is so named because it is just that,  
perform complex tasks implicitly is going to be difficult since it was not 
designed for use cases like these.   




On May 10, 2010, at 11:37 AM, GHZ wrote:

 Hi,
 
 Using plain Declarative, I am able to redefine a primary key column
 that has been autoloaded, so that I can link it to an oracle sequence
 and give it a new name:
 
 Id = Column('id', Integer, Sequence('table_sq'), primary_key=True)
 
 
 However, if I then try to add some methods to the class using a
 metaclass, the foreign key relationships pointing to this column, seem
 to go missing.
 
 Apologies for not being able to track down the exact cause of this,
 but it seems to be something I am doing wrong with the combination of
 autloading, redefining the primary key, and adding to the class
 through a metaclass.
 
 The following example should work against an empty oracle schema.
 
 
 I get: sqlalchemy.exc.ArgumentError: Could not determine join
 condition between parent/child tables on relationship
 Customer.addresses.  Specify a 'primaryjoin' expression.  If this is a
 many-to-many relationship, 'secondaryjoin' is needed as well.
 
 
 http://python.pastebin.com/7V8MEfH3
 
 
 from sqlalchemy import MetaData, Column, Integer, Sequence,
 ForeignKey, create_engine
 from sqlalchemy.orm import relationship, sessionmaker, scoped_session
 from sqlalchemy.ext.declarative import DeclarativeMeta,
 declarative_base
 
 engine = create_engine('oracle://fred:f...@mig01')
 
 ddls = [
drop table customer,
drop table address,
drop table country,
drop table customer_type,
create table customer_type (
id number primary key,
name varchar2(10)),
create table country (
id number primary key,
name varchar2(10)),
create table customer (
id number primary key,
name varchar2(10),
customer_type_id number references customer_type),
create table address (
id number primary key,
name varchar2(10),
country_id number references country,
customer_id number