from openpyxl import Workbook
from openpyxl import load_workbook
import pandas as pd
import os
import sys
sys.path.append(os.getcwd())
from tool.tool import file_name
SEED_FILE = r".\seed\Column_gen.xlsx"
meta = pd.read_excel(SEED_FILE)
f = open(file_name("Gen_Script", ".sql"), "w")
sql_txt = '''
IF NOT EXISTS(SELECT * from dbo.syscolumns WHERE id=object_id('dbo.D_CUSTOMER_PROFILE') AND name='column_to_replace')
BEGIN
ALTER TABLE D_CUSTOMER_PROFILE ADD column_to_replace smallint NULL
exec sys.sp_addextendedproperty 'MS_Description', 'xxxxxxxxxxxxxxxxxx', 'schema', 'dbo', 'table', 'D_CUSTOMER_PROFILE', 'column', 'column_to_replace'
PRINT '[INFO] ADD COLUMN [DBO].[D_CUSTOMER_PROFILE].[column_to_replace]'
END
'''
for index, col in meta.iterrows():
print(
sql_txt.replace('column_to_replace',
col[0]).replace('xxxxxxxxxxxxxxxxxx', col[1]))
f.write(
sql_txt.replace('column_to_replace',
col[0]).replace('xxxxxxxxxxxxxxxxxx', col[1]))
f.close()
'''
import pandas as pd
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.writer.excel import ExcelWriter
import time
import openpyxl.styles as sty
import conf.acct as acct
import db_connect.db_operator as DB
from tool.tool import file_name,logger,identify_backup_tables
SEED_FILE = r".\seed\DDL_GAP_AB.xlsx"
excelName = file_name('DDL_GAP_AB','xlsx')
workbook = load_workbook(SEED_FILE)
ddl_sheet = workbook.get_sheet_by_name('DDL')
sp_sheet = workbook.get_sheet_by_name('SP')
index_sheet = workbook.get_sheet_by_name('INDEX')
def merge_ddl(db1, db2, sheet):
db1 = db1.fillna('null')
db2 = db2.fillna('null')
gap = pd.merge(db1, db2, on = ['table_name','column_name'], how='outer')
for index, col in gap.iterrows():
if identify_backup_tables(col[0].lower()):
###
# Genearte data dictionary based on metadata and sample value
###
from openpyxl import Workbook
from openpyxl import load_workbook
from db_connect.sqlserver_db import UseSqlserverDB
import conf.acct as acct
import tool.TSQL as TSQL_function
import tool.tool as tool
CURRENT_DB = acct.UAT_UT_CAMPING_MART
SEED_FILE = r'.\seed\DataDictionary_Template.xlsx'
excelName = tool.file_name('DataDictionary', 'xlsx')
workbook = load_workbook(SEED_FILE)
with UseSqlserverDB(CURRENT_DB) as cursor:
sheet = workbook.get_sheet_by_name('DataDictionary')
rows = sheet.rows
columns = sheet.columns
for row in range(2, sheet.max_row + 1):
tableName = str(sheet.cell(row=row, column=1).value)
columnName = str(sheet.cell(row=row, column=4).value)
#print (tableName + ":" + columnName)
query = "SELECT TOP 1 [" + columnName + "] FROM " + tableName + " WITH(NOLOCK) WHERE [" + columnName + "] IS NOT NULL"
result = str(TSQL_function.inquery_single_row(query, cursor))
sheet.cell(row=row, column=12).value = result
import pandas as pd
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.writer.excel import ExcelWriter
import time
import openpyxl.styles as sty
import conf.acct_oracle as acct
from db_connect.oracle_db import UseOracleDB
import tool.oracle_tool as oracle_tool
from tool.tool import file_name, logger
SEED_FILE = ".\seed\ORACLE_DDL_GAP.xlsx"
nameTime = time.strftime('%Y%m%d_%H%M%S')
excelName = file_name('ORACLE_DDL_GAP', 'xlsx')
workbook = load_workbook(SEED_FILE)
ddl_sheet = workbook.get_sheet_by_name('DDL')
synonyms_sheet = workbook.get_sheet_by_name('SYNONYMS')
def merge(dev, qa, sheet, key_column):
dev = dev.fillna('null')
qa = qa.fillna('null')
gap = pd.merge(dev, qa, on=key_column, how='outer')
for index, col in gap.iterrows():
if str(col[1]) == 'nan' or str(col[2]) == 'nan':
pass
else:
14:new_table_end_postion]
original_table_start_position = line.find("FROM")
if line.endswith("WITH(NOLOCK)"):
new_table_end_postion = line.find(" WITH(NOLOCK)")
original_table_nm = line.rstrip()[original_table_start_position +
5:new_table_end_postion]
else:
original_table_nm = line.rstrip()[original_table_start_position +
5:]
add_backup_table_sql += gen_add_back_table(original_table_nm,
new_table_nm)
else:
rest_sql += line + "\n"
rollback_sql += drop_index_sql
rollback_sql += column_sql
rollback_sql += add_index_sql
rollback_sql += rename_sql
rollback_sql += add_backup_table_sql
rollbackfilename = file_name("Rollback", "sql")
with open(rollbackfilename, 'w') as file_object:
file_object.write(rollback_sql)
restfilename = file_name("restforaduit", "sql")
with open(restfilename, 'w') as file_object:
file_object.write(rest_sql)
import datetime
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.writer.excel import ExcelWriter
import time
import openpyxl.styles as sty
import conf.acct as acct
import db_connect.db_operator as DB
from db_connect.sqlserver_db import UseSqlserverDB
from tool.tool import file_name,logger, save_file
from tool.TSQL import search_db, search_column
SEED_FILE = r".\seed\Search_columns.xlsx"
nameTime = time.strftime('%Y%m%d_%H%M%S')
excelName = file_name('Search_Columns','xlsx')
workbook = load_workbook(SEED_FILE)
tempate_sheet = workbook.get_sheet_by_name('Template')
SERVER_LIST = (
#(acct.DEV_CO_HF_MART, 'DEV'),
#(acct.QA_CO_HF_MART, 'QA_US'),
#(acct.QA_AB_HF_MART, 'QA_CA'),
#(acct.UAT_CO_HF_MART, 'UAT_US'),
#(acct.UAT_AB_HF_MART, 'UAT_CA'),
(acct.PROD_CO_HF_MART, 'PROD_US'),
(acct.PROD_AB_HF_MART, 'PROD_CA')
)
def gen_alter_sql(table_nm, column_nm, type_nm, length, new_type, new_len)->str:
from openpyxl import load_workbook
from openpyxl.writer.excel import ExcelWriter
import time
import conf.acct_oracle as acct_oracle
from db_connect.oracle_db import UseOracleDB
from tool.df_compare import has_gap
import tool.oracle_tool as oracle_tool
import tool.tool as tool
CURRENT_DB = acct_oracle.PROD_US
os.system("")
SEED_FILE = r".\seed\Domain Data Template.xlsx"
nameTime = time.strftime('%Y%m%d_%H%M%S')
excelName = tool.file_name('Domain Data Template', 'xlsx')
workbook = load_workbook(SEED_FILE)
sheetnames = workbook.get_sheet_names()
query_sheet = workbook.get_sheet_by_name('Checking_Query')
check_list = []
with UseOracleDB(CURRENT_DB) as cursor:
for sheetname in sheetnames:
sheet = workbook.get_sheet_by_name(sheetname)
if sheetname != 'Checking_Query':
rows = sheet.rows
columns = sheet.columns