Python sql.read_sql_table方法代码示例

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_read_table_index_col(self):
        # test columns argument in read_table
        sql.to_sql(self.test_frame1, 'test_frame', self.conn)

        result = sql.read_sql_table('test_frame', self.conn, index_col="index")
        assert result.index.names == ["index"]

        result = sql.read_sql_table(
            'test_frame', self.conn, index_col=["A", "B"])
        assert result.index.names == ["A", "B"]

        result = sql.read_sql_table('test_frame', self.conn,
                                    index_col=["A", "B"],
                                    columns=["C", "D"])
        assert result.index.names == ["A", "B"]
        assert result.columns.tolist() == ["C", "D"] 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [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) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [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) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_datetime_time(self):
        # test support for datetime.time
        df = DataFrame([time(9, 0, 0), time(9, 1, 30)], columns=["a"])
        df.to_sql('test_time', self.conn, index=False)
        res = read_sql_table('test_time', self.conn)
        tm.assert_frame_equal(res, df)

        # GH8341
        # first, use the fallback to have the sqlite adapter put in place
        sqlite_conn = TestSQLiteFallback.connect()
        sql.to_sql(df, "test_time2", sqlite_conn, index=False)
        res = sql.read_sql_query("SELECT * FROM test_time2", sqlite_conn)
        ref = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
        tm.assert_frame_equal(ref, res)  # check if adapter is in place
        # then test if sqlalchemy is unaffected by the sqlite adapter
        sql.to_sql(df, "test_time3", self.conn, index=False)
        if self.flavor == 'sqlite':
            res = sql.read_sql_query("SELECT * FROM test_time3", self.conn)
            ref = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
            tm.assert_frame_equal(ref, res)
        res = sql.read_sql_table("test_time3", self.conn)
        tm.assert_frame_equal(df, res) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [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) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_get_schema_create_table(self):
        # Use a dataframe without a bool column, since MySQL converts bool to
        # TINYINT (which read_sql_table returns as an int and causes a dtype
        # mismatch)

        self._load_test3_data()
        tbl = 'test_get_schema_create_table'
        create_sql = sql.get_schema(self.test_frame3, tbl, con=self.conn)
        blank_test_df = self.test_frame3.iloc[:0]

        self.drop_table(tbl)
        self.conn.execute(create_sql)
        returned_df = sql.read_sql_table(tbl, self.conn)
        tm.assert_frame_equal(returned_df, blank_test_df,
                              check_index_type=False)
        self.drop_table(tbl) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_chunksize_read(self):
        df = DataFrame(np.random.randn(22, 5), columns=list('abcde'))
        df.to_sql('test_chunksize', self.conn, index=False)

        # reading the query in one time
        res1 = sql.read_sql_query("select * from test_chunksize", self.conn)

        # reading the query in chunks with read_sql_query
        res2 = DataFrame()
        i = 0
        sizes = [5, 5, 5, 5, 2]

        for chunk in sql.read_sql_query("select * from test_chunksize",
                                        self.conn, chunksize=5):
            res2 = concat([res2, chunk], ignore_index=True)
            assert len(chunk) == sizes[i]
            i += 1

        tm.assert_frame_equal(res1, res2)

        # reading the query in chunks with read_sql_query
        if self.mode == 'sqlalchemy':
            res3 = DataFrame()
            i = 0
            sizes = [5, 5, 5, 5, 2]

            for chunk in sql.read_sql_table("test_chunksize", self.conn,
                                            chunksize=5):
                res3 = concat([res3, chunk], ignore_index=True)
                assert len(chunk) == sizes[i]
                i += 1

            tm.assert_frame_equal(res1, res3) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_read_table_columns(self):
        # test columns argument in read_table
        sql.to_sql(self.test_frame1, 'test_frame', self.conn)

        cols = ['A', 'B']
        result = sql.read_sql_table('test_frame', self.conn, columns=cols)
        assert result.columns.tolist() == cols 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_read_sql_delegate(self):
        iris_frame1 = sql.read_sql_query(
            "SELECT * FROM iris", self.conn)
        iris_frame2 = sql.read_sql(
            "SELECT * FROM iris", self.conn)
        tm.assert_frame_equal(iris_frame1, iris_frame2)

        iris_frame1 = sql.read_sql_table('iris', self.conn)
        iris_frame2 = sql.read_sql('iris', self.conn)
        tm.assert_frame_equal(iris_frame1, iris_frame2) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_not_reflect_all_tables(self):
        # create invalid table
        qry = """CREATE TABLE invalid (x INTEGER, y UNKNOWN);"""
        self.conn.execute(qry)
        qry = """CREATE TABLE other_table (x INTEGER, y INTEGER);"""
        self.conn.execute(qry)

        with warnings.catch_warnings(record=True) as w:
            # Cause all warnings to always be triggered.
            warnings.simplefilter("always")
            # Trigger a warning.
            sql.read_sql_table('other_table', self.conn)
            sql.read_sql_query('SELECT * FROM other_table', self.conn)
            # Verify some things
            assert len(w) == 0 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_read_table(self):
        iris_frame = sql.read_sql_table("iris", con=self.conn)
        self._check_iris_loaded_frame(iris_frame) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_read_table_absent(self):
        pytest.raises(
            ValueError, sql.read_sql_table, "this_doesnt_exist", con=self.conn) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_default_type_conversion(self):
        df = sql.read_sql_table("types_test_data", self.conn)

        assert issubclass(df.FloatCol.dtype.type, np.floating)
        assert issubclass(df.IntCol.dtype.type, np.integer)
        assert issubclass(df.BoolCol.dtype.type, np.bool_)

        # Int column with NA values stays as float
        assert issubclass(df.IntColWithNull.dtype.type, np.floating)
        # Bool column with NA values becomes object
        assert issubclass(df.BoolColWithNull.dtype.type, np.object) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_bigint(self):
        # int64 should be converted to BigInteger, GH7433
        df = DataFrame(data={'i64': [2**62]})
        df.to_sql('test_bigint', self.conn, index=False)
        result = sql.read_sql_table('test_bigint', self.conn)

        tm.assert_frame_equal(df, result) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import read_sql_table [as 别名]
def test_datetime_with_timezone_roundtrip(self):
        # GH 9086
        # Write datetimetz data to a db and read it back
        # For dbs that support timestamps with timezones, should get back UTC
        # otherwise naive data should be returned
        expected = DataFrame({'A': date_range(
            '2013-01-01 09:00:00', periods=3, tz='US/Pacific'
        )})
        expected.to_sql('test_datetime_tz', self.conn, index=False)

        if self.flavor == 'postgresql':
            # SQLAlchemy "timezones" (i.e. offsets) are coerced to UTC
            expected['A'] = expected['A'].dt.tz_convert('UTC')
        else:
            # Otherwise, timestamps are returned as local, naive
            expected['A'] = expected['A'].dt.tz_localize(None)

        result = sql.read_sql_table('test_datetime_tz', self.conn)
        tm.assert_frame_equal(result, expected)

        result = sql.read_sql_query(
            'SELECT * FROM test_datetime_tz', self.conn
        )
        if self.flavor == 'sqlite':
            # read_sql_query does not return datetime type like read_sql_table
            assert isinstance(result.loc[0, 'A'], string_types)
            result['A'] = to_datetime(result['A'])
        tm.assert_frame_equal(result, expected)