Python pandas.io.sql.to_sql() Examples

The following are 30 code examples of pandas.io.sql.to_sql(). You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module pandas.io.sql , or try the search function .
Example #1
Source File: test_sql.py    From recruit with Apache License 2.0 6 votes vote down vote up
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) 
Example #2
Source File: test_sql.py    From recruit with Apache License 2.0 6 votes vote down vote up
def test_nan_fullcolumn(self):
        # full NaN column (numeric float column)
        df = DataFrame({'A': [0, 1, 2], 'B': [np.nan, np.nan, np.nan]})
        df.to_sql('test_nan', self.conn, index=False)

        # with read_table
        result = sql.read_sql_table('test_nan', self.conn)
        tm.assert_frame_equal(result, df)

        # with read_sql -> not type info from table -> stays None
        df['B'] = df['B'].astype('object')
        df['B'] = None
        result = sql.read_sql_query('SELECT * FROM test_nan', self.conn)
        tm.assert_frame_equal(result, df) 
Example #3
Source File: test_sql.py    From recruit with Apache License 2.0 6 votes vote down vote up
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' 
Example #4
Source File: test_sql.py    From recruit with Apache License 2.0 6 votes vote down vote up
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' 
Example #5
Source File: test_sql.py    From recruit with Apache License 2.0 6 votes vote down vote up
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) 
Example #6
Source File: test_sql.py    From recruit with Apache License 2.0 6 votes vote down vote up
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"] 
Example #7
Source File: test_sql.py    From vnpy_crypto with MIT License 6 votes vote down vote up
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') 
Example #8
Source File: test_sql.py    From vnpy_crypto with MIT License 6 votes vote down vote up
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"] 
Example #9
Source File: test_sql.py    From recruit with Apache License 2.0 6 votes vote down vote up
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') 
Example #10
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up
def test_onecolumn_of_integer(self):
        # GH 3628
        # a column_of_integers dataframe should transfer well to sql

        mono_df = DataFrame([1, 2], columns=['c0'])
        sql.to_sql(mono_df, con=self.conn, name='mono_df', index=False)
        # computing the sum via sql
        con_x = self.conn
        the_sum = sum(my_c0[0]
                      for my_c0 in con_x.execute("select * from mono_df"))
        # it should not fail, and gives 3 ( Issue #3628 )
        assert the_sum == 3

        result = sql.read_sql("select * from mono_df", con_x)
        tm.assert_frame_equal(result, mono_df) 
Example #11
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up
def test_to_sql_index_label(self):
        temp_frame = DataFrame({'col1': range(4)})

        # no index name, defaults to 'index'
        sql.to_sql(temp_frame, 'test_index_label', self.conn)
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == 'index'

        # specifying index_label
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label='other_label')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "other_label"

        # using the index name
        temp_frame.index.name = 'index_name'
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "index_name"

        # has index name, but specifying index_label
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label='other_label')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "other_label"

        # index name is integer
        temp_frame.index.name = 0
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "0"

        temp_frame.index.name = None
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label=0)
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "0" 
Example #12
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up
def test_chunksize_read_type(self):
        frame = tm.makeTimeDataFrame()
        frame.index.name = "index"
        drop_sql = "DROP TABLE IF EXISTS test"
        cur = self.conn.cursor()
        cur.execute(drop_sql)
        sql.to_sql(frame, name='test', con=self.conn)
        query = "select * from test"
        chunksize = 5
        chunk_gen = pd.read_sql_query(sql=query, con=self.conn,
                                      chunksize=chunksize, index_col="index")
        chunk_df = next(chunk_gen)
        tm.assert_frame_equal(frame[:chunksize], chunk_df) 
Example #13
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up
def _check_roundtrip(self, frame):
        drop_sql = "DROP TABLE IF EXISTS test_table"
        cur = self.conn.cursor()
        with warnings.catch_warnings():
            warnings.filterwarnings("ignore", "Unknown table.*")
            cur.execute(drop_sql)
        sql.to_sql(frame, name='test_table', con=self.conn, index=False)
        result = sql.read_sql("select * from test_table", self.conn)

        # HACK! Change this once indexes are handled properly.
        result.index = frame.index
        result.index.name = frame.index.name

        expected = frame
        tm.assert_frame_equal(result, expected)

        frame['txt'] = ['a'] * len(frame)
        frame2 = frame.copy()
        index = Index(lrange(len(frame2))) + 10
        frame2['Idx'] = index
        drop_sql = "DROP TABLE IF EXISTS test_table2"
        cur = self.conn.cursor()
        with warnings.catch_warnings():
            warnings.filterwarnings("ignore", "Unknown table.*")
            cur.execute(drop_sql)
        sql.to_sql(frame2, name='test_table2',
                   con=self.conn, index=False)
        result = sql.read_sql("select * from test_table2", self.conn,
                              index_col='Idx')
        expected = frame.copy()

        # HACK! Change this once indexes are handled properly.
        expected.index = index
        expected.index.names = result.index.names
        tm.assert_frame_equal(expected, result) 
Example #14
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up
def _to_sql(self):
        self.drop_table('test_frame1')

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

        # Nuke table
        self.drop_table('test_frame1') 
Example #15
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up
def _to_sql_empty(self):
        self.drop_table('test_frame1')
        self.pandasSQL.to_sql(self.test_frame1.iloc[:0], 'test_frame1') 
Example #16
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up
def test_roundtrip_chunksize(self):
        sql.to_sql(self.test_frame1, 'test_frame_roundtrip', con=self.conn,
                   index=False, chunksize=2)
        result = sql.read_sql_query(
            'SELECT * FROM test_frame_roundtrip',
            con=self.conn)
        tm.assert_frame_equal(result, self.test_frame1) 
