优化程序

unicornfxf

      最近拿到了一个文件管理软件，需要把里面的文件结构都搞出来。忙活了一周多，总算能实现了，但是在运行过程中发现效率极低，速度很慢，处理一条数据需要3、4秒的时间，3个半小时才处理了三千多。我感觉好像是在递归过程中每个递归都开启一次数据库链接并进行查询导致的速度太慢，但是不太敢肯定，毕竟这是大学毕业10多年后第一次编程，仅仅能实现功能就让我谢天谢地了，所以希望各位帮忙看看有哪些地方不太合理，需要进行优化的。拜谢～～～

        需求：从mysql中获取文件所在的文件夹路径，形成完整的文件路径写入数据库记录。

        条件：1、数据库中目标表为doc表，其中的列为id（文件ID）、filename（文件名称）、folderid（文件所在的第一级目录）、full_folder（文件全路径）,其中full_folder列为目标列，每个文件对应的绝对路径需要写入此列。
                 2、数据库表中条件表为folder表，其中列为id（文件夹id）、name（文件夹名称）、parent_id（父文件夹id）
doc表

id	filename	folderid	full_folder
1	语文.txt	23
2	数学.pdf	17
3	英语.xls	8

folder表

id	name	parent_id
3	根目录	null
6	课程	3
8	语文	6
17	数学	6
23	英语	6

经过程序处理后的doc表应该为

id	filename	folderid	full_folder
1	语文.txt	23	根目录\课程\语文
2	数学.pdf	17	根目录\课程\数学
3	英语.xls	8	根目录\课程\英语

源代码如下

1. import pymysql  # 导入pymysql访问数据库模块
   
2. import contextlib  # 导入上下文管理模块，用于简化数据库连接
   
3. import os
   
4. # coding=utf-8
   
5. class DataFetch:
   
6.               # 我自己想到过将数据从数据库中拿出来放到列表中进行处理，但是doc表有1.2W记录，感觉会占用很大的内存，所以就直接在数据库进行了操作。不知道这样做是否合适？
   
7.     @contextlib.contextmanager  # 调用上下文管理以方便访问数据库
   
8.     def mysql(self):                           
   
9.         dbconf = {      # 定义数据库连接参数
   
10.             "host": "localhost",
    
11.             "port": 3307,
    
12.             "user": "root",
    
13.             "password": "*****",
    
14.             "database": "db",
    
15.             "charset": "utf8"
    
16.         }
    
17.         db = pymysql.connect(**dbconf)
    
18.         cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
    
19.         try:
    
20.             yield cursor
    
21.         finally:
    
22.             db.commit()
    
23.             cursor.close()
    
24.             db.close()
    
25. 
    
26.     def pfolder(self, fn, fid, _judge):  # 获取父文件夹名字,传入参数分别为文件名（递归头）或文件夹名（递归体），文件夹id，递归头判断参数
    
27.         with self.mysql() as curs:
    
28.             folder_sql = "select name,parent_id from folder where id = '%s'"
    
29.             curs.execute(folder_sql % fid)  # 获取sql总数
    
30.             data_folder = curs.fetchall()  # 将所有数据写入data_folder列表     这里让我感到奇怪，通过fetchall获取到的内容我原以为应该是个list，但是调试中发现是个dict，不知道原因是什么？
    
31.             if not _judge:  # 如果第一次调用此方法
    
32.                 restring = ''  # 则传入的fn不写入结果，及文件名不写入路径
    
33.             else:  # 如果进行了递归
    
34.                 restring = fn  # 则各层文件夹名写入文件全地址
    
35.             if data_folder[0]["parent_id"] and 1:  # 判断父文件夹id是否为空
    
36.                 ff = DataFetch() # 创建递归对象
    
37.                 fd = ff.pfolder(data_folder[0]["name"], data_folder[0]["parent_id"],1)  # 递归调用pfolder方法
    
38.                 restring = fd[0] + os.sep + restring  # 递归结果写入文件全地址
    
39.             else:  # 如果父文件夹id为空
    
40.                 restring = data_folder[0]["name"] + os.sep + restring  # 则自身文件夹写入文件全地址
    
41.             return restring, fid
    
42. 
    
43.     def docw(self, fullname, did):
    
44.         with self.mysql() as curs:
    
45.             _sql = "update doc set full_folder=%s where id=%s"
    
46.             _param = (fullname, did)
    
47.             curs.execute(_sql, _param)
    
48.         return 0
    
49. 
    
50. 
    
51. if __name__ == '__main__':
    
52.     df = DataFetch()
    
53.     with df.mysql() as curs:
    
54.         docsql = "select id, filename, folderid from doc"  # 读取文件表中文件的名字和文件夹ID
    
55.         num_doc = curs.execute(docsql)  # 获取sql总数
    
56.         data = [[] for i in range(num_doc)]  # 定义二元列表data  
    
57.         counter = 0  # 定义计数器
    
58.         while (counter < num_doc):
    
59.             data = curs.fetchone()  # 将所有数据写入data二元列表    此处让我一直很疑惑，fetchone取出的数据就是一个list，fetchall取出的数据却是一个dict？是这两种方法的输出规定的吗？如果是我就去看看代码
    
60.             counter += 1  # 计数器加1
    
