Python sqlalchemy.func.lower() Examples

def find_user(self, case_insensitive=False, **kwargs):
        from sqlalchemy import func as alchemyFn

        query = self.user_model.query
        if config_value("JOIN_USER_ROLES") and hasattr(self.user_model, "roles"):
            from sqlalchemy.orm import joinedload

            query = query.options(joinedload("roles"))

        if case_insensitive:
            # While it is of course possible to pass in multiple keys to filter on
            # that isn't the normal use case. If caller asks for case_insensitive
            # AND gives multiple keys - throw an error.
            if len(kwargs) > 1:
                raise ValueError("Case insensitive option only supports single key")
            attr, identifier = kwargs.popitem()
            subquery = alchemyFn.lower(
                getattr(self.user_model, attr)
            ) == alchemyFn.lower(identifier)
            return query.filter(subquery).first()
        else:
            return query.filter_by(**kwargs).first() 

def get_user( session, broker_id, username=None, email=None, user_id=None ):
    if username:
      username = username.lower().strip()
    if email:
      email = email.lower().strip()


    if user_id:
      filter_obj = or_(User.id == user_id)
    elif username and email:
      filter_obj = or_( func.lower(User.username)==username, func.lower(User.email)==email )
    elif username:
      filter_obj = or_( func.lower(User.username)==username )
    elif email:
      filter_obj = or_( func.lower(User.email)==email )
    else:
      return  None
    user = session.query(User).filter(User.broker_id == broker_id).filter(filter_obj).first()
    if user:
      return  user
    return None 

def make_id(namespace, name):
    """
    pass a language name or host in with a name, get a Package.id str
    """

    namespace = namespace.lower()

    if namespace in ["cran", "pypi"]:
        return namespace + ":" + name

    elif namespace == "python":
        return "pypi:" + name

    elif namespace == "r":
        return "cran:" + name

    else:
        raise ValueError("Invalid namespace for package id") 

def test_render_add_index_func(self):
        m = MetaData()
        t = Table(
            "test",
            m,
            Column("id", Integer, primary_key=True),
            Column("code", String(255)),
        )
        idx = Index("test_lower_code_idx", func.lower(t.c.code))
        op_obj = ops.CreateIndexOp.from_index(idx)

        eq_ignore_whitespace(
            autogenerate.render_op_text(self.autogen_context, op_obj),
            "op.create_index('test_lower_code_idx', 'test', "
            "[sa.text(!U'lower(code)')], unique=False)",
        ) 

def players():
    form = SearchPlayerForm()
    player_query = Player.query

    if form.validate_on_submit():
        player_query = player_query.join(User)
        if form.player_name.data:
            player_query = player_query.filter(
                func.lower(User.name).contains(form.player_name.data.lower()))
        if form.aga_id.data:
            player_query = player_query.filter(
                User.aga_id == form.aga_id.data)

    players = player_query.limit(100).all()

    return render_template('players.html', players=players, form=form) 

def get_subscription(application=None):
    """
    Return a list of subscriptions for a certain application
    If application is omitted, all applications are returned.

    :param application: Name of the application
    :return: list of subscription dictionaries
    """
    subscriptions = []
    sql_query = Subscription.query
    if application:
        sql_query = sql_query.filter(func.lower(Subscription.application) ==
                                     application.lower())

    for sub in sql_query.all():
        subscriptions.append(sub.get())

    return subscriptions 

def registerCommon(request, form):
    mails = request.db.query(models.User.email).all()
    mails = [m.email.lower() for m in mails]
    if form['email'].lower() in mails:
        request.session.flash('Ein Benutzer mit dieser E-Mail-Adresse existiert bereits.', queue='messages')
        return False
    else:
        user = models.User()
        form.obj = user
        form.saveValues()
        request.db.add(user)
        confirmation = models.Confirmation()
        confirmation.source = 'user/register'
        confirmation.user = user
        request.db.add(confirmation)
        request.db.commit()
        send_confirmation_mail(request, user, confirmation)
        return True 

