Hello, here is the answer:
# -*- coding: utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, Unicode,
MetaData, ForeignKey
from sqlalchemy.orm import sessionmaker, mapper,
dynamic_loader,backref, relation, composite, comparable_property,
contains_eager,aliased
from sqlalchemy.sql import text, and_
from orm.object.factory import make_papped_class
from sqlalchemy.orm import PropComparator
from common import Application
app = Application()
session = app.session
metadata = app.metadata
"""
Book :
isbn -> integer
translations -> many translations
prices -> many prices
Translation :
book -> FK to book
laguage -> FK to Language (oneTOone)
title -> string
Language :
code -> string
name -> string
Currency :
code -> string
name -> string
Price :
currency -> FK
book ->FK
brutto -> int
netto -> int
"""
book_table = Table('book', metadata,
Column('isbn', Integer, primary_key=True)
)
language_table = Table('language', metadata,
Column('code', String(5), primary_key=True),
Column('name', Unicode(20)),
)
currency_table = Table('currency', metadata,
Column('code', String(5), primary_key=True),
Column('name', Unicode(20)),
)
translation_table = Table('translation', metadata,
Column('book_id', Integer,
ForeignKey(book_table.c.isbn)),
Column('language_id', String(5),
ForeignKey(language_table.c.code)),
Column('title', Unicode(512)),
)
price_table = Table('price', metadata,
Column('currency_id', String(5),
ForeignKey(currency_table.c.code)),
Column('book_id', Integer,
ForeignKey(book_table.c.isbn)),
Column('brutto', Integer),
Column('netto', Integer)
)
metadata.create_all()
#create objects
class Book(object):
def __init__(self,isbn):
self.isbn = isbn
class Language(object):
def __init__(self,code,name):
self.code = code
self.name = name
class Currency(object):
def __init__(self,code,name):
self.code = code
self.name = name
class Translation(object):
def __init__(self,book_id,language_id,title):
self.book_id = book_id
self.language_id = language_id
self.title = title
class Price(object):
def __init__(self,currency_id,book_id,brutto,netto):
self.currency_id = currency_id
self.book_id = book_id
self.brutto = brutto
self.netto = netto
mapper(Book,book_table)
mapper(Language,language_table)
mapper(Currency,currency_table)
mapper(Translation,translation_table,properties = {
'book': relation(Book,
lazy = False, #1 note lazy here, it means that
we
# will use lazy loading (more
details in the docs
backref = backref('translations',lazy = False)),
'language': relation(Language,
uselist = False,#2 note uselist, it means
#we use one-to-one instead of
one-to-many
lazy = False),
},
primary_key = [translation_table.c.book_id,
translation_table.c.language_id]
#explicit primary key is needed when SQLA can not assemble the one for
you automatically
);
mapper(Price,price_table,properties = {
'currency': relation(Currency,lazy = False),
'book': relation(Book,lazy = False, backref =
backref('prices',lazy = False))
},primary_key = [price_table.c.book_id,price_table.c.currency_id]);
session = app.session
if False: #change this to True to issue add statement
session.add(Language('en',u'English'))
session.add(Language('ru',u'Русский'))
session.add(Currency('usd',u'Dollar'))
session.add(Currency('rub',u'Рубль'))
session.add(Book(1))
session.add(Book(2))
session.flush()
session.add(Translation(book_id = 1, language_id = 'en', title =
u'The book'))
session.add(Translation(book_id = 1, language_id = 'ru', title =
u'Книжка'))
session.add(Translation(book_id = 2, language_id = 'en', title =
u'Book'))
session.add(Translation(book_id = 2, language_id = 'ru', title =
u'Книжка2'))
session.add(Price(book_id = 1, currency_id = 'usd', brutto = 12,
netto = 20))
session.add(Price(book_id = 1, currency_id = 'rub', brutto = 250,
netto = 500))
session.add(Price(book_id = 2, currency_id = 'usd', brutto = 10,
netto = 18))
session.add(Price(book_id = 2, currency_id = 'rub', brutto = 200,
netto = 440))
"""
would like now to get books that:
isbn>1
translation.title in english starts with "The" ( I don't need other
languages for this select)
price.netto < 100 USD ( I don't need other prices, only in USD for
this select)
There are 5 Tables to join.
Lets say I have 100 objects maching, I dont want to hit the db 100
times.
"""
# the common approach here, as Michael says,
# is to construct the query as you would do it yourself
# and to tel sql alchemy where to pick the data for objects from
#aliases to control the query
ATranslation = aliased(Translation)
APrice = aliased(Price)
books = session.query(Book)\
.outerjoin((ATranslation,Book.translations))\
.outerjoin((APrice,Book.prices))\
.filter(and_(
Book.isbn >= 1,
ATranslation.language_id == 'en',
ATranslation.title.like(u'The%'),
APrice.currency_id == 'usd',
APrice.netto < 25,))\
.options(contains_eager('translations',alias =
ATranslation))\
.options(contains_eager('prices',alias = APrice))
"""
.outerjoin((ATranslation,Book.translations)) - creates explicit
join to the named alias,
and tells that this join implements Book.translations relation
ATranslation.language_id == 'en', - now we can use the named alias
to issue filter statements
.options(contains_eager('translations',alias = ATranslation)) -
now refer to the note #1,
this statement says to SQLA that it needs to load data for the
translation relation from this alias
"""
"""
at the end the query gives you the following statement (MySQL):
SELECT book.isbn AS book_isbn,
currency_1.code AS currency_1_code,
currency_1.name AS currency_1_name,
price_1.currency_id AS price_1_currency_id,
price_1.book_id AS price_1_book_id,
price_1.brutto AS price_1_brutto,
price_1.netto AS price_1_netto,
language_1.code AS language_1_code,
language_1.name AS language_1_name,
translation_1.book_id AS translation_1_book_id,
translation_1.language_id AS translation_1_language_id,
translation_1.title AS translation_1_title
FROM book
LEFT OUTER JOIN translation AS translation_1
ON book.isbn = translation_1.book_id
LEFT OUTER JOIN price AS price_1
ON book.isbn = price_1.book_id
LEFT OUTER JOIN currency AS currency_1
ON currency_1.code = price_1.currency_id
LEFT OUTER JOIN language AS language_1
ON language_1.code = translation_1.language_id
WHERE
translation_1.language_id = %s
AND translation_1.title LIKE %s
AND price_1.currency_id = %s
AND price_1.netto < %s
with the bind args as:
['en', 'The%', 'rub', 25]
If it's not what you expected, let me know
"""
#now when you access the results, SQLA issues no additional queries
for you
print [(book.isbn, book.translations[0].title,book.prices[0].netto,)
for book in books]
"""
a helper application class used in this example (from common import
Application)
class Application(object):
url = URL( drivername = 'mysql',
username = 'root',
password = '',
host = 'localhost',
port = '3306',
database = 'test',
query = {'charset':'utf8'})
def __init__(self,echo = True):
engine = create_engine(Application.url,encoding = 'utf-8',echo
= echo)
Session = sessionmaker(bind=engine)
self.session = Session()
self.metadata = MetaData()
self.metadata.bind = self.session.connection()
def close(self):
self.session.commit()
self.session.close()
"""
app.close()
On Sep 22, 10:25 pm, g00fy <[EMAIL PROTECTED]> wrote:
> I have this problem with setting relations with specific models:
>
> Book :
> isbn -> integer
> translations -> many translations
> prices -> many prices
>
> Translation :
> book -> FK to book
> laguage -> FK to Language (oneTOone)
> title -> string
>
> Language :
> code -> string
> name -> string
>
> Currency :
> code -> string
> name -> string
>
> Price :
> currency -> FK
> book ->FK
> brutto -> int
> netto -> int
>
> so those are my models.
>
> I would like now to get books that:
> isbn>1
> translation.title in english starts with "The" ( I don't need other
> languages for this select)
> price.netto < 100 USD ( I don't need other prices, only in USD for
> this select)
>
> There are 5 Tables to join.
> Lets say I have 100 objects maching, I dont want to hit the db 100
> times.
>
> I am begginer in SQLAlchemy so please understand my maybe "Stupid"
> question.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
