Python sqlalchemy.schema方法代码示例

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def test_datetime(self):
        df = DataFrame({'A': date_range('2013-01-01 09:00:00', periods=3),
                        'B': np.arange(3.0)})
        df.to_sql('test_datetime', self.conn)

        # with read_table -> type information from schema used
        result = sql.read_sql_table('test_datetime', self.conn)
        result = result.drop('index', axis=1)
        tm.assert_frame_equal(result, df)

        # with read_sql -> no type information -> sqlite has no native
        result = sql.read_sql_query('SELECT * FROM test_datetime', self.conn)
        result = result.drop('index', axis=1)
        if self.flavor == 'sqlite':
            assert isinstance(result.loc[0, 'A'], string_types)
            result['A'] = to_datetime(result['A'])
            tm.assert_frame_equal(result, df)
        else:
            tm.assert_frame_equal(result, df) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def test_datetime_NaT(self):
        df = DataFrame({'A': date_range('2013-01-01 09:00:00', periods=3),
                        'B': np.arange(3.0)})
        df.loc[1, 'A'] = np.nan
        df.to_sql('test_datetime', self.conn, index=False)

        # with read_table -> type information from schema used
        result = sql.read_sql_table('test_datetime', self.conn)
        tm.assert_frame_equal(result, df)

        # with read_sql -> no type information -> sqlite has no native
        result = sql.read_sql_query('SELECT * FROM test_datetime', self.conn)
        if self.flavor == 'sqlite':
            assert isinstance(result.loc[0, 'A'], string_types)
            result['A'] = to_datetime(result['A'], errors='coerce')
            tm.assert_frame_equal(result, df)
        else:
            tm.assert_frame_equal(result, df) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [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 schema [as 别名]
def has_table(table_name, con, schema=None):
    """
    Check if DataBase has named table.

    Parameters
    ----------
    table_name: string
        Name of SQL table.
    con: SQLAlchemy connectable(engine/connection) or sqlite3 DBAPI2 connection
        Using SQLAlchemy makes it possible to use any DB supported by that
        library.
        If a DBAPI2 object, only sqlite3 is supported.
    schema : string, default None
        Name of SQL schema in database to write to (if database flavor supports
        this). If None, use default schema (default).

    Returns
    -------
    boolean
    """
    pandas_sql = pandasSQL_builder(con, schema=schema)
    return pandas_sql.has_table(table_name) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def __init__(self, name, pandas_sql_engine, frame=None, index=True,
                 if_exists='fail', prefix='pandas', index_label=None,
                 schema=None, keys=None, dtype=None):
        self.name = name
        self.pd_sql = pandas_sql_engine
        self.prefix = prefix
        self.frame = frame
        self.index = self._index_name(index, index_label)
        self.schema = schema
        self.if_exists = if_exists
        self.keys = keys
        self.dtype = dtype

        if frame is not None:
            # We want to initialize based on a dataframe
            self.table = self._create_table_setup()
        else:
            # no data provided, read-only mode
            self.table = self.pd_sql.get_table(self.name, self.schema)

        if self.table is None:
            raise ValueError(
                "Could not init table '{name}'".format(name=name)) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def get_schema(frame, name, keys=None, con=None, dtype=None):
    """
    Get the SQL db table schema for the given frame.

    Parameters
    ----------
    frame : DataFrame
    name : string
        name of SQL table
    keys : string or sequence, default: None
        columns to use a primary key
    con: an open SQL database connection object or a SQLAlchemy connectable
        Using SQLAlchemy makes it possible to use any DB supported by that
        library, default: None
        If a DBAPI2 object, only sqlite3 is supported.
    dtype : dict of column name to SQL type, default None
        Optional specifying the datatype for columns. The SQL type should
        be a SQLAlchemy type, or a string for sqlite3 fallback connection.

    """

    pandas_sql = pandasSQL_builder(con=con)
    return pandas_sql._create_sql_schema(frame, name, keys=keys, dtype=dtype) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def _create_table_setup(self):
        from sqlalchemy import Table, Column, PrimaryKeyConstraint

        column_names_and_types = \
            self._get_column_names_and_types(self._sqlalchemy_type)

        columns = [Column(name, typ, index=is_index)
                   for name, typ, is_index in column_names_and_types]

        if self.keys is not None:
            if not is_list_like(self.keys):
                keys = [self.keys]
            else:
                keys = self.keys
            pkc = PrimaryKeyConstraint(*keys, name=self.name + '_pk')
            columns.append(pkc)

        schema = self.schema or self.pd_sql.meta.schema

        # At this point, attach to new metadata, only attach to self.meta
        # once table is created.
        from sqlalchemy.schema import MetaData
        meta = MetaData(self.pd_sql, schema=schema)

        return Table(self.name, meta, *columns, schema=schema) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def test_table_kwargs(metadata):
    Table("simple_items", metadata, Column("id", INTEGER, primary_key=True), schema="testschema")

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
    __tablename__ = 'simple_items'
    __table_args__ = {'schema': 'testschema'}

    id = Column(Integer, primary_key=True)
"""
    ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def test_schema_table(metadata):
    Table("simple_items", metadata, Column("name", VARCHAR), schema="testschema")

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, MetaData, String, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('name', String),
    schema='testschema'
)
"""
    ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def test_schema_boolean(metadata):
    Table(
        "simple_items", metadata, Column("bool1", INTEGER), CheckConstraint("testschema.simple_items.bool1 IN (0, 1)"), schema="testschema"
    )

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Boolean, Column, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('bool1', Boolean),
    schema='testschema'
)
"""
    ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def __init__(self, source_cls, target_cls, assocation_table):
        super(ManyToManyRelationship, self).__init__(source_cls, target_cls)

        prefix = (assocation_table.schema + ".") if assocation_table.schema else ""
        self.kwargs["secondary"] = repr(prefix + assocation_table.name)
        constraints = [c for c in assocation_table.constraints if isinstance(c, ForeignKeyConstraint)]
        constraints.sort(key=_get_constraint_sort_key)
        colname = _get_column_names(constraints[1])[0]
        tablename = constraints[1].elements[0].column.table.name
        self.preferred_name = tablename if not colname.endswith("_id") else colname[:-3] + "s"

        # Handle self referential relationships
        if source_cls == target_cls:
            self.preferred_name = "parents" if not colname.endswith("_id") else colname[:-3] + "s"
            pri_pairs = zip(_get_column_names(constraints[0]), constraints[0].elements)
            sec_pairs = zip(_get_column_names(constraints[1]), constraints[1].elements)
            pri_joins = ["{0}.{1} == {2}.c.{3}".format(source_cls, elem.column.name, assocation_table.name, col) for col, elem in pri_pairs]
            sec_joins = ["{0}.{1} == {2}.c.{3}".format(target_cls, elem.column.name, assocation_table.name, col) for col, elem in sec_pairs]
            self.kwargs["primaryjoin"] = repr("and_({0})".format(", ".join(pri_joins))) if len(pri_joins) > 1 else repr(pri_joins[0])
            self.kwargs["secondaryjoin"] = repr("and_({0})".format(", ".join(sec_joins))) if len(sec_joins) > 1 else repr(sec_joins[0]) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def render_table(self, model):
        # Manual edit:
        # replace invalid chars
        table_name = model.table.name.replace("$", "_S_")
        rendered = "t_{0} = Table(\n{1}{0!r}, metadata,\n".format(table_name, self.indentation)

        for column in model.table.columns:
            rendered += "{0}{1},\n".format(self.indentation, self.render_column(column, True))

        for constraint in sorted(model.table.constraints, key=_get_constraint_sort_key):
            if isinstance(constraint, PrimaryKeyConstraint):
                continue
            if isinstance(constraint, (ForeignKeyConstraint, UniqueConstraint)) and len(constraint.columns) == 1:
                continue
            rendered += "{0}{1},\n".format(self.indentation, self.render_constraint(constraint))

        for index in model.table.indexes:
            if len(index.columns) > 1:
                rendered += "{0}{1},\n".format(self.indentation, self.render_index(index))

        if model.schema:
            rendered += "{0}schema='{1}',\n".format(self.indentation, model.schema)

        return rendered.rstrip("\n,") + "\n)\n" 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def pandasSQL_builder(con, flavor=None, schema=None, meta=None,
                      is_cursor=False):
    """
    Convenience function to return the correct PandasSQL subclass based on the
    provided parameters.
    """
    _validate_flavor_parameter(flavor)

    # When support for DBAPI connections is removed,
    # is_cursor should not be necessary.
    con = _engine_builder(con)
    if _is_sqlalchemy_connectable(con):
        return SQLDatabase(con, schema=schema, meta=meta)
    elif isinstance(con, string_types):
        raise ImportError("Using URI string without sqlalchemy installed.")
    else:
        return SQLiteDatabase(con, is_cursor=is_cursor) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def _get_all_tables(self):
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        table_list = meta.tables.keys()
        return table_list 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import schema [as 别名]
def _get_sqlite_column_type(self, schema, column):

        for col in schema.split('\n'):
            if col.split()[0].strip('""') == column:
                return col.split()[1]
        raise ValueError('Column %s not found' % (column))