Python sql.to_sql方法代碼示例

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [as 別名]
def _to_sql_append(self):
        # Nuke table just in case
        self.drop_table('test_frame1')

        self.pandasSQL.to_sql(
            self.test_frame1, 'test_frame1', if_exists='fail')

        # Add to table again
        self.pandasSQL.to_sql(
            self.test_frame1, 'test_frame1', if_exists='append')
        assert self.pandasSQL.has_table('test_frame1')

        num_entries = 2 * len(self.test_frame1)
        num_rows = self._count_rows('test_frame1')

        assert num_rows == num_entries
        self.drop_table('test_frame1') 

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [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 to_sql [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 to_sql [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 to_sql [as 別名]
def test_connectable_issue_example(self):
        # This tests the example raised in issue
        # https://github.com/pandas-dev/pandas/issues/10104

        def foo(connection):
            query = 'SELECT test_foo_data FROM test_foo_data'
            return sql.read_sql_query(query, con=connection)

        def bar(connection, data):
            data.to_sql(name='test_foo_data',
                        con=connection, if_exists='append')

        def main(connectable):
            with connectable.connect() as conn:
                with conn.begin():
                    foo_data = conn.run_callable(foo)
                    conn.run_callable(bar, foo_data)

        DataFrame({'test_foo_data': [0, 1, 2]}).to_sql(
            'test_foo_data', self.conn)
        main(self.conn) 

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [as 別名]
def test_dtype(self):
        if self.flavor == 'mysql':
            pytest.skip('Not applicable to MySQL legacy')
        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': 'STRING'})

        # sqlite stores Boolean values as INTEGER
        assert self._get_sqlite_column_type(
            'dtype_test', 'B') == 'INTEGER'

        assert self._get_sqlite_column_type(
            'dtype_test2', 'B') == 'STRING'
        pytest.raises(ValueError, df.to_sql,
                      'error', self.conn, dtype={'B': bool})

        # single dtype
        df.to_sql('single_dtype_test', self.conn, dtype='STRING')
        assert self._get_sqlite_column_type(
            'single_dtype_test', 'A') == 'STRING'
        assert self._get_sqlite_column_type(
            'single_dtype_test', 'B') == 'STRING' 

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [as 別名]
def test_notna_dtype(self):
        if self.flavor == 'mysql':
            pytest.skip('Not applicable to MySQL legacy')

        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)

        assert self._get_sqlite_column_type(tbl, 'Bool') == 'INTEGER'
        assert self._get_sqlite_column_type(tbl, 'Date') == 'TIMESTAMP'
        assert self._get_sqlite_column_type(tbl, 'Int') == 'INTEGER'
        assert self._get_sqlite_column_type(tbl, 'Float') == 'REAL' 

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [as 別名]
def test_illegal_names(self):
        # For sqlite, these should work fine
        df = DataFrame([[1, 2], [3, 4]], columns=['a', 'b'])

        # Raise error on blank
        pytest.raises(ValueError, df.to_sql, "", self.conn)

        for ndx, weird_name in enumerate(
                ['test_weird_name]', 'test_weird_name[',
                 'test_weird_name`', 'test_weird_name"', 'test_weird_name\'',
                 '_b.test_weird_name_01-30', '"_b.test_weird_name_01-30"',
                 '99beginswithnumber', '12345', u'\xe9']):
            df.to_sql(weird_name, self.conn)
            sql.table_exists(weird_name, self.conn)

            df2 = DataFrame([[1, 2], [3, 4]], columns=['a', weird_name])
            c_tbl = 'test_weird_col_name%d' % ndx
            df2.to_sql(c_tbl, self.conn)
            sql.table_exists(c_tbl, self.conn)


# -----------------------------------------------------------------------------
# -- Old tests from 0.13.1 (before refactor using sqlalchemy) 

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [as 別名]
def _to_sql(self, method=None):
        self.drop_table('test_frame1')

        self.pandasSQL.to_sql(self.test_frame1, 'test_frame1', method=method)
        assert self.pandasSQL.has_table('test_frame1')

        num_entries = len(self.test_frame1)
        num_rows = self._count_rows('test_frame1')
        assert num_rows == num_entries

        # Nuke table
        self.drop_table('test_frame1') 

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [as 別名]
def _to_sql_empty(self):
        self.drop_table('test_frame1')
        self.pandasSQL.to_sql(self.test_frame1.iloc[:0], 'test_frame1') 

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [as 別名]
def _to_sql_fail(self):
        self.drop_table('test_frame1')

        self.pandasSQL.to_sql(
            self.test_frame1, 'test_frame1', if_exists='fail')
        assert self.pandasSQL.has_table('test_frame1')

        pytest.raises(ValueError, self.pandasSQL.to_sql,
                      self.test_frame1, 'test_frame1', if_exists='fail')

        self.drop_table('test_frame1') 

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [as 別名]
def _to_sql_replace(self):
        self.drop_table('test_frame1')

        self.pandasSQL.to_sql(
            self.test_frame1, 'test_frame1', if_exists='fail')
        # Add to table again
        self.pandasSQL.to_sql(
            self.test_frame1, 'test_frame1', if_exists='replace')
        assert self.pandasSQL.has_table('test_frame1')

        num_entries = len(self.test_frame1)
        num_rows = self._count_rows('test_frame1')

        assert num_rows == num_entries
        self.drop_table('test_frame1') 

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [as 別名]
def _roundtrip(self):
        self.drop_table('test_frame_roundtrip')
        self.pandasSQL.to_sql(self.test_frame1, 'test_frame_roundtrip')
        result = self.pandasSQL.read_query(
            'SELECT * FROM test_frame_roundtrip')

        result.set_index('level_0', inplace=True)
        # result.index.astype(int)

        result.index.name = None

        tm.assert_frame_equal(result, self.test_frame1) 

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [as 別名]
def _to_sql_save_index(self):
        df = DataFrame.from_records([(1, 2.1, 'line1'), (2, 1.5, 'line2')],
                                    columns=['A', 'B', 'C'], index=['A'])
        self.pandasSQL.to_sql(df, 'test_to_sql_saves_index')
        ix_cols = self._get_index_columns('test_to_sql_saves_index')
        assert ix_cols == [['A', ], ] 

# 需要導入模塊: from pandas.io import sql [as 別名]
# 或者: from pandas.io.sql import to_sql [as 別名]
def test_to_sql(self):
        sql.to_sql(self.test_frame1, 'test_frame1', self.conn)
        assert sql.has_table('test_frame1', self.conn)