python 通过winform模块操作access数据库

这两天有个access数据库某个字段写进了几百条乱码, 需要修复一下, 想着用C++太费事, 就用python整了一个简单的脚本, 下面是代码, 如有疏漏, 欢迎指正

首先命令行 pip install pypiwin32 装一下模块

 1 import win32com.client
 2 
 3 conn = win32com.client.Dispatch(r'ADODB.Connection')
 4 DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=D:\\Dev\\Access.mdb;'
 5 conn.Open(DSN) 
 6 
 7 #打开记录集
 8 rs = win32com.client.Dispatch(r'ADODB.Recordset')
 9 table_name = 'Table'
10 
11 rs.Open('Select * FROM [Table]', conn)
12 
13 rs.MoveFirst()
14 
15 sql_list = []
16 
17 #遍历记录
18 while True:
19     test_id = ""
20     serial_number = ""
21     serial_number_after_fix = ""
22 
23     if rs.EOF:
24         break
25     else:
26         #检查每一条记录
27         for each_field in range(rs.Fields.Count):
28             #如果是ID
29             if (rs.Fields[each_field].Name == 'ID'):
30                 test_id = rs.Fields[each_field].Value
31             #如果是序号
32             elif (rs.Fields[each_field].Name == 'serial_number'):
33                 serial_number = rs.Fields[each_field].Value
34 
35         #检查每一个字符
36         for each_char in serial_number:
37             ascii_char = ord(each_char)
38             # 0~9
39             if (ascii_char >= 48 and ascii_char <= 57):
40                 serial_number_after_fix += chr(ascii_char)
41             # a~z
42             elif (ascii_char >= 65 and ascii_char <= 90):
43                 serial_number_after_fix += chr(ascii_char)
44             # A~Z
45             elif (ascii_char >= 97 and ascii_char <= 122):
46                 serial_number_after_fix += chr(ascii_char)
47             else:
48                 continue
49         
50         if (serial_number == serial_number_after_fix):
51             rs.MoveNext()
52             continue
53 
54         #生成更新语句
55         sql_statement = "Update Table Set serial_number = '"
56         sql_statement += serial_number_after_fix
57         sql_statement += "' Where ID = '"
58         sql_statement += str(test_id)
59         sql_statement += "'"
60 
61         sql_list.append(sql_statement)
62         rs.MoveNext()
63 
64 #更新数据
65 for each_sql in sql_list:
66     conn.Execute(each_sql)
67 
68 conn.Close()

 写着是挺简单快捷的, 跑45w条记录跑了将近20分钟......虽然有sql语句和代码细节的关系......

posted @ 2020-05-08 09:53  public_tsing  阅读(470)  评论(0)  编辑  收藏  举报