Example #17
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up
def test_to_sql_series(self):
        s = Series(np.arange(5, dtype='int64'), name='series')
        sql.to_sql(s, "test_series", self.conn, index=False)
        s2 = sql.read_sql_query("SELECT * FROM test_series", self.conn)
        tm.assert_frame_equal(s.to_frame(), s2) 
Example #18
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up
def test_to_sql_panel(self):
        with catch_warnings(record=True):
            panel = tm.makePanel()
            pytest.raises(NotImplementedError, sql.to_sql, panel,
                          'test_panel', self.conn) 
Example #19
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up
def test_roundtrip(self):
        sql.to_sql(self.test_frame1, 'test_frame_roundtrip',
                   con=self.conn)
        result = sql.read_sql_query(
            'SELECT * FROM test_frame_roundtrip',
            con=self.conn)

        # HACK!
        result.index = self.test_frame1.index
        result.set_index('level_0', inplace=True)
        result.index.astype(int)
        result.index.name = None
        tm.assert_frame_equal(result, self.test_frame1) 
Example #20
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up
def test_keyword_as_column_names(self):
        df = DataFrame({'From': np.ones(5)})
        sql.to_sql(df, con=self.conn, name='testkeywords', index=False) 
Example #21
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up
def test_unicode_column_name(self):
        # GH 11431
        df = DataFrame([[1, 2], [3, 4]], columns=[u'\xe9', u'b'])
        df.to_sql('test_unicode', self.conn, index=False) 
Example #22
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up
def test_escaped_table_name(self):
        # GH 13206
        df = DataFrame({'A': [0, 1, 2], 'B': [0.2, np.nan, 5.6]})
        df.to_sql('d1187b08-4943-4c8d-a7f6', self.conn, index=False)

        res = sql.read_sql_query('SELECT * FROM `d1187b08-4943-4c8d-a7f6`',
                                 self.conn)

        tm.assert_frame_equal(res, df) 
Example #23
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up
def test_datetime_time(self):
        # test support for datetime.time, GH #8341
        df = DataFrame([time(9, 0, 0), time(9, 1, 30)], columns=["a"])
        df.to_sql('test_time', self.conn, index=False)
        res = read_sql_query('SELECT * FROM test_time', self.conn)
        if self.flavor == 'sqlite':
            # comes back as strings
            expected = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
            tm.assert_frame_equal(res, expected) 
Example #24
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up
def test_datetime_date(self):
        # test support for datetime.date
        df = DataFrame([date(2014, 1, 1), date(2014, 1, 2)], columns=["a"])
        df.to_sql('test_date', self.conn, index=False)
        res = read_sql_query('SELECT * FROM test_date', self.conn)
        if self.flavor == 'sqlite':
            # comes back as strings
            tm.assert_frame_equal(res, df.astype(str))
        elif self.flavor == 'mysql':
            tm.assert_frame_equal(res, df) 
Example #25
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up
def test_copy_from_callable_insertion_method(self):
        # GH 8953
        # Example in io.rst found under _io.sql.method
        # not available in sqlite, mysql
        def psql_insert_copy(table, conn, keys, data_iter):
            # gets a DBAPI connection that can provide a cursor
            dbapi_conn = conn.connection
            with dbapi_conn.cursor() as cur:
                s_buf = compat.StringIO()
                writer = csv.writer(s_buf)
                writer.writerows(data_iter)
                s_buf.seek(0)

                columns = ', '.join('"{}"'.format(k) for k in keys)
                if table.schema:
                    table_name = '{}.{}'.format(table.schema, table.name)
                else:
                    table_name = table.name

                sql_query = 'COPY {} ({}) FROM STDIN WITH CSV'.format(
                    table_name, columns)
                cur.copy_expert(sql=sql_query, file=s_buf)

        expected = DataFrame({'col1': [1, 2], 'col2': [0.1, 0.2],
                              'col3': ['a', 'n']})
        expected.to_sql('test_copy_insert', self.conn, index=False,
                        method=psql_insert_copy)
        result = sql.read_sql_table('test_copy_insert', self.conn)
        tm.assert_frame_equal(result, expected) 
Example #26
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up
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 
Example #27
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up
def test_read_procedure(self):
        import pymysql
        # see GH7324. Although it is more an api test, it is added to the
        # mysql tests as sqlite does not have stored procedures
        df = DataFrame({'a': [1, 2, 3], 'b': [0.1, 0.2, 0.3]})
        df.to_sql('test_procedure', self.conn, index=False)

        proc = """DROP PROCEDURE IF EXISTS get_testdb;

        CREATE PROCEDURE get_testdb ()

        BEGIN
            SELECT * FROM test_procedure;
        END"""

        connection = self.conn.connect()
        trans = connection.begin()
        try:
            r1 = connection.execute(proc)  # noqa
            trans.commit()
        except pymysql.Error:
            trans.rollback()
            raise

        res1 = sql.read_sql_query("CALL get_testdb();", self.conn)
        tm.assert_frame_equal(df, res1)

        # test delegation to read_sql_query
        res2 = sql.read_sql("CALL get_testdb();", self.conn)
        tm.assert_frame_equal(df, res2) 
Example #28
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up
def test_bigint_warning(self):
        # test no warning for BIGINT (to support int64) is raised (GH7433)
        df = DataFrame({'a': [1, 2]}, dtype='int64')
        df.to_sql('test_bigintwarning', self.conn, index=False)

        with warnings.catch_warnings(record=True) as w:
            warnings.simplefilter("always")
            sql.read_sql_table('test_bigintwarning', self.conn)
            assert len(w) == 0 
Example #29
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up
def _to_sql_empty(self):
        self.drop_table('test_frame1')
        self.pandasSQL.to_sql(self.test_frame1.iloc[:0], 'test_frame1') 
Example #30
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up
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)