Python sqlalchemy.MetaData方法代碼示例

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def connect_db(self):
        """Connect the state manager to the persistent DB."""
        self.db_engine = create_engine(
            config.config_mgr.conf.database.database_connect_string,
            pool_size=config.config_mgr.conf.database.pool_size,
            pool_pre_ping=config.config_mgr.conf.database.pool_pre_ping,
            max_overflow=config.config_mgr.conf.database.pool_overflow,
            pool_timeout=config.config_mgr.conf.database.pool_timeout,
            pool_recycle=config.config_mgr.conf.database.connection_recycle)
        self.db_metadata = MetaData(bind=self.db_engine)

        self.tasks_tbl = tables.Tasks(self.db_metadata)
        self.result_message_tbl = tables.ResultMessage(self.db_metadata)
        self.active_instance_tbl = tables.ActiveInstance(self.db_metadata)
        self.boot_action_tbl = tables.BootAction(self.db_metadata)
        self.ba_status_tbl = tables.BootActionStatus(self.db_metadata)
        self.build_data_tbl = tables.BuildData(self.db_metadata)
        return 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def upgrade():
    op.add_column("resource_type", sa.Column('tablename', sa.String(18),
                                             nullable=True))

    resource_type = sa.Table(
        'resource_type', sa.MetaData(),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('tablename', sa.String(18), nullable=True)
    )
    op.execute(resource_type.update().where(
        resource_type.c.name == "instance_network_interface"
    ).values({'tablename': op.inline_literal("'instance_net_int'")}))
    op.execute(resource_type.update().where(
        resource_type.c.name != "instance_network_interface"
    ).values({'tablename': resource_type.c.name}))

    op.alter_column("resource_type", "tablename", type_=sa.String(18),
                    nullable=False)
    op.create_unique_constraint("uniq_resource_type0tablename",
                                "resource_type", ["tablename"]) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def upgrade():
    op.add_column('keypairs', sa.Column('ssh_public_key', sa.String(length=750), nullable=True))
    op.add_column('keypairs', sa.Column('ssh_private_key', sa.String(length=2000), nullable=True))

    # partial table to be preserved and referred
    metadata = sa.MetaData(naming_convention=convention)
    keypairs = sa.Table(
        'keypairs', metadata,
        sa.Column('access_key', sa.String(length=20), primary_key=True),
        sa.Column('ssh_public_key', sa.String(length=750), nullable=True),
        sa.Column('ssh_private_key', sa.String(length=2000), nullable=True),
    )

    # Fill in SSH keypairs in every keypairs.
    conn = op.get_bind()
    query = sa.select([keypairs.c.access_key]).select_from(keypairs)
    rows = conn.execute(query).fetchall()
    for row in rows:
        pubkey, privkey = generate_ssh_keypair()
        query = (sa.update(keypairs)
                   .values(ssh_public_key=pubkey, ssh_private_key=privkey)
                   .where(keypairs.c.access_key == row.access_key))
        conn.execute(query) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def test_notna_dtype(self):
        cols = {'Bool': Series([True, None]),
                'Date': Series([datetime(2012, 5, 1), None]),
                'Int': Series([1, None], dtype='object'),
                'Float': Series([1.1, None])
                }
        df = DataFrame(cols)

        tbl = 'notna_dtype_test'
        df.to_sql(tbl, self.conn)
        returned_df = sql.read_sql_table(tbl, self.conn)  # noqa
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        if self.flavor == 'mysql':
            my_type = sqltypes.Integer
        else:
            my_type = sqltypes.Boolean

        col_dict = meta.tables[tbl].columns

        assert isinstance(col_dict['Bool'].type, my_type)
        assert isinstance(col_dict['Date'].type, sqltypes.DateTime)
        assert isinstance(col_dict['Int'].type, sqltypes.Integer)
        assert isinstance(col_dict['Float'].type, sqltypes.Float) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def db_version():
    repository = _find_migrate_repo()
    try:
        return versioning_api.db_version(get_engine(), repository)
    except versioning_exceptions.DatabaseNotControlledError:
        meta = sqlalchemy.MetaData()
        engine = get_engine()
        meta.reflect(bind=engine)
        tables = meta.tables
        if len(tables) == 0:
            db_version_control(INIT_VERSION)
            return versioning_api.db_version(get_engine(), repository)
        else:
            # Some pre-Essex DB's may not be version controlled.
            # Require them to upgrade using Essex first.
            raise exception.EC2Exception(
                _("Upgrade DB using Essex release first.")) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def bind(self):
        """Return the current "bind".

        In online mode, this is an instance of
        :class:`sqlalchemy.engine.Connection`, and is suitable
        for ad-hoc execution of any kind of usage described
        in :ref:`sqlexpression_toplevel` as well as
        for usage with the :meth:`sqlalchemy.schema.Table.create`
        and :meth:`sqlalchemy.schema.MetaData.create_all` methods
        of :class:`~sqlalchemy.schema.Table`,
        :class:`~sqlalchemy.schema.MetaData`.

        Note that when "standard output" mode is enabled,
        this bind will be a "mock" connection handler that cannot
        return results and is only appropriate for a very limited
        subset of commands.

        """
        return self.connection 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def upgrade():
	conn = alembic.context.get_context().bind
	meta = sqlalchemy.MetaData(bind=conn)
	meta.reflect()
	game_stats = meta.tables["game_stats"]
	shows = meta.tables["shows"]

	constraint_name = None
	for fk in game_stats.c.stat_id.foreign_keys:
		if fk.column.table is shows and fk.column.name == "id":
			constraint_name = fk.name
			break
	else:
		raise Exception("Failed to find a foreign key on `game_stats.stat_id` that references `shows.id`")

	alembic.op.drop_constraint(constraint_name, 'game_stats')
	alembic.op.create_foreign_key(constraint_name, 'game_stats', 'stats', ["stat_id"], ["id"], onupdate="CASCADE", ondelete="CASCADE") 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def downgrade():
	conn = alembic.context.get_context().bind
	meta = sqlalchemy.MetaData(bind=conn)
	meta.reflect()
	game_stats = meta.tables["game_stats"]
	stats = meta.tables["stats"]

	constraint_name = None
	for fk in game_stats.c.stat_id.foreign_keys:
		if fk.column.table is stats and fk.column.name == "id":
			constraint_name = fk.name
			break
	else:
		raise Exception("Failed to find a foreign key on `game_stats.stat_id` that references `stats.id`")

	alembic.op.drop_constraint(constraint_name, 'game_stats')
	alembic.op.create_foreign_key(constraint_name, 'game_stats', 'shows', ["stat_id"], ["id"], onupdate="CASCADE", ondelete="CASCADE") 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def downgrade():
	conn = alembic.context.get_context().bind
	meta = sqlalchemy.MetaData(bind=conn)
	meta.reflect()
	users = meta.tables["users"]

	datafile = alembic.context.config.get_section_option("lrrbot", "datafile", "data.json")
	with open(datafile) as f:
		data = json.load(f)
	data["autostatus"] = [nick for nick, in conn.execute(sqlalchemy.select([users.c.name]).where(users.c.autostatus))]
	data["subs"] = [nick for nick, in conn.execute(sqlalchemy.select([users.c.name]).where(users.c.is_sub))]
	data["mods"] = [nick for nick, in conn.execute(sqlalchemy.select([users.c.name]).where(users.c.is_mod))]
	data["twitch_oauth"] = {
		name: key
		for name, key in conn.execute(sqlalchemy.select([users.c.name, users.c.twitch_oauth]).where(users.c.twitch_oauth != None))
	}
	with open(datafile, "w") as f:
		json.dump(data, f, indent=2, sort_keys=True)
	alembic.op.drop_table("users") 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def downgrade():
	alembic.op.add_column("users", sqlalchemy.Column("patreon_access_token", sqlalchemy.Text))
	alembic.op.add_column("users", sqlalchemy.Column("patreon_refresh_token", sqlalchemy.Text))
	alembic.op.add_column("users", sqlalchemy.Column("patreon_token_expires", sqlalchemy.DateTime(timezone=True)))

	conn = alembic.op.get_bind()
	meta = sqlalchemy.MetaData(bind=conn)
	meta.reflect()
	users = meta.tables["users"]
	patreon_users = meta.tables["patreon_users"]
	alembic.op.execute(users.update().where(users.c.patreon_id == patreon_users.c.id)).values({
		"patreon_access_token": patreon_users.c.access_token,
		"patreon_refresh_token": patreon_users.c.refresh_token,
		"patreon_token_expires": patreon_users.c.token_expires,
	})

	alembic.op.drop_column("users", "patreon_id")
	alembic.op.drop_table("patreon_users") 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def export_to_csv(directory='.'):
    """
    Export the data in the psql to a csv

    :param session ether_sql_session: ether_sql session
    :param str directory: Directory where the data should be exported
    """

    current_session = get_current_session()
    # create the directory is it does not exist
    if not os.path.exists(directory):
        os.makedirs(directory)

    metadata = MetaData(current_session.db_engine)
    metadata.reflect()
    conn = current_session.db_engine.raw_connection()
    cursor = conn.cursor()
    for _table_name in metadata.tables:
        dbcopy_to = open('{}/{}.csv'.format(directory, _table_name), 'wb')
        copy_sql = 'COPY {} TO STDOUT WITH CSV HEADER'.format(_table_name)

        cursor.copy_expert(copy_sql, dbcopy_to)
        logger.debug('exported table {}'.format(_table_name))

    conn.close() 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def _create_table(engine, table_name, columns):
    meta = sqlalchemy.MetaData()
    meta.bind = engine

    if engine.has_table(table_name):
        table = sqlalchemy.schema.Table(table_name, meta, autoload=True)
        table.drop()

    table = sqlalchemy.schema.Table(table_name, meta)
    id_col = sqlalchemy.schema.Column('_id', sqlalchemy.types.Integer, primary_key=True)
    table.append_column(id_col)
    for (_, name, typ) in sorted(_column_specs(columns)):
        col = sqlalchemy.schema.Column(name, typ, primary_key=name in ('cap_or_cur', 'cofog1_name'))
        table.append_column(col)

    table.create(engine)
    return table 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def sa_table():
    choices = ['a', 'b', 'c']
    meta = sa.MetaData()
    post = sa.Table(
        'test_post', meta,
        sa.Column('id', sa.Integer, nullable=False),
        sa.Column('title', sa.String(200), nullable=False),
        sa.Column('category', sa.String(200), nullable=True),
        sa.Column('body', sa.Text, nullable=False),
        sa.Column('views', sa.Integer, nullable=False),
        sa.Column('average_note', sa.Float, nullable=False),
        # sa.Column('pictures', postgresql.JSON, server_default='{}'),
        sa.Column('published_at', sa.DateTime, nullable=False),
        # sa.Column('tags', postgresql.ARRAY(sa.Integer), server_default='{}'),
        sa.Column('status',
                  sa.Enum(*choices, name="enum_name", native_enum=False),
                  server_default="a", nullable=False),
        sa.Column('visible', sa.Boolean, nullable=False),

        # Indexes #
        sa.PrimaryKeyConstraint('id', name='post_id_pkey'))
    return post 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def reflect_hints_db(db_path):
    """
    Reflect the database schema of the hints database, automapping the existing tables

    The NullPool is used to avoid concurrency issues with luigi. Using this activates pooling, but since sqlite doesn't
    really support pooling, what effectively happens is just that it locks the database and the other connections wait.

    :param db_path: path to hints sqlite database
    :return: sqlalchemy.MetaData object, sqlalchemy.orm.Session object
    """
    engine = sqlalchemy.create_engine('sqlite:///{}'.format(db_path), poolclass=NullPool)
    metadata = sqlalchemy.MetaData()
    metadata.reflect(bind=engine)
    Base = automap_base(metadata=metadata)
    Base.prepare()
    speciesnames = Base.classes.speciesnames
    seqnames = Base.classes.seqnames
    hints = Base.classes.hints
    featuretypes = Base.classes.featuretypes
    Session = sessionmaker(bind=engine)
    session = Session()
    return speciesnames, seqnames, hints, featuretypes, session 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import MetaData [as 別名]
def upgrade():
    bind = op.get_bind()
    for table_name in ('resource', 'resource_history'):
        table = sa.Table(table_name, sa.MetaData(),
                         sa.Column('id',
                                   sqlalchemy_utils.types.uuid.UUIDType(),
                                   nullable=False),
                         sa.Column('original_resource_id', sa.String(255)))

        # NOTE(gordc): mysql stores id as binary so we need to rebuild back to
        # string uuid.
        if bind and bind.engine.name == "mysql":
            vals = {'original_resource_id':
                    clean_substr(table.c.id, 1, 8) + '-' +
                    clean_substr(table.c.id, 9, 4) + '-' +
                    clean_substr(table.c.id, 13, 4) + '-' +
                    clean_substr(table.c.id, 17, 4) + '-' +
                    clean_substr(table.c.id, 21, 12)}
        else:
            vals = {'original_resource_id': table.c.id}

        op.execute(table.update().where(
            table.c.original_resource_id.is_(None)).values(vals))
        op.alter_column(table_name, "original_resource_id", nullable=False,
                        existing_type=sa.String(255),
                        existing_nullable=True)