def test_except_style3(self):
# aaa, bbb, ccc - (aaa, bbb, ccc - (ccc)) = ccc
e = except_(
select([t1.c.col3]), # aaa, bbb, ccc
except_(select([t2.c.col3]), select([t3.c.col3], t3.c.col3 == "ccc")), # aaa, bbb, ccc # ccc
)
eq_(e.execute().fetchall(), [("ccc",)])
eq_(e.alias("foo").select().execute().fetchall(), [("ccc",)])
def test_except_style3(self):
# aaa, bbb, ccc - (aaa, bbb, ccc - (ccc)) = ccc
e = except_(
select([t1.c.col3]), # aaa, bbb, ccc
except_(
select([t2.c.col3]), # aaa, bbb, ccc
select([t3.c.col3], t3.c.col3 == 'ccc'), # ccc
))
eq_(e.execute().fetchall(), [('ccc', )])
eq_(e.alias('foo').select().execute().fetchall(), [('ccc', )])
def test_except_style3(self):
# aaa, bbb, ccc - (aaa, bbb, ccc - (ccc)) = ccc
e = except_(
select([t1.c.col3]), # aaa, bbb, ccc
except_(
select([t2.c.col3]), # aaa, bbb, ccc
select([t3.c.col3], t3.c.col3 == 'ccc'), # ccc
)
)
eq_(e.execute().fetchall(), [('ccc',)])
eq_(e.alias('foo').select().execute().fetchall(), [('ccc',)])
def eval(self, builder):
""" return a query object
"""
dbh = builder._get_dbh()
tokens = self.value[1:]
expr_1 = self.value[0].eval( builder )
while tokens:
op = tokens[0]
eval_2 = tokens[1].eval( builder )
tokens = tokens[2:]
if op == '|':
eval_1 = sqla.union(eval_1, eval_2)
elif op == '&':
eval_1 = sqla.intersect(eval_1, eval_2)
elif op == ':':
eval_1 = sqla.except_(
dbh.session().query(dbh.Sample.id).filter(
dbh.Sample.id.in_( sqla.union(eval_1, eval_2)) ),
dbh.session().query(dbh.Sample.id).filter(
dbh.Sample.iid.n_( sqla.intersect(eval_1, eval_2)) )
)
q = dbh.session().query(dbh.Sample.id).filter( dbh.Sample.id.in_( eval_1 ) )
return q
def before_update(self, engine):
# save all added guideposts
sql = sa.except_(
sa.select([self.src.c.geom.ST_Transform(self.srid)]).where(
self.src.c.id.in_(self.src.select_add_modify())),
sa.select([self.c.geom
]).where(self.c.id.in_(self.src.select_add_modify())))
self.updates.add_from_select(engine, sql)
def test_compound(self):
t1 = table('t1', column('c1'), column('c2'), column('c3'))
t2 = table('t2', column('c1'), column('c2'), column('c3'))
self.assert_compile(union(t1.select(), t2.select()),
'SELECT t1.c1, t1.c2, t1.c3 FROM t1 UNION '
'SELECT t2.c1, t2.c2, t2.c3 FROM t2')
self.assert_compile(except_(t1.select(), t2.select()),
'SELECT t1.c1, t1.c2, t1.c3 FROM t1 MINUS '
'SELECT t2.c1, t2.c2, t2.c3 FROM t2')
def test_compound(self):
t1 = table('t1', column('c1'), column('c2'), column('c3'))
t2 = table('t2', column('c1'), column('c2'), column('c3'))
self.assert_compile(union(t1.select(), t2.select()),
'SELECT t1.c1, t1.c2, t1.c3 FROM t1 UNION '
'SELECT t2.c1, t2.c2, t2.c3 FROM t2')
self.assert_compile(except_(t1.select(), t2.select()),
'SELECT t1.c1, t1.c2, t1.c3 FROM t1 MINUS '
'SELECT t2.c1, t2.c2, t2.c3 FROM t2')
def test_except_style1(self):
e = except_(
union(select([t1.c.col3, t1.c.col4]), select([t2.c.col3, t2.c.col4]), select([t3.c.col3, t3.c.col4])),
select([t2.c.col3, t2.c.col4]),
)
wanted = [("aaa", "aaa"), ("aaa", "ccc"), ("bbb", "aaa"), ("bbb", "bbb"), ("ccc", "bbb"), ("ccc", "ccc")]
found = self._fetchall_sorted(e.alias().select().execute())
eq_(found, wanted)
def test_except_style1(self):
e = except_(union(
select([t1.c.col3, t1.c.col4]),
select([t2.c.col3, t2.c.col4]),
select([t3.c.col3, t3.c.col4]),
), select([t2.c.col3, t2.c.col4]))
wanted = [('aaa', 'aaa'), ('aaa', 'ccc'), ('bbb', 'aaa'),
('bbb', 'bbb'), ('ccc', 'bbb'), ('ccc', 'ccc')]
found = self._fetchall_sorted(e.alias().select().execute())
eq_(found, wanted)
def test_compound(self):
t1 = table("t1", column("c1"), column("c2"), column("c3"))
t2 = table("t2", column("c1"), column("c2"), column("c3"))
self.assert_compile(
union(t1.select(), t2.select()),
"SELECT t1.c1, t1.c2, t1.c3 FROM t1 UNION "
"SELECT t2.c1, t2.c2, t2.c3 FROM t2",
)
self.assert_compile(
except_(t1.select(), t2.select()),
"SELECT t1.c1, t1.c2, t1.c3 FROM t1 MINUS "
"SELECT t2.c1, t2.c2, t2.c3 FROM t2",
)
def test_compound(self):
t1 = table("t1", column("c1"), column("c2"), column("c3"))
t2 = table("t2", column("c1"), column("c2"), column("c3"))
self.assert_compile(
union(t1.select(), t2.select()),
"SELECT t1.c1, t1.c2, t1.c3 FROM t1 UNION "
"SELECT t2.c1, t2.c2, t2.c3 FROM t2",
)
self.assert_compile(
except_(t1.select(), t2.select()),
"SELECT t1.c1, t1.c2, t1.c3 FROM t1 MINUS "
"SELECT t2.c1, t2.c2, t2.c3 FROM t2",
)
def test_except_style2(self):
# same as style1, but add alias().select() to the except_().
# sqlite can handle it now.
e = except_(
union(select([t1.c.col3, t1.c.col4]), select([t2.c.col3, t2.c.col4]), select([t3.c.col3, t3.c.col4]))
.alias()
.select(),
select([t2.c.col3, t2.c.col4]),
)
wanted = [("aaa", "aaa"), ("aaa", "ccc"), ("bbb", "aaa"), ("bbb", "bbb"), ("ccc", "bbb"), ("ccc", "ccc")]
found1 = self._fetchall_sorted(e.execute())
eq_(found1, wanted)
found2 = self._fetchall_sorted(e.alias().select().execute())
eq_(found2, wanted)
def test_except_style2(self):
# same as style1, but add alias().select() to the except_().
# sqlite can handle it now.
e = except_(union(
select([t1.c.col3, t1.c.col4]),
select([t2.c.col3, t2.c.col4]),
select([t3.c.col3, t3.c.col4]),
).alias().select(), select([t2.c.col3, t2.c.col4]))
wanted = [('aaa', 'aaa'), ('aaa', 'ccc'), ('bbb', 'aaa'),
('bbb', 'bbb'), ('ccc', 'bbb'), ('ccc', 'ccc')]
found1 = self._fetchall_sorted(e.execute())
eq_(found1, wanted)
found2 = self._fetchall_sorted(e.alias().select().execute())
eq_(found2, wanted)
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union, union_all, except_, intersect
engine = create_engine('sqlite:///college.db', echo=True)
meta = MetaData()
conn = engine.connect()
addresses = Table('addresses', meta, Column('id', Integer, primary_key=True),
Column('st_id', Integer), Column('postal_add', String),
Column('email_add', String))
u = union(addresses.select().where(addresses.c.email_add.like('*****@*****.**')),
addresses.select().where(addresses.c.email_add.like('*****@*****.**')))
result = conn.execute(u)
result.fetchall()
u = union_all(
addresses.select().where(addresses.c.email_add.like('*****@*****.**')),
addresses.select().where(addresses.c.email_add.like('*****@*****.**')))
result = conn.execute(u)
result.fetchall()
u = except_(
addresses.select().where(addresses.c.email_add.like('*****@*****.**')),
addresses.select().where(addresses.c.postal_add.like('%Pune')))
result = conn.execute(u)
result.fetchall()
u = intersect(
addresses.select().where(addresses.c.email_add.like('*****@*****.**')),
addresses.select().where(addresses.c.postal_add.like('%Delhi')))
result = conn.execute(u)
result.fetchall()
print (res)
# SELECT "ADDRESSES".id, "ADDRESSES".st_id, "ADDRESSES".postal_add, "ADDRESSES".email_add FROM "ADDRESSES"
# WHERE "ADDRESSES".email_add LIKE ? UNION ALL SELECT "ADDRESSES".id, "ADDRESSES".st_id, "ADDRESSES".postal_add, "ADDRESSES".email_add
# FROM "ADDRESSES" WHERE "ADDRESSES".email_add LIKE ?
u = union_all(addresses.select().where(addresses.c.email_add.like('*****@*****.**')), addresses.select().where(addresses.c.email_add.like('*****@*****.**')))
result = conn.execute(u)
for res in result:
print (res)
# EXCEPT
# SELECT "ADDRESSES".id, "ADDRESSES".st_id, "ADDRESSES".postal_add, "ADDRESSES".email_add FROM "ADDRESSES"
# WHERE "ADDRESSES".email_add LIKE ? EXCEPT SELECT "ADDRESSES".id, "ADDRESSES".st_id, "ADDRESSES".postal_add, "ADDRESSES".email_add
# FROM "ADDRESSES" WHERE "ADDRESSES".email_add LIKE ?
u = except_(addresses.select().where(addresses.c.email_add.like('*****@*****.**')), addresses.select().where(addresses.c.email_add.like('*****@*****.**')))
result = conn.execute(u)
for res in result:
print (res)
# INTERSECT
# SELECT "ADDRESSES".id, "ADDRESSES".st_id, "ADDRESSES".postal_add, "ADDRESSES".email_add FROM "ADDRESSES"
# WHERE "ADDRESSES".email_add LIKE ? INTERSECT SELECT "ADDRESSES".id, "ADDRESSES".st_id, "ADDRESSES".postal_add, "ADDRESSES".email_add
# FROM "ADDRESSES" WHERE "ADDRESSES".email_add LIKE ?
u = intersect(addresses.select().where(addresses.c.email_add.like('*****@*****.**')), addresses.select().where(addresses.c.email_add.like('*****@*****.**')))
result = conn.execute(u)
for res in result:
print (res)
meta.drop_all(engine)
def real_update(self, forced=False):
if not self.xmlUrl: # Not a real feed
af=self.allFeeds()
for f in af:
f.update()
if self.lastModified:
mod=self.lastModified
else:
mod=datetime.datetime(1970, 1, 1)
if self.title:
statusQueue.put(u"Updating: "+ self.title)
d=fp.parse(self.xmlUrl, etag=self.etag, modified=mod.timetuple())
try:
self.lastUpdated=datetime.datetime.now()
elixir.session.commit()
except:
elixir.session.rollback()
if d.status==304: # No need to fetch
return
if d.status==301: # Permanent redirect
self.xmlUrl=d.href
if d.status==410: # Feed deleted. FIXME: tell the user and stop trying!
return
self.updating=True
# Notify feed is updating
# feedStatusQueue.put([0, self.id])
posts=[]
for post in d['entries']:
try:
# Date can be one of several fields
if 'created_parsed' in post:
dkey='created_parsed'
elif 'published_parsed' in post:
dkey='published_parsed'
elif 'modified_parsed' in post:
dkey='modified_parsed'
else:
dkey=None
if dkey and post[dkey]:
date=datetime.datetime.\
fromtimestamp(time.mktime(post[dkey]))
else:
date=datetime.datetime.now()
# So can the "unique ID for this entry"
if 'id' in post:
idkey='id'
elif 'link' in post:
idkey='link'
# So can the content
if 'content' in post:
content='<hr>'.join([c.value for c in post['content']])
elif 'summary' in post:
content=post['summary']
elif 'value' in post:
content=post['value']
# Rudimentary NON-html detection
if not '<' in content:
content=escape(content).replace('\n\n', '<p>')
# Author if available, else None
author=''
# First, we may have author_detail, which is the nicer one
if 'author_detail' in post:
ad=post['author_detail']
author=detailToAuthor(ad)
# Or maybe just an author
elif 'author' in post:
author=post['author']
# But we may have a list of contributors
if 'contributors' in post:
# Which may have the same detail as the author's
author+=' - '.join([detailToAuthor(contrib) \
for contrib in post[contributors]])
if not author:
#FIXME: how about using the feed's author,
# or something like that
author=None
# The link should be simple ;-)
if 'link' in post:
link=post['link']
else:
link=None
# Titles may be in plain title, but a title_detail is preferred
if 'title_detail' in post:
title=detailToTitle(post['title_detail'])
else:
title=post['title']
# Search for enclosures
if 'enclosures' in post:
enclosures=post['enclosures']
else:
enclosures=None
try:
# FIXME: if I use date to check here, I get duplicates on
# posts where I use artificial date because it's not in the
# feed's entry. If I don't I don't re-get updated posts.
p = Post.get_by(feed=self, post_id=post[idkey])
if p:
if p.content<>content:
p.content=content
if p.title<>title:
p.title=title
else:
# This is because of google news: the same news gets
# reposted over and over with different post_id :-(
p = Post.get_by(feed=self, title=title)
if p:
if p.post_id<>post[idkey]:
p.post_id=post[idkey]
if p.content<>content:
p.content=content
if p.title<>title:
p.title=title
else:
p=Post(feed=self, date=date, title=title,
post_id=post[idkey], content=content,
author=author, link=link)
if self.markRead:
p.unread=False
# Tag support
if 'tags' in post:
p.tags=','.join([t.term for t in post['tags']])
posts.append(p)
# Create enclosures
for e in enclosures:
enc=Enclosure(post=p,
href=e.href,
filetype=e.type,
length=e.length,
filename=None)
elixir.session.commit()
except:
traceback.print_exc(1)
elixir.session.rollback()
except KeyError:
debug(post)
try:
self.updateFeedData(d)
if 'modified' in d:
self.lastModified=datetime.datetime(*d['modified'][:6])
if 'etag' in d:
self.etag=d['etag']
elixir.session.commit()
except:
elixir.session.rollback()
try:
# Silly way to release the posts objects
# we don't need anymore
post_ids=[post.id for post in posts]
if len(post_ids):
# Mark feed UI for updating
self.curUnread=-1
# Fix freshness
Post.table.update().where(
sql.except_(Post.table.select(Post.feed==self),
Post.table.select(Post.id.in_(post_ids)))).\
values(fresh=False).execute()
elixir.session.commit()
except:
elixir.session.rollback()