Python sql.select方法代碼示例

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def get_tasks(self):
        """Get all tasks in the database."""
        try:
            with self.db_engine.connect() as conn:
                query = sql.select([self.tasks_tbl])
                rs = conn.execute(query)

                task_list = [objects.Task.from_db(dict(r)) for r in rs]

                self._assemble_tasks(task_list=task_list)

                # add reference to this state manager to each task
                for t in task_list:
                    t.statemgr = self

            return task_list
        except Exception as ex:
            self.logger.error("Error querying task list: %s" % str(ex))
            return [] 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def _assemble_tasks(self, task_list=None):
        """Attach all the appropriate result messages to the tasks in the list.

        :param task_list: a list of objects.Task instances to attach result messages to
        """
        if task_list is None:
            return None

        with self.db_engine.connect() as conn:
            query = sql.select([
                self.result_message_tbl
            ]).where(self.result_message_tbl.c.task_id == sql.bindparam(
                'task_id')).order_by(self.result_message_tbl.c.sequence.asc())
            query.compile(self.db_engine)

            for t in task_list:
                rs = conn.execute(query, task_id=t.task_id.bytes)
                error_count = 0
                for r in rs:
                    msg = objects.TaskStatusMessage.from_db(dict(r))
                    if msg.error:
                        error_count = error_count + 1
                    t.result.message_list.append(msg)
                t.result.error_count = error_count 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def get_boot_action(self, action_id):
        """Query for a single boot action by ID.

        :param action_id: string ULID bootaction id
        """
        try:
            with self.db_engine.connect() as conn:
                query = self.ba_status_tbl.select().where(
                    self.ba_status_tbl.c.action_id == ulid2.decode_ulid_base32(
                        action_id))
                rs = conn.execute(query)
                r = rs.fetchone()
                if r is not None:
                    ba_dict = dict(r)
                    ba_dict['action_id'] = bytes(ba_dict['action_id'])
                    ba_dict['identity_key'] = bytes(ba_dict['identity_key'])
                    ba_dict['task_id'] = uuid.UUID(bytes=ba_dict['task_id'])
                    return ba_dict
                else:
                    return None
        except Exception as ex:
            self.logger.error(
                "Error querying boot action %s" % action_id, exc_info=ex) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def for_update_clause(self, select, **kw):
        if self.is_subquery():
            return ""

        tmp = ' FOR UPDATE'

        if select._for_update_arg.of:
            tmp += ' OF ' + ', '.join(
                self.process(elem, **kw) for elem in
                select._for_update_arg.of
            )

        if select._for_update_arg.nowait:
            tmp += " NOWAIT"

        return tmp 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def create_uuids(migrate_engine, primary_table_name, revision_table_name):
    # have changed type of cols so recreate metadata
    metadata = MetaData(migrate_engine)

    # 4 create uuids for primary entities and in related tables
    primary_table = Table(primary_table_name, metadata, autoload=True)
    if revision_table_name:
        revision_table = Table(revision_table_name, metadata, autoload=True)
    # fetchall wouldn't be optimal with really large sets of data but here <20k
    ids = [ res[0] for res in
            migrate_engine.execute(select([primary_table.c.id])).fetchall() ]
    for count,id in enumerate(ids):
        # if count % 100 == 0: print(count, id)
        myuuid = make_uuid()
        update = primary_table.update().where(primary_table.c.id==id).values(id=myuuid)
        migrate_engine.execute(update)
    if revision_table_name:
        # ensure each id in revision table match its continuity id.
        q = revision_table.update().values(id=revision_table.c.continuity_id)
        migrate_engine.execute(q) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def get_profit_for_pair(exchange, pair):
    """Iterates through all trades for given exchange pair over the course of trading. Starts by subtracting the long positions (the buys) and adding the short positions (the sells) to arrive at the difference (profit"""
    """The buys are always the even rows and the sells are the odd rows (buy always before sell starting from zero)"""
    profit = 0
    counter = 0
    s = select([database.TradingPositions]).where(and_(database.TradingPositions.c.Exchange == exchange, database.TradingPositions.c.Pair == pair))
    result = conn.execute(s)

    for row in result:
        if counter % 2 == 0:
            profit = profit - row[5]
            counter += 1
        else:
            profit = profit + row[5]
            counter += 1
        return profit 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def test_no_net_change_timestamp(self):
        t = self._timestamp_fixture()

        import datetime

        self.conn.execute(
            t.insert(), {"x": datetime.datetime(2012, 5, 18, 15, 32, 5)}
        )

        with self.op.batch_alter_table("hasts") as batch_op:
            batch_op.alter_column("x", type_=DateTime())

        eq_(
            self.conn.execute(select([t.c.x])).fetchall(),
            [(datetime.datetime(2012, 5, 18, 15, 32, 5),)],
        ) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def intersect(self,qs):
        #here the .self_group() is necessary to ensure the correct grouping within the INTERSECT...
        my_s = self.get_bare_select(columns = [self.table.c.pk.label('pk')]).alias()
        qs_s = qs.get_bare_select(columns = [qs.table.c.pk.label('pk')]).alias()
        my_pk_s = select([column('pk')]).select_from(my_s)
        qs_pk_s = select([column('pk')]).select_from(qs_s)
        condition = self.table.c.pk.in_(expression.intersect(my_pk_s,qs_pk_s))
        new_qs = QuerySet(self.backend,
                          self.table,
                          self.cls,
                          condition = condition,
                          order_bys = self.order_bys,
                          raw = self.raw,
                          include = self.include,
                          only = self.only)
        return new_qs 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def append(self,obj):
        with self.obj.backend.transaction(implicit = True):

            #if the object is not yet in a DB, we save it first.

            if obj.pk is None:
                self.obj.backend.save(obj)

            relationship_table = self.params['relationship_table']
            condition = and_(relationship_table.c[self.params['related_pk_field_name']] == obj.pk,
                             relationship_table.c[self.params['pk_field_name']] == self.obj.pk)
            s = select([func.count(text('*'))]).where(condition)
            result = self.obj.backend.connection.execute(s)
            cnt = result.first()[0]
            if cnt:
                return #the object is already inside
            values = {
                self.params['pk_field_name'] : self.obj.pk,
                self.params['related_pk_field_name'] : obj.pk
            }
            insert = relationship_table.insert().values(**values)
            self.obj.backend.connection.execute(insert)
            self._queryset = None 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def upgrade():
    relative_modifier = table('relative_modifier',
            column('id', sa.Integer),
            column('value', sa.Float),
            column('numeric_value', sa.Numeric(precision=8, scale=5)))
    op.add_column('relative_modifier',
            sa.Column('numeric_value', sa.Numeric(precision=8, scale=5)))
    conn = op.get_bind()
    sel = select([relative_modifier.c.id, relative_modifier.c.value])
    results = conn.execute(sel)
    q = Decimal(10) ** -5
    for id_, float_value in results:
        decimal_value = Decimal(float_value).quantize(q)
        up = update(relative_modifier).where(relative_modifier.c.id == id_)\
                .values({'numeric_value': decimal_value})
        conn.execute(up)
    op.drop_column('relative_modifier', 'value')
    op.alter_column('relative_modifier', 'numeric_value', nullable=True,
            new_column_name='value', existing_type=sa.Numeric(precision=8,
                    scale=5)) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def downgrade():
    relative_modifier = table('relative_modifier',
            column('id', sa.Integer),
            column('value', sa.Numeric(precision=8, scale=5)),
            column('float_value', sa.Float))
    op.add_column('relative_modifier', sa.Column('float_value', sa.Float))
    conn = op.get_bind()
    sel = select([relative_modifier.c.id, relative_modifier.c.value])
    results = conn.execute(sel)
    for id_, decimal_value in results:
        float_value = float(decimal_value)
        up = update(relative_modifier).where(relative_modifier.c.id == id_)\
                .values({'float_value': float_value})
        conn.execute(up)
    op.drop_column('relative_modifier', 'value')
    op.alter_column('relative_modifier', 'float_value', nullable=True,
            new_column_name='value', existing_type=sa.Float) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def add_note(db, server, user, text):
    id_query = select([sqlalchemy.sql.expression.func.max(table.c.note_id).label("maxid")]) \
        .where(table.c.user == user.lower())
    max_id = db.execute(id_query).scalar()

    if max_id is None:
        note_id = 1
    else:
        note_id = max_id + 1

    query = table.insert().values(
        note_id=note_id,
        connection=server,
        user=user.lower(),
        text=text,
        deleted=False,
        added=datetime.today()
    )
    db.execute(query)
    db.commit() 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def test_limit_offset(self):
        t1 = table('t1', column('c1'), column('c2'), column('c3'))
        s = select([t1]).limit(3).offset(5)
        #assert s ==
        s = select([t1]).limit(3)
        #assert s ==
        s = select([t1]).limit(3).distinct()
        #assert s ==
        s = select([t1]).order_by(t1.c.c2).limit(3).offset(5).distinct()
        #assert s ==
        s = select([t1]).order_by(t1.c.c2).limit(3).offset(5)
        #assert s ==
        s = select([t1]).order_by(t1.c.c2).offset(5)
        #assert s ==
        s = select([t1]).order_by(t1.c.c2).limit(3)
        #assert s ==
        stmt = s.compile(self.engine) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def setup(self):
        self.conn = testing.db.connect()
        self.engine = self.conn.engine
        self.dialect = self.conn.dialect
        self.metadata = MetaData()

        self.user_name = self.engine.execute('sel user').scalar()
        self.db_schema = self.engine.execute('sel database').scalar()
        self.tbl_name = self.user_name + '_test'
        self.view_name = self.user_name + '_test_view'

        # Setup test table (user should have necessary rights to create table)
        self.test_table = Table(self.tbl_name, self.metadata,
                                Column('id', Integer, primary_key=True),
                                PrimaryKeyConstraint('id', name='my_pk'))
        # Setup a test view 
        #self.test_view = CreateView(self.view_name, select([self.test_table.c.id.label('view_id')]))

        # Create tables
        self.metadata.create_all(self.engine)

        #Create views
        #self.conn.execute(self.test_view) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import select [as 別名]
def get_next_queued_task(self, allowed_actions=None):
        """Query the database for the next (by creation timestamp) queued task.

        If specified, only select tasks for one of the actions in the allowed_actions
        list.

        :param allowed_actions: list of string action names
        """
        try:
            with self.db_engine.connect() as conn:
                if allowed_actions is None:
                    query = self.tasks_tbl.select().where(
                        self.tasks_tbl.c.status == hd_fields.TaskStatus.
                        Queued).order_by(self.tasks_tbl.c.created.asc())
                    rs = conn.execute(query)
                else:
                    query = sql.text("SELECT * FROM tasks WHERE "
                                     "status = :queued_status AND "
                                     "action = ANY(:actions) "
                                     "ORDER BY created ASC")
                    rs = conn.execute(
                        query,
                        queued_status=hd_fields.TaskStatus.Queued,
                        actions=allowed_actions)

                r = rs.first()

            if r is not None:
                task = objects.Task.from_db(dict(r))
                self._assemble_tasks(task_list=[task])
                task.statemgr = self
                return task
            else:
                return None
        except Exception as ex:
            self.logger.error(
                "Error querying for next queued task: %s" % str(ex),
                exc_info=True)
            return None