def test_as_from(self):
        # TODO: shouldn't this work on oracle too ?
        x = func.current_date(bind=testing.db).execute().scalar()
        y = func.current_date(bind=testing.db).select().execute().scalar()
        z = func.current_date(bind=testing.db).scalar()
        w = select(['*'], from_obj=[func.current_date(bind=testing.db)]).\
            scalar()

        assert x == y == z == w

    def test_as_from(self):
        # TODO: shouldn't this work on oracle too ?
        x = func.current_date(bind=testing.db).execute().scalar()
        y = func.current_date(bind=testing.db).select().execute().scalar()
        z = func.current_date(bind=testing.db).scalar()
        w = select(['*'], from_obj=[func.current_date(bind=testing.db)]).\
            scalar()

        # construct a column-based FROM object out of a function,
        # like in [ticket:172]
        s = select([sql.column('date', type_=DateTime)],
                   from_obj=[func.current_date(bind=testing.db)])
        q = s.execute().first()[s.c.date]
        r = s.alias('datequery').select().scalar()

        assert x == y == z == w == q == r

 def test_native_datetime(self):
     dbapi = testing.db.dialect.dbapi
     connect_args = {
         'detect_types': dbapi.PARSE_DECLTYPES | dbapi.PARSE_COLNAMES}
     engine = engines.testing_engine(
         options={'connect_args': connect_args, 'native_datetime': True})
     t = Table(
         'datetest', MetaData(),
         Column('id', Integer, primary_key=True),
         Column('d1', Date), Column('d2', sqltypes.TIMESTAMP))
     t.create(engine)
     try:
         engine.execute(t.insert(), {
             'd1': datetime.date(2010, 5, 10),
             'd2': datetime.datetime(2010, 5, 10, 12, 15, 25)
         })
         row = engine.execute(t.select()).first()
         eq_(
             row,
             (1, datetime.date(2010, 5, 10),
                 datetime.datetime(2010, 5, 10, 12, 15, 25)))
         r = engine.execute(func.current_date()).scalar()
         assert isinstance(r, util.string_types)
     finally:
         t.drop(engine)
         engine.dispose()

    def test_conn_execute(self):
        from sqlalchemy.sql.expression import FunctionElement
        from sqlalchemy.ext.compiler import compiles

        class myfunc(FunctionElement):
            type = Date()

        @compiles(myfunc)
        def compile(elem, compiler, **kw):
            return compiler.process(func.current_date())

        conn = testing.db.connect()
        try:
            x = conn.execute(func.current_date()).scalar()
            y = conn.execute(func.current_date().select()).scalar()
            z = conn.scalar(func.current_date())
            q = conn.scalar(myfunc())
        finally:
            conn.close()
        assert (x == y == z == q) is True

    def req_has_access_to_node_id(node_id, accesstype, req=None, date=func.current_date()):
        # XXX: the database-independent code could move to core.node
        from core.transition import request
        from core.users import user_from_session

        if req is None:
            req = request

        user = user_from_session(req.session)

        # XXX: like in mysql version, what's the real solution?
        try:
            ip = IPv4Address(req.remote_addr)
        except AddressValueError:
            logg.warn("illegal IP address %s, refusing IP-based access", req.remote_addr)
            ip = None

        return Node.has_access_to_node_id(node_id, accesstype, user, ip, date)

    def has_access_to_node_id(node_id, accesstype, user=None, ip=None, date=None):
        # XXX: the database-independent code could move to core.node
        from core import db
        from core.users import get_guest_user

        if user is None:
            user = get_guest_user()

        if user.is_admin:
            return True

        if ip is None:
            ip = IPv4Address("0.0.0.0")

        if date is None:
            date = func.current_date()

        accessfunc = access_funcs[accesstype]
        group_ids = user.group_ids
        access = accessfunc(node_id, group_ids, ip, date)
        return db.session.execute(select([access])).scalar()

    def returned_on(self, date=None):
        """
        Marks the loan as returned and clears loaned flag in related movies.
        """

        if date is None:
            date = func.current_date()
        # note that SQLAlchemy will convert YYYYMMDD strings to datetime, no need to touch it

        if self.return_date: # already returned, just update the date
            self.return_date = date
            return True

        session = object_session(self)

        if self.collection_id:
            self.collection.loaned = False # will update the loaned flag in all associated movies as well
        if self.volume_id:
            self.volume.loaned = False # will update the loaned flag in all associated movies as well
        if self.movie_id:
            self.movie.loaned = False
        self.return_date = date

    def test_text_in_select_nonfrom(self):

        generate_series = text("generate_series(:x, :y, :z) as s(a)").\
            bindparams(x=None, y=None, z=None)

        s = select([
            (func.current_date() + literal_column("s.a")).label("dates")
        ]).select_from(generate_series)

        self.assert_compile(
            s,
            "SELECT CURRENT_DATE + s.a AS dates FROM "
            "generate_series(:x, :y, :z) as s(a)",
            checkparams={'y': None, 'x': None, 'z': None}
        )

        self.assert_compile(
            s.params(x=5, y=6, z=7),
            "SELECT CURRENT_DATE + s.a AS dates FROM "
            "generate_series(:x, :y, :z) as s(a)",
            checkparams={'y': 6, 'x': 5, 'z': 7}
        )

