TurboGears with existing MySQL database

[Tina Matter]

I have a few questions regarding existing MySQL databases.
If you have an already existing MySQL database, does TurboGears
automatically create a module with the object classes for you?
Or would you basically have to re-create everything in TurboGears?
Also, if you decide later to change or delete a field in a table, would
that change automatically be reflected in all the code automatically
(like WebObjects does)? Or would you have to find all the code that
references that table and make the adjustments yourself?
What if you make changes through PHPMyAdmin? Does TurboGears recognize
that something has changed?

Thank you.
Tina Matter

--

-------------------------------------------------------
*Tina Matter*
Web Software Developer
Department of Mechanical Engineering
University of Michigan
734-763-3184
-------------------------------------------------------

[Jeff Miller]

Tina Matter wrote:
> I have a few questions regarding existing MySQL databases.
> If you have an already existing MySQL database, does TurboGears
> automatically create a module with the object classes for you?
> Or would you basically have to re-create everything in TurboGears?
> Also, if you decide later to change or delete a field in a table, would
> that change automatically be reflected in all the code automatically
> (like WebObjects does)? Or would you have to find all the code that
> references that table and make the adjustments yourself?
> What if you make changes through PHPMyAdmin? Does TurboGears recognize
> that something has changed?
>
> Thank you.
> Tina Matter
>
>

Tina,

If your using SQLObject, then check this page:
http://docs.turbogears.org/1.0/GettingStarted/UseDatabase under Define
Data Models.

I haven't learned SQLAlchemy yet (next thing to do).

HTH,

Jeff

[tinab]

This is a helpful start. Thank you.
However, if we decide to use TurboGears, we would need
to use SQLAlchemy because we have an existing database
that has multiple columns that represent primary keys in
many of our tables.
I was hoping that there would be a way to generate the
python model code from an existing MySQL database,
and then be able to update that model code automatically
or through some program whenever changes are made to
the database through something like PHPMyAdmin.
It sounds like it would be more difficult to write code
in TurboGears if the database is not represented in the
python module.

Tina

[Christopher Arndt]

tinab schrieb:

> I was hoping that there would be a way to generate the
> python model code from an existing MySQL database,

Have you read this?

http://www.sqlalchemy.org/docs/metadata.html#metadata_tables_reflecting

Chris

[iain duncan]

On Tue, 2007-17-07 at 20:57 +0200, Christopher Arndt wrote:
> tinab schrieb:
> > I was hoping that there would be a way to generate the
> > python model code from an existing MySQL database,

I just did this with SQLalchemy and it was surprisingly easy. You will
probably want to go straight to the SA docs. All you need to do is make
a stub class and then use assign_mapper to map your stub class to the
existing table. You will probably also need to explicitly state the
relationship keys, which can be found in the SA docs in the advanced
data mapping section.

Here is an example from my project, please excuse the poor formatting:

# to load the table definition by introspecting the database
heard_table = Table('how_heard', metadata, autoload=True)
class Heard(object):
pass
assign_mapper(session.context, Heard, heard_table )

champions_table = Table('users', metadata, autoload=True)
class Champions(object):
pass
assign_mapper(session.context, Champions, champions_table )

# to load the table definition by introspecting the database
orders_table = Table('orders', metadata, autoload=True)
class Orders(object):
pass
assign_mapper(session.context, Orders, orders_table )

locations_table = Table('locations', metadata, autoload=True)
class Locations(object):
# attribute for total number of boxes ordered by a location
def _box_total(self):
count = 0
for order in self.orders:
if order.status == 'verified': count += order.quantity
return count
box_total = property( _box_total )

locations_dates_table = Table('locations_dates', metadata,
autoload=True )
class LocationDates(object):
pass

# LocationDates mapper
assign_mapper(session.context, LocationDates, locations_dates_table,
primary_key= [ locations_dates_table.c.location_id,
locations_dates_table.c.date ],
properties=dict(
locations=relation( Locations,
primaryjoin=and_( locations_table.c.location_id==locations_dates_table.c.location_id ), foreignkey=locations_dates_table.c.location_id ),
)
)


# because the old tables don't have foreign key defs, we need to
explicitly state them here
assign_mapper(session.context, Locations, locations_table,
properties=dict(
orders=relation(Orders,
primaryjoin=and_( orders_table.c.location_id==locations_table.c.location_id ), foreignkey=orders_table.c.location_id ),
champion=relation( Champions,
primaryjoin=and_( locations_table.c.user_id==champions_table.c.user_id ), foreignkey=locations_table.c.user_id ),
dates=relation( LocationDates,
primaryjoin=and_( locations_table.c.location_id==locations_dates_table.c.location_id ),
foreignkey=locations_dates_table.c.location_id,
order_by=locations_dates_table.c.date ),
) )

[Paul Howell]

Tina,

And the code here may help too:

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode

and the addon AutoCode2 is tested with MySQL, it appears.
Paul

[tinab]

This is very helpful. Thank you. :-)
Thanks for the sample code too. It's always easier to start
with something that you know works.

Tina

[tinab]

This is really cool. I'll have to run this by my supervisor
to let her know that this is available.

Thank you.
Tina