[sqlalchemy] [Q] Struggle with exists

[Ladislav Lenart]

Hello.

I don't know how to write the following query in SA (using constructs such as
query, select, exists):

SELECT EXISTS(
SELECT 1
FROM
imported_client_share
JOIN imported_partner_share ON imported_client_share.deal_id =
imported_partner_share.deal_id
JOIN deal ON imported_client_share.deal_id = deal.id
WHERE
imported_client_share.client_id = :client_id
AND imported_partner_share.partner_id = :partner_id
AND deal.external_id IS NULL
) OR EXISTS(
SELECT 1
FROM
client_share
JOIN partner_share ON client_share.deal_id = partner_share.deal_id
JOIN deal ON client_share.deal_id = deal.id
WHERE
client_share.client_id = :client_id
AND partner_share.partner_id = :partner_id
AND deal.external_id IS NULL
)

Can you help me?

(I have read tutorial and API documentation several times but I still don't get 
it.)


Thank you in advance,

Ladislav Lenart

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Relationship setup problem

[Werner]

Hi,

I have an ORM class:

class Wineracku(DeclarativeBase, mix.StandardColumnMixin):
__tablename__ = u'wineracku'

description = sa.Column(sa.Unicode(length=30))
shortdesc = sa.Column(sa.Unicode(length=10))

# only used with single bottle type units
maxcol = sa.Column(sa.Integer(), default=0)
maxrow = sa.Column(sa.Integer(), default=0)

fk_winerack_id = sautils.reference_col('winerack')
fk_combrack_id = sautils.reference_col('wineracku')


And I would like a relationship which relates to same table based on 
fk_combrack_id.


I tried this but combrack is always an empty list:
Wineracku.combrack = sao.relationship('Wineracku')

I tried this but combrack is always an empty list:
Wineracku.combrack = sao.relationship('Wineracku')

and this with the same result:
Wineracku.combrack = sao.relationship('Wineracku', 
primaryjoin=('Wineracku.fk_combrack_id==Wineracku.id'))


What am I doing wrong?

Werner

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Relationship setup problem

[Werner]

Hi,

Found it in the doc, the Adjacency List Relationship is what I wanted.

http://docs.sqlalchemy.org/en/latest/orm/relationships.html#adjacency-list-relationships

Werner

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] [Q] Struggle with exists

[Michael Bayer]

we should probably add a method to Query called exists() that just turns any 
query into EXISTS (SELECT 1), here's how to make it work for now

from sqlalchemy import exists

q1 = session.query(ImportedClientShare)
q1 = q1.join(ImportedPartnerShare,
ImportedClientShare.deal_id == ImportedPartnerShare.deal_id)
q1 = q1.join(Deal, ImportedClientShare.deal_id == Deal.id)
q1 = q1.filter(
ImportedClientShare.client_id == client_id,
ImportedPartnerShare.partner_id == partner_id,
Deal.external_id != None,
)

q2 = session.query(ClientShare)
q2 = q2.join(PartnerShare, ClientShare.deal_id == PartnerShare.deal_id)
q2 = q2.join(Deal, ClientShare.deal_id == Deal.id)

q2 = q2.filter(
ClientShare.client_id == client_id,
PartnerShare.partner_id == partner_id,
Deal.external_id == None,
)

q = session.query(exists(q1.with_entities('1').statement) | 
exists(q2.with_entities('1').statement))



On Mar 1, 2013, at 7:41 AM, Ladislav Lenart lenart...@volny.cz wrote:

 SELECT EXISTS(
SELECT 1
FROM
imported_client_share
JOIN imported_partner_share ON imported_client_share.deal_id =
 imported_partner_share.deal_id
JOIN deal ON imported_client_share.deal_id = deal.id
WHERE
imported_client_share.client_id = :client_id
AND imported_partner_share.partner_id = :partner_id
AND deal.external_id IS NULL
 ) OR EXISTS(
SELECT 1
FROM
client_share
JOIN partner_share ON client_share.deal_id = partner_share.deal_id
JOIN deal ON client_share.deal_id = deal.id
WHERE
client_share.client_id = :client_id
AND partner_share.partner_id = :partner_id
AND deal.external_id IS NULL
 )

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Is querying a relationship on an AbstractBaseClass possible?

