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)