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"