def test_2__validate_and_augment_if_needed_sql_configuration_method(self):
sql_configuration_original = SqlConfiguration(
server_type="microsoft",
server_name="localhost\\sqlexpress",
database_name="master",
schema_name="dbo",
table_name=None
)
self.assertEqual("temp",
sql_configuration_original.validate_and_augment(file_name_without_extension="temp").table_name)
self.assertEqual(None, sql_configuration_original.table_name)
def test__get_column_names(self):
self.assertEqual(
SqlTable.get_column_names(
SqlConfiguration("microsoft", r"localhost\sqlexpress",
"master", "dbo", "spt_monitor"), "|"),
"lastrun|cpu_busy|io_busy|idle|pack_received|pack_sent|connections|pack_errors|total_read|total_write|total_errors"
)
def test_to_sqlserer_additional_static_data_method(self):
df_expected = pd.DataFrame({
'seconds': ["3600"],
'minutes': ["10"]
},
columns=['seconds', 'minutes'])
sql_configuration = SqlConfiguration(
server_type="microsoft",
server_name="localhost\\sqlexpress",
database_name="master",
schema_name="dbo",
table_name="staging_test5")
additional_static_data_dict = {
'static_field1': 'some info 1',
'static_field2': 'some info 2'
}
dataframe = Dataframe.to_sqlserver_creating_instance(
df_expected, sql_configuration, additional_static_data_dict)
self.assertEqual("<class 'zeppos_bcpy.dataframe.Dataframe'>",
str(type(dataframe)))
df_actual = pd.read_sql(
"select SECONDS, MINUTES, STATIC_FIELD1, STATIC_FIELD2 from dbo.staging_test5",
pyodbc.connect(
r'DRIVER={ODBC Driver 17 for SQL Server}; SERVER=localhost\sqlexpress; DATABASE=master; Trusted_Connection=yes;'
))
df_expected = df_expected[[
'SECONDS', 'MINUTES', 'STATIC_FIELD1', 'STATIC_FIELD2'
]]
assert_frame_equal(df_actual, df_expected)
def test_to_csv_method(self):
sql_configuration = SqlConfiguration(
server_type="microsoft",
server_name="localhost\\sqlexpress",
database_name="master",
schema_name="dbo",
table_name="spt_monitor")
dataframe = Dataframe.to_csv_creating_instance(
sql_configuration=sql_configuration, csv_root_directory=r"c:\data")
self.assertEqual("<class 'zeppos_bcpy.dataframe.Dataframe'>",
str(type(dataframe)))
def test_constructor_method(self):
sql_configuration = SqlConfiguration("microsoft", "server", "database", "schema", "table")
self.assertEqual("<class 'zeppos_bcpy.sql_configuration.SqlConfiguration'>",
str(type(sql_configuration)))
self.assertEqual("microsoft", sql_configuration.server_type)
self.assertEqual("server", sql_configuration.server_name)
self.assertEqual("database", sql_configuration.database_name)
self.assertEqual("schema", sql_configuration.schema_name)
self.assertEqual("table", sql_configuration.table_name)
self.assertEqual(None, sql_configuration.username)
self.assertEqual(None, sql_configuration.password)
def save_dataframe_in_bulk(self, df, schema_name, table_name, use_existsing=False):
try:
sql_configuration = SqlConfiguration(
server_type="microsoft",
server_name=self.connection.connection_string.server_name,
database_name=self.connection.connection_string.database_name,
schema_name=schema_name,
table_name=table_name
)
Dataframe.to_sqlserver_creating_instance(df, sql_configuration)
return True
except Exception as error:
return False
def test_3_create_method(self):
TestTheProjectMethods._execute_sql(
"drop table if exists dbo.stating_test_3")
df = pd.DataFrame({
'seconds': [3600],
'minutes': [10]
},
columns=['seconds', 'minutes'])
self.assertEqual(
True,
SqlTable.create(SqlConfiguration("microsoft",
r"localhost\sqlexpress", "master",
"dbo", "staging_test_3"),
df.dtypes.to_dict(),
use_existing=False))
df_actual = TestTheProjectMethods._get_data(
"select count(1) as record_count from information_schema.tables where TABLE_NAME = 'staging_test_3' and TABLE_SCHEMA = 'dbo'"
)
self.assertEqual(1, next(df_actual.iterrows())[1]['record_count'])
TestTheProjectMethods._execute_sql(
"drop table if exists dbo.stating_test_3")
def test_2_create_method(self):
TestTheProjectMethods._execute_sql(
"drop table if exists dbo.stating_test_2")
TestTheProjectMethods._execute_sql(
"create table dbo.stating_test_2 (test int)")
self.assertEqual(
True,
SqlTable.create(SqlConfiguration("microsoft",
r"localhost\sqlexpress", "master",
"dbo", "staging_test_2"),
pd.DataFrame({
'seconds': [3600],
'minutes': [10]
},
columns=['seconds',
'minutes']).dtypes.to_dict(),
use_existing=True))
df = TestTheProjectMethods._get_data(
"select count(1) as record_count from information_schema.tables where table_schema='dbo' and table_name='staging_test_2'"
)
self.assertEqual(1, next(df.iterrows())[1]['record_count'])
TestTheProjectMethods._execute_sql(
"drop table if exists dbo.stating_test_2")
def test__does_table_exist_method(self):
self.assertEqual(
True,
SqlTable._does_table_exist(
SqlConfiguration("microsoft", r"localhost\sqlexpress",
"master", "dbo", "spt_fallback_db"), ))
def test_1_get_pyodbc_connection_string_method(self):
sql_configuration = SqlConfiguration("microsoft", r"localhost\sqlexpress", "master", "dbo", "staging_test")
self.assertEqual("DRIVER={ODBC Driver 17 for SQL Server}; SERVER=localhost\sqlexpress; DATABASE=master; Trusted_Connection=yes;",
sql_configuration.get_pyodbc_connection_string())
def test_create_method(self):
sql_configuration = SqlConfiguration("microsoft", r"localhost\sqlexpress", "master", "dbo", "staging_test")
self.assertEqual(True, sql_configuration.create({"col1", "int"}, use_existing=True))