def insert_data(db_name, db_table_name, uploaded_by="没有填写", data_map={}): print("Starting insert data") if not db_name or not db_table_name or not data_map: return False, '插入失败,检查参数', common.format_ymdhms_time_now(), "1" print("插入的数据库", db_name) print("插入的表", db_table_name) db = pymysql.connect("192.168.1.100", "root", "root", db_name) # 使用cursor()方法获取操作游标 cursor = db.cursor() select_col_sql = "SELECT column_name FROM information_schema.columns WHERE table_schema=" + "\"" + db_name + "\"" + " AND table_name=" + "\"" + db_table_name + "\"" # print(select_col_sql) cursor.execute(select_col_sql) results = cursor.fetchall() col_name_list = [] # print(results) for i in results: col_name_list.append(str(i[0])) # print("列名:", col_name_list) db.commit() cursor = db.cursor() data_map = collections.OrderedDict(data_map) print("要进行插入的数据:", data_map) try: sql = "INSERT INTO " + db_table_name + "(" + ",".join(list(data_map.keys())) + ")" + "VALUES" + "(\"" + "\",\"".join( [str(i) for i in list(data_map.values())]) + "\")" print("sqlsqlsql", sql) cursor.execute(sql) except: # 关闭数据库连接 db.close() return False, '插入失败', '0', '0' db.commit() # 关闭数据库连接 db.close() return True, '插入成功', common.format_ymdhms_time_now(), "1"
def insert_photos_MD5(db_name, table_name, **kw): print("Starting") db = pymysql.connect("192.168.1.100", "root", "root", db_name) # 使用cursor()方法获取操作游标 cursor = db.cursor() if kw.get('data_list'): data_list = kw.get('data_list') else: data_list = [] if kw.get('city'): city = kw.get('city') else: city = "未传城市参数city" data_need_update_list = data_list # 需要更新的数组 logger.info("[%s] city: %s, db: %s, table: %s, total update MD5 files: %s", common.format_ymdhms_time_now(), city, db_name, table_name, str(len(data_need_update_list))) step = 10000 # 每次更新step个 step_num = math.ceil(len(data_need_update_list) / step) try: for i in range(step_num): head = i * step if i == (step_num - 1): tail = len(data_need_update_list) else: tail = (i + 1) * step insert_sql = "" insert_sql_head = " INSERT INTO `{table_name}`(`city`, `file_name`, `iden_id`, `MD5`) " insert_sql_head = insert_sql_head.format(table_name=table_name) insert_sql_body = " (\"{city}\", \"{file_name}\", \"{iden_id}\", \"{MD5}\") " temp_index = 0 for city, file_name, iden_id, MD5 in data_need_update_list[head:tail]: if temp_index == 0: insert_sql += insert_sql_head insert_sql += " VALUES " else: insert_sql += ", " insert_sql += insert_sql_body.format(city=city, file_name=file_name, iden_id=iden_id, MD5=MD5) temp_index += 1 # logger.info("[%s] city: %s, db: %s, table: %s, sql: %s", common.format_ymdhms_time_now(), city, # db_name, table_name, insert_sql) cursor.execute(insert_sql) except: print("更新到数据库失败") return False db.commit() db.close() return True
def __init__(self, id, username, password, contact_num, truename, department, department_id, usertype="common", working_city="", projects_index=0, install_app_version=""): self.id = id self.username = username self.password = password self.contact_num = contact_num self.department = department self.department_id = department_id self.usertype = usertype self.truename = truename self.working_city = working_city self.projects_index = projects_index self.register_time = common.format_ymdhms_time_now() self.install_app_version = install_app_version
def projects_PUT_POST(): app.logger.info("projects_PUT_POST") app.logger.info("request: %s", request) app.logger.info("当前访问用户:%s", request.remote_addr) # app.logger.info("Authorization:", request.headers['Authorization']) result = Auth.identify(Auth, request) # app.logger.info(result) app.logger.info("状态: %s 用户: %s ", result.get('status'), result.get('data')) if (result['status'] and result['data']): pass else: # return json.dumps(result, ensure_ascii=False) return jsonify(result) request_json = request.get_json() if request.method == 'PUT': if request_json.get('type') == PROJECTS_REQUEST_TYPE.projects.name: if not request_json.get("update_datas"): return jsonify(common.falseReturn("", "没有要修改的数据")) else: update_datas_map = request_json.get("update_datas") select_status, select_msg, select_date, result = mysql.select_data( "ws_doorplate", "projects", data_map=update_datas_map, select_col=["index"]) # print("resultresult", result) if not result: pass else: return jsonify( common.falseReturn( {"index": result[0][0]}, "数据已经存在,新建失败,直接使用", addition=common.false_Type.exist)) if not request_json.get("index"): return jsonify(common.falseReturn("", "没有传入要修改数据的index")) else: index = request_json.get("index") status, msg, date, num = mysql.update_data( "ws_doorplate", "projects", data_map=update_datas_map, data_select_map={"index": index}) if status: long_time_cache.clear() return jsonify(common.trueReturn("", msg)) else: return jsonify(common.falseReturn("", msg)) else: return jsonify(common.falseReturn("", "传入的type不正确或者没有传type")) elif request.method == 'POST': if request_json.get('type') == PROJECTS_REQUEST_TYPE.projects.name: if not request_json.get("uploaded_by"): if not result.get('data').get('username'): uploaded_by = "没有填写" else: uploaded_by = result.get('data')["username"] else: uploaded_by = request.args.get("uploaded_by") if not request_json.get("insert_datas"): return jsonify(common.falseReturn("", "没有要新建的数据")) else: insert_datas_map = request_json.get("insert_datas") select_status, select_msg, select_date, result = mysql.select_data( "ws_doorplate", "projects", data_map=insert_datas_map, select_col=["index"]) #print("resultresult", result) if not result: pass else: return jsonify( common.falseReturn( {"index": result[0][0]}, "数据已经存在,新建失败,直接使用", addition=common.false_Type.exist)) # 如果数据不存在,才增加时间然后往下走插入数据 insert_datas_map[ "uploaded_date"] = common.format_ymdhms_time_now() status, msg, date, num = mysql.insert_data( "ws_doorplate", "projects", uploaded_by=uploaded_by, data_map=insert_datas_map) select_status, select_msg, select_date, result = mysql.select_data( "ws_doorplate", "projects", data_map=insert_datas_map, select_col=["index"]) status = status & select_status msg = msg + " \n " + select_msg if status: long_time_cache.clear() return jsonify( common.trueReturn({"index": result[0][0]}, msg)) else: return jsonify(common.falseReturn("", msg)) else: return jsonify(common.falseReturn("", "传入的type不正确或者没有传type")) else: return jsonify(common.falseReturn("", "后端出错"))
def update_data(db_name, db_table_name, updated_by="没有填写", data_map={}, data_select_map={}): print("Starting update data") if not db_name or not db_table_name or not data_map: return False, '更新失败,检查参数', common.format_ymdhms_time_now(), "1" print("更新的数据库", db_name) print("更新的表", db_table_name) db = pymysql.connect("192.168.1.100", "root", "root", db_name) # 使用cursor()方法获取操作游标 cursor = db.cursor() select_col_sql = "SELECT column_name FROM information_schema.columns WHERE table_schema=" + "\"" + db_name + "\"" + " AND table_name=" + "\"" + db_table_name + "\"" # print(select_col_sql) cursor.execute(select_col_sql) results = cursor.fetchall() col_name_list = [] # print(results) for i in results: col_name_list.append(str(i[0])) # print("列名:", col_name_list) db.commit() cursor = db.cursor() data_map = collections.OrderedDict(data_map) print("要进行更新的数据:", data_map) print("要进行更新的数据是:", data_select_map) try: sql_head = "UPDATE " + db_table_name + " " sql_tail = "" for name, value in data_map.items(): if not sql_tail: sql_tail = " SET " + "`" + name + "`=" + "\"" + str(value) + "\"" else: sql_tail += ", " + "`" + name + "`=" + "\"" + str(value) + "\"" condition_sql = "" for name, value in data_select_map.items(): if not condition_sql: condition_sql = " WHERE " + "`" + name + "`=" + "\"" + str(value) + "\"" else: condition_sql += " AND " + "`" + name + "`=" + "\"" + str(value) + "\"" sql = sql_head + sql_tail + condition_sql print("sqlsqlsql", sql) cursor.execute(sql) except: # 关闭数据库连接 db.close() return False, '更新失败', '0', '0' db.commit() # 关闭数据库连接 db.close() return True, '更新成功', common.format_ymdhms_time_now(), "1"
def select_data(db_name, db_table_name, data_map={}, select_col=[]): print("Starting select data") if not db_name or not db_table_name or not data_map: return False, '查询失败,检查参数', common.format_ymdhms_time_now(), "1" print("查询的数据库", db_name) print("查询的表", db_table_name) db = pymysql.connect("192.168.1.100", "root", "root", db_name) # 使用cursor()方法获取操作游标 cursor = db.cursor() select_col_sql = "SELECT column_name FROM information_schema.columns WHERE table_schema=" + "\"" + db_name + "\"" + " AND table_name=" + "\"" + db_table_name + "\"" # print(select_col_sql) cursor.execute(select_col_sql) results = cursor.fetchall() col_name_list = [] # print(results) for i in results: col_name_list.append(str(i[0])) # print("列名:", col_name_list) db.commit() cursor = db.cursor() if not select_col: select_col_sql = " * " else: select_col_sql = "`" + "`,`".join(select_col) + "`" data_map = collections.OrderedDict(data_map) condition_sql = "" print("要进行查询的数据条件:", data_map) for key, value in data_map.items(): if not condition_sql: condition_sql += " `" + key + "`=\"" + str(value) + "\"" else: condition_sql += " AND `" + key + "`=\"" + str(value) + "\"" try: sql = "SELECT " + select_col_sql + " FROM " + db_table_name + " WHERE " + condition_sql print("sqlsqlsql", sql) cursor.execute(sql) result = cursor.fetchall() except: # 关闭数据库连接 db.close() return False, '查询失败', '0', (()) # 关闭数据库连接 db.close() return True, '查询成功', common.format_ymdhms_time_now(), result
def update_photos(db_name, table_name, **kw): print("Starting") db = pymysql.connect("192.168.1.100", "root", "root", db_name) # 使用cursor()方法获取操作游标 cursor = db.cursor() # cursor.execute( # " SELECT column_name FROM information_schema.columns WHERE table_schema=\'"+ db_name +"\' AND table_name=\'"+ table_name +"\'") # results = cursor.fetchall() # col_name_list = [] # print(results) # for i in results: # col_name_list.append(str(i[0])) # # print("列名:", col_name_list) # db.commit() # 更新的条件 update_condition = "" update_photos_type = "" update_condition_list = [] if not kw.get("picture_type"): print("mysql.py update_photos() kw 没有 picture_type") return False else: picture_type = kw.get("picture_type") picture_type_by = "" if kw.get("picture_type_by"): picture_type_by = kw.get("picture_type_by") else: picture_type_by = "" mark_picture_type = 0 if kw.get("mark_picture_type") == 1: mark_picture_type = kw.get("mark_picture_type") else: mark_picture_type = 0 if kw.get("picture_type_date"): picture_type_date = kw.get("picture_type_date") else: picture_type_date = "" if kw.get('picture_type_photos_upload_by'): picture_type_photos_upload_by = kw.get('picture_type_photos_upload_by') else: picture_type_photos_upload_by = "未知" if kw.get("picture_type_photos_upload_date"): picture_type_photos_upload_date = kw.get("picture_type_photos_upload_date") else: picture_type_photos_upload_date = common.format_ymdhms_time_now() if kw.get("file_from"): file_from = kw.get("file_from") else: file_from = "没有填写" for k, v in kw.items(): if k.find('list') >= 0: update_condition = k.split("_")[0] + "_" + k.split("_")[1] update_condition_list = v if k.find('cls') >= 0: # global_id_cls_list update_photos_type = 'installed_photos_cls' photos_type = "cls" elif k.find('far') >= 0: # dp_id_far_list # global_id_far_list update_photos_type = 'installed_photos_far' photos_type = "far" if picture_type == "collected": update_photos_type = "collected_photos" if not update_photos_type or not update_condition or not update_condition_list: print("更新到数据库失败") print("update_photos_type: ", update_photos_type) print("update_condition: ", update_condition) print("update_condition_list len: ", len(update_condition_list)) return False print("总共要进行更新的数据:", len(update_condition_list)) condition_sql = "" if picture_type_by: condition_sql += ", `{picture_type}_by`=\"" + picture_type_by + "\" " condition_sql = condition_sql.format(picture_type=picture_type) if mark_picture_type == 1: condition_sql += ", `{picture_type}`=`{picture_type}`+1 " condition_sql = condition_sql.format(picture_type=picture_type) if picture_type_date: try: picture_type_date = datetime.datetime.strptime(picture_type_date, "%Y-%m-%d") picture_type_date += +datetime.timedelta(hours=14) picture_type_date = picture_type_date.strftime("%Y-%m-%d %H:%M:%S") except: picture_type_date = datetime.datetime.strptime(picture_type_date, "%Y-%m-%d %H:%M:%S") picture_type_date = picture_type_date.strftime("%Y-%m-%d %H:%M:%S") condition_sql += ", `{picture_type}_date`=\"" + picture_type_date + "\" " condition_sql = condition_sql.format(picture_type=picture_type) else: picture_type_date = "" if picture_type == "collected": condition_sql += ", `{picture_type}_photos_upload_by`=\"{picture_type_photos_upload_by}\" " + \ ", `{picture_type}_photos_upload_date`=\"{picture_type_photos_upload_date}\" " condition_sql += ", `{picture_type}_photos_from`=\"{file_from}\" " condition_sql = condition_sql.format(picture_type=picture_type, picture_type_photos_upload_by=picture_type_photos_upload_by, picture_type_photos_upload_date=picture_type_photos_upload_date, file_from=file_from) else: condition_sql += ", `{picture_type}_photos_{photos_type}_upload_by`=\"{picture_type_photos_upload_by}\" " + \ ", `{picture_type}_photos_{photos_type}_upload_date`=\"{picture_type_photos_upload_date}\" " condition_sql += ", `{picture_type}_photos_{photos_type}_from`=\"{file_from}\" " condition_sql = condition_sql.format(picture_type=picture_type, photos_type=photos_type, picture_type_photos_upload_by=picture_type_photos_upload_by, picture_type_photos_upload_date=picture_type_photos_upload_date, file_from=file_from) print("picture_type_date", picture_type_date) #query_data_col = col_name_list.index(update_condition) # 要检索的信息所在的列 step = 10000 # 每次更新step个 step_num = math.ceil(len(update_condition_list) / step) try: for i in range(step_num): head = i * step if i == (step_num - 1): tail = len(update_condition_list) else: tail = (i + 1) * step UPDATE_sql_head = "UPDATE " + table_name + " SET `"+ update_photos_type + "`=`"+ update_photos_type + "`+1 " + condition_sql + " WHERE `"+ update_condition + "` in (%s) " % ( ','.join(['%s'] * len(update_condition_list[head:tail]))) sql = UPDATE_sql_head print(sql) cursor.execute(sql, update_condition_list[head:tail]) print('%d/%d done' % (tail, len(update_condition_list))) except: print("更新到数据库失败") return False db.commit() # 关闭数据库连接 db.close() print("更新照片包成功") return True
def update_exported_produce(dbname, table_name, by_who="unknown", dp_id_list=[], global_id_list=[], need_connect=0, custom_addition=""): print('开始更新已经生成生产单的数据', ' 用户: ', by_who) print('表名', table_name) ''' dbname: need_connect=0 为数据库连接后返回的对象 need_connect=1 时需要传dbname table_name: 表名 cols: 列名 dp_id_list: dp_id_list数组 global_id_list: global_id_list数组 注意: dp_id_list和global_id_list数组只能二选一 custom_addition: 自定义附加在后面的条件 ''' # db = pymysql.connect("localhost", "root", "root", "ws_doorplate") # db = pymysql.connect(config.DB_HOST, config.DB_USER, config.DB_PASSWORD, config.DB_NAME) db = db_connect(dbname) # 使用cursor()方法获取操作游标 cursor = db.cursor() sql_head = "UPDATE "+ table_name sql_cols = " SET `exported_produce`=`exported_produce`+1 " + \ ", `exported_produce_by` = " + "\'" + by_who + "\'" + \ ", `exported_produce_date` = " + "\'" + common.format_ymdhms_time_now() + "\'" # sql_where = " WHERE `global_id` IN (\'%s\') " % ('\',\''.join(list(global_id_and_date_map.keys())[head:tail])) where_name = 'dp_id' datas = dp_id_list if len(dp_id_list) > 0: where_name = 'dp_id' datas = dp_id_list elif len(global_id_list) > 0: where_name = 'global_id' datas = global_id_list step = 1000 step_num = math.ceil(len(datas) / step) # done_count = 0 for i in range(step_num): temp_data_list = [] head = i * step if i == (step_num - 1): tail = len(datas) else: tail = (i + 1) * step sql_where = " WHERE `" + where_name + "` IN (\'%s\') " % ('\',\''.join(datas[head:tail])) sql = sql_head+sql_cols+sql_where #print(sql) try: cursor.execute(sql) except: print('Error! At %d/%d ' % (tail, len(datas))) return False print('%d/%d done' % (tail, len(datas))) db.commit() print('All done!') # 关闭数据库连接 db.close() return True
def insert_datas(path, filename='未填写_未填写_未填写', projects_index=-1, db_name='ws_doorplate', db_table_name='gz_orders_test', uploaded_by="没有填写"): #def update_datas(path): print("Starting insert datas") if projects_index==-1: return False, '插入失败, 没有传入projects_index', '0', '0' if not filename: filename = '未填写_未填写_未填写' print("文件名", filename) print("插入的数据库", db_name) print("插入的表", db_table_name) print("项目id", projects_index) db = pymysql.connect("192.168.1.100", "root", "root", db_name) # 使用cursor()方法获取操作游标 cursor = db.cursor() select_col_sql = "SELECT column_name FROM information_schema.columns WHERE table_schema=" + "\"" + db_name + "\"" + " AND table_name=" + "\"" + db_table_name + "\"" #print(select_col_sql) cursor.execute(select_col_sql) results = cursor.fetchall() col_name_list = [] #print(results) for i in results: col_name_list.append(str(i[0])) #print("列名:", col_name_list) db.commit() cursor = db.cursor() select_col_name_sql = " SELECT col_name, col_name_chinese FROM col_name_map " cursor.execute(select_col_name_sql) col_name_tuple = cursor.fetchall() global col_name_map, col_name_map_reverse # 从数据库拉取最新col name 字典表 然后更新到col_name_map,col_name_map_reverse for i,j in col_name_tuple: col_name_map[i] = j col_name_map_reverse[j] = i from_filename = filename try: distirct_id = filename.split("_")[0] except: distirct_id = "未能从文件名提取" try: order_id = filename.split("_")[1] except: order_id = "未能从文件名提取" # 读取数据 datas, title_col_map = load_Excel(path) print ("总共要进行插入的数据:", len(datas)) uploaded_date = common.format_ymdhms_time_now() if "projects_index" in title_col_map.keys(): other_datas = [from_filename, distirct_id, order_id, uploaded_by, uploaded_date] # other_col = ["from_filename", "distirct_id", "order_id", "uploaded_by", # "uploaded_date", "dp_num_trans", "global_id_with_dp_name"] other_col = ["from_filename", "distirct_id", "order_id", "uploaded_by", "uploaded_date"] else: other_datas = [projects_index, from_filename, distirct_id, order_id, uploaded_by, uploaded_date] # other_col = ["projects_index", "from_filename", "distirct_id", "order_id", "uploaded_by", # "uploaded_date", "dp_num_trans", "global_id_with_dp_name"] other_col = ["projects_index", "from_filename", "distirct_id", "order_id", "uploaded_by", "uploaded_date"] if db_table_name == "gz_orders": # 为广州单独加的 if "producer" not in title_col_map.keys(): other_col.append("producer") other_datas.append("广州市伟圣实业有限公司") if "produce_date" not in title_col_map.keys(): other_col.append("produce_date") other_datas.append((datetime.datetime.now() + datetime.timedelta(days=10)).strftime('%Y-%m-%d')) # 在当前时间上+10天 if "installer" not in title_col_map.keys(): other_col.append("installer") other_datas.append("广州市伟圣实业有限公司") if "factory_index" not in title_col_map.keys(): other_col.append("factory_index") # other_datas.append("广州市伟圣实业有限公司") select_order_batch_sql = "SELECT order_batch FROM projects WHERE `index`={projects_index}" select_order_batch_sql = select_order_batch_sql.format(projects_index=projects_index) cursor.execute(select_order_batch_sql) order_batch = cursor.fetchall() if not order_batch: order_batch = "未知" else: order_batch = order_batch[0][0] other_datas.append(order_batch) if "factory_batch" not in title_col_map.keys(): other_col.append("factory_batch") #other_datas.append("广州市伟圣实业有限公司") select_max_factory_batch_sql = "SELECT max(cast(SUBSTRING_INDEX(t1.s1,\"-\",-1) as SIGNED INTEGER)) from (SELECT DISTINCT(factory_batch) as s1 FROM gz_orders WHERE factory_batch LIKE \"%{doorplates_type}%\") t1;" if not order_batch: doorplates_type = "NONE" else: if order_batch[0] == "L": doorplates_type = "LSMP" else: doorplates_type = "ZSMP" select_max_factory_batch_sql = select_max_factory_batch_sql.format(doorplates_type=doorplates_type) cursor.execute(select_max_factory_batch_sql) factory_batch_num_max = cursor.fetchall() if not factory_batch_num_max: factory_batch_num_max = str(1) else: factory_batch_num_max = int(factory_batch_num_max[0][0]) factory_batch_num_max = str(factory_batch_num_max + 1) other_datas.append("WS-GZ-" + doorplates_type + "-" + factory_batch_num_max) if "dp_num_trans" not in title_col_map.keys(): other_col.append("dp_num_trans") if "global_id_with_dp_name" not in title_col_map.keys(): other_col.append("global_id_with_dp_name") # other_datas = [projects_index, from_filename, distirct_id, order_id, uploaded_by, uploaded_date] other_datas = [str(i) for i in other_datas] # other_col = ["projects_index", "from_filename", "distirct_id", "order_id", "uploaded_by", "uploaded_date", "dp_num_trans", "global_id_with_dp_name", ] #datas = ["(\""+ "\",\"".join(i) +"\")" for i in datas] step = 1000 # 每次更新step个 step_num = math.ceil(len(datas) / step) try: for i in range(step_num): head = i * step if i == (step_num - 1): tail = len(datas) else: tail = (i + 1) * step if "global_id_with_dp_name" not in title_col_map.keys(): temp_datas = ["(\""+ "\",\"".join(i + other_datas + [chinese_to_arabic_with_connection(i[title_col_map["dp_num"]])] + [i[title_col_map["global_id"]]+i[title_col_map["dp_name"]]]) +"\")" for i in datas[head:tail]] else: if "dp_num_trans" not in title_col_map.keys(): temp_datas = ["(\"" + "\",\"".join( i + other_datas + [chinese_to_arabic_with_connection(i[title_col_map["dp_num"]])]) + "\")" for i in datas[head:tail]] else: temp_datas = ["(\"" + "\",\"".join( i + other_datas) + "\")" for i in datas[head:tail]] #print(temp_datas) sql = "INSERT INTO "+ db_table_name + "(" + ",".join(list(title_col_map.keys())) + "," + ",".join(other_col) +")" + " VALUES " + ",".join(temp_datas) #print (sql) #UPDATE_sql_head = "UPDATE fs_dp SET produced=produced+1 WHERE global_id in (%s) " % (','.join(['%s'] * len(global_id_list[head:tail]))) #sql = UPDATE_sql_head #cursor.execute(sql, global_id_list[head:tail]) cursor.execute(sql) print ('%d/%d done' % (tail, len(datas))) except: # 关闭数据库连接 db.close() print("INSERT INTO "+ db_table_name + "(" + ",".join(list(title_col_map.keys())) + "," + ",".join(other_col) +")" + " VALUES " + temp_datas[0]) return False, '插入失败', '0', '0' # for i in range(step_num): # head = i * step # if i == (step_num - 1): # tail = len(datas) # else: # tail = (i + 1) * step # # temp_datas = ["(\""+ "\",\"".join(i + other_datas + [chinese_to_arabic_with_connection(i[title_col_map["dp_num"]])] + [i[title_col_map["global_id"]]+i[title_col_map["dp_name"]]]) +"\")" for i in datas[head:tail]] # #print(temp_datas) # sql = "INSERT INTO "+ db_table_name + "(" + ",".join(list(title_col_map.keys())) + "," + ",".join(other_col) +")" + "VALUES" + ",".join(temp_datas) # #print (sql) # #UPDATE_sql_head = "UPDATE fs_dp SET produced=produced+1 WHERE global_id in (%s) " % (','.join(['%s'] * len(global_id_list[head:tail]))) # #sql = UPDATE_sql_head # #cursor.execute(sql, global_id_list[head:tail]) # cursor.execute(sql) # print ('%d/%d done' % (tail, len(datas))) # if sql: # try: # # 执行sql语句 # #print(sql) # cursor.execute(sql, query_data) # #print (query_data) # print ("excel的数据有:%d个, 检索出来的有:%d个, 其中是空的有: %d个," % (len(query_data), len(results), empty)) # print("这些数据不在数据库", empty_list) # results = cursor.fetchall() # # 提交到数据库执行 # db.commit() # except pymysql.ProgrammingError as error: # code, message = error.args # print(">>>>>>>>>>>>>", code, message) # logging.error('SQL执行失败,执行语句为:%s'%str(sql)) # traceback.print_exc() # # 如果发生错误则回滚 # db.rollback() # #db.undo() # pass db.commit() # 获取更新时间 select_date_sql = "SELECT MAX(last_update_date) FROM " + db_table_name + " WHERE global_id=" + "\'" + datas[0][title_col_map["global_id"]] + "\'" #print(select_date_sql) #print(select_col_sql) cursor.execute(select_date_sql) update_date = cursor.fetchall()[0][0] # 关闭数据库连接 db.close() return True, '更新成功', update_date.strftime('%Y-%m-%d %H:%M:%S'), str(len(datas))
def insert_data(filename='未填写_未填写_未填写', projects_index=-1, db_name='ws_doorplate', db_table_name='gz_orders_test', uploaded_by="没有填写", data_map={}): # def update_datas(path): print("Starting insert data") # if not contract_batch: # contract_batch = "没有填写" # if not order_batch: # order_batch = "没有填写" if not filename: filename = '未填写_未填写_未填写' if projects_index == -1: return False, '插入失败, 没有传入projects_index', '0', '0' print("文件名", filename) print("插入的数据库", db_name) print("插入的表", db_table_name) print("项目id", projects_index) # print("合同批号", contract_batch) # print("订单批号", order_batch) db = pymysql.connect("192.168.1.100", "root", "root", db_name) # 使用cursor()方法获取操作游标 cursor = db.cursor() select_col_sql = "SELECT column_name FROM information_schema.columns WHERE table_schema=" + "\"" + db_name + "\"" + " AND table_name=" + "\"" + db_table_name + "\"" # print(select_col_sql) cursor.execute(select_col_sql) results = cursor.fetchall() col_name_list = [] # print(results) for i in results: col_name_list.append(str(i[0])) # print("列名:", col_name_list) db.commit() cursor = db.cursor() select_col_name_sql = " SELECT col_name, col_name_chinese FROM col_name_map " cursor.execute(select_col_name_sql) col_name_tuple = cursor.fetchall() global col_name_map, col_name_map_reverse # 从数据库拉取最新col name 字典表 然后更新到col_name_map,col_name_map_reverse for i, j in col_name_tuple: col_name_map[i] = j col_name_map_reverse[j] = i from_filename = filename distirct_id = filename.split("_")[0] order_id = filename.split("_")[1] #如果有门牌号,则生成门牌号纯数字 if data_map.get("dp_num") and not data_map.get("dp_num_trans"): data_map["dp_num_trans"] = dp_sort.chinese_to_arabic_with_connection(data_map.get("dp_num")) data_map = collections.OrderedDict(data_map) #print("要进行插入的数据:", data_map) print("要进行插入的数据数量:", len(data_map)) uploaded_date = common.format_ymdhms_time_now() # other_datas = [contract_batch, order_batch, from_filename, distirct_id, order_id, uploaded_by, uploaded_date] # # other_col = ["contract_batch", "order_batch", "from_filename", "distirct_id", "order_id", "uploaded_by", # "uploaded_date"] if "projects_index" in data_map.keys(): other_datas = [from_filename, distirct_id, order_id, uploaded_by, uploaded_date] other_col = ["from_filename", "distirct_id", "order_id", "uploaded_by", "uploaded_date"] else: other_datas = [projects_index, from_filename, distirct_id, order_id, uploaded_by, uploaded_date] other_col = ["projects_index", "from_filename", "distirct_id", "order_id", "uploaded_by", "uploaded_date"] sql = "INSERT INTO " + db_table_name + "(" + ",".join(list(data_map.keys())) + "," + ",".join( other_col) + ")" + "VALUES" + "(\"" + "\",\"".join( [str(i) for i in list(data_map.values())] + [str(i) for i in other_datas]) + "\")" cursor.execute(sql) ''' try: sql = "INSERT INTO " + db_table_name + "(" + ",".join(list(data_map.keys())) + "," + ",".join( other_col) + ")" + "VALUES" + "(\"" + "\",\"".join([str(i) for i in list(data_map.values())]+other_datas) + "\")" cursor.execute(sql) except: # 关闭数据库连接 db.close() return False, '更新失败', '0', '0' ''' db.commit() # 关闭数据库连接 db.close() return True, '更新成功', common.format_ymdhms_time_now(), "1"