Python expression.column函数代码示例

    def migrate(self):
        self.add_userid_column()
        self.migrate_data()
        self.remove_watcherid_column()
        self.make_userid_column_required()

        self.notifications_table = table("notifications", column("id"), column("activity_id"), column("watcher_id"))

    def migrate_data(self):
        activities_table = table(
            "activities", column("id"), column("kind"), column("summary"), column("title"), column("description")
        )

        activities_translation_table = table(
            "activities_translation",
            column("id"),
            column("locale"),
            column("title"),
            column("label"),
            column("summary"),
            column("description"),
        )

        activities = self.connection.execute(activities_table.select()).fetchall()
        for activity in activities:
            self.execute(
                activities_translation_table.insert(
                    values={
                        "id": activity.id,
                        "locale": DEFAULT_LOCALE,
                        "title": activity.title,
                        # the label column is new so we use the kind
                        # for existing entries
                        "label": activity.kind,
                        "summary": activity.summary,
                        "description": activity.description,
                    }
                )
            )

    def add_meeting_sequence_to_period(self):
        self.op.add_column("periods", Column("meeting_sequence_number", Integer, nullable=True))

        periods_table = table("periods", column("id"), column("meeting_sequence_number"))
        self.execute(periods_table.update().values(meeting_sequence_number=0))

        self.op.alter_column("periods", "meeting_sequence_number", existing_type=Integer, nullable=False)

    def raise_column_length(self):

        # add new column with the new size
        self.op.add_column(
            'favorites',
            Column('tmp_plone_uid', String(UID_LENGTH)))

        # migrate_data
        _table = table(
            'favorites',
            column("id"),
            column('plone_uid'),
            column('tmp_plone_uid'))

        items = self.connection.execute(_table.select()).fetchall()
        for item in items:
            self.execute(
                _table.update()
                .values(tmp_plone_uid=item.plone_uid)
                .where(_table.columns.id == item.id)
            )

        # drop old column
        self.op.drop_column('favorites', 'plone_uid')

        # rename new column
        self.op.alter_column('favorites',
                             'tmp_plone_uid',
                             new_column_name='plone_uid')

def downgrade():
    op.create_table(
        'planet_name',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String, nullable=False),
    )
    op.create_unique_constraint('uc_planet_name', 'planet_name', ['name'])
    op.add_column('libration', sa.Column(
        'first_planet_name_id', sa.Integer, sa.ForeignKey('planet_name.id'), nullable=True
    ))
    op.add_column('libration', sa.Column(
        'second_planet_name_id', sa.Integer, sa.ForeignKey('planet_name.id'), nullable=True
    ))

    planet_name_table = table('planet_name', column('id', sa.Integer), column('name', sa.String))
    op.bulk_insert(planet_name_table, [
        {'id': 9, 'name': 'PLUTO'},
        {'id': 8, 'name': 'NEPTUNE'},
        {'id': 7, 'name': 'URANUS'},
        {'id': 6, 'name': 'SATURN'},
        {'id': 5, 'name': 'JUPITER'},
        {'id': 4, 'name': 'MARS'},
        {'id': 3, 'name': 'EARTHMOO'},
        {'id': 2, 'name': 'VENUS'},
        {'id': 1, 'name': 'MERCURY'},
    ])

    op.execute('UPDATE libration SET first_planet_name_id=5, second_planet_name_id=6')

    op.alter_column('libration', 'first_planet_name_id', nullable=False)
    op.alter_column('libration', 'second_planet_name_id', nullable=False)
    op.drop_constraint('libration_resonance_id_key', 'libration')
    op.create_unique_constraint('uc_resonance_planet_names', 'libration',
                                ['resonance_id', 'first_planet_name_id', 'second_planet_name_id'])

 def get_existing_id_lookup(self):
     contact_table = table(
         "contacts",
         column('id'),
         column('former_contact_id'))
     stmt = select([contact_table.c.former_contact_id, contact_table.c.id])
     return {key: value for (key, value) in self.db_session.execute(stmt)}

    def add_bucket_grouping_to_query(self, q, q_entities, entity, mapped_entity):
        # Get min, max if not provided.
        entity_min = 0
        entity_max = 0
        if (not entity.has_key('min') or not entity.has_key('max')):
            entity_min, entity_max = self.get_entity_min_max(entity)

        # Override calculated min/max if values were provided.
        if entity.has_key('min'): entity_min = entity.get('min')
        if entity.has_key('max'): entity_max = entity.get('max')

        num_buckets = entity.get('num_buckets', 10)
        entity_range = entity_max - entity_min

        bucket_width = (entity_max - entity_min)/float(num_buckets)

        # Get bucket field entities.

        # Can use the line below in case db doesn't have width_bucket function.
        #bucket_id_entity = func.greatest(func.round( (((mapped_entity - entity_min)/entity_range) * num_buckets ) - .5) + 1, num_buckets).label(self.get_bucket_id_label(entity))
        bucket_id_entity = func.width_bucket(mapped_entity, entity_min, entity_max, num_buckets).label(self.get_bucket_id_label(entity))
        q_entities.add(bucket_id_entity)
        bucket_label_entity = case(
                [(bucket_id_entity == num_buckets + 1, '[' + cast( entity_max, String) + ', ...)')], 
                else_ = '[' + cast(entity_min + bucket_width * (bucket_id_entity - 1), String ) + ', ' + cast(entity_min + bucket_width * (bucket_id_entity), String) + ')'  ).label(entity['label'])
        q_entities.add(bucket_id_entity)
        q_entities.add(bucket_label_entity)

        q = q.group_by(column(bucket_id_entity._label), column(bucket_label_entity._label))

        return q