def find_user(self, case_insensitive=False, **kwargs):
        from peewee import fn as peeweeFn

        try:
            if case_insensitive:
                # While it is of course possible to pass in multiple keys to filter on
                # that isn't the normal use case. If caller asks for case_insensitive
                # AND gives multiple keys - throw an error.
                if len(kwargs) > 1:
                    raise ValueError("Case insensitive option only supports single key")
                attr, identifier = kwargs.popitem()
                return self.user_model.get(
                    peeweeFn.lower(getattr(self.user_model, attr))
                    == peeweeFn.lower(identifier)
                )
            else:
                return self.user_model.filter(**kwargs).get()
        except self.user_model.DoesNotExist:
            return None 

def grant(self, sess, comment, grantee, badge):
        """
        This is how admins can grant badges manually
        """
        author = comment.author.name
        badge = badge.lower().replace('\\', '')
        grantee_unescaped = grantee.replace('\\', '')

        if author in config.ADMIN_ACCOUNTS:
            investor = sess.query(Investor).\
                filter(Investor.name == grantee_unescaped).\
                first()

            if not investor:
                return comment.reply_wrap(message.modify_grant_failure("no such investor"))

            badge_list = json.loads(investor.badges)
            if badge in badge_list:
                return comment.reply_wrap(message.modify_grant_failure("already owned"))

            badge_list.append(badge)
            investor.badges = json.dumps(badge_list)
            return comment.reply_wrap(message.modify_grant_success(grantee, badge)) 

def test_returning_insert_functional(self):
        t1 = table(
            "t1", column("c1"), column("c2", String()), column("c3", String())
        )
        fn = func.lower(t1.c.c2, type_=String())
        stmt = t1.insert().values(c1=1).returning(fn, t1.c.c3)
        compiled = stmt.compile(dialect=oracle.dialect())
        eq_(
            compiled._create_result_map(),
            {
                "c3": ("c3", (t1.c.c3, "c3", "c3"), t1.c.c3.type),
                "lower": ("lower", (fn, "lower", None), fn.type),
            },
        )

        self.assert_compile(
            stmt,
            "INSERT INTO t1 (c1) VALUES (:c1) RETURNING "
            "lower(t1.c2), t1.c3 INTO :ret_0, :ret_1",
        ) 

def get(self):

        symbols = get_parsed_arg_list('symbols', to_lower=True)
        exchange_pairs = get_parsed_arg_list('exchange_pairs', to_lower=True)

        if len(symbols) > 0:
            tokens = Token.query.filter(func.lower(Token.symbol).in_(symbols)).all()
        else:
            tokens = Token.query.all()

        exchange_pair_tokens = Token.query.filter(func.lower(Token.symbol).in_(exchange_pairs)).all()

        tokens_schema.context = {'exchange_pairs': exchange_pair_tokens}

        response_object = {
            'message': 'success',
            'data': {
                'tokens': tokens_schema.dump(tokens).data
            }
        }

        return make_response(jsonify(response_object)), 200 

def count(column, value, glob=False):
    """Counts number of rows with value in a column. This function is case-insensitive.

    Positional arguments:
    column -- the SQLAlchemy column object to search in (e.g. Table.a_column).
    value -- the value to search for, any string.

    Keyword arguments:
    glob -- enable %globbing% search (default False).

    Returns:
    Number of rows that match. Equivalent of SELECT count(*) FROM.
    """
    query = db.session.query(func.count('*'))
    if glob:
        query = query.filter(column.ilike(value))
    else:
        query = query.filter(func.lower(column) == value.lower())
    return query.one()[0] 

def get_all_without_views(cls, formats=[]):
        '''Returns all resources that have no resource views

        :param formats: if given, returns only resources that have no resource
            views and are in any of the received formats
        :type formats: list

        :rtype: list of ckan.model.Resource objects
        '''
        query = meta.Session.query(cls).outerjoin(ckan.model.ResourceView) \
                    .filter(ckan.model.ResourceView.id == None)

        if formats:
            lowercase_formats = [f.lower() for f in formats]
            query = query.filter(func.lower(cls.format).in_(lowercase_formats))

        return query.all() 

def get_user_from_google_member(member, db=None):
    """
    Get User object for all members on a Google project by checking db.

    Args:
        member(cirrus.google_cloud.iam.GooglePolicyMember): Member on
            the google project who are of type User

    Return:
        fence.models.User: User from our db for member on Google project
    """
    session = get_db_session(db)

    linked_google_account = (
        session.query(UserGoogleAccount)
        .filter(func.lower(UserGoogleAccount.email) == member.email_id.lower().strip())
        .first()
    )
    if linked_google_account:
        return (
            session.query(User).filter(User.id == linked_google_account.user_id).first()
        )

    return None 

