def test_expanding_in_special_chars(self):
        testing.db.execute(
            users.insert(),
            [
                dict(user_id=7, user_name='jack'),
                dict(user_id=8, user_name='fred'),
            ]
        )

        with testing.db.connect() as conn:
            stmt = select([users]).where(
                users.c.user_name.in_(bindparam('u35', expanding=True))
            ).where(
                users.c.user_id == bindparam("u46")
            ).order_by(users.c.user_id)

            eq_(
                conn.execute(
                    stmt, {"u35": ['jack', 'fred'], "u46": 7}).fetchall(),
                [(7, 'jack')]
            )

            stmt = select([users]).where(
                users.c.user_name.in_(bindparam('u.35', expanding=True))
            ).where(
                users.c.user_id == bindparam("u.46")
            ).order_by(users.c.user_id)

            eq_(
                conn.execute(
                    stmt, {"u.35": ['jack', 'fred'], "u.46": 7}).fetchall(),
                [(7, 'jack')]
            )

def auto_validate_stored_procGSM_Argos(ptt, ind_id,user,type_,freq,session):
    procStockDict = {
    'argos': '[sp_auto_validate_Argos_GPS]',
    'gsm': '[sp_auto_validate_GSM]'
    }

    if type_ == 'argos' :
        table = ArgosDatasWithIndiv
    elif type_ == 'gsm' :
        table = GsmDatasWithIndiv

    if ind_id is None:
        stmt = update(table).where(and_(table.c['FK_Individual'] == None, table.c['FK_ptt'] == ptt)
            ).where(table.c['checked'] == 0).values(checked =1)
 
        session.execute(stmt)
        nb_insert = exist = error = 0
    else:
        stmt = text(""" DECLARE @nb_insert int , @exist int , @error int;
        exec """+ dbConfig['data_schema'] + """."""+procStockDict[type_]+""" :ptt , :ind_id , :user ,:freq , @nb_insert OUTPUT, @exist OUTPUT, @error OUTPUT;
        SELECT @nb_insert, @exist, @error; """
        ).bindparams(bindparam('ind_id', ind_id),bindparam('user', user),bindparam('freq', freq),bindparam('ptt', ptt))
        nb_insert, exist , error= session.execute(stmt).fetchone()

    return nb_insert, exist , error

 def copy(self, conn, ins_rows, table_bound):
     ins = (
         table_bound.update()
         .where(table_bound.c.property == sqlalchemy.bindparam("_property"))
         .values({table_bound.c.item: sqlalchemy.bindparam("_item")})
     )
     conn.execute(ins, ins_rows)

