彰化一整天的論壇

 找回密碼
 立即註冊
查看: 87|回復: 0

python去除重複

[複製鏈接]
發表於 2021-3-18 08:49:16 | 顯示全部樓層 |閱讀模式
  1. import pymssql
  2. import datetime
  3. import decimal

  4. connMing = pymssql.connect(server='10.0.0.214\mssql01',user="bestdaylong", password="bestdaylong", database="ming")
  5. connStddb = pymssql.connect(server='10.0.0.214\mssql01',user="bestdaylong", password="bestdaylong", database="stddb")


  6. cursorMing =connMing.cursor(as_dict=False)
  7. cursorStddb=connStddb.cursor()

  8. strSQL="select year,smtr from ref_yearsmtr"

  9. cursorMing.execute(strSQL)

  10. rowMing=cursorMing.fetchone()

  11. if rowMing:
  12.         academic_year=rowMing[0]
  13.         semester=rowMing[1]

  14. #start_date=end_date=datetime.datetime.now()
  15. now=datetime.datetime.now()
  16. yy=now.strftime("%Y")
  17. mm=now.strftime("%m")
  18. dd=now.strftime("%d")

  19. start_date="{0}/{1}/{2}".format(yy,mm,dd)
  20. end_date="{0}/{1}/{2}".format(int(yy)+10,mm,dd)

  21. try:
  22.         strSQL="select unit,course_name,teach_no from [dbo].[tronclass_course_apply] where send is null or send=0"
  23.         cursorStddb.execute(strSQL)
  24.         rowStddb1=cursorStddb.fetchall()
  25.         for row in rowStddb1:
  26.                 unit=row[0]
  27.                 course_name=row[1]
  28.                 teach_no=row[2]
  29.                 department_code=unit[-5::] #取出單位代碼       
  30.                 emp_nos=teach_no.split(", ")
  31.                
  32.                 #找單位已經開課次數
  33.                 strSQL="select count(*) as course_count from [dbo].[u1_tronclass_course] where department_code='{0}'".format(department_code)
  34.                 print(strSQL)
  35.                 cursorStddb.execute(strSQL)
  36.                 rowStddb=cursorStddb.fetchone()
  37.                 subject_code=format("%02d" % (rowStddb[0]+1))
  38.                 course_code=academic_year+semester+department_code+subject_code
  39.                 strSQL="insert into [u1_tronclass_course](course_code,subject_code,academic_year,semester,department_code,grade_name,class_name,stopped,name,compulsory,credit,start_date,end_date) values('{0}','{1}',{2},{3},'{4}',{5},{6},'{7}','{8}','{9}','{10}','{11}','{12}')".format(course_code,subject_code,academic_year,semester,department_code,'null','null',0,course_name,1,0,start_date,end_date)
  40.                 print(strSQL)
  41.                 cursorStddb.execute(strSQL)
  42.                 connStddb.commit()
  43.                
  44.                 #去除重復人事編號               
  45.                 emp_no_onlys=[]
  46.                 for emp_no in emp_nos:
  47.                         emp_no=emp_no.split("-")[0]               
  48.                         emp_no_onlys.append(emp_no)
  49.                 emp_no_onlys=set(emp_no_onlys)                       
  50.                

  51.                 for emp_no in emp_no_onlys:
  52.                         emp_no=emp_no.split("-")[0]
  53.                         strSQL="insert into [u1_tronclass_enrollment](course_code,user_no,stopped,enrollment_role,enrollment_department_code,enrollment_grade_name,enrollment_class_name,seat_number) values('{0}','{1}',{2},'{3}',{4},{5},{6},{7})".format(course_code,emp_no,0,1,'null','null','null','null')
  54.                         print(strSQL)
  55.                         cursorStddb.execute(strSQL)
  56.                         connStddb.commit()
  57.                 #新增老師       
  58.                 strSQL="insert into [u1_tronclass_enrollment](course_code,user_no,stopped,enrollment_role,enrollment_department_code,enrollment_grade_name,enrollment_class_name,seat_number) values('{0}','{1}',{2},'{3}',{4},{5},{6},{7})".format(course_code,department_code,0,2,'null','null','null','null')
  59.                 print(strSQL)
  60.                 cursorStddb.execute(strSQL)
  61.                 connStddb.commit()
  62.                 #更新
  63.                 strSQL="update [dbo].[tronclass_course_apply] set send='1'  where unit='{0}' and course_name='{1}'".format(unit,course_name)
  64.                 print(strSQL)
  65.                 cursorStddb.execute(strSQL)
  66.                 connStddb.commit()
  67. except:
  68.         connStddb.rollback()
  69.                


  70. cursorMing.close()
  71. cursorStddb.close()
  72. connMing.close()
  73. connStddb.close()
複製代碼


回復

使用道具 舉報

您需要登錄後才可以回帖 登錄 | 立即註冊

本版積分規則

 ㄚ母滴雞湯
 員林香純滴雞精

Archiver|手機版|小黑屋|彰化一整天的論壇(Excel,Office)

GMT+8, 2021-4-11 03:12 , Processed in 0.106711 second(s), 16 queries .

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回復 返回頂部 返回列表