[sqlalchemy] SQL join on multiple columns with no foreign key
Hi all,
Suppose I have two tables t1 and t2 defined as following declarative
classes.
There is no foreign key setup in database between these two tables and
I want to write the join statement in sqlalchemy format which equates
to sql like this:
select t1.c11, t1.c12, t2.c23
from t1 join t2
on t1.c11==t2.c21 and
t1.c12==t2.c22 and
t2.c24 xx;
Anyone could inform me how to achieve this?
class T1(object):
__tablename__ = 't1'
_id = Column('id', Integer, primary_key = True)
c11 = Column('c11', Integer)
c12 = Column('c12', Integer)
class T2(object):
__tablename__ = 't2'
_id = Column('id', Integer, primary_key = True)
c21 = Column('c21', Integer)
c22 = Column('c22', Integer)
c23 = Column('c23', Integer)
c24 = Column('c24', Integer)
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Linsanity - Learn Chinese (Mandarin) faster by using flashcards with pictures
http://www.ichineseflashcards.com will help you learn Chinese (Mandarin) faster by using flashcards with pictures, thanks -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQL join on multiple columns with no foreign key
Nobody seems to be answering this one so here you go:
from sqlalchemy import and_
session.query(
T1.c11, T1.c12, T2.c23).join(
T2, and_(
T1.c11==T2.c21,
T1.c12==T2.c22,
T2.c24 xx)
)
relevant concepts:
querying individual columns:
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#querying
comparison operations, AND:
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#common-filter-operators
joining:
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#querying-with-joins
On Feb 26, 2012, at 10:12 AM, Flair wrote:
Hi all,
Suppose I have two tables t1 and t2 defined as following declarative
classes.
There is no foreign key setup in database between these two tables and
I want to write the join statement in sqlalchemy format which equates
to sql like this:
select t1.c11, t1.c12, t2.c23
from t1 join t2
on t1.c11==t2.c21 and
t1.c12==t2.c22 and
t2.c24 xx;
Anyone could inform me how to achieve this?
class T1(object):
__tablename__ = 't1'
_id = Column('id', Integer, primary_key = True)
c11 = Column('c11', Integer)
c12 = Column('c12', Integer)
class T2(object):
__tablename__ = 't2'
_id = Column('id', Integer, primary_key = True)
c21 = Column('c21', Integer)
c22 = Column('c22', Integer)
c23 = Column('c23', Integer)
c24 = Column('c24', Integer)
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Reducing instrumentation overhead for read-only entities
On Feb 19, 2012, at 5:24 AM, Andrey Popp wrote: Regarding rationale let me describe where I can find this feature useful: * You have a part of you tables read-only, so you only query data from them. SQLAlchemy doesn't have to track changes on objects of classes mapped to these tables. According to this stackoverflow post[1] we can get a not so negligible performance gain here. * We can map same table on same class using different mappers (one in read-only mode and other in persistence mode). That way we can use read-only mapper to query data in case we don't want to change it and we can use persistence mapper otherwise. I believe this will also lead to better correctness of application itself. Hello, After reading your stackoverflow post (implying that a profile revealed much instrumentation that you don't need), it occurred to me that you could use SQLAlchemy to generate the SQL query which you then pass directly to an execute method on a the underlying session bind to bypass generating SQLAlchemy models. Ideally, you could further shrink the query by only requesting the columns you actually need in your calculations. This seems at least the least-instrusive approach before you jump to C. Cheers, M -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
