Example #1
0
 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",)])
Example #2
0
 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', )])
Example #3
0
 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',)])
Example #4
0
    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
Example #5
0
 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)
Example #6
0
 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')
Example #7
0
 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')
Example #8
0
    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",
     )
Example #11
0
 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",
     )
Example #12
0
    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)
Example #14
0
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()
Example #15
0
    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)
Example #16
0
    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()