def updateRegion(mapper, connection, target):
    sitePosition = target.newPosition
    stmt = text('''SELECT dbo.[fn_GetRegionFromLatLon] (:lat,:lon)
        ''').bindparams(bindparam('lat', sitePosition.LAT),
                        bindparam('lon', sitePosition.LON))
    regionID = connection.execute(stmt).scalar()
    target.FK_Region = regionID

    def list_notifications(self, tenant_id, sort_by, offset, limit):

        rows = []

        with self._db_engine.connect() as conn:
            nm = self.nm

            select_nm_query = (select([nm])
                               .where(nm.c.tenant_id == bindparam('b_tenant_id')))

            parms = {'b_tenant_id': tenant_id}

            if sort_by is not None:
                order_columns = [literal_column(col) for col in sort_by]
                if 'id' not in sort_by:
                    order_columns.append(nm.c.id)
            else:
                order_columns = [nm.c.id]

            select_nm_query = select_nm_query.order_by(*order_columns)

            select_nm_query = (select_nm_query
                               .order_by(nm.c.id)
                               .limit(bindparam('b_limit')))

            parms['b_limit'] = limit + 1

            if offset:
                select_nm_query = select_nm_query.offset(bindparam('b_offset'))
                parms['b_offset'] = offset

            rows = conn.execute(select_nm_query, parms).fetchall()

        return [dict(row) for row in rows]

 def test_select_where(self):
     stmt = (
         select([self.tables.foo])
         .where(self.tables.foo.c.data == bindparam("data"))
         .where(self.tables.foo.c.x == bindparam("x"))
     )
     self._assert_raises(stmt, {"data": "data"})

    def test_updatemany(self):
        # MySQL-Python 1.2.2 breaks functions in execute_many :(
        if (testing.against('mysql+mysqldb') and
                testing.db.dialect.dbapi.version_info[:3] == (1, 2, 2)):
            return

        t.insert().execute({}, {}, {})

        t.update(t.c.col1 == sa.bindparam('pkval')).execute(
            {'pkval': 51, 'col7': None, 'col8': None, 'boolcol1': False})

        t.update(t.c.col1 == sa.bindparam('pkval')).execute(
            {'pkval': 51},
            {'pkval': 52},
            {'pkval': 53})

        l = t.select().execute()
        ctexec = currenttime.scalar()
        today = datetime.date.today()
        eq_(l.fetchall(),
            [(51, 'im the update', f2, ts, ts, ctexec, False, False,
              13, today, 'py', 'hi'),
             (52, 'im the update', f2, ts, ts, ctexec, True, False,
              13, today, 'py', 'hi'),
             (53, 'im the update', f2, ts, ts, ctexec, True, False,
              13, today, 'py', 'hi')])

    def test_update(self):
        with self.engine.connect() as conn:
            conn.execute(
                self.tables.data.insert(),
                [
                    {"x": "x1", "y": "y1"},
                    {"x": "x2", "y": "y2"},
                    {"x": "x3", "y": "y3"}
                ]
            )

            conn.execute(
                self.tables.data.update().
                where(self.tables.data.c.x == bindparam('xval')).
                values(y=bindparam('yval')),
                [
                    {"xval": "x1", "yval": "y5"},
                    {"xval": "x3", "yval": "y6"}
                ]
            )
            eq_(
                conn.execute(
                    select([self.tables.data]).
                    order_by(self.tables.data.c.id)).
                fetchall(),
                [
                    (1, "x1", "y5", 5),
                    (2, "x2", "y2", 5),
                    (3, "x3", "y6", 5)
                ]
            )

 def search_for_scraped(session, argument_oyez_id=None, section_number=None, number=None):
   baked_query = bakery(lambda session: session.query(Turn).join(Section, Turn.section).join(Argument, Section.argument))
   baked_query += lambda q: q.filter(Argument.oyez_id == bindparam('argument_oyez_id'))
   baked_query += lambda q: q.filter(Section.number == bindparam('section_number'))
   baked_query += lambda q: q.filter(Turn.number == bindparam('number'))
   result = baked_query(session).params(argument_oyez_id=argument_oyez_id, section_number=section_number, number=number).one_or_none()
   return result

    def fast_load_module_id(cls, name, version, path, code_hash,                 # pylint: disable=too-many-arguments
                            session=None):
        """Load module id by name, version and code_hash

        Compile SQLAlchemy core query into string for optimization

        Keyword arguments:
        session -- specify session for loading (default=relational.session)
        """
        session = session or relational.session
        if not hasattr(cls, "_load_or_create_module_id"):
            tmodule = cls.t
            _query = select([tmodule.c.id]).where(
                (tmodule.c.name == bindparam("name")) &
                ((is_none(tmodule.c.version)) |
                 (tmodule.c.version == bindparam("version"))) &
                ((is_none(tmodule.c.code_hash)) |
                 (tmodule.c.code_hash == bindparam("code_hash")))
            )
            cls._load_or_create_module_id = str(_query)

        info = dict(name=name, path=path, version=version, code_hash=code_hash)
        an_id = session.execute(
            cls._load_or_create_module_id, info).fetchone()
        if an_id:
            return an_id[0]