def build_accessfunc_arguments(user=None, ip=None, date=None, req=None):
    """Build the expected arguments for the DB permission procedures has_*_access_to_node()
    IP and date are returned unchanged when passed to this function. 
    For missing arguments, default values are set from request information or current date.
    :returns: 3-tuple of group_ids, ip and date
        For admin users, it returns (None, None, None) which means: ignore all access checks.
        Users can test for this and skip permission checks completely.
    """
    from core.users import get_guest_user

    if user is None and ip is None:
        if req is None:
            req = request

        from core.users import user_from_session

        user = user_from_session(req.session)
        # XXX: like in mysql version, what's the real solution?
        try:
            ip = IPv4Address(req.remote_addr)
        except AddressValueError:
            logg.warn("illegal IP address %s, refusing IP-based access", req.remote_addr)
            ip = None

    if user is None:
        user = get_guest_user()

    # admin sees everything ;)
    if user.is_admin:
        return (None, None, None)

    if ip is None:
        ip = IPv4Address("0.0.0.0")
    
    if date is None:
        date = sqlfunc.current_date()

    return user.group_ids, ip, date

 def compile(elem, compiler, **kw):
     return compiler.process(func.current_date())

    def setup_class(cls):
        global t, f, f2, ts, currenttime, metadata, default_generator

        db = testing.db
        metadata = MetaData(db)
        default_generator = {"x": 50}

        def mydefault():
            default_generator["x"] += 1
            return default_generator["x"]

        def myupdate_with_ctx(ctx):
            conn = ctx.connection
            return conn.execute(sa.select([sa.text("13")])).scalar()

        def mydefault_using_connection(ctx):
            conn = ctx.connection
            try:
                return conn.execute(sa.select([sa.text("12")])).scalar()
            finally:
                # ensure a "close()" on this connection does nothing,
                # since its a "branched" connection
                conn.close()

        use_function_defaults = testing.against("postgresql", "mssql", "maxdb")
        is_oracle = testing.against("oracle")

        # select "count(1)" returns different results on different DBs also
        # correct for "current_date" compatible as column default, value
        # differences
        currenttime = func.current_date(type_=sa.Date, bind=db)
        if is_oracle:
            ts = db.scalar(
                sa.select([func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label("today")])
            )
            assert isinstance(ts, datetime.date) and not isinstance(ts, datetime.datetime)
            f = sa.select([func.length("abcdef")], bind=db).scalar()
            f2 = sa.select([func.length("abcdefghijk")], bind=db).scalar()
            # TODO: engine propigation across nested functions not working
            currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date)
            def1 = currenttime
            def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date)

            deftype = sa.Date
        elif use_function_defaults:
            f = sa.select([func.length("abcdef")], bind=db).scalar()
            f2 = sa.select([func.length("abcdefghijk")], bind=db).scalar()
            def1 = currenttime
            deftype = sa.Date
            if testing.against("maxdb"):
                def2 = sa.text("curdate")
            elif testing.against("mssql"):
                def2 = sa.text("getdate()")
            else:
                def2 = sa.text("current_date")
            ts = db.scalar(func.current_date())
        else:
            f = len("abcdef")
            f2 = len("abcdefghijk")
            def1 = def2 = "3"
            ts = 3
            deftype = Integer

        t = Table(
            "default_test1",
            metadata,
            # python function
            Column("col1", Integer, primary_key=True, default=mydefault),
            # python literal
            Column("col2", String(20), default="imthedefault", onupdate="im the update"),
            # preexecute expression
            Column("col3", Integer, default=func.length("abcdef"), onupdate=func.length("abcdefghijk")),
            # SQL-side default from sql expression
            Column("col4", deftype, server_default=def1),
            # SQL-side default from literal expression
            Column("col5", deftype, server_default=def2),
            # preexecute + update timestamp
            Column("col6", sa.Date, default=currenttime, onupdate=currenttime),
            Column("boolcol1", sa.Boolean, default=True),
            Column("boolcol2", sa.Boolean, default=False),
            # python function which uses ExecutionContext
            Column("col7", Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx),
            # python builtin
            Column("col8", sa.Date, default=datetime.date.today, onupdate=datetime.date.today),
            # combo
            Column("col9", String(20), default="py", server_default="ddl"),
        )
        t.create()

