No Description

tables_script.py 11KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295
  1. # script to grab the zip file from the google drive and populate the database
  2. # with the data. Phase 1 of the proyect since we wont have user entries yet
  3. # Not all of the buildings and images and fichas will be uploaded due to the
  4. # formating discrepencies in the directories, this
  5. # will be fixed later, for now we will just upload the ones we can
  6. import mysql.connector
  7. import zipfile
  8. from sys import argv
  9. from os import listdir, mkdir
  10. from os.path import isdir, isfile, abspath, join, basename
  11. # using copy for now instead of move so I can delete the folders and not have to set everything up
  12. from shutil import move, copy
  13. # import module from current directory
  14. import ficha_script
  15. import json
  16. # making a simple connection to the database
  17. # this will be changed later to a more secure connection
  18. def connect_db():
  19. user = 'root'
  20. password = ''
  21. host = 'localhost'
  22. port = '3306'
  23. database = 'fotoexploratorio'
  24. connection = mysql.connector.connect(user=user, password=password, host=host, port=port, database=database)
  25. return connection
  26. def create_tables(connection):
  27. # create the tables if they dont exist
  28. cursor = connection.cursor()
  29. cursor.execute('''CREATE TABLE IF NOT EXISTS buildings (
  30. id INT AUTO_INCREMENT PRIMARY KEY,
  31. name VARCHAR(255) CHARACTER SET utf8 UNIQUE NOT NULL
  32. )''')
  33. cursor.execute('''CREATE TABLE IF NOT EXISTS images (
  34. id INT AUTO_INCREMENT PRIMARY KEY,
  35. building_id INT,
  36. path VARCHAR(255) CHARACTER SET utf8 UNIQUE NOT NULL,
  37. year INT,
  38. medium VARCHAR(255) CHARACTER SET utf8 DEFAULT NULL,
  39. author VARCHAR(255) CHARACTER SET utf8 DEFAULT NULL,
  40. recovery_method VARCHAR(255) CHARACTER SET utf8 DEFAULT NULL,
  41. recovery_date DATE DEFAULT NULL,
  42. FOREIGN KEY (building_id) REFERENCES buildings(id)
  43. )''')
  44. # thinking separating authors into a seperate table eventually but some FICHAS dont have authors
  45. # so I will leave it like this for now
  46. # cursor.execute('CREATE TABLE IF NOT EXIST authors (id INT NOT NULL AUTO_INCREMENT), name VARCHAR(255) encoding="utf8", PRIMARY KEY (id))')
  47. connection.commit()
  48. cursor.close()
  49. # reciveve the path of the zip file
  50. def unzip(zip):
  51. # if already exists, skip this step
  52. if not 'FotoExploratorio 2022 2023' in listdir():
  53. with zipfile.ZipFile(zip, 'r') as zip_ref:
  54. zip_ref.extractall()
  55. print('unziped')
  56. path = abspath('FotoExploratorio 2022 2023')
  57. return path
  58. def get_edificios(path, img_reject, connection):
  59. # search for the directory called EDIFICIOS
  60. edificio_folder = join(path, 'EDIFICIOS')
  61. # get the list of edificios
  62. edificios = []
  63. # recurse through the EDIFICIOS folder
  64. for edificio_folders in listdir(edificio_folder):
  65. nested_folder = False
  66. # recurse throgh the folders inside said edificio folder
  67. for objects in listdir(join(edificio_folder, edificio_folders)):
  68. # if its a file put in the image reject list
  69. if isfile(edificio_folder + edificio_folders + '/' + objects):
  70. img_reject.append(edificio_folder + edificio_folders + '/' + objects)
  71. # if its a folder called imagenes, then we will add the images from there so it goes to the editios list
  72. if isdir(join(edificio_folder, edificio_folders, objects)) and objects == 'imágenes':
  73. path_images = join(edificio_folder, edificio_folders, objects)
  74. for items in listdir(path_images):
  75. # if its a folder, then we will not add the folder name to the edificios list
  76. if isdir (join(path_images, items)):
  77. # since this indicates theres an indented folder, I will not write the folder name of
  78. # the upper directory
  79. nested_folder = True
  80. # check if the folder is already in the list
  81. if join(path_images, items) not in edificios:
  82. edificios.append(abspath(join(path_images, items)))
  83. # just skips the folders that are not imagenes:
  84. else:
  85. continue
  86. # if nested_folder is false then will add the folder name to the edifcio list
  87. if not nested_folder:
  88. # check if thats its not already in the list
  89. if join(edificio_folder, edificio_folders) not in edificios:
  90. edificios.append(abspath(join(edificio_folder, edificio_folders)))
  91. # enter the edificios in the list into the database
  92. edificios = [edificio for edificio in edificios if 'Fotos' not in edificio]
  93. for edificio in edificios:
  94. # remove the path
  95. edificio = basename(edificio)
  96. # remove the _uprrp from the name
  97. edificio = edificio.replace('_uprrp', '')
  98. # enter the edificio into the database
  99. cursor = connection.cursor()
  100. cursor.execute('INSERT IGNORE INTO buildings (name) VALUES (%s)', (edificio,))
  101. connection.commit()
  102. cursor.close()
  103. return edificios
  104. def get_images(edificios_list, connection):
  105. # make a folder to store the images
  106. if not 'accepted_images' in listdir():
  107. mkdir('accepted_images')
  108. images = []
  109. #recurse the edificios folder
  110. for edificio in edificios_list:
  111. # append the images in the folder to the list, if the folder has an imagenes folder
  112. # then we will add the image from there
  113. cursor = connection.cursor(buffered=True)
  114. cursor.execute('SELECT id FROM buildings WHERE name = %s', (basename(edificio.replace('_uprrp', '')),))
  115. building_id = cursor.fetchone()[0]
  116. cursor.close()
  117. for folders in listdir(edificio):
  118. # if its a folder called imagenes, then we will add the images from there so it goes to the editios list
  119. if isdir(join(edificio, folders)) and folders == 'imágenes':
  120. for img in listdir(join(edificio, folders)):
  121. cursor = connection.cursor(buffered=True)
  122. # append the image to the list
  123. img = basename(img)
  124. images.append({'path': folders + img, 'building_id': building_id})
  125. copy(join(edificio, folders, img), join('accepted_images', img))
  126. cursor.execute('INSERT IGNORE INTO images (path, building_id) VALUES (%s, %s)', ('accepted_images/' + img, building_id))
  127. connection.commit()
  128. cursor.close()
  129. # due to formatting issues, some images are in the edificio folder, so we will add to the database from here
  130. if isfile(join(edificio, folders)):
  131. cursor = connection.cursor(buffered=True)
  132. img = basename(folders)
  133. images.append({'path': img, 'building_id': building_id})
  134. copy(join(edificio, folders), join('accepted_images', img))
  135. cursor.execute('INSERT IGNORE INTO images (path, building_id) VALUES (%s, %s)', ('accepted_images/' + img, building_id))
  136. connection.commit()
  137. cursor.close()
  138. return images
  139. def get_fichas(path, images_list, connection):
  140. fichas = []
  141. fichas_path = path + '/FICHAS/FICHAS_PRELIMINARES/'
  142. # recurse the fichas folder
  143. ficha_script.run_script(path)
  144. fichas_path = 'fichas_jsons/'
  145. # recurse the fichas json folder, read the image reference and try and find it in the images list
  146. for ficha in listdir(fichas_path):
  147. # read the json file for the "Código de imagen" key
  148. with open(fichas_path + ficha, encoding='utf-8') as ficha_file:
  149. key = json.load(ficha_file)['Código de la imagen']
  150. # the key may not have a path so we need to search the key inside the edificio folder
  151. for img in images_list:
  152. # grab the image name only, remove file extension
  153. img_name = img['path'].split('.')[0]
  154. # if the image name is the same as the key, then we will add the ficha to the list
  155. if img_name == key:
  156. # fichas.append(fichas_path + ficha)
  157. # get the data from the json and insert it into the database
  158. with open(fichas_path + ficha, encoding='utf-8') as ficha_file:
  159. img_path = 'accepted_images/' + img['path']
  160. data = json.load(ficha_file)
  161. cursor = connection.cursor(buffered=True)
  162. # turn on autocommit
  163. # insert the data into the database
  164. # update the image table with the data from the ficha
  165. cursor.execute('''UPDATE images SET
  166. year = IFNULL(%s, year),
  167. author = IFNULL(%s, author),
  168. medium = IFNULL(%s, medium),
  169. recovery_method = IFNULL(%s, recovery_method),
  170. recovery_date = IFNULL(%s, recovery_date)
  171. WHERE path = %s''',
  172. (data['Año'], data['Autor de la fotografía o dibujo'], data['Medio'], data['Recuperado'], data['Fecha de recuperación de la imagen'], img_path))
  173. connection.commit()
  174. cursor.close()
  175. break
  176. def main():
  177. try:
  178. mkdir('fichas_jsons')
  179. except:
  180. pass
  181. try:
  182. zip_path = argv[1]
  183. except:
  184. zip_path = 'FotoExploratorio 2022 2023.zip'
  185. path = unzip(zip_path)
  186. # create the list of rejected images and fichas
  187. img_reject = []
  188. # connection = connect_db()
  189. connection = connect_db()
  190. # create the tables
  191. create_tables(connection)
  192. # get the list of edificios
  193. edificios = get_edificios(path, img_reject, connection)
  194. # get the list of images
  195. images = get_images(edificios, connection)
  196. # get the list of fichas
  197. get_fichas(path, images, connection)
  198. # write the list of rejected images and fichas to a file
  199. with open('rejected_images.txt', 'w', encoding='utf-8') as f:
  200. for img in img_reject:
  201. f.write(img + '\n')
  202. # with open('rejected_fichas.txt', 'w', encoding='utf-8') as f:
  203. # for ficha in fichas_reject:
  204. # f.write(ficha + '\n')
  205. # write edificio list to see the buildings listed because some of them are not buildings
  206. with open('edificios.txt', 'w', encoding='utf-8') as f:
  207. for edificio in edificios:
  208. f.write(edificio + '\n')
  209. # end the program
  210. print('done')
  211. if __name__ == '__main__':
  212. main()