python去除重複

imingho

1. import pymssql
   
2. import datetime
   
3. import decimal
   
4. 
   
5. connMing = pymssql.connect(server='10.0.0.214\mssql01',user="bestdaylong", password="bestdaylong", database="ming")
   
6. connStddb = pymssql.connect(server='10.0.0.214\mssql01',user="bestdaylong", password="bestdaylong", database="stddb")
   
7. 
   
8. 
   
9. cursorMing =connMing.cursor(as_dict=False)
   
10. cursorStddb=connStddb.cursor()
    
11. 
    
12. strSQL="select year,smtr from ref_yearsmtr"
    
13. 
    
14. cursorMing.execute(strSQL)
    
15. 
    
16. rowMing=cursorMing.fetchone()
    
17. 
    
18. if rowMing:
    
19.         academic_year=rowMing[0]
    
20.         semester=rowMing[1]
    
21. 
    
22. #start_date=end_date=datetime.datetime.now()
    
23. now=datetime.datetime.now()
    
24. yy=now.strftime("%Y")
    
25. mm=now.strftime("%m")
    
26. dd=now.strftime("%d")
    
27. 
    
28. start_date="{0}/{1}/{2}".format(yy,mm,dd)
    
29. end_date="{0}/{1}/{2}".format(int(yy)+10,mm,dd)
    
30. 
    
31. try:
    
32.         strSQL="select unit,course_name,teach_no from [dbo].[tronclass_course_apply] where send is null or send=0"
    
33.         cursorStddb.execute(strSQL)
    
34.         rowStddb1=cursorStddb.fetchall()
    
35.         for row in rowStddb1:
    
36.                 unit=row[0]
    
37.                 course_name=row[1]
    
38.                 teach_no=row[2]
    
39.                 department_code=unit[-5::] #取出單位代碼       
    
40.                 emp_nos=teach_no.split(", ")
    
41.                
    
42.                 #找單位已經開課次數
    
43.                 strSQL="select count(*) as course_count from [dbo].[u1_tronclass_course] where department_code='{0}'".format(department_code)
    
44.                 print(strSQL)
    
45.                 cursorStddb.execute(strSQL)
    
46.                 rowStddb=cursorStddb.fetchone()
    
47.                 subject_code=format("%02d" % (rowStddb[0]+1))
    
48.                 course_code=academic_year+semester+department_code+subject_code
    
49.                 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)
    
50.                 print(strSQL)
    
51.                 cursorStddb.execute(strSQL)
    
52.                 connStddb.commit()
    
53.                
    
54.                 #去除重復人事編號               
    
55.                 emp_no_onlys=[]
    
56.                 for emp_no in emp_nos:
    
57.                         emp_no=emp_no.split("-")[0]               
    
58.                         emp_no_onlys.append(emp_no)
    
59.                 emp_no_onlys=set(emp_no_onlys)                       
    
60.                
    
61. 
    
62.                 for emp_no in emp_no_onlys:
    
63.                         emp_no=emp_no.split("-")[0]
    
64.                         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')
    
65.                         print(strSQL)
    
66.                         cursorStddb.execute(strSQL)
    
67.                         connStddb.commit()
    
68.                 #新增老師       
    
69.                 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')
    
70.                 print(strSQL)
    
71.                 cursorStddb.execute(strSQL)
    
72.                 connStddb.commit()
    
73.                 #更新
    
74.                 strSQL="update [dbo].[tronclass_course_apply] set send='1'  where unit='{0}' and course_name='{1}'".format(unit,course_name)
    
75.                 print(strSQL)
    
76.                 cursorStddb.execute(strSQL)
    
77.                 connStddb.commit()
    
78. except:
    
79.         connStddb.rollback()
    
80.                
    
81. 
    
82. 
    
83. cursorMing.close()
    
84. cursorStddb.close()
    
85. connMing.close()
    
86. connStddb.close()
    

複製代碼