# Create dummy user
User1 = User(name="Guru Shetti", email="[email protected]",
             picture='https://pbs.twimg.com/profile_images/2671170543/18debd694829ed78203a5a36dd364160_400x400.png')
session.add(User1)
session.commit()

# Menu for UrbanBurger
catalog_header1 = CatalogHeader(user_id=1, name="Soccer")

session.add(catalog_header1)
session.commit()

catalogItem2 = CatalogItem(user_id=1, name="Soccer Cleats", description="Really Fast",
                     price="$7.50", section="Women", first_stock_date= func.current_date(), image="shoes.jpg",  catalog_header=catalog_header1)

session.add(catalogItem2)
session.commit()


catalogItem1 = CatalogItem(user_id=1, name="Shin Guards", description="Max Protection",
                     price="$2.99", section="Men", first_stock_date= datetime.date(2015,06,15), image="shoes.jpg", catalog_header=catalog_header1)

session.add(catalogItem1)
session.commit()

catalogItem2 = CatalogItem(user_id=1, name="Shirt", description="Dry Fit",
                     price="$5.50", section="Women", first_stock_date= datetime.date(2015,06,15), image="swimtrunk.jpg", catalog_header=catalog_header1)

session.add(catalogItem2)

def showCatalogHeaders():
    catalog_headers = session.query(CatalogHeader).order_by(asc(CatalogHeader.name))
    items_with_category = session.query(CatalogHeader, CatalogItem).filter(CatalogHeader.id == CatalogItem.catalog_header_id).filter(CatalogItem.first_stock_date == func.current_date()).all()
    
    if 'username' not in login_session:
        return render_template('publiccatalog.html', catalog_headers=catalog_headers, items_with_category=items_with_category)
    else:
        return render_template('catalog.html', catalog_headers=catalog_headers, items_with_category=items_with_category)

    Column('image', Unicode(128)), # XXX: deprecated
    )

people = Table('people', metadata,
    Column('person_id', Integer, primary_key=True),
    Column('name', Unicode(256), nullable=False, unique=True),
    Column('email', Unicode(128)),
    Column('phone', Unicode(64)))

loans = Table('loans', metadata,
    Column('loan_id', Integer, primary_key=True),
    Column('person_id',  ForeignKey(people.c.person_id), nullable=False),
    Column('movie_id', ForeignKey(movies.c.movie_id), nullable=False),
    Column('volume_id', ForeignKey(volumes.c.volume_id)),
    Column('collection_id', ForeignKey(collections.c.collection_id)),
    Column('date', Date, nullable=False, default=func.current_date()),
    Column('return_date', Date, nullable=True))

languages = Table('languages', metadata,
    Column('lang_id', Integer, primary_key=True),
    Column('name', Unicode(64), nullable=False, unique=True))

acodecs = Table('acodecs', metadata,
    Column('acodec_id', Integer, primary_key=True),
    Column('name', Unicode(64), nullable=False, unique=True))

achannels = Table('achannels', metadata,
    Column('achannel_id', Integer, primary_key=True),
    Column('name', Unicode(64), nullable=False, unique=True))

