Python sqlalchemy.MetaData() Examples

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") 

def test_sa_default_numeric_precision_and_scale(
    con, backend, dialects, default_precisions, default_scales
):
    # TODO: find a better way to access ibis.sql.alchemy
    import ibis.sql.alchemy as alch

    dialect = dialects[backend.name]
    default_precision = default_precisions[backend.name]
    default_scale = default_scales[backend.name]

    typespec = [
        # name, sqlalchemy type, ibis type
        ('n1', dialect.NUMERIC, dt.Decimal(default_precision, default_scale)),
        ('n2', dialect.NUMERIC(5), dt.Decimal(5, default_scale)),
        ('n3', dialect.NUMERIC(None, 4), dt.Decimal(default_precision, 4)),
        ('n4', dialect.NUMERIC(10, 2), dt.Decimal(10, 2)),
    ]

    sqla_types = []
    ibis_types = []
    for name, t, ibis_type in typespec:
        sqla_type = sa.Column(name, t, nullable=True)
        sqla_types.append(sqla_type)
        ibis_types.append((name, ibis_type(nullable=True)))

    # Create a table with the numeric types.
    table_name = 'test_sa_default_param_decimal'
    engine = con.con
    table = sa.Table(table_name, sa.MetaData(bind=engine), *sqla_types)

    # Check that we can correctly recover the default precision and scale.
    schema = alch.schema_from_table(table)
    expected = ibis.schema(ibis_types)

    assert_equal(schema, expected)
    con.drop_table(table_name, force=True) 

def __init__(self, con: sa.engine.Engine) -> None:
        super().__init__()
        self.con = con
        self.meta = sa.MetaData(bind=con)
        self._inspector = sa.inspect(con)
        self._reflection_cache_is_dirty = False
        self._schemas = {} 

def test_dtype(self):
        cols = ['A', 'B']
        data = [(0.8, True),
                (0.9, None)]
        df = DataFrame(data, columns=cols)
        df.to_sql('dtype_test', self.conn)
        df.to_sql('dtype_test2', self.conn, dtype={'B': sqlalchemy.TEXT})
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        sqltype = meta.tables['dtype_test2'].columns['B'].type
        assert isinstance(sqltype, sqlalchemy.TEXT)
        pytest.raises(ValueError, df.to_sql,
                      'error', self.conn, dtype={'B': str})

        # GH9083
        df.to_sql('dtype_test3', self.conn, dtype={'B': sqlalchemy.String(10)})
        meta.reflect()
        sqltype = meta.tables['dtype_test3'].columns['B'].type
        assert isinstance(sqltype, sqlalchemy.String)
        assert sqltype.length == 10

        # single dtype
        df.to_sql('single_dtype_test', self.conn, dtype=sqlalchemy.TEXT)
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        sqltypea = meta.tables['single_dtype_test'].columns['A'].type
        sqltypeb = meta.tables['single_dtype_test'].columns['B'].type
        assert isinstance(sqltypea, sqlalchemy.TEXT)
        assert isinstance(sqltypeb, sqlalchemy.TEXT) 

def _connect(self):
        from sqlalchemy import create_engine, MetaData
        if self.driver == 'pyhive':
            try:
                import pyhive.hive
            except ImportError:
                raise ImportError("""
                    Omniduct is attempting to use the 'pyhive' driver, but it
                    is not installed. Please either install the pyhive package,
                    or reconfigure this Duct to use the 'impyla' driver.
                    """)
            self.__hive = pyhive.hive.connect(host=self.host,
                                              port=self.port,
                                              auth=self.auth_mechanism,
                                              database=self.schema,
                                              username=self.username,
                                              password=self.password,
                                              **self.connection_options)
            self._sqlalchemy_engine = create_engine('hive://{}:{}/{}'.format(self.host, self.port, self.schema))
            self._sqlalchemy_metadata = MetaData(self._sqlalchemy_engine)
        elif self.driver == 'impyla':
            try:
                import impala.dbapi
            except ImportError:
                raise ImportError("""
                    Omniduct is attempting to use the 'impyla' driver, but it
                    is not installed. Please either install the impyla package,
                    or reconfigure this Duct to use the 'pyhive' driver.
                    """)
            self.__hive = impala.dbapi.connect(host=self.host,
                                               port=self.port,
                                               auth_mechanism=self.auth_mechanism,
                                               database=self.schema,
                                               user=self.username,
                                               password=self.password,
                                               **self.connection_options)
            self._sqlalchemy_engine = create_engine('impala://{}:{}/{}'.format(self.host, self.port, self.schema))
            self._sqlalchemy_metadata = MetaData(self._sqlalchemy_engine) 

