The problem
I am extracting data from one database and loading the data into another.
There isn't much transformation of the data other than the columns are
different in each table but a simple conversion of pascal casing to
lower_case can be done. For example "TestName" in my source database is
"test_name" in my destination.
I would like to map both tables that have different column names
automatically somehow so that when I move the data I'm not doing it
manually. Here is an example of two models.
Model Examples
Source Database
class TestUL(Base):
__tablename__ = 'Tests'
TestKey = Column(UNIQUEIDENTIFIER, primary_key=True)
TestName = Column(String(200), nullable=False)
Description = Column(String(80))
EffectiveDate = Column(Date, nullable=False)
EndDate = Column(Date)
CreatedByUserName = Column(String(50), nullable=False)
CreatedDate = Column(DateTime, nullable=False)
LastUpdatedByUserName = Column(String(50))
LastUpdatedDate = Column(DateTime)
LastSaveID = Column(UNIQUEIDENTIFIER)
Destination Database
class Test(db.Model):
__tablename__ = 'test'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
test_key = db.Column(UUID)
test_name = db.Column(db.String(200), nullable=False)
description = db.Column(db.String(80))
effective_date = db.Column(db.Date, nullable=False)
end_date = db.Column(db.Date)
created_by_user_name = db.Column(db.String(50), nullable=False)
created_date = db.Column(db.DateTime, nullable=False)
last_updated_by_user_name = db.Column(db.String(50))
last_updated_date = db.Column(db.DateTime)
last_save_id = db.Column(UUID)
What I'm doing now
def load_tests(self):
for test in
self.source_session.query(TestUL).order_by(TestUL.TestName).all():
self.destination_session.add(Test(test_key=test.TestKey,
test_name=test.TestName,
description=test.Description,
effective_date=test.EffectiveDate,
end_date=test.EndDate,
created_by_user_name=test.CreatedByUserName,
created_date=test.CreatedDate,
last_updated_by_user_name=test.LastUpdatedByUserName,
last_updated_date=test.LastUpdatedDate,
last_save_id=test.LastSaveID))
self.destination_session.commit()
Is there an easier way to do this mapping? I need to do this mapping for
many tables I'm trying another method right now reflecting the source table
and altering the column names but I'm still not putting two and two
together. Example:
def convert(name):
s1 = re.sub('(.)([A-Z][a-z]+)', r'\1_\2', name)
return re.sub('([a-z0-9])([A-Z])', r'\1_\2', s1).lower()
# From the sqlalchemy [email protected]_for(Table, "column_reflect")
def column_reflect(inspector, table, column_info):
column_info['key'] = convert(column_info['name'])
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
