昨天写一个excel信息读取然后录入数据库的需求,由于excel的栏位数量和栏位值都与数据库model不一样也不固定,一时犯了难
从前做数据更新时一直用 字段名=字段值 的写法User.objects.filter(id=1).update(字段名=字段值, …)
# 其实还可以这样
data = {'username':'nick','is_active':'0'}
User.objects.filter(id=1).update(**data)
故而茅塞顿开:
user_update = {}
for update_filed in updateFields:
if update_filed != 'user_no' and update_filed != 'promotion_period':
update_infos = xlsx_target[update_filed].tolist()
user_update[update_filed] = update_infos[user_nos.index(user_no)]# 我这里是前面循环了user_no,所以用user_no的序列号取相应的值
Tech_User_Info.objects.filter(user_no=user_no, promotion_period=promotionPeriod) \
.update(**user_update)
(update)
有人提醒我这写法意味着这张excel有几万个人 就要访问几万次数据库 做几万次更新处理,所以我换了个写法
update_sql = ''
for filed in Fields:
if filed != 'user_no' and filed != 'promotion_period':
update_infos = xlsx_target[filed].tolist()
update_infos = [str(x) for x in update_infos]
user_update = ''
for user in users:
mini_sql = "when '"+user_no+"' then '"+update_infos[user_nos.index(user_no)]+"' "
user_update = user_update+mini_sql # 第一处循环when…then…拼接sql
sql = ""+filed+" = case user_no "+user_update+"END, "
update_sql = update_sql+sql # 第二处循环field拼接sql
list_str = list(update_sql)
list_str.pop(-2) # 最后一个END后面的逗号要去掉(在倒数第二个位置,倒数第一个位置上是空格)
update_SQL = ''.join(list_str)
querymysql(user_nos, promotionPeriod, update_SQL)
def querymysql(user_nos, promotionPeriod, update_SQL):
user_nos = "','".join(user_nos)
user_nos = "'" + user_nos + "'"
# print(user_nos)
promotionPeriod = promotionPeriod.strftime('%Y-%m-%d') # 所有变量转为str
connect = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='1234', database='test_t', charset='utf8', autocommit=True)
cursor = connect.cursor()
sql = "UPDATE promotion_tech_user_info SET "+update_SQL+" " \
"where promotion_period = '" + promotionPeriod + "' and user_no in (" + user_nos + ") "
# 符合where条件但没有被when到的值 一律赋为null(如果该field不被允许赋null就会报错)
cursor.execute(sql)
cursor.close()
connect.close()
ps基础语法:
UPDATE table_name SET
field_name1= case user_no WHEN…… THEN … WHEN …… THEN…END,
field_name2= case user_no WHEN…… THEN … ,
……
where …… and ……