On Thu, Aug 11, 2011 at 2:12 PM, Michael Bayer <[email protected]>wrote:
> Just FYI the Oreilly book is super, duper, extremely old and out of date.
> A good read through the documentation on the SQLAlchemy site should be more
> complete at this point and up to date, also stressing a whole set of new
> techniques that weren't available when the OReilly book was written (it was
> written against version 0.3).
>
OK, I think I am getting the hang of this. My "working" sample code in fact
didn't work when you tried to do anything with it.
I got the sample code working with two SQLite engines by dropping twophase,
which is not supported in SQLite. And I got the sample vertical partitioning
code working with two MySQL engines by removing the ForeignKey()
relationship and specifying the relationship explicitly in the mapper.
At this point, I was feeling adventurous, so I decided to try one MySQL
engine and one SQLite engine. It seems to work. Pretty cool. Example code
with comments below for anyone who might want to do this in the future. Run
"python vertical_test.py init" to populate the databases with data. On
successive runs, just run "python vertical_test.py":
#!/usr/bin/python
import sys
from sqlalchemy import *
from sqlalchemy.orm import *
engine1 = create_engine('mysql://car:cargofast@localhost/car_res')
engine2 = create_engine('sqlite:///x.db')
engine1.echo = engine2.echo = True
metadata = MetaData()
class Product(object):
__table__ = Table( 'product', metadata,
Column('sku', String(20), primary_key=True),
Column('msrp', Numeric))
class ProductSummary(object):
__table__ = Table( 'product_summary', metadata,
Column('sku', String(20), primary_key=True),
Column('name', Unicode(255)),
Column('description', Unicode(255)),
mysql_engine='InnoDB')
# create tables in different databases:
Product.__table__.create(bind=engine1,checkfirst=True)
ProductSummary.__table__.create(bind=engine2,checkfirst=True)
# map tables to classes and define relationships between the tables:
mapper(ProductSummary, ProductSummary.__table__,
properties=dict(
product=relationship(
Product,
# Since there is no database-level foreign key
relationship,
# we need to define primaryjoin and foreign_keys
explicitly
# so SQLAlchemy understands how the tables are
connected:
primaryjoin=Product.__table__.c.sku ==
ProductSummary.__table__.c.sku,
foreign_keys=[Product.__table__.c.sku],
backref=backref('summary', uselist=False)
)
)
)
mapper(Product, Product.__table__)
# Create session, and bind each class to the appropriate engine:
Session = sessionmaker()
Session.configure(binds={Product:engine1, ProductSummary:engine2})
session = Session()
# Run with "init" as first argument to create tables and populate them
# with data:
# Run with "init" as first argument to create tables and populate them
# with data:
if __name__ == "__main__" and len(sys.argv) == 2 and sys.argv[1] == "init":
# create records using statements:
stmt = Product.__table__.insert()
engine1.execute(
stmt,
[
dict(sku="123", msrp=12.34),
dict(sku="456", msrp=22.12)
])
stmt = ProductSummary.__table__.insert()
engine2.execute(
stmt,
[
dict(sku="123", name="Shoes", description="Some
Shoes"),
dict(sku="456", name="Pants", description="Some
Pants"),
])
# or create records using ORM:
a = Product()
a.sku = "blarg"
session.add(a)
b = ProductSummary()
b.sku = a.sku
b.name = "blarg"
b.description = "some blarg"
session.add(b)
session.commit()
# Query records and SQLAlchemy relationships will help you to grab related
records
# from totally disparate database engines:
for p in session.query(Product):
print("PRODUCT INFO:",p.sku, p.msrp, p.summary.name,
p.summary.description)
Enjoy,
Daniel
--
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.
