def _create_tbl_with_data(self): # first create a new table and put some data into it execute_sql_statement( self.sql_creds.engine, f"DROP TABLE IF EXISTS {self.schema_name}.{self.table_name}" ) to_sql( df=self.df, table_name=self.table_name, creds=self.sql_creds, if_exists="replace", index=False, sql_type=self.sql_type, )
def _test_not_exists(self, df, index): execute_sql_statement( self.pyodbc_creds.engine, f"DROP TABLE IF EXISTS dbo.{self.table_name}" ) assume_not_all_delims_and_quotechars(df) to_sql( df=df, table_name=self.table_name, creds=self.sql_creds, index=index, sql_type=self.sql_type, if_exists="fail", ) actual = pd.read_sql_query(sql=f"SELECT * FROM {self.table_name}", con=self.pyodbc_creds) expected = prep_df_for_comparison(df=df, index=index) assert_frame_equal(expected, actual, check_column_type="equiv")
def test_tosql_empty_df(df, sql_creds): tbl_name = "tbl_df_empty" schema_name = "dbo" execute_sql_statement(sql_creds.engine, f"DROP TABLE IF EXISTS {schema_name}.{tbl_name}") to_sql(df=df, table_name=tbl_name, creds=sql_creds, schema=schema_name, if_exists="replace") # make sure nothing happened in the database qry = """ SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{_schema}' AND TABLE_NAME = '{_tbl}' """.format( _tbl=tbl_name, _schema=schema_name ) res = pd.read_sql_query(sql=qry, con=sql_creds.engine) # assert that rows == 0, it has columns even without rows because it is an internal system table assert res.shape[0] == 0
def test_tosql_nan_null_inf(df, sql_creds): tbl_name = "tbl_df_nan_null_last_col" schema_name = "dbo" execute_sql_statement(sql_creds.engine, f"DROP TABLE IF EXISTS {schema_name}.{tbl_name}") to_sql( df=df, table_name=tbl_name, creds=sql_creds, schema=schema_name, if_exists="replace", index=False, ) # check result actual = pd.read_sql_query(sql=f"SELECT * FROM {schema_name}.{tbl_name}", con=sql_creds.engine) expected = prep_df_for_comparison(df=df, index=False) assert_frame_equal(expected, actual)
def test_tosql_full_bcp_path(bcp_path, sql_creds): df = pd.DataFrame({"col1": ["a", "b", "c", "d"], "col2": [1.5, 2.5, 3.5, 4.5]}) tbl_name = "tbl_df_full_bcp_path" schema_name = "dbo" execute_sql_statement(sql_creds.engine, f"DROP TABLE IF EXISTS {schema_name}.{tbl_name}") to_sql( df=df, table_name=tbl_name, creds=sql_creds, schema=schema_name, if_exists="replace", index=False, bcp_path=bcp_path, ) # check result actual = pd.read_sql_query(sql=f"SELECT * FROM {schema_name}.{tbl_name}", con=sql_creds.engine) expected = prep_df_for_comparison(df=df, index=False) assert_frame_equal(expected, actual)
def _test_not_exists(self, df, index): execute_sql_statement( self.pyodbc_creds.engine, f"DROP TABLE IF EXISTS dbo.{self.table_name}" ) self._test_base(df, index)