def update_data(table, thing_id, **vals):
    transactions.add_engine(table.bind)

    u = table.update(sa.and_(table.c.thing_id == thing_id, table.c.key == sa.bindparam("_key")))
    d = table.delete(sa.and_(table.c.thing_id == thing_id, table.c.key == sa.bindparam("_key")))

    inserts = []
    for key, val in vals.iteritems():
        if key[0:5] == "multi":
            # vks = [py2db(v, return_kind=True) for v in vs]
            # vals = [vk[0] for vk in vks]
            val = [py2db(v, return_kind=False) for v in val]
            kind = "num"

            d.execute(_key=key)
            for v in val:
                inserts.append({"key": key, "value": v, "kind": kind})
        else:
            val, kind = py2db(val, return_kind=True)

            uresult = u.execute(_key=key, value=val, kind=kind)
            if not uresult.rowcount:
                inserts.append({"key": key, "value": val, "kind": kind})

    # do one insert
    if inserts:
        i = table.insert(values=dict(thing_id=thing_id))
        i.execute(*inserts)

 def insert_dataset(self, metadata_doc, dataset_id, dataset_type_id):
     """
     Insert dataset if not already indexed.
     :type metadata_doc: dict
     :type dataset_id: str or uuid.UUID
     :type dataset_type_id: int
     :return: whether it was inserted
     :rtype: bool
     """
     try:
         dataset_type_ref = bindparam('dataset_type_ref')
         ret = self._connection.execute(
             DATASET.insert().from_select(
                 ['id', 'dataset_type_ref', 'metadata_type_ref', 'metadata'],
                 select([
                     bindparam('id'), dataset_type_ref,
                     select([
                         DATASET_TYPE.c.metadata_type_ref
                     ]).where(
                         DATASET_TYPE.c.id == dataset_type_ref
                     ).label('metadata_type_ref'),
                     bindparam('metadata', type_=JSONB)
                 ])
             ),
             id=dataset_id,
             dataset_type_ref=dataset_type_id,
             metadata=metadata_doc
         )
         return ret.rowcount > 0
     except IntegrityError as e:
         if e.orig.pgcode == PGCODE_UNIQUE_CONSTRAINT:
             raise DuplicateRecordError('Duplicate dataset, not inserting: %s' % dataset_id)
         raise

    def resolve(cls, localpart, domain_name):
        alias_preserve_case = cls.query.filter(
                sqlalchemy.and_(cls.domain_name == domain_name,
                    sqlalchemy.or_(
                        sqlalchemy.and_(
                            cls.wildcard == False,
                            cls.localpart == localpart
                        ), sqlalchemy.and_(
                            cls.wildcard == True,
                            sqlalchemy.bindparam("l", localpart).like(cls.localpart)
                        )
                    )
                )
            ).order_by(cls.wildcard, sqlalchemy.func.char_length(cls.localpart).desc()).first()
        if alias_preserve_case:
            return alias_preserve_case

        if localpart:
            localpart = localpart.lower()
        return cls.query.filter(
                sqlalchemy.and_(cls.domain_name == domain_name,
                    sqlalchemy.or_(
                        sqlalchemy.and_(
                            cls.wildcard == False,
                            sqlalchemy.func.lower(cls.localpart) == localpart
                        ), sqlalchemy.and_(
                            cls.wildcard == True,
                            sqlalchemy.bindparam("l", localpart).like(sqlalchemy.func.lower(cls.localpart))
                        )
                    )
                )
            ).order_by(cls.wildcard, sqlalchemy.func.char_length(sqlalchemy.func.lower(cls.localpart)).desc()).first()

 def test_missing_bind_posn(self):
     assert_raises_message(
         exc.ArgumentError,
         "This text\(\) construct doesn't define a bound parameter named 'bar'",
         text(":foo").bindparams,
         bindparam('foo', value=5), bindparam('bar', value=7)
     )

    def test_count_with_bindparams(self):
        User = self.classes.User

        bq = self.bakery(lambda s: s.query(User))

        sess = Session()

        eq_(
            bq(sess).count(),
            4
        )

        bq += lambda q: q.filter(User.name == bindparam("uname"))
        # calling with *args
        eq_(
            bq(sess).params(uname='fred').count(), 1
        )
        # with multiple params, the **kwargs will be used
        bq += lambda q: q.filter(User.id == bindparam("anid"))
        eq_(
            bq(sess).params(uname='fred', anid=9).count(), 1
        )
        eq_(
            # wrong id, so 0 results:
            bq(sess).params(uname='fred', anid=8).count(), 0
        )

    def __init__(self, meta, name, osmtables, subset=None, change=None,
                 column_geom='geom', geom_change=None):
        super().__init__(meta, name, osmtables.node, subset=subset,
                             change=change)
        # need a geometry column
        if isinstance(column_geom, Column):
            self.column_geom = column_geom
            srid = column_geom.type.srid
        else:
            srid = meta.info.get('srid', self.src.data.c.geom.type.srid)
            self.column_geom = Column(column_geom, Geometry('POINT', srid=srid))
        self.data.append_column(self.column_geom)

        # add an additional transform to the insert statement if the srid changes
        params = {}
        for c in self.data.c:
            if c == self.column_geom and self.src.data.c.geom.type.srid != srid:
                geomparam = bindparam(c.name, type_=self.column_geom.type)
                params[c.name] = ST_Transform(geomparam, self.column_geom.type.srid)
            else:
                params[c.name] = bindparam(c.name)
        self.stm_insert = self.stm_insert.values(params)

        # the table to remember geometry changes
        self.geom_change = geom_change