[Derek Litz]

Sorry, was out due to moving... BLEH.

I like second solution since I then don't need to declare the relationship 
on every sub class.

Basicly:

1. Configure as I had previously
2. Make sure configure_mappers() is ran after all sub classes are 
declared.
3. Monkey Wrench the base abstract class so it can also be queried on 
the relationship.

# See code above..
import sqlalchemy
sqlalchemy.orm.configure_mappers()
AbstractConcreteAbstraction.something = relationship(Something)

Nice work! :)

On Wednesday, February 27, 2013 8:44:00 PM UTC-6, Michael Bayer wrote:

 simpler, just stick the relationship on ACA:

 session = Session(engine)
 print session.query(ConcreteConcreteAbstraction).filter(
 ConcreteConcreteAbstraction.something.has(id=1)).all()

 AbstractConcreteAbstraction.something = relationship(Something)
 print session.query(AbstractConcreteAbstraction).filter(
 AbstractConcreteAbstraction.something.has(id=1)).all()



 On Feb 27, 2013, at 9:41 PM, Michael Bayer 
 mik...@zzzcomputing.comjavascript: 
 wrote:

 just features that weren't anticipated (I never use concrete inheritance). 
here's what will work for now.

 class AbstractConcreteAbstraction(AbstractConcreteBase, sqlite):
 __table_args__ = (UniqueConstraint('derpa',
 'derp'),)
 id = Column(Integer, primary_key=True)
 derpa = Column(Integer)
 derp = Column(Integer)

 @declared_attr
 def something_id(cls):
 return Column(ForeignKey(Something.id))

 class ConcreteConcreteAbstraction(AbstractConcreteAbstraction):
 __tablename__ = u'cca'
 __mapper_args__ = {'polymorphic_identity': 'ccb',
 'concrete': True}

 something = relationship(Something)

 import sqlalchemy
 sqlalchemy.orm.configure_mappers()

 AbstractConcreteAbstraction.something = relationship(Something)

 sqlite.metadata.create_all()

 # Works
 print session.query(ConcreteConcreteAbstraction).filter(
 ConcreteConcreteAbstraction.something.has(id=1)).all()
 # Don't work
 print session.query(AbstractConcreteAbstraction).filter(
 AbstractConcreteAbstraction.something.has(id=1)).all()



 On Feb 27, 2013, at 8:06 PM, Derek Litz litzo...@gmail.com javascript: 
 wrote:

 Having fun with AbstractBaseClasses tonight :) ... Anyways am I missing 
 something here as well?

 I tried playing with querying the AbstractBaseClass and filtering on sub 
 classes but that just produced
 a query that did not execute.

 from sqlalchemy.engine import Engine
 from sqlalchemy import event
 from sqlalchemy import (Column, Integer, Unicode, DateTime, ForeignKey,
 Boolean, Numeric, Time)


 # Taken from http://docs.sqlalchemy.org/ru/latest/dialects/sqlite.html
 @event.listens_for(Engine, connect)
 def set_sqlite_pragma(dbapi_connection, connection_record):
 cursor = dbapi_connection.cursor()
 cursor.execute(PRAGMA foreign_keys=ON)
 cursor.close()


 from sqlalchemy import create_engine
 from sqlalchemy.ext.declarative import (declarative_base, declared_attr,
 AbstractConcreteBase)
 from sqlalchemy.orm import sessionmaker, relationship, backref, 
 object_session

 engine = create_engine('sqlite:///test.db')
 sqlite = declarative_base(bind=engine)
 get_session = sessionmaker(bind=engine)
 session = get_session()

 from sqlalchemy.schema import UniqueConstraint


 class Something(sqlite):
 __tablename__ = u'something'
 id = Column(Integer, primary_key=True)


 class AbstractConcreteAbstraction(AbstractConcreteBase, sqlite):
 __table_args__ = (UniqueConstraint('derpa',
 'derp'),)
 id = Column(Integer, primary_key=True)
 derpa = Column(Integer)
 derp = Column(Integer)

 @declared_attr
 def something_id(cls):
 return Column(ForeignKey(Something.id))

 @declared_attr
 def something(cls):
 return relationship(Something)


 class ConcreteConcreteAbstraction(AbstractConcreteAbstraction):
 __tablename__ = u'cca'
 __mapper_args__ = {'polymorphic_identity': 'ccb',
 'concrete': True}


 import sqlalchemy
 sqlalchemy.orm.configure_mappers()
 sqlite.metadata.create_all()

 # Works
 print session.query(ConcreteConcreteAbstraction).filter(
 ConcreteConcreteAbstraction.something.has(id=1)).all()
 # Don't work
 print session.query(AbstractConcreteAbstraction).filter(
 AbstractConcreteAbstraction.something.has(id=1)).all()


 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+...@googlegroups.com javascript:.
 To post to this group, send email to sqlal...@googlegroups.comjavascript:
 .
 Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
 For more options, visit https://groups.google.com/groups/opt_out.
  
  



 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop 

