Updating a table in MySQL with content of files

I’ve got an interesting case where we all might benefit from. My code is collecting 3 things from multiple files in a folder (file a,b and c). It is collecting the filenames, the difference between ctime and mtime and lastly it is doing something with the size of the file. All these 3 are inserted in 3 variables and placed in a list.

I’m trying to read this list in a manually (4 collumn) created table in MySQL. The first column is manually edited by me, and for the other 3 columns I need to insert the 3 variables I mentioned above.

The first picture shows the table before running the script. The second picture shows the table after running the script. As you can see, in the second picture it pastes just the variables of file C in column B, C and D. I want the variables of file A in row 1, file B in row 2 and file C in row 3. first picture second picture

This is my code:

import mysql.connector import csv import os import time from datetime import datetime   #Make a connection with the workbench mydb = mysql.connector.connect(         host= 'localhost',         user = 'root',         passwd = '*****',         database= 'db_one'     )  cursor = mydb.cursor()  select_post = "SELECT * FROM `my_table`"  cursor.execute(select_post)  source = r'c:\data\JS\Desktop\Source'  names_mysql= [i[0] for i in cursor.fetchall()] names_folder= os.listdir(source)       def my_table1(): #look for corresponding names in the names_folder.     for x in names_folder:         if x in names_mysql:             subdir_path = os.path.join(source, x)              for file in os.listdir(subdir_path):               a = os.path.join(subdir_path, file)                             #Place all absolute paths in a list.                     all_paths= [os.path.join(subdir_path, file) for file in os.listdir(subdir_path)]             if all_paths!= []:                 newest_paths= max(all_paths, key=os.path.getctime)                 print(newest_paths)                                            #Get the filenamnes out of the paths and make VARIABLE 1.                             var_1 = ntpath.basename(newest_paths)  #            print(last_hb)  #Calculate the current time - modification time and make VARIABLE 2             cr_time = datetime.now()             m_time = mode_time.strftime("%M")                    cr_time = os.path.getctime(newest_paths)             rmode_time = time.strftime("%M", time.localtime(cr_time )) #           print(rmode_time)                    var_2 = int(cr_time ) - int(rmode_time)  #            print(var_2)              #Make a list and place a VARIABLE 1 and VARIABLE 2 in that list.                                     line_data = list()             line_data.insert(0, var_1)             line_data.insert(1, var_2)                               #Get the size of the files in  and add it as VARIABLE 3 to the existing list.                              statinfo = os.stat(newest_paths)                                      if statinfo.st_size > 1000:                 line_data.insert(2, 'Checked')             else:                 line_data.insert(2, '*')  #Place the 3 variables in 1 List                       y = list()             y.append(line_data)                          cursor = mydb.cursor()     #Run the query                        query3 = ("UPDATE my_table SET B= %s, C = %s, D=%s")                          for line in y:                 cursor.execute(query3, (line))                                                               mydb.commit()     mydb.close()    my_table1()      
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.