def validateInput(self):
        '''
        Validate user input.
        '''
        if self._dbmodel:
            if callable(self._dbmodel):
                modelObj = self._dbmodel()
            
            #Then it is a class instance    
            else:
                modelObj = self._dbmodel
                self._dbmodel = self._dbmodel.__class__
                
            objQueryProperty = getattr(self._dbmodel,self._attrName)
            modelRecord = modelObj.queryObject().filter(func.lower(objQueryProperty) == func.lower(self.text())).first()
                                                        
            if modelRecord != None:
                self.setStyleSheet(INVALIDATESTYLESHEET)
                self._currInvalidMsg = self._invalidMsg.format("'" + self.text() + "'")
                self._isValid = False
                
                if self._notifBar:
                    self._notifBar.insertErrorNotification(self._currInvalidMsg) 

def test_select_cols_use_labels_result_map_targeting(self):
        table = self._fixture()

        compiled = select([table]).apply_labels().compile()
        assert table.c.y in compiled._create_result_map()["test_table_y"][1]
        assert table.c.x in compiled._create_result_map()["test_table_x"][1]

        # the lower() function goes into the result_map, we don't really
        # need this but it's fine
        self.assert_compile(
            compiled._create_result_map()["test_table_y"][1][3],
            "lower(test_table.y)",
        )
        # then the original column gets put in there as well.
        # as of 1.1 it's important that it is first as this is
        # taken as significant by the result processor.
        self.assert_compile(
            compiled._create_result_map()["test_table_y"][1][0], "test_table.y"
        ) 

def add_foreign_key_constraint_if_not_exist(
    table_name, column_name, fk_table_name, fk_column_name, driver, metadata
):
    table = Table(table_name, metadata, autoload=True, autoload_with=driver.engine)
    foreign_key_name = "{}_{}_fkey".format(table_name.lower(), column_name)

    if column_name in table.c:
        foreign_keys = [fk.name for fk in getattr(table.c, column_name).foreign_keys]
        if foreign_key_name not in foreign_keys:
            with driver.session as session:
                session.execute(
                    'ALTER TABLE "{}" ADD CONSTRAINT {} '
                    'FOREIGN KEY({}) REFERENCES "{}" ({});'.format(
                        table_name,
                        foreign_key_name,
                        column_name,
                        fk_table_name,
                        fk_column_name,
                    )
                )
                session.commit() 

def _revoke_from_db(self, sess, to_delete):
        """
        Revoke user access to projects in the auth database

        Args:
            sess: sqlalchemy session
            to_delete: a set of (username, project.auth_id) to be revoked from db
        Return:
            None
        """
        for (username, project_auth_id) in to_delete:
            q = (
                sess.query(AccessPrivilege)
                .filter(AccessPrivilege.project.has(auth_id=project_auth_id))
                .join(AccessPrivilege.user)
                .filter(func.lower(User.username) == username)
                .all()
            )
            for access in q:
                self.logger.info(
                    "revoke {} access to {} in db".format(username, project_auth_id)
                )
                sess.delete(access) 

def doublets(request):
    emails = [e.email for e in request.db.query(models.User.email)]
    doublets = collections.defaultdict(lambda: [])
    for user in request.db.query(models.User).all():
        doublets[user.email.lower()].append(user)
        # doublets[user.name().lower()].append(user)
    for key, value in list(doublets.items()):
        if len(value)<=1:
            del doublets[key]
    doublets_list = list(doublets.items())
    doublets_list.sort(key=lambda e: e[1][0].last_name)
    return {'doublets': doublets_list} 

def get_query_param_set(params):
    """
    Strip, lowercase, and remove empty params to be used in a query
    """
    param_set = params.strip().lower().split(" ")
    param_set = [p for p in param_set if len(p) > 0]
    return param_set 

def _fixture(self):
        class MyString(String):

            # supercedes any processing that might be on
            # String
            def bind_expression(self, bindvalue):
                return func.lower(bindvalue)

            def column_expression(self, col):
                return func.lower(col)

        return self._test_table(MyString) 