Re: [sqlalchemy] [Q] Struggle with exists

[Ladislav Lenart]

Thank you!

I was missing the following bit(s):

q = session.query(
exists(q1.with_entities('1').statement)
| exists(q2.with_entities('1').statement)
)

I knew about Query.statement but I did not figure out how to combine that with
OR. It did not occur to me that I can write session.query(or_(...)) directly.
with_entities() construct is also new to me, though I presume that SQL engines
optimize SELECTs in EXISTS automatically.

I must admit that I did not understand your example the first time I saw it. But
once I run it in the debugger, everything has become clear and logical:

session.query( # renders top-level SELECT
or_(
# q.exists() is a core construct and thus cannot accept
# a query object. q.statement returns select represented
# by the query, which IS a core construct.
# q.with_entities('1') replaces q's SELECT... part.
exists(q1.with_entities('1').statement),
exists(q2.with_entities('1').statement),
)
)

One unrelated question: What is the difference between Query.add_column() and
Query.add_entity()?


Thank you again,

Ladislav Lenart


On 1.3.2013 18:01, Michael Bayer wrote:
 we should probably add a method to Query called exists() that just turns any 
 query into EXISTS (SELECT 1), here's how to make it work for now
 
 from sqlalchemy import exists
 
 q1 = session.query(ImportedClientShare)
 q1 = q1.join(ImportedPartnerShare,
 ImportedClientShare.deal_id == 
 ImportedPartnerShare.deal_id)
 q1 = q1.join(Deal, ImportedClientShare.deal_id == Deal.id)
 q1 = q1.filter(
 ImportedClientShare.client_id == client_id,
 ImportedPartnerShare.partner_id == partner_id,
 Deal.external_id != None,
 )
 
 q2 = session.query(ClientShare)
 q2 = q2.join(PartnerShare, ClientShare.deal_id == PartnerShare.deal_id)
 q2 = q2.join(Deal, ClientShare.deal_id == Deal.id)
 
 q2 = q2.filter(
 ClientShare.client_id == client_id,
 PartnerShare.partner_id == partner_id,
 Deal.external_id == None,
 )
 
 q = session.query(exists(q1.with_entities('1').statement) | 
 exists(q2.with_entities('1').statement))
 
 
 
 On Mar 1, 2013, at 7:41 AM, Ladislav Lenart lenart...@volny.cz wrote:
 
 SELECT EXISTS(
SELECT 1
FROM
imported_client_share
JOIN imported_partner_share ON imported_client_share.deal_id =
 imported_partner_share.deal_id
JOIN deal ON imported_client_share.deal_id = deal.id
WHERE
imported_client_share.client_id = :client_id
AND imported_partner_share.partner_id = :partner_id
AND deal.external_id IS NULL
 ) OR EXISTS(
SELECT 1
FROM
client_share
JOIN partner_share ON client_share.deal_id = partner_share.deal_id
JOIN deal ON client_share.deal_id = deal.id
WHERE
client_share.client_id = :client_id
AND partner_share.partner_id = :partner_id
AND deal.external_id IS NULL
 )

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.