def _test_exists(self, df, index):
assume_not_all_delims_and_quotechars(df)
# first populate the data
to_sql(
df=df,
table_name=self.table_name,
creds=self.sql_creds,
index=index,
sql_type=self.sql_type,
if_exists="replace",
)
# then test to sql with option of 'append'
to_sql(
df=df,
table_name=self.table_name,
creds=self.sql_creds,
index=index,
sql_type=self.sql_type,
if_exists="append",
)
actual = pd.read_sql_query(
sql=f"SELECT * FROM dbo.{self.table_name}", con=self.pyodbc_creds
)
df = prep_df_for_comparison(df=df, index=index)
expected = pd.concat([df, df], axis=0, ignore_index=True) # appended
assert_frame_equal(expected, actual, check_column_type="equiv")
def _check_result(self, df_modified):
# check result
actual = pd.read_sql_query(
sql=f"SELECT * FROM {self.schema_name}.{self.table_name}", con=self.sql_creds.engine
)
expected = prep_df_for_comparison(
df=pd.concat([self.df, df_modified], axis=0, ignore_index=True, sort=False), index=False
)
assert_frame_equal(expected, actual)
def _test_base(self, df, index):
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="replace",
)
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_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_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)