Hello Everyone,
I am using SA with Oracle and i am trying to use dual table in one my of
query to select records based on dual table where condition.
Below is my raw query which i wanted to convert it to ORM:
Select name, fullname from users where exists (
Select 1 from
( select 'PSD' name from dual
union all
select 'ARD' name from dual
) A
where
users.name=A.name
)
I have attached my sample program 'sa.py' with this post.
If any one helps me to build Query using session.query will helps me to
remove this raw_query.
Thanks,
PV
--
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.
For more options, visit https://groups.google.com/d/optout.
from datetime import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, DateTime
engine = create_engine('sqlite:///:memory:', echo=True)
#This has to replace with oracle
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
dob = Column(DateTime, default=datetime.now)
Base.metadata.create_all(engine)
u = User()
u.name = 'PSD'
u.fullname = 'PSD TEST'
u.password = "****"
u.dob = datetime.now()
session.add(u)
u1 = User()
u1.name = 'ARD'
u1.fullname = 'ARD TEST'
u1.password = "****"
u1.dob = datetime.now()
session.add(u1)
u1 = User()
u1.name = 'ABR'
u1.fullname = 'ABR TEST'
u1.password = "****"
u1.dob = datetime.now()
session.add(u1)
session.commit()
from sqlalchemy.sql.expression import select, union_all, alias
from sqlalchemy.sql import and_, or_, between, exists
from sqlalchemy.orm import aliased
raw_qry = """Select name, fullname from users where exists (
Select 1 from
( select 'PSD' name from dual
union all
select 'ARD' name from dual
) A
where
users.name=A.name
)
"""
# print session.execute(s_qry)
# Wanted to convert to session.query instead raw query.
# Failed !!!
qry = session.query(User)
s1 = select(["PBD name"], "dual")
s2 = select(["PRD name"], "dual")
u_l = union_all(s1, s2)
qry = qry.filter(exists(u_l.select()))
#
print qry
# qry.all()