61.             folders = df.pfolder(data["filename"],data["folderid"],0)
    
62.             print(folders[0], data["id"])
    
63.             df.docw(folders[0],data["id"])
    
64. 
    
65. 
    
66. 
    
67. 
    
68. 
    

复制代码

unicornfxf

木有人来么，是我发错地方了吗？

fan1993423

帮顶一下，论坛里面有大佬，看他们帮你解决吧

wongyusing

简单来说，
你有一个很大很大的文件夹，然后你想把这个文件夹的所有文件按照路径传入mysql对吧？？

wongyusing

说一下，我的看法吧。  
你的描述，很烂。  
我看了20分钟才读懂问题。  
感觉上，可以用两三条sql语句解决问题。  
我先试试

wwhywhy

你调用一次mysql() 就会连接一次数据库（db = pymysql.connect(**dbconf)），关闭一次数据库（db.close()）。这个是最花时间的。
建议只连接一次。剩下的就是直接使用连接句柄就可以了。
程序结束的时候，再关闭数据库。这样执行速度就正常了。

wongyusing

果然，三句sql语句搞定

1. CREATE TABLE doc(id int,filename VARCHAR(20),folderid int,full_folder VARCHAR(500),PRIMARY KEY(id));
   
2. 
   
3. ALTER TABLE doc ADD test_var VARCHAR(100);
   
4. 
   
5. INSERT INTO doc(id, filename,folderid) VALUES(1,'语文.txt', 23);
   
6. 
   
7. INSERT INTO doc(id, filename,folderid) VALUES(2,'数学.pdf', 17);
   
8. 
   
9. INSERT INTO doc(id, filename,folderid) VALUES(3,'英语.xls', 8);
   
10. 
    
11. 
    
12. CREATE TABLE folder(id int,name VARCHAR(40),parent_id int,PRIMARY KEY(id));
    
13. 
    
14. INSERT INTO folder(id, name,parent_id) VALUES(3,'根目录', null);
    
15. 
    
16. INSERT INTO folder(id, name,parent_id) VALUES(23,'语文', 6);
    
17. 
    
18. INSERT INTO folder(id, name,parent_id) VALUES(17,'数学', 6);
    
19. 
    
20. INSERT INTO folder(id, name,parent_id) VALUES(8,'英语', 6);
    
21. 
    
22. INSERT INTO folder(id, name,parent_id) VALUES(6,'课程', 3);
    
23. 
    
24. 
    
25. 
    
26. 
    
27. 
    
28. 
    
29. 
    
30. 
    
31. UPDATE doc SET
    
32. full_folder = CONCAT(folder.name, '\',doc.filename),test_var = folder.parent_id
    
33. FROM folder WHERE doc.folderid = folder.id;
    
34. 
    
35. UPDATE doc SET
    
36. full_folder = CONCAT(folder.name, '\',doc.full_folder),test_var = folder.parent_id
    
37. FROM folder WHERE to_number(doc.test_var,'999999999999999999') = folder.id;
    
38. 
    
39. 
    
40. UPDATE doc SET
    
41. full_folder = CONCAT(folder.name, '\',doc.full_folder),test_var = folder.parent_id
    
42. FROM folder WHERE to_number(doc.test_var,'999999999999999999') = folder.id;

复制代码

注意要把to_number哪里换成mysql的字符转整数函数

我电脑没有mysql，我用的是postgresql。  

刚学的sql语句，只能分三步进行，而且还要新建一个栏位。  

如果有大神的话，可能一句就搞定了。  

只能说我平时用关系型数据库比较少吧。

unicornfxf

wongyusing 发表于 2018-12-2 15:39
果然，三句sql语句搞定

抱歉我的描述确实是按照我自己已经明白问题的角度来进行的，实际上的情况应该是这样：

      我现在拿到的是一个文件管理系统，在这个系统中可以构建一个目录结构，并将上传的文件重命名后保存起来。

      目录结构和文件的对应关系是通过那两个表来实现的，doc表记录了文件的名字和所属的文件夹，以及在文件管理系统中的名字（即ID）。而folder表记录了文件夹及其上层文件夹的关系。

      我现在需要通过这两个表格来将目录结构重新建立在操作系统中，并将文件放入相应的文件夹。我的代码目前是想将目录结构按照绝对路径的方式先还原出来，放在数据库中，后续再添加代码在操作系统中建立整个目录树并重命名和移动文件到相应的文件夹。

十分抱歉之前的描述不清楚，浪费了你的时间在错误的需求上

unicornfxf

wwhywhy 发表于 2018-12-2 13:59
你调用一次mysql() 就会连接一次数据库（db = pymysql.connect(**dbconf)），关闭一次数据库（db.close()） ...

      果然瓶颈是在这里，我之前就觉得递归重复打开数据库是个问题，但是第一次接触到with的方法，感觉很有意思就用进去了。

      只连接一次应该不太容易，因为在获取文件所属文件夹的时候，如果不是根目录就需要进行递归来获取完整的绝对路径，fetchone的游标来进行这样的递归感觉好像好麻烦的样子。

      现在数据量不是很大，一万多条数据，是否可以将数据库的记录都写入列表，在内存中进行数据处理呢？这样是否效率更高些？