def upgrade():
    op.create_table(
        "planet_name", sa.Column("id", sa.Integer, primary_key=True), sa.Column("name", sa.String, nullable=False)
    )
    op.create_unique_constraint("uc_planet_name", "planet_name", ["name"])
    op.add_column(
        "libration", sa.Column("first_planet_name_id", sa.Integer, sa.ForeignKey("planet_name.id"), nullable=True)
    )
    op.add_column(
        "libration", sa.Column("second_planet_name_id", sa.Integer, sa.ForeignKey("planet_name.id"), nullable=True)
    )

    planet_name_table = table("planet_name", column("id", sa.Integer), column("name", sa.String))
    op.bulk_insert(
        planet_name_table,
        [
            {"id": 9, "name": "PLUTO"},
            {"id": 8, "name": "NEPTUNE"},
            {"id": 7, "name": "URANUS"},
            {"id": 6, "name": "SATURN"},
            {"id": 5, "name": "JUPITER"},
            {"id": 4, "name": "MARS"},
            {"id": 3, "name": "EARTHMOO"},
            {"id": 2, "name": "VENUS"},
            {"id": 1, "name": "MERCURY"},
        ],
    )

    op.execute("UPDATE libration SET first_planet_name_id=5, second_planet_name_id=6")

    def insert_default_value(self, tablename):
        contact_table = table(
            tablename,
            column("id"),
            column("is_active"))

        self.connection.execute(
            contact_table.update().values(is_active=True))

    def get_watcherid_userid_mapping(self):
        mapping = {}
        watchers_table = table("watchers", column("id"), column("user_id"))

        watchers = self.connection.execute(watchers_table.select()).fetchall()
        for watcher in watchers:
            mapping[watcher.id] = watcher.user_id

        return mapping

    def insert_placeholders(self):
        """Fill empty MailAddress with a placeholder.
        """
        mailaddress_table = table('mail_addresses',
                                  column("id"),
                                  column("address"))

        self.execute(mailaddress_table.update()
                     .values(address=EMPTY_ADDRESS_PLACEHOLDER)
                     .where(mailaddress_table.columns.address == None))

    def migrate_data(self):
        """Temporarily insert placeholders as repository_folder_title,
        the real value will be inserted by the 4633 upgradestep.
        """
        default_language = get_preferred_language_code()

        proposal_table = table("proposals", column("id"), column("repository_folder_title"), column("language"))

        self.execute(proposal_table.update().values(repository_folder_title=u"-"))
        self.execute(proposal_table.update().values(language=default_language))

    def migrate_data(self):
        """Temporarily insert placeholders as dossier_reference_numbers,
        the real value will be inserted by the 4603 upgradestep.
        """
        proposal_table = table("proposals",
                               column("id"),
                               column("dossier_reference_number"))

        self.execute(
            proposal_table.update().values(dossier_reference_number=u'-'))

    def insert_default_modified(self):
        """Insert time of migration as last modified timestamp."""

        meeting_table = table(
            'meetings',
            column('id'),
            column('modified'),
        )

        self.execute(meeting_table.update().values(modified=utcnow_tz_aware()))

def add_admin_user():
    stmt = table('users',
            column('username'),
            column('_password_hash')).insert().\
                values\
                  (
                    username=sa.bindparam('username'),
                    _password_hash=sa.bindparam('_password_hash'),
                  )
    op.get_bind().execute(stmt,[dict(username='admin',_password_hash=pw('admin'))])