def insertCByT(db, tb):
conn1 = MySQL(config.hivemeta)
conn2 = MySQL(config.washmeta)
hivesql = """select
'{}' AS Data_Tblid,
t1.COLUMN_NAME as Fld_Phys_Nm,
t1.COMMENT as Fld_Cn_Nm,
t1.TYPE_NAME as Fld_Data_Type,
t1.INTEGER_IDX as Fld_Ord
from columns_v2 t1
left join sds t2
on t1.cd_id = t2.cd_id
left join tbls t3
on t2.sd_id = t3.sd_id
left join dbs t4
on t3.db_id=t4.db_id
where t4.name='{}' and t3.tbl_name='{}'"""
cs1 = conn1.execute(
hivesql.format(tb.get('Data_Tbl_Phys_Nm'), db,
tb.get('Data_Tbl_Phys_Nm')))
cs1 = getTableID(cs1)
for c in cs1:
c['Create_Dt'] = tb['Create_Dt']
try:
logging.debug("插入新增表字段：" + str(c['Fld_Phys_Nm']))
conn2.insert('data_fld', c)
except Exception as e:
logging.error("插入新增表字段失败：" + str(c['Fld_Phys_Nm']))
print traceback.format_exc()
del conn1
del conn2
async def save_file_to_database_server(path):
conn = MySQL()
db_config = {'host': 'localhost', 'user': '******', 'password': '******'}
try:
await conn.create_connection(db_config)
fileData = convertToBinaryData(path)
except FileNotFoundError:
print("File path not found...")
except Exception as err:
print(err)
else:
await conn.create_cursor()
sql = """INSERT INTO testData (fileName, fileData, fileSaveDate) VALUES ("{}", ?, "{}")""".format(
path, datetime.now())
await conn.execute_blob(sql, fileData)
await conn.commit()
await conn.close_cursor()
await conn.close_connection()
def deleteT(db, date):
tables1 = gethiveT(db) #传库名
tables2 = getCT(db) #传库名
conn = MySQL(config.washmeta)
tables1 = getDBs(tables1) #传库名
len2 = len(tables2)
flag = True
if len2 == 0:
for table1 in tables1:
table1['Create_Dt'] = date
table1['Data_Tbl_UUID'] = uuid.uuid1()
logging.debug('table1:' + table1['Data_Tbl_Phys_Nm'])
conn.insert("data_tbl", table1)
for table2 in tables2:
for table1 in tables1:
if table2.get('Data_Tbl_Phys_Nm') == table1.get(
'Data_Tbl_Phys_Nm'):
flag = False
break
if flag:
logging.debug('table1:' + table1['Data_Tbl_Phys_Nm'])
try:
conn.execute(
"update data_tbl set Del_Dt='{}' where Data_Tbl_Phys_Nm='{}'"
.format(date, table2.get('Data_Tbl_Phys_Nm')))
except Exception:
logging.error('删除表元数据失败，数据为：' + str(table2))
print traceback.format_exc()
sys.exit(1)
flag = True
del conn
def __init__(self, config, sgdb=None):
Main._check_configuration(config)
self.cli = CLI()
self.config = config
self.log = LOG(self.config.get("log_dir", None))
self.sgdb = sgdb
if self.sgdb is None and not self.config.get("new_migration", None):
if self.config.get("database_engine") == 'mysql':
from mysql import MySQL
self.sgdb = MySQL(config)
elif self.config.get("database_engine") == 'oracle':
from oracle import Oracle
self.sgdb = Oracle(config)
elif self.config.get("database_engine") == 'mssql':
from mssql import MSSQL
self.sgdb = MSSQL(config)
elif self.config.get("database_engine") == 'postgresql':
from postgresql import PostgreSQL
self.sgdb = PostgreSQL(config)
else:
raise Exception("engine not supported '%s'" % self.config.get("database_engine"))
self.db_migrate = SimpleDBMigrate(self.config)
def douyin_count():
douyin_dir = "/mnt/data/douyin_bak/account"
douyin_size = count(character, douyin_dir)
account_size = 0 if douyin_size == None else douyin_size
article_dir = "/mnt/data/douyin_bak/article"
article_size = count(character, article_dir)
article_size = 0 if article_size == None else article_size
update_dir = "/mnt/data/douyin_bak/update"
update_size = count(character, update_dir)
update_size = 0 if update_size == 0 else update_size
video_dir = "/mnt/data/douyin_bak/video"
video_size = video_count("mp4", video_dir)
video_size = 0 if video_dir == None else video_size
# 总量数据
total_data = account_size + article_size + update_size + video_size
count_result = Field.get_fields()
count_result['total_data'] = total_data
count_result['account_total'] = account_size
count_result['article_total'] = article_size
count_result['update_total'] = update_size
count_result['video_total'] = video_size
count_result['cycle_type'] = interval
mysql = MySQL(user=user, pwd=password, host=host, db=db, tb=douyin_tb)
mysql.update_data(count_result)
def findByName(self,name):
n = MySQL()
n.selectDb('images')
tbname = 'similar_images'
n.query("select name,features from similar_images where name='"+name+"'")
return n.fetchRow()
def findAll(self):
n = MySQL()
n.selectDb('images')
tbname = 'similar_images'
n.query("select name,features from similar_images")
return n.fetchAll()
def __init__(self):
self.dns = MySQL(host=DNS_HOST,
user=DNS_USER,
passwd=DNS_PASSWD,
port=DNS_PORT,
db=DNS_DB)
self.initCppLib()
def insert(self,d):
n = MySQL()
n.selectDb('images')
tbname = 'similar_images'
n.insert(tbname, d)
n.commit()
def compareC(db, table1, table2):
conn1 = MySQL(config.hivemeta)
conn2 = MySQL(config.washmeta)
c1 = conn1.execute(
const.getTCSql.format(db, table1.get('Data_Tbl_Phys_Nm')))
c2 = conn2.execute(
const.getCTCSql.format(db, table2.get('Data_Tbl_Phys_Nm')))
del conn1
del conn2
c1.sort()
c2.sort()
if cmp(c1, c2) != 0:
# print 'c1:',c1
# print 'c2:',c2
return True
else:
return False
def get_change_ddl(table_id):
flag = False
db = MySQL(config.dqc_mysql)
result = db.execute(mask_const.TABLE_CHANGE, (table_id, ))
if len(result) != 0:
flag = True
del db
return flag
def get_field(table_id):
db = MySQL(config.dqc_mysql)
result = db.execute(const.FIELD_TABLE, (table_id, ))
del db
item = {}
for rs in result:
item[rs['Fld_Phys_Nm']] = rs['Fldid']
return item
async def test_mysql_createConnection(self):
conn = MySQL()
await conn.create_connection({
'host': 'localhost',
'user': '******',
'password': '******'
})
await self.assertNotEqual(conn.connection(), None)
def get_label_id(table_id):
db = MySQL(config.dqc_mysql)
result = db.execute(mask_const.MASK_LABLE, (table_id, ))
del db
if len(result) == 0:
raise exception.MaskException(
"table lable is non-exist. [table_id:%s]" % table_id)
return result[0]["Labelid"]
def get_target_database(database, usage):
db = MySQL(config.dqc_mysql)
result = db.execute(const.TARGET_DATABASE, (database, usage))
del db
if len(result) == 0:
raise exception.DQCException(
"target database is non-exist. [database:%s]" % database)
return result[0]["db_phys_nm"]
def get_database_table(table_id):
db = MySQL(config.dqc_mysql)
result = db.execute(const.DB_TABLE, (table_id, ))
del db
if len(result) == 0:
raise exception.DQCException(
"database table is non-exist. [table_id:%s]" % table_id)
return result[0]["db_phys_nm"], result[0]["data_tbl_phys_nm"]
def get_partition_path(table_id, partition_date):
db = MySQL(config.dqc_mysql)
result = db.execute(const.PARTITION_PATH, (table_id, partition_date))
del db
if len(result) == 0:
return None
else:
return result[0]["dp_path"]
def get_check_item():
db = MySQL(config.dqc_mysql)
result = db.execute(const.CHECK_ITEM)
del db
item = {}
for rs in result:
item[rs['Chk_Proj_Cd']] = rs['Chk_Projid']
return item
def get_partition_latest(table_id):
db = MySQL(config.dqc_mysql)
result = db.execute(const.MAX_PARTITION_DATE, (table_id, ))
del db
if result[0]["latest"] is None:
raise exception.DQCException(
"table partition is non-exist. [table_id:%s]" % table_id)
else:
return result[0]["latest"]
def get_mask_cmpu():
db = MySQL(config.dqc_mysql)
result = db.execute(mask_const.MASK_CMPU)
del db
mask_cmpu = {
rs["Data_Wash_Cmpu_Cd"]: rs["Data_Wash_Cmpuid"]
for rs in result
}
return mask_cmpu
def insert_data(data):
n=MySQL()
sql = "select id from urls_crawled where url='"+data['url']+"';"
result = n.query(sql)
if result == 0:
try:
n.insert('urls_crawled',data)
except:
return 200
n.commit()
def getCC(db):
try:
conn = MySQL(config.washmeta)
columns = conn.execute(const.getCCSql.format(db))
except Exception:
logging.error('获取清洗库 字段数据 失败！')
print traceback.format_exc()
sys.exit(1)
del conn
return columns
def getCT(db):
try:
conn = MySQL(config.washmeta)
tables = conn.execute(const.getCTSql.format(db))
except Exception:
logging.error('获取清洗库 表元数据 失败！')
print traceback.format_exc()
sys.exit(1)
del conn
return tables
def gethiveC(db):
try:
conn = MySQL(config.hivemeta)
columns = conn.execute(const.getCSql.format(db))
except Exception:
logging.error('获取hive 字段元数据失败')
print traceback.format_exc()
sys.exit(1)
del conn
return columns
def gethiveP(db):
try:
conn = MySQL(config.hivemeta)
partitions = conn.execute(const.getPSql.format(db))
except Exception:
logging.error('获取hive 分区元数据失败！')
print traceback.format_exc()
sys.exit(1)
del conn
return partitions
def update_load_status(self, users):
db = MySQL()
con = db.connect()
with con:
with con.cursor() as cursor:
for user in users:
sql = "INSERT INTO afts.OPERATION_HIST (context_id, emp_id, update_time, status) VALUES ('{0}-{1}', '{2}', '{3}','LOADED');"\
.format(user, datetime.today().strftime("%y%m%d"), user, datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
logging.info("Excute SQL: " + sql)
cursor.execute(sql)
con.commit()
def compareP(db, table1, table2):
conn1 = MySQL(config.hivemeta)
conn2 = MySQL(config.washmeta)
ps1 = conn1.execute(
const.getTPSql.format(db, table1.get('Data_Tbl_Phys_Nm')))
ps2 = conn2.execute(
const.getCTPSql.format(db, table2.get('Data_Tbl_Phys_Nm')))
del conn1
del conn2
flag = False
for p1 in ps1:
flag = True
for p2 in ps2:
if p1 == p2:
flag = False
ps2.remove(p2)
break
if flag == True:
break
return flag
def client_handler(connection, fingerprint):
try:
code = random.randrange(0, 999999999999999)
# print(code) # DEBUG
connection.send(b"\033[1mItrago WebTool Emergency Access Shell\033[0m\nPlease decrypt the following message to verify that you are the Administrator ([email protected])!\n")
connection.send(bytes("Encrypted Message:\n%s\n" % str(gpg.encrypt(str(code), fingerprint)),'utf-8'))
connection.send(b"Message: ")
if code == int(str(connection.recv(8192).decode('utf-8')).strip('\r').strip('\n')):
connection.send(b"\n\033[1mEmergency Shell Access Granted\033[0m\n")
while True:
try:
connection.send(b"$ ")
data = str(connection.recv(8192).decode('utf-8')).strip('\r').strip('\n').strip()
if data == "exit" or data == "quit":
connection.close()
break;
elif data == "copyright":
connection.send(b"\033[1mCopyright\033[0m\n")
connection.send(b"Tim Supelir Intrago Company XY Consultment\n")
elif data == "help":
connection.send(b"\033[1mHelppage\033[0m\n")
connection.send(b"<SQL Query> - Execute an SQL query\n")
connection.send(b"exit - Quit the emergency shell\n")
connection.send(b"quit - Quit the emergency shell\n")
connection.send(b"copyright - Show the copyright informations\n")
connection.send(b"help - Show this helppage\n")
else:
db = MySQL(*connection_data)
if data.upper().startswith("INSERT INTO"):
try:
rid = db.update(data)
connection.send(bytes("Okay, last inserted id: %s\n" % str(rid), 'utf-8'))
except Exception as e:
connection.send(
bytes(data + ": sql error\n",'utf-8'))
elif data.upper().startswith("SELECT") or data.upper().startswith("SHOW TABLES"):
try:
connection.send(bytes(str(db.queryAll(data)) + "\n", 'utf-8')) # convert to bytes
except Exception as e:
connection.send(bytes(data + ": sql error\n", 'utf-8'))
else:
connection.send(bytes(data + ": command not found\n", 'utf-8'))
except ValueError:
connection.send(b"\n")
connection.close()
else:
connection.send(b"Access Denied\n")
connection.close()
except ValueError:
connection.send(b"\n")
connection.close()
except BrokenPipeError:
connection.send(b"\n")
connection.close()
def run(ip, port, db_host, db_user, db_pass, db_name, lda):
app.config['DB_HOST'] = db_host
app.config['DB_USER'] = db_user
app.config['DB_PASS'] = db_pass
app.config['DB_NAME'] = db_name
app.config['LDA_SUBFOLDER'] = lda
connection = MySQL(db_host, db_user, db_pass, db_name)
with connection as test:
pass
app.run(host=ip, port=port, threaded=True)
def insertNewP(db, date):
partitions1 = gethiveP(db) #传库名
partitions2 = getCP(db) #传库名
conn = MySQL(config.washmeta)
partitions1 = getTableID(partitions1)
len2 = len(partitions2)
if len2 == 0:
for p1 in partitions1:
logging.debug('partition:' + str(p1['Data_Tblid']) + ':' +
p1['Dp_Path'])
try:
conn.insert("dp", p1)
except Exception:
logging.error('第一次插入分区数据失败，数据为：' + str(p1))
print traceback.format_exc()
sys.exit(1)
else:
for p1 in partitions1:
flag = True
for p2 in partitions2:
if p1.get('Data_Tblid') == p2.get('Data_Tblid') and p1.get(
'Dp_Path') == p2.get('Dp_Path'):
flag = False
break
if flag:
logging.debug('插入分区partition:' + str(p1['Data_Tblid']) + ':' +
p1['Dp_Path'])
try:
conn.insert("dp", p1)
except Exception:
logging.error('插入分区数据失败，数据为：' + str(p1))
print traceback.format_exc()
sys.exit(1)
for p2 in partitions2:
flag = True
for p1 in partitions1:
if p2.get('Data_Tblid') == p1.get('Data_Tblid') and p2.get(
'Dp_Path') == p1.get('Dp_Path'):
flag = False
break
if flag:
logging.debug('删除分区partition:' + str(p2['Data_Tblid']) + ':' +
p2['Dp_Path'])
try:
conn.execute(
"delete from dp where Data_Tblid='{}' and Dp_Path='{}'"
.format(p2.get('Data_Tblid'), p2.get('Dp_Path')))
except Exception:
logging.error('删除分区数据失败，数据为：' + str(p1))
print traceback.format_exc()
sys.exit(1)
del conn
