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