def test_select_cols_use_labels(self):
        table = self._fixture()

        self.assert_compile(
            select([table]).apply_labels(),
            "SELECT test_table.x AS test_table_x, "
            "lower(test_table.y) AS test_table_y FROM test_table",
        ) 

def ajaxComplete(request):
    search_str = request.POST['name']+'%'
    lecture_students = request.context.lecture.lecture_students_for_tutorials(tutorials=request.context.tutorials)
    lecture_students = lecture_students.filter(or_((func.lower(models.User.first_name).like(func.lower(search_str))),
                                                   (func.lower(models.User.last_name).like(func.lower(search_str))),
                                                   (func.lower(models.User.email).like(func.lower(search_str)))))
    students = [ls.student for ls in lecture_students]
    return {'users': students} 

def joinfirm(self, sess, comment, investor, firm_name):
        if investor.firm != 0:
            return comment.reply_wrap(message.joinfirm_exists_failure_org)

        firm = sess.query(Firm).\
            filter(func.lower(Firm.name) == func.lower(firm_name)).\
            first()
        if firm == None:
            return comment.reply_wrap(message.joinfirm_failure_org)

        max_members = max_members_for_rank(firm.rank)
        if firm.size >= max_members:
            return comment.reply_wrap(message.modify_joinfirm_full(firm))

        if firm.private:
            invite = sess.query(Invite).\
                filter(Invite.investor == investor.id).\
                filter(Invite.firm == firm.id).\
                first()
            if invite == None:
                return comment.reply_wrap(message.joinfirm_private_failure_org)

        investor.firm = firm.id
        investor.firm_role = ""
        firm.size += 1

        # Updating the flair in subreddits
        if not config.TEST:
            for subreddit in config.SUBREDDITS:
                REDDIT.subreddit(subreddit).flair.set(investor.name, f"{firm.name} | Floor Trader")

        return comment.reply_wrap(message.modify_joinfirm(firm)) 

def test_composed_multiple(self):
        table = self.tables.some_table
        lx = (table.c.x + table.c.y).label('lx')
        ly = (func.lower(table.c.q) + table.c.p).label('ly')
        self._assert_result(
            select([lx, ly]).order_by(lx, ly.desc()),
            [(3, util.u('q1p3')), (5, util.u('q2p2')), (7, util.u('q3p1'))]
        ) 

def valid_package_names(cls, module_names):
        """
        this will normally be called by subclasses, to filter by specific package hosts
        """
        lowercase_module_names = [n.lower() for n in module_names]
        q = db.session.query(cls.project_name)
        q = q.filter(func.lower(cls.project_name).in_(lowercase_module_names))
        response = [row[0] for row in q.all()]
        return response 

def test_composed_multiple(self):
        table = self.tables.some_table
        lx = (table.c.x + table.c.y).label('lx')
        ly = (func.lower(table.c.q) + table.c.p).label('ly')
        self._assert_result(
            select([lx, ly]).order_by(lx, ly.desc()),
            [(3, util.u('q1p3')), (5, util.u('q2p2')), (7, util.u('q3p1'))]
        ) 

def like_domain_query(term):
    domain_query = database.session_query(Domain.id)
    domain_query = domain_query.filter(func.lower(Domain.name).like(term.lower()))
    assoc_query = database.session_query(certificate_associations.c.certificate_id)
    assoc_query = assoc_query.filter(certificate_associations.c.domain_id.in_(domain_query))
    return assoc_query 

def parse_projects(self, user_projects):
        """
        helper function for parsing projects
        """
        return {key.lower(): value for key, value in user_projects.items()} 

def _update_from_db(self, sess, to_update, user_project):
        """
        Update user access to projects in the auth database

        Args:
            sess: sqlalchemy session
            to_update:
                a set of (username, project.auth_id) to be updated from db

        Return:
            None
        """

        for (username, project_auth_id) in to_update:
            q = (
                sess.query(AccessPrivilege)
                .filter(AccessPrivilege.project.has(auth_id=project_auth_id))
                .join(AccessPrivilege.user)
                .filter(func.lower(User.username) == username)
                .all()
            )
            for access in q:
                access.privilege = user_project[username][project_auth_id]
                self.logger.info(
                    "update {} with {} access to {} in db".format(
                        username, access.privilege, project_auth_id
                    )
                )