subformats = Table('subformats', metadata,

    def setup_class(cls):
        global t, f, f2, ts, currenttime, metadata, default_generator

        db = testing.db
        metadata = MetaData(db)
        default_generator = {'x': 50}

        def mydefault():
            default_generator['x'] += 1
            return default_generator['x']

        def myupdate_with_ctx(ctx):
            conn = ctx.connection
            return conn.execute(sa.select([sa.text('13')])).scalar()

        def mydefault_using_connection(ctx):
            conn = ctx.connection
            try:
                return conn.execute(sa.select([sa.text('12')])).scalar()
            finally:
                # ensure a "close()" on this connection does nothing,
                # since its a "branched" connection
                conn.close()

        use_function_defaults = testing.against('postgresql', 'mssql')
        is_oracle = testing.against('oracle')

        class MyClass(object):
            @classmethod
            def gen_default(cls, ctx):
                return "hi"

        # select "count(1)" returns different results on different DBs also
        # correct for "current_date" compatible as column default, value
        # differences
        currenttime = func.current_date(type_=sa.Date, bind=db)
        if is_oracle:
            ts = db.scalar(sa.select([func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label('today')]))
            assert isinstance(ts, datetime.date) and not isinstance(ts, datetime.datetime)
            f = sa.select([func.length('abcdef')], bind=db).scalar()
            f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar()
            # TODO: engine propigation across nested functions not working
            currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date)
            def1 = currenttime
            def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date)

            deftype = sa.Date
        elif use_function_defaults:
            f = sa.select([func.length('abcdef')], bind=db).scalar()
            f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar()
            def1 = currenttime
            deftype = sa.Date
            if testing.against('mssql'):
                def2 = sa.text("getdate()")
            else:
                def2 = sa.text("current_date")
            ts = db.scalar(func.current_date())
        else:
            f = len('abcdef')
            f2 = len('abcdefghijk')
            def1 = def2 = "3"
            ts = 3
            deftype = Integer

        t = Table('default_test1', metadata,
            # python function
            Column('col1', Integer, primary_key=True,
                   default=mydefault),

            # python literal
            Column('col2', String(20),
                   default="imthedefault",
                   onupdate="im the update"),

            # preexecute expression
            Column('col3', Integer,
                   default=func.length('abcdef'),
                   onupdate=func.length('abcdefghijk')),

            # SQL-side default from sql expression
            Column('col4', deftype,
                   server_default=def1),

            # SQL-side default from literal expression
            Column('col5', deftype,
                   server_default=def2),

            # preexecute + update timestamp
            Column('col6', sa.Date,
                   default=currenttime,
                   onupdate=currenttime),

            Column('boolcol1', sa.Boolean, default=True),
            Column('boolcol2', sa.Boolean, default=False),

            # python function which uses ExecutionContext
            Column('col7', Integer,
                   default=mydefault_using_connection,
                   onupdate=myupdate_with_ctx),

#.........这里部分代码省略.........

 def daily_amount_select():
     return select([func.cast(func.sum(SalesOrderLine.unit_price * SalesOrderLine.quantity)
                    /func.greatest(func.cast(func.date_part('DAY', func.current_date() - Supplier.create_date),Integer), 1), Integer)]).as_scalar()

 def get_where(current_sel):
     return current_sel.where(SalesOrder.order_date >= func.date_trunc('month', func.current_date()))

def newCatalogItem(catalog_header_id):
    print "In new items"
    
    if 'username' not in login_session:
        return redirect('/login')
    catalog_header = session.query(CatalogHeader).filter_by(id=catalog_header_id).one()
    print catalog_header.user_id, login_session['user_id']
    if login_session['user_id'] != catalog_header.user_id:
        return "<script>function myFunction() {alert('You are not authorized to add catalogitems items to this catalog. Please create your own catalog in order to add items.');}</script><body onload='myFunction()''>"
     
    if request.method == 'POST':
            print "In post"
            newItem = CatalogItem(name=request.form['name'], description=request.form['description'], price=request.form[
                               'price'], section=request.form['section'], catalog_header_id=catalog_header_id, user_id=catalog_header.user_id, first_stock_date=func.current_date())
            session.add(newItem)
            session.commit()
            flash('New Catalog Item %s Item Successfully Created' % (newItem.name))
            return redirect(url_for('showCatalogItems', catalog_header_id=catalog_header_id))
    else:
        print "In login not same"
        return render_template('newcatalogitem.html', catalog_header_id=catalog_header_id)

def list(request):
    events = Event.query.filter(cast(Event.start_time, Date) >= func.current_date()).order_by(Event.start_time.asc())
    return {'events': events}

 def __init__(self, diag, paciente):
     self.fecha= func.current_date()
     self.comentarios= diag
     self.paciente = paciente