I am thinking about moving from home grown data access classes, to
sqlalchemy for a simple ETL tool for moving from legacy databases - at
least for the Extract and simplest of Transformations.
Example of what I'm trying to achieve.. if I have a customer table in
the legacy database, and a new_customer table (I show two examples of
target databases) in the target system.
(source and target are currently Oracle)
create table old_customer (cunr number, cutype char(1));
insert into customer values (1,'A');
--create table new_1_customer(id number, type number);
--create table new_2_customer(cunr number, cutype number);
I want to use SA to provide a customer object containing both old and
new columns, with any required column level transformations.
e.g. in the first example, I want an object with the following
properties
customer.o_cunr
customer.o_cutype
customer.id
customer.type (converted from cutype through mapping function)
and in the second:
customer.o_cunr
customer.o_cutype
customer.cunr
customer.cutype (converted from cutype through mapping function)
eventually I want to provide these mapping separate from the main
class definition.. but I start with them inline:
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine=create_engine('oracle://gw_val:[EMAIL PROTECTED]')
Base = declarative_base(engine=engine)
class Customer(Base):
__tablename__ = 'old_customer'
__table_args__ = {'autoload':True}
__mapper_args__ = {'column_prefix' : 'o_'}
id = Column('cunr', Integer, primary_key=True)
def _get_cutype(self):
# Trival mapping
def mapper(old_type):
return ord(old_type)
return mapper(self.o_cutype)
type = property(_get_cutype)
Session = sessionmaker()
session = Session()
customer = session.query(Customer).filter_by(id=1).one()
print customer.id
print 'type mapping %s -> %d' % (customer.o_cutype, customer.type)
Which works fine.
However, I can't use the same construct for the second example (where
the columns have the same names)
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine=create_engine('oracle://gw_val:[EMAIL PROTECTED]')
Base = declarative_base(engine=engine)
class Customer(Base):
__tablename__ = 'old_customer'
__table_args__ = {'autoload':True}
__mapper_args__ = {'column_prefix' : 'o_'}
o_cunr = Column('cunr', Integer, primary_key=True)
def _get_cutype(self):
# Trival mapping
def mapper(old_type):
return ord(old_type)
return mapper(self.o_cutype)
cutype = property(_get_cutype)
Session = sessionmaker()
session = Session()
customer = session.query(Customer).filter_by(o_cunr=1).one()
print customer.o_cunr
print 'type mapping %s -> %d' % (customer.o_cutype, customer.cutype)
Gives me:
Traceback (most recent call last):
File "./test2.py", line 33, in <module>
print 'type mapping %s -> %d' % (customer.o_cutype,
customer.cutype)
AttributeError: 'Customer' object has no attribute 'o_cutype'
Any suggestions on the best way to code this so I don't overwrite the
prefixed columns if they happen to have the same name in legacy and
target schemas, also with a long term goal of taking the explicit
mapping code out of each class writing something more like this:
class Customer(MyBase):
__tablename__ = 'old_customer'
__table_args__ = {'autoload':True}
__column_mappings__ = {
('cunr', 'id', None),
('cutype', 'type', lambda x : ord(x))
}
Thanks
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