def delete(jobs):
    """Delete a job or a list of jobs. This does **NOT** resolve any
    dependencies but removes the relationships.

    **Note** that no searched on the jobs dependencies are performed. You
    have to create the job list with all the jobs you want updated manually.
    You can use :py:func:`jip.jobs.get_subgraph` to get a full subgraph of a
    job, or :py:func:`jip.jobs.get_group_jobs` to create a list of all jobs
    that are related due to grouping or piping.

    :param jobs: single job or list of jobs
    """
    if not isinstance(jobs, (list, tuple)):
        jobs = [jobs]
    # create delete statement for the job
    stmt = [Job.__table__.delete().where(Job.id == bindparam("_id"))]
    # delete entries in the relationship tables
    for relation_table in [job_dependencies, job_pipes, job_groups]:
        dep = relation_table.delete().where(
            (relation_table.c.source == bindparam("_id")) |
            (relation_table.c.target == bindparam("_id"))
        )
        stmt.append(dep)
    # delete entries in file tables
    for relation_table in [InputFile.__table__, OutputFile.__table__]:
        dep = relation_table.delete().where(
            relation_table.c.job_id == bindparam("_id")
        )
        stmt.append(dep)

    # convert the job values
    values = [{"_id": j.id} for j in jobs if j.id is not None]
    if values:
        _execute(stmt[::-1], values) # Reverse the elements in the list to remove first the relations

def update_canonicals(canonicals):
    '''
    Update canonical data for android devices.
    '''
    global ENGINE
    binding = [{"p_{}".format(k): v for k, v in canonical.items()} for canonical in canonicals]
    device_table = model.metadata.tables['device']
    stmt = update(device_table).\
        values(device_token_new=bindparam('p_new_token')).\
        where(and_(device_table.c.login_id == bindparam('p_login_id'),
                   func.coalesce(device_table.c.device_token_new, device_table.c.device_token) == bindparam('p_old_token')))
    ENGINE.execute(stmt, binding)

    with session_scope() as session:
        query = text('SELECT keep_max_users_per_device( \
                     (:platform_id)::int2, :device_token, (:max_users_per_device)::int2)')
        for canonical in canonicals:
            session.execute(query,
                            {'platform_id': constants.PLATFORM_ANDROID,
                             'device_token': canonical['new_token'],
                             'max_users_per_device': config.max_users_per_device
                            })
            session.execute(query,
                            {'platform_id': constants.PLATFORM_ANDROID_TABLET,
                             'device_token': canonical['new_token'],
                             'max_users_per_device': config.max_users_per_device
                            })
        session.commit()

    def _add_missing_mau(self, table_prefix, days_ago):
        # Locate beginning table
        tables = self._get_filtered_tables(table_prefix, days_ago+30)
        first_date = self._date_from_tablename(tables[0].name)

        grouped_monthly_unique = select([
            monthly_rollup.c.date,
            func.sum(monthly_rollup.c.count).label("count")
        ]).\
            where(monthly_rollup.c.date >= first_date).\
            group_by(monthly_rollup.c.date).\
            order_by(monthly_rollup.c.date)

        # Average them over 6 days prior inclding today for MAU's
        maus = select([
            grouped_monthly_unique.c.date,
            func.avg(grouped_monthly_unique.c.count).over(
                order_by=text("date ROWS 6 PRECEDING")
            ).label("mau")
        ]).\
            order_by(grouped_monthly_unique.c.date)
        results = self._conn.execute(maus).fetchall()

        if not results:
            return

        # Update appropriate rows
        stmt = daily_stats.update().\
            where(daily_stats.c.date == bindparam("update_date")).\
            values(mau=bindparam("mau"))
        self._conn.execute(stmt, [
            {"update_date": x.date, "mau": x.mau}
            for x in results[-days_ago:]
        ])

    def test_functions_with_cols(self):
        users = table(
            'users',
            column('id'),
            column('name'),
            column('fullname'))
        calculate = select([column('q'), column('z'), column('r')], from_obj=[
                           func.calculate(
                               bindparam('x', None), bindparam('y', None)
                           )])

        self.assert_compile(select([users], users.c.id > calculate.c.z),
                            "SELECT users.id, users.name, users.fullname "
                            "FROM users, (SELECT q, z, r "
                            "FROM calculate(:x, :y)) "
                            "WHERE users.id > z"
                            )

        s = select([users], users.c.id.between(
            calculate.alias('c1').unique_params(x=17, y=45).c.z,
            calculate.alias('c2').unique_params(x=5, y=12).c.z))

        self.assert_compile(
            s, "SELECT users.id, users.name, users.fullname "
            "FROM users, (SELECT q, z, r "
            "FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r "
            "FROM calculate(:x_2, :y_2)) AS c2 "
            "WHERE users.id BETWEEN c1.z AND c2.z", checkparams={
                'y_1': 45, 'x_1': 17, 'y_2': 12, 'x_2': 5})