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