Thnx a lot Alex! I already love Pylons and SQLAlchemy!
On 23 Wrz, 12:16, Alex K <[EMAIL PROTECTED]> wrote:
> 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 sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
