def get_fileds_type(tablename): query = "SHOW FIELDS FROM %s " % tablename filed_type = my_query(query) # 对外键不能设空值,排除带有id的字段 filed_to_datatype = { filed['Field']: re.search(pattern, filed['Type']).group() for filed in filed_type if not filed['Field'].endswith('_id') } return filed_to_datatype
def related_lot_id(auction_id): query = "select t.id as t_id,t.lot, l.lot_no,l.id as l_id from transaction as t " \ "left join lot as l on t.lot= l.lot_no where l.auction_id='%s' and t.auction_id=%s" % (auction_id,auction_id) count = 0 lots = my_query(query) args = [] for lot in lots: args.append([lot['l_id'], lot['t_id'], auction_id]) query_update = "UPDATE transaction set lot_id=%s where id=%s and auction_id=%s" my_insert(query_update, args) print '-------------------------------------------------' print u'<总结>第%s届拍卖会关联' % (auction_id) print '--------------------------------------------------'
def crm_client(): query = "select number from crms_paper where number <>'' " ids_from_pg = pg_query('CRM', query) ids_from_pg = [item[0] for item in ids_from_pg] for num in ids_from_pg: try: # print '->>',num match = re.search(r'[\w-]+', num).group() # print match except re.error as e: print e query = "select identity_number from contract where identity_number LIKE '%s'" % ('%%%s%%' % match) records = my_query(query) if records: print match
def check_identity(): query = "select identity_num from sellers where identity_num <>'' " ids_from_pg = pg_query(db, query) ids_from_pg = [item[0] for item in ids_from_pg] for num in ids_from_pg: try: # print '->>',num match = re.search(r'[\w-]+', num).group() # print match except re.error as e: print e query = "select number from customer_identity where number LIKE '%s'" % ('%%%s%%' % match) records = my_query(query) if records: print match
def related_lot_id(auction_id): """ 合同id和拍品信息关联起来 :param auction_id: :return: """ query = ( "select l.id, c.id,c.electronic_id from lot as l left join contract as c on c.electronic_id = l.contract_id " "where l.auction_id=%s" % auction_id) contract_ids = my_query(query) args = [] for item in contract_ids: args.append([item['c.id'], item['id'], auction_id]) print(item['c.id'], item['id'], auction_id) query_update = 'update lot set contract_id=%s where id=%s and auction_id=%s' my_insert(query_update, args) print '%关联成功'
def handle_fileds(tablename, conn): """ set specail filed(Null,NONE) to a defualt value :return: """ cursor = conn.cursor() filed_to_datatype = get_fileds_type(tablename) fileds = [filed for filed in filed_to_datatype.keys()] query = "SELECT * FROM %s " % tablename records = my_query(query) null_filed = [] for record in records: flag = False for fd in fileds: if record[fd] in [None, 'NULL']: # 如果该字段为空的处理 fd 为字段 null_filed.append(fd) flag = True if filed_to_datatype[fd] in TYPE_MAPPING: default_values = TYPE_MAPPING[ filed_to_datatype[fd]] # 获得字段的类型及对应的默认值 record[fd] = default_values else: print u'请把%s加入映射表' % filed_to_datatype[fd] else: pass if flag: filed_id = record.get('id') if record.get('id') else record.get( 'ID') # 代码片段:更新数据库 query = update_table(tablename, record, null_filed, filed_id) cursor.execute(query) # 清空列表内容 null_filed = [] # 一次性提交,效率较高 conn.commit() cursor.close()
def handle_fileds(tablename, conn): """ set specail filed(Null,NONE) to a defualt value :return: """ cursor = conn.cursor() filed_to_datatype = get_fileds_type(tablename) fileds = [filed for filed in filed_to_datatype.keys()] query = "SELECT * FROM %s " % tablename records = my_query(query) null_filed = [] for record in records: flag = False for fd in fileds: if record[fd] in [None, 'NULL']: # 如果该字段为空的处理 fd 为字段 null_filed.append(fd) flag = True if filed_to_datatype[fd] in TYPE_MAPPING: default_values = TYPE_MAPPING[filed_to_datatype[fd]] # 获得字段的类型及对应的默认值 record[fd] = default_values else: print u'请把%s加入映射表' % filed_to_datatype[fd] else: pass if flag: filed_id = record.get('id') if record.get('id') else record.get('ID') # 代码片段:更新数据库 query = update_table(tablename, record, null_filed, filed_id) cursor.execute(query) # 清空列表内容 null_filed = [] # 一次性提交,效率较高 conn.commit() cursor.close()
def check_customer(name): query = "select name from customer where name like '%s' " % ('%%%s%%' % name) records = my_query(query) if not records: print name
def get_fileds_type(tablename): query = "SHOW FIELDS FROM %s " % tablename filed_type = my_query(query) # 对外键不能设空值,排除带有id的字段 filed_to_datatype = {filed['Field']: re.search(pattern, filed['Type']).group() for filed in filed_type if not filed['Field'].endswith('_id')} return filed_to_datatype