def _connect(self):
        import sqlalchemy
        if self.protocol not in ['mysql']:
            logger.warning("While querying and executing should work as "
                           "expected, some operations on this database client "
                           "(such as listing tables, querying to tables, etc) "
                           "may not function as expected due to the backend "
                           "not supporting ANSI SQL.")

        self.engine = sqlalchemy.create_engine(self.db_uri, **self.engine_opts)
        self._sqlalchemy_metadata = sqlalchemy.MetaData(self.engine) 

def _connect(self):
        from sqlalchemy import create_engine, MetaData
        logging.getLogger('pyhive').setLevel(1000)  # Silence pyhive logging.
        logger.info('Connecting to Presto coordinator...')
        self._sqlalchemy_engine = create_engine('presto://{}:{}/{}/{}'.format(self.host, self.port, self.catalog, self.schema))
        self._sqlalchemy_metadata = MetaData(self._sqlalchemy_engine) 

def _get_selectable(self, engine_or_conn, columns=None):
        import sqlalchemy as sa
        from sqlalchemy import sql
        from sqlalchemy.exc import NoSuchTableError

        # process table_name
        if self._selectable is not None:
            selectable = self._selectable
        else:
            if isinstance(self._table_or_sql, sa.Table):
                selectable = self._table_or_sql
                self._table_or_sql = selectable.name
            else:
                m = sa.MetaData()
                try:
                    selectable = sa.Table(self._table_or_sql, m, autoload=True,
                                          autoload_with=engine_or_conn, schema=self._schema)
                except NoSuchTableError:
                    temp_table_name = 'temp_' + binascii.b2a_hex(uuid.uuid4().bytes).decode()
                    if columns:
                        selectable = sql.text(self._table_or_sql).columns(*[sql.column(c) for c in columns])
                    else:
                        selectable = sql.select(
                            '*', from_obj=sql.text('(%s) AS %s' % (self._table_or_sql, temp_table_name)))
                    self._selectable = selectable
        return selectable 

def sync_trigger(conn, table_name):
    """
    Synchronizes versioning trigger for given table with given connection.

    ::


        sync_trigger(conn, 'my_table')


    :param conn: SQLAlchemy connection object
    :param table_name: Name of the table to synchronize versioning trigger for

    .. versionadded: 1.1.0
    """
    meta = sa.MetaData()
    version_table = sa.Table(
        table_name,
        meta,
        autoload=True,
        autoload_with=conn
    )
    parent_table = sa.Table(
        table_name[0:-len('_version')],
        meta,
        autoload=True,
        autoload_with=conn
    )
    excluded_columns = (
        set(c.name for c in parent_table.c) -
        set(c.name for c in version_table.c if not c.name.endswith('_mod'))
    )
    drop_trigger(conn, parent_table.name)
    create_trigger(conn, table=parent_table, excluded_columns=excluded_columns) 

