title: Making tables in MySQL with python who are monitoring your filemanagement system
body: <p>I have a very interesting case. I have a built a filemanagement system in python which moves files from source to destination or archive everytime I run it. Now I want to make 2 tables in MySQL (using python) who are actually monitoring the filemanagement system. </p>
  
  <p>The first table monitors the last time the filemanagementsystem ran. So just a small table with 1 column and 1 row which contains the following information --> Last run: 1-1-2020 10:30.</p>
  
  <p>The second table has to give me all the content of the last file or files which were/was moved from source to destination in table form. </p>
  
  <p><a href="https://i.stack.imgur.com/mfycK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mfycK.png" alt="So the table in MySQL has to look like this:"></a></p>
  
  <p>Everytime I run my python script 2 things need to happen. 1. The files are being moved and 2. the MySQL monitoring tables are being updated. Does anyone knows how this needs to be done? Please note I'am using a MySQL Workbench 8.0. Thank you indeed. </p>
  
  <p>Here is the code I have right now for moving the files.</p>
  
  <pre><code>import os
  import time
  from datetime import datetime
  import pathlib
  
  SOURCE = r'C:\Users\AM\Desktop\Source'
  DESTINATION = r'C:\Users\AM\Desktop\Destination'
  ARCHIVE =r'C:\Users\AM\Desktop\Archive'
  
  
  def get_time_difference(date, time_string):
      """
      You may want to modify this logic to change the way the time difference is calculated.
      """
      time_difference = datetime.now() - datetime.strptime(f"{date} {time_string}", "%d-%m-%Y %H:%M")
      hours = time_difference.total_seconds() // 3600
      minutes = (time_difference.total_seconds() % 3600) // 60
      return f"{int(hours)}:{int(minutes)}"
  
  def move_and_transform_file(file_path, dst_path, delimiter="\t"):
      """
      Reads the data from the old file, writes it into the new file and then 
      deletes the old file.
      """
      with open(file_path, "r") as input_file, open(dst_path, "w") as output_file:
          data = {
              "Date": None,
              "Time": None,
              "Power": None,
          }
          time_difference_seen = False
          for line in input_file:
              (line_id, item, line_type, value) = line.strip().split()
              if item in data:
                  data[item] = value
                  if not time_difference_seen and data["Date"] is not None and data["Time"] is not None:
                      time_difference = get_time_difference(data["Date"], data["Time"])
                      time_difference_seen = True
                      print(delimiter.join([line_id, "TimeDif", line_type, time_difference]), file=output_file)
                  if item == "Power":
                      value = str(int(value) * 10)
              print(delimiter.join((line_id, item, line_type, value)), file=output_file)
  
      os.remove(file_path)
  
  def process_files(all_file_paths, newest_file_path, subdir):
      """
      For each file, decide where to send it, then perform the transformation.
      """
      for file_path in all_file_paths:
          if file_path == newest_file_path and os.path.getctime(newest_file_path) &lt; time.time() - 120:
              dst_root = DESTINATION
          else:
              dst_root = ARCHIVE
  
          dst_path = os.path.join(dst_root, subdir, os.path.basename(file_path))
          move_and_transform_file(file_path, dst_path)
  
  def main():
      """
      Gather the files from the directories and then process them.
      """
      for subdir in os.listdir(SOURCE):
          subdir_path = os.path.join(SOURCE, subdir)
          if not os.path.isdir(subdir_path):
              continue
  
          all_file_paths = [
              os.path.join(subdir_path, p) 
              for p in os.listdir(subdir_path) 
              if os.path.isfile(os.path.join(subdir_path, p))
          ]
  
          if all_file_paths:
              newest_path = max(all_file_paths, key=os.path.getctime)
              process_files(all_file_paths, newest_path, subdir)
  
  if __name__ == "__main__":
  main()
  </code></pre>