def to_sqlserver(self,
pandas_dataframe,
additional_static_data_dict=None,
index=False,
discover_data_type=False,
use_existing=False,
batch_size=10000,
audit_date=datetime.utcnow(),
csv_full_file_name=None):
# set properties first
self.pandas_dataframe = \
Dataframe._conform_pandas_dataframe(
Dataframe._add_fields(
pandas_dataframe=pandas_dataframe,
additional_static_data_dict=additional_static_data_dict,
audit_date=audit_date,
csv_full_file_name=csv_full_file_name
)
)
bcp_file_format = BcpFileFormat.create_bcp_format_file_instance_from_dataframe(
self.pandas_dataframe,
TempFile().temp_full_file_name)
bcp_temp_csv_file = BcpTempCsvFile.write_df_to_csv_creating_instance(
self.pandas_dataframe, bcp_file_format, index)
SqlTable.create(self.sql_configuration,
self.pandas_dataframe.dtypes.to_dict(), use_existing)
BcpIn(self.sql_configuration, bcp_file_format, bcp_temp_csv_file,
batch_size).execute()
bcp_temp_csv_file.remove_file()
bcp_file_format.remove_file()
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 create(self, columns_dict, use_existing=False):
return \
SqlTable.create(
sql_configuration=self,
column_dict=columns_dict,
use_existing=use_existing
)
def to_csv(self, csv_root_directory, separator="|", batch_size=10000):
# set properties first
self.csv_full_file_name = path.join(
csv_root_directory, self.sql_configuration.server_name_clean,
self.sql_configuration.database_name,
f'{datetime.now().strftime("%Y_%m_%d_%H_%M_%S")}__'
f'{self.sql_configuration.schema_name}__'
f'{self.sql_configuration.table_name}.csv')
makedirs(path.dirname(self.csv_full_file_name), exist_ok=True)
BcpOut(self.sql_configuration, self.csv_full_file_name, separator,
batch_size).execute()
Dataframe._add_header_row(header=SqlTable.get_column_names(
self.sql_configuration, separator),
data_full_file_name=self.csv_full_file_name)
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_constructor_method(self):
self.assertEqual(str(type(SqlTable())),
"<class 'zeppos_bcpy.sql_table.SqlTable'>")
def test__does_table_exist_method(self):
self.assertEqual(
True,
SqlTable._does_table_exist(
SqlConfiguration("microsoft", r"localhost\sqlexpress",
"master", "dbo", "spt_fallback_db"), ))