def init_alembic(engine, schema_version):
	"""
	Creates the alembic_version table and sets the value of the table according
	to the specified schema version.

	:param engine: The engine used to connect to the database.
	:type engine: :py:class:`sqlalchemy.engine.Engine`
	:param int schema_version: The MetaData schema_version to set the alembic version to.
	"""
	pattern = re.compile(r'[a-f0-9]{10,16}_schema_v\d+\.py')
	alembic_revision = None
	alembic_directory = find.data_directory('alembic')
	if not alembic_directory:
		raise errors.KingPhisherDatabaseError('cannot find the alembic data directory')
	alembic_versions_files = os.listdir(os.path.join(alembic_directory, 'versions'))
	for file in alembic_versions_files:
		if not pattern.match(file):
			continue
		if not file.endswith('_schema_v' + str(schema_version) + '.py'):
			continue
		alembic_revision = file.split('_', 1)[0]
		break
	if not alembic_revision:
		raise errors.KingPhisherDatabaseError("cannot find current alembic version for schema version {0}".format(schema_version))

	alembic_metadata = sqlalchemy.MetaData(engine)
	alembic_table = sqlalchemy.Table(
		'alembic_version',
		alembic_metadata,
		sqlalchemy.Column(
			'version_num',
			sqlalchemy.String,
			primary_key=True,
			nullable=False
		)
	)
	alembic_metadata.create_all()
	alembic_version_entry = alembic_table.insert().values(version_num=alembic_revision)
	engine.connect().execute(alembic_version_entry)
	logger.info("alembic_version table initialized to {0}".format(alembic_revision)) 

def upgrade():
	conn = alembic.context.get_context().bind
	meta = sqlalchemy.MetaData(bind=conn)
	meta.reflect()
	clips = meta.tables["clips"]

	for clipid, clipjson in conn.execute(sqlalchemy.select([clips.c.id, clips.c.data])):
		conn.execute(clips.update()
			.values(data=json.loads(clipjson))
			.where(clips.c.id == clipid)
		) 

def downgrade():
	conn = alembic.context.get_context().bind
	meta = sqlalchemy.MetaData(bind=conn)
	meta.reflect()
	clips = meta.tables["clips"]

	for clipid, clipjson in conn.execute(sqlalchemy.select([clips.c.id, clips.c.data])):
		conn.execute(clips.update()
			.values(data=json.dumps(clipjson))
			.where(clips.c.id == clipid)
		) 

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") 

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") 

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") 

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 

def upgrade():
	# Create an auto-increment sequence for cards.id
	conn = alembic.context.get_context().bind
	meta = sqlalchemy.MetaData(bind=conn)
	meta.reflect()
	cards = meta.tables['cards']
	# This table already has a (not-previously-used) auto-increment sequence in
	# the production DB, but new DBs created from scratch via the alembic setup
	# won't have it, so check if it already exists and create if it's missing
	# to bring everything back into sync
	if not cards.c.id.server_default or 'cards_id_seq' not in cards.c.id.server_default.arg.text:
		maxid, = conn.execute(sqlalchemy.select([sqlalchemy.func.max(cards.c.id)])).first()
		if maxid is None:
			maxid = 0
		alembic.op.execute(CreateSequence(Sequence('cards_id_seq', start=maxid + 1)))
		alembic.op.alter_column("cards", "id", nullable=False, server_default=sqlalchemy.text("nextval('cards_id_seq'::regclass)"))

	# Add cards.game column
	# create it with a default but then remove the default, to set the value on
	# all existing rows, but have the column mandatory in the future
	alembic.op.drop_index("cards_name_idx")
	alembic.op.add_column('cards',
		sqlalchemy.Column('game', sqlalchemy.Integer, nullable=False, server_default='1')
	)
	alembic.op.alter_column("cards", "game", server_default=None)
	alembic.op.create_index("cards_name_idx", "cards", ["game", "filteredname"], unique=True) 

def _make_iris_table_metadata(self):
        sa = sqlalchemy
        metadata = sa.MetaData()
        iris = sa.Table('iris', metadata,
                        sa.Column('SepalLength', sa.REAL),
                        sa.Column('SepalWidth', sa.REAL),
                        sa.Column('PetalLength', sa.REAL),
                        sa.Column('PetalWidth', sa.REAL),
                        sa.Column('Name', sa.TEXT)
                        )

        return iris 

def _get_all_tables(self):
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        table_list = meta.tables.keys()
        return table_list 

def new_engine_and_metadata():
	"""
	Create new SQLAlchemy engine and metadata.

	NOTE: Every process should have AT MOST one engine.
	"""
	engine = sqlalchemy.create_engine(config["postgres"], echo=config["debugsql"], execution_options={"autocommit": False})
	metadata = sqlalchemy.MetaData(bind=engine)
	with warnings.catch_warnings():
		# Yes, I know you can't understand FTS indexes.
		warnings.simplefilter("ignore", category=sqlalchemy.exc.SAWarning)
		metadata.reflect()
	sqlalchemy.event.listen(engine, "engine_connect", ping_connection)
	return engine, metadata 

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() 

def __init__(self, engine, name, model, fact_table=None):
        self.name = name
        if not isinstance(model, Model):
            model = Model(model)
        self._tables = {}
        if fact_table is not None:
            self._tables[model.fact_table_name] = fact_table
        self.model = model
        self.engine = engine
        self.meta = MetaData(bind=engine) 

def sqla_engine():
    DATABASE_URI = os.environ.get('BABBAGE_TEST_DB')
    assert DATABASE_URI, 'Set the envvar BABBAGE_TEST_DB to a PostgreSQL URI'
    engine = sqlalchemy.create_engine(DATABASE_URI)

    try:
        yield engine
    finally:
        meta = sqlalchemy.MetaData(bind=engine, reflect=True)
        meta.drop_all() 

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 

def upgrade():
    bind = op.get_bind()
    md = sa.MetaData()
    sa.Table('operating_status', md, autoload=True, autoload_with=bind)
    op.bulk_insert(md.tables['operating_status'], [{'name': 'DRAINING'}]) 

def upgrade():
    bind = op.get_bind()
    md = sa.MetaData()
    sa.Table('operating_status', md, autoload=True, autoload_with=bind)
    op.bulk_insert(md.tables['operating_status'], [{'name': 'NO_MONITOR'}]) 

def test_get_type_of_fields(resources):
    table = sa.Table(
        'Test', sa.MetaData(),
        sa.Column('integer', sa.Integer, primary_key=True),
        sa.Column('text', sa.Text),
        sa.Column('float', sa.Float),
        sa.Column('date', sa.Date),
        sa.Column('boolean', sa.Boolean),
        sa.Column('json', postgresql.JSON),
    )
    fields = ['integer', 'text', 'float', 'date', 'boolean', 'json', ]

    data_type_fields = resources.get_type_of_fields(fields, table)
    expected_type_fields = {
        'integer': rc.TEXT_FIELD.value,
        'text': rc.TEXT_FIELD.value,
        'float': rc.NUMBER_FIELD.value,
        'date': rc.DATE_FIELD.value,
        'boolean': rc.BOOLEAN_FIELD.value,
        'json': rc.JSON_FIELD.value,
    }

    assert data_type_fields == expected_type_fields

    fields = None
    data_type_fields = resources.get_type_of_fields(fields, table)
    expected_type_fields = {
        'integer': rc.TEXT_FIELD.value,
    }

    assert data_type_fields == expected_type_fields


# TODO: added Mongo 

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 

def table():

    meta = sa.MetaData()
    post = sa.Table(
        'post', meta,
        sa.Column('id', sa.Integer, nullable=False),
        sa.Column('title', sa.String(200), nullable=False),
        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.Date, nullable=False),
        sa.Column('tags', postgresql.ARRAY(sa.Integer), server_default='[]'),

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

def initialize_base_schema():
    schema = Schema()

    @schema.register
    class Test(models.ModelAdmin):
        fields = ('id',)

        class Meta:
            resource_type = PGResource
            table = sa.Table(
                'test',
                sa.MetaData(),
                sa.Column('id', sa.Integer)
            )

    @schema.register
    class Test2(models.ModelAdmin):
        fields = ('id',)

        class Meta:
            resource_type = PGResource
            table = sa.Table(
                'test',
                sa.MetaData(),
                sa.Column('id', sa.Integer)
            )

    return schema