Python sql.select函数代码示例

	def extractData(self):
		self.logger.info('Connecting to database')
		engine = create_engine(self.config['database'])
		meta = MetaData()
		meta.bind = engine
		self.job = Table('job', meta, autoload=True)
		self.job_status = Table('job_status', meta, autoload=True)
		self.job_status_type_description = Table('job_status_type_description', meta, autoload=True)

		job=self.job
		job_status=self.job_status

		s=self.job_status_type_description.select()
		self.type_db_values = s.execute().fetchall()

		self.queue_db_values = self.getJobSummary(job.c.queue)
		self.user_db_values = self.getJobSummary(job.c.userId, job.c.localUser)
		self.node_db_values = self.getJobSummary(job.c.workerNode)

		self.logger.info('Generating job list')
		node_job_status = self.config['node_job_status'].split(',')

		maximum = func.max(self.job_status.c.id).label('m')
		s1 = select([maximum]).group_by(job_status.c.jobId).alias('a')
		s2 = select([job.c.id, job.c.lrmsAbsLayerJobId, job.c.workerNode, job_status.c.type,
			job_status.c.time_stamp]).select_from(job.join(job_status).join(s1,job_status.c.id==text('m'))). \
			where(and_(job_status.c.type.in_(node_job_status)))

		self.job_db_values = s2.execute().fetchall()

		return {}

    def test_sql_create_extra_fields(self):
        """
        Test if `SqlStore` creates extra columns.
        """

        # extend the db
        self._make_store(self.db_url,
                         extra_fields={sqlalchemy.Column('extra', sqlalchemy.VARCHAR(length=128)): (lambda arg: arg.foo.value)})

        # if this query does not error out, the column is defined
        q = sql.select([sqlfunc.count(self.store.t_store.c.extra)]).distinct()
        results = self.conn.execute(q)
        # self.c.execute("select distinct count(extra) from %s" % self.store.table_name)
        rows = results.fetchall()
        assert_equal(len(rows), 1)

        # create and save an object
        obj = SimplePersistableObject('an object')
        obj.foo = SimplePersistableObject('an attribute')
        id_ = db.save(obj)

        # check that the value has been saved
        q = sql.select([self.store.t_store.c.extra]).where(self.store.t_store.c.id == id_)
        # self.c.execute("select extra from %s where id=%d"
        #                % (self.store.table_name, id_))
        results = self.conn.execute(q)
        rows = results.fetchall()
        assert_equal(len(rows), 1)
        assert_equal(rows[0][0], obj.foo.value)

    def test_all_aliases(self):
        orders = table('order', column('order'))
        s = select([orders.c.order]).cte("regional_sales")

        r1 = s.alias()
        r2 = s.alias()

        s2 = select([r1, r2]).where(r1.c.order > r2.c.order)

        self.assert_compile(
            s2,
            'WITH regional_sales AS (SELECT "order"."order" '
            'AS "order" FROM "order") '
            'SELECT anon_1."order", anon_2."order" '
            'FROM regional_sales AS anon_1, '
            'regional_sales AS anon_2 WHERE anon_1."order" > anon_2."order"'
        )

        s3 = select([orders]).select_from(orders.join(r1, r1.c.order == orders.c.order))

        self.assert_compile(
            s3,
            'WITH regional_sales AS '
            '(SELECT "order"."order" AS "order" '
            'FROM "order")'
            ' SELECT "order"."order" '
            'FROM "order" JOIN regional_sales AS anon_1 ON anon_1."order" = "order"."order"'
        )

def create_type(sess, fqname):
    u'''
    クラスなり型なりを追加
    '''

    t = tables.Type.__table__

    query = sql.select([t.c.id], t.c.fqname == fqname, t)

    result = sess.execute(query).fetchall()

    if result:
        return result[0][0]

    name = fqname.split('.')[-1]

    typ = tables.Type(name=name, fqname=fqname)

    with sess.begin():
        sess.add(typ)

    query = sql.select([t.c.id], t.c.fqname == fqname, t)

    result = sess.execute(query).fetchone()[0]

    return result

    def create_bought_product(self, qty, user_uuid, product_uuid):

        """
        Create new bought item given product_uuid and user_uuid 
        of product or user with given uuid doesnt exists returns None

        Returns primary_key that has been inserted
        
        Keyword Arguments:
        qty -- amount of items bought (int),
        user_uuid -- unique user uuid (str),
        product_uuid -- unique product uuid (str)
        """
        try:
            user_id = self.conn.execute(select([users.c.user_id])\
                                        .where(users.c.user_uuid == user_uuid)).scalar()
            product_id = self.conn.execute(select([products.c.product_id])\
                                       .where(products.c.product_uuid == product_uuid)).scalar()
        except:
            raise
        if product_id and user_id:
            ins = bought_products.insert()\
                    .values(quantity = qty, user_id = user_id, product_id = product_id)
            trans = self.conn.begin()
            try:
                res = self.conn.execute(ins)
                trans.commit()
                return res.inserted_primary_key[0]
            except Exception as e:
                trans.rollback()
                raise
                logging.error(sys.exc_info[0])
        else:
            return

def grant_permission_for_group(permission_name, group_name):
    execute(
        groups_permissions.insert().values(
            group_id=select([groups.c.group_id]).where(groups.c.group_name == group_name),
            permission_id=select([permissions.c.permission_id]).where(permissions.c.permission_name == permission_name)
        )
    )

    def _get_trace_id(self, trace):

        # Get the hash.
        trace_hash = self.get_hash(trace)

        # Check the database.
        stmt = select([traces_table.c.id]).where(
            traces_table.c.trace_hash == trace_hash
        )
        row = self._execute(stmt).first()
        if row:
            return row.id

        # Attempt to insert a new trace.
        compressed = zlib.compress(str(trace))
        stmt = traces_table.insert().from_select([
            traces_table.c.trace_hash,
            traces_table.c.data,
        ], select([
            literal(trace_hash),
            literal(compressed, type_=Binary),
        ]).where(
            ~exists([traces_table.c.id]).where(
                traces_table.c.trace_hash == trace_hash
            )
        ))
        self._execute(stmt)

        return self._get_trace_id(trace)

def tags(metadata):
    conn = metadata.bind.connect()
    session = DBSession()
    taggings = metadata.tables["taggings"]
    tags = metadata.tables["tags"]
    tag_result = conn.execute(select([tags]))
    for row in tag_result:
        tag = Tag(id=row["id"], name=row["name"])
        session.add(tag)
    session.flush()
    result = conn.execute(select([taggings]))
    for row in result:
        # get type
        tag_type = row["taggable_type"]
        if tag_type == "Group":
            Model = Group
        elif tag_type == "Person":
            Model = Profile
        elif tag_type == "Company":
            Model = Company
        # get tag id
        tag = Tag.query.get(row["tag_id"])

        obj = Model.query.get(row["taggable_id"])
        if obj:
            obj.tags.append(tag)
        # get taggable id
        session.flush()

def get_tx_addresses(tx=None):

    in_addresses = []
    out_addresses = []

    if tx['removed']==True:
       in_addresses = ALL_VOUT.query.with_entities(ALL_VOUT.address, ALL_VOUT.value, ALL_VOUT.txin_tx_id, ALL_VOUT.txout_tx_hash).filter(ALL_VOUT.txin_tx_id==int(tx['id'])).order_by(ALL_VOUT.in_idx).all()
       out_addresses = ALL_VOUT.query.with_entities(ALL_VOUT.address, ALL_VOUT.value, ALL_VOUT.txin_tx_id, ALL_VOUT.txin_tx_hash).filter(ALL_VOUT.txout_tx_id==int(tx['id'])).order_by(ALL_VOUT.out_idx).all()

       return in_addresses , out_addresses

    s1 = select([STXO.address, STXO.value, STXO.txin_tx_id, STXO.txout_tx_hash, STXO.in_idx]).where(STXO.txin_tx_id == int(tx['id']))
    
    s2 = select([VTXO.address, VTXO.value, VTXO.txin_tx_id, VTXO.txout_tx_hash, VTXO.in_idx]).where(VTXO.txin_tx_id == int(tx['id']))
    
    q = s1.union(s2).alias('in_addresses')
    
    in_addresses=db_session.query(q).order_by('in_idx').all()

    s1 = select([STXO.address, STXO.value, STXO.txin_tx_id, STXO.txout_tx_hash, STXO.out_idx]).where(STXO.txout_tx_id == tx['id'])
    
    s2 = select([VTXO.address, VTXO.value, VTXO.txin_tx_id, VTXO.txout_tx_hash, VTXO.out_idx]).where(VTXO.txout_tx_id == tx['id'])
    
    q = s1.union(s2).alias('out_addresses')
    
    out_addresses=db_session.query(q).order_by('out_idx').all()
 
    return in_addresses , out_addresses

    def _get_memory_id(self, mem):

        # Check the local cache.
        mem_hash = self.get_hash(mem)
        if mem_hash in self.memories:
            return self.memories[mem_hash]

        # Attempt to insert a new memory.
        # This is the expected case.
        stmt = memories_table.insert().from_select([
                memories_table.c.name_hash,
                memories_table.c.name,
            ], select([
                literal(mem_hash),
                literal(mem),
            ]).where(
                ~exists([memories_table.c.id]).where(
                    memories_table.c.name_hash == mem_hash
                )
            )
        )
        self._execute(stmt)

        # Check the database.
        stmt = select([memories_table.c.id]).where(
            memories_table.c.name_hash == mem_hash
        )
        row = self._execute(stmt).first()
        ident = row.id
        self.memories[mem_hash] = ident
        return ident

async def create_conversion_batch(entity_name, entity_id, format, user_id):
    entity_name = entity_name.upper()
    if entity_name == 'AUTHOR':
        author = model.Author.__table__
        q = select([case([(author.c.first_name == None, author.c.last_name)],
                   else_ = author.c.first_name + ' ' + author.c.last_name)])\
            .where(author.c.id == entity_id)
    elif entity_name == 'SERIES':
        series = model.Series.__table__
        q = select([series.c.title]).where(series.c.id == entity_id)
    elif entity_name == 'BOOKSHELF':
        shelf = model.Bookshelf.__table__
        q = select([shelf.c.name]).where(shelf.c.id == entity_id)
    else:
        raise ValueError('Invalid entity name')
    
    format_id = await get_format_id(format)
    
    async with engine.acquire() as conn:
        batch = model.ConversionBatch.__table__
        res = await conn.execute(q)
        name = await res.scalar()
        name = "Books for %s %s" % (entity_name.lower(), name)
        res = await conn.execute(batch.insert()\
                                 .values(name=name, for_entity=entity_name,
                                    entity_id=entity_id, format_id=format_id,
                                    created_by_id = user_id,
                                    modified_by_id = user_id, version_id =1 )\
                                 .returning(batch.c.id))
        
        return await res.scalar()

def start(conf):
    # connect to db
    db.engine = engine = engine_from_config(dict(conf.items('sqlalchemy')), prefix='')
    db.metadata.bind = engine
    conn = engine.connect()

    Session = sessionmaker(bind=engine)
    session = Session()
    
    profiles = []
    topics = []
    for user in session.query(User):
        for profile in user.profiles:
            if profile.origin == 5:
                profiles.append(profile.profile_id)
        for topic in user.topics:
            if topic.profile_id in profiles:
                topics.append(topic.topic_id)

    for topic_id in topics:
        print "checking", topic_id
        s = select([func.count(db.t_message.c.message_id)], and_(db.t_message.c.origin == 5, db.t_message.c.topic_id == topic_id))
        (count,) = conn.execute(s).fetchone()
        if count > 1000:
            (m_id,) = conn.execute(select([db.t_message.c.message_id],
                                           db.t_message.c.topic_id == topic_id).order_by(
                                                    db.t_message.c.message_id.desc()).offset(1000).limit(1)).fetchone()
            print "purging", topic_id, count, m_id
            conn.execute(db.t_message.delete().where(and_(db.t_message.c.message_id < m_id, db.t_message.c.topic_id == topic_id)))

def create_mapper(tag_tbl, tag_domain_tbl, tag_predicate_tbl, tag_value_tbl,
                  tagging_tbl):
    "Mapper factory."
    m = mapper(Tag,
               tag_tbl,
               id_attribute='tag_id',
               slug_expression=lambda cls: as_slug_expression(
                                        func.concatenate(cls.domain, ':',
                                                         cls.predicate, '=',
                                                         cls.value)),
               extension=TagMapperExtension(tag_domain_tbl,
                                            tag_predicate_tbl, tag_value_tbl),
               properties=
                    dict(tagged=relationship(Tagged,
                                             secondary=tagging_tbl,
                                             back_populates='tags'),
                         domain=column_property(
                                    select([tag_domain_tbl.c.domain]) \
                                    .where(tag_tbl.c.tag_domain_id ==
                                        tag_domain_tbl.c.tag_domain_id)
                                    ),
                         predicate=column_property(
                                    select([tag_predicate_tbl.c.predicate]) \
                                    .where(tag_tbl.c.tag_predicate_id ==
                                        tag_predicate_tbl.c.tag_predicate_id)
                                    ),
                         value=column_property(
                                    select([tag_value_tbl.c.value]) \
                                    .where(tag_tbl.c.tag_value_id ==
                                        tag_value_tbl.c.tag_value_id)
                                    ),
                         )
               )
    return m

    def testMetricDataTimeStampQueryParams(uid):
      '''
        This test makes MetricDataHandler GET calls with from and to params :
          _models/<uid>/data?from=<>&to=<>
      '''
      with repository.engineFactory().connect() as conn:
        firstMetricData = conn.execute(
          sql.select([schema.metric_data])
          .where(schema.metric_data.c.uid == uid)
          .order_by(sql.expression.asc(schema.metric_data.c.timestamp))
          .limit(1)).fetchall()

        lastMetricData = conn.execute(
          sql.select([schema.metric_data])
          .where(schema.metric_data.c.uid == uid)
          .order_by(sql.expression.desc(schema.metric_data.c.timestamp))
          .limit(1)).fetchall()
      firstTimeStamp = firstMetricData[0].timestamp
      lastTimeStamp = lastMetricData[0].timestamp
      response = self.app.get("/%s/data?from=%s&to=%s"
        % (uid, firstTimeStamp, lastTimeStamp), headers=self.headers)
      assertions.assertSuccess(self, response)
      getAllModelsResult = utils.jsonDecode(response.body)
      for metricData in getAllModelsResult['data']:
        self.assertGreaterEqual(datetime.strptime(metricData[0],
          '%Y-%m-%d %H:%M:%S'), firstTimeStamp)
        self.assertLessEqual(datetime.strptime(metricData[0],
          '%Y-%m-%d %H:%M:%S'), lastTimeStamp)

def get_school_info(connection,school_id=None):
    if school_id is None:
        stmt = select([school])
        return connection.execute(stmt)
    else:
        stmt = select([school]).where(school.c.id == school_id)
        return connection.execute(stmt)