Source code for updatemysql1B
"""
This is the documentation for updatemysql.py.
This file contains a set of functions that:
1. Create tables in the MySQL database if they do not yet exist.
2. After establishing a connection to the MySQL server,
tables for farside, XT-cut, composite map, SO-PHI, and synoptic map can be created
and populated with the following information: tag, filename, date_range, latest_date, caption, file_creation_time, and filepath.
"""
import os, re
from datetime import datetime
import pymysql
[docs]
def connect_to_mysqlA():
"""
Summary:
Connect to a MySQL server using the provided configuration and return the connection object.
Description:
This function establishes a connection to a MySQL server using the parameters specified in the
~/.my.cnf configuration file and connects to the 'Farside' database. If the connection is
successful, it returns the connection object. If the connection fails, a RuntimeError is raised.
Parameters: None
Returns:
pymysql.connections.Connection: A connection object to the MySQL server.
Raises:
RuntimeError: If the connection to the MySQL server is not open.
Example:
>>> connection = connect_to_mysql()
>>> # Perform database operations using the 'connection' object.
"""
connection = pymysql.connect(
read_default_file='~/.my.cnf',
database='Farside',
)
if connection.open:
return connection
else:
raise RuntimeError('connection to mysql server is not open')
[docs]
def create_tableA(cursor, overwrite=False):
"""
After the connection to MySQL server is established, create tables in the Farside database.
Example:
>>> Table= create_table()
"""
# create farside_movies
if overwrite:
cursor.execute("DROP TABLE IF EXISTS farside_movies")
cursor.execute("""
CREATE TABLE IF NOT EXISTS farside_movies (
farsideID INT AUTO_INCREMENT PRIMARY KEY,
tag VARCHAR(50),
filename VARCHAR(255) UNIQUE,
date_range VARCHAR(50) UNIQUE,
latest_date DATE,
caption TEXT,
file_creation_time DATE,
filepath VARCHAR(255) UNIQUE
)
""")
# create farside_comp_maps
if overwrite:
cursor.execute("DROP TABLE IF EXISTS farside_comp_maps")
cursor.execute("""
CREATE TABLE IF NOT EXISTS farside_comp_maps (
farsideID INT AUTO_INCREMENT PRIMARY KEY,
tag VARCHAR(50),
filename VARCHAR(255) UNIQUE,
date_range VARCHAR(50) UNIQUE,
latest_date DATE,
caption TEXT,
file_creation_time DATE,
filepath VARCHAR(255) UNIQUE
)
""")
# create farside_xtcut_longmap
if overwrite:
cursor.execute("DROP TABLE IF EXISTS farside_xtcut_longmap")
cursor.execute("""
CREATE TABLE IF NOT EXISTS farside_xtcut_longmap (
farsideID INT AUTO_INCREMENT PRIMARY KEY,
tag VARCHAR(50),
filename VARCHAR(255) UNIQUE,
date_range VARCHAR(50) UNIQUE,
latest_date DATE,
caption TEXT,
file_creation_time DATE,
filepath VARCHAR(255) UNIQUE
)
""")
# create synoptic_map
if overwrite:
cursor.execute("DROP TABLE IF EXISTS synoptic_map")
cursor.execute("""
CREATE TABLE IF NOT EXISTS synoptic_map (
farsideID INT AUTO_INCREMENT PRIMARY KEY,
tag VARCHAR(50),
filename VARCHAR(255) UNIQUE,
date_range VARCHAR(50) UNIQUE,
latest_date DATE,
caption TEXT,
file_creation_time DATE,
filepath VARCHAR(255) UNIQUE
)
""")
# create SO_PHI
if overwrite:
cursor.execute("DROP TABLE IF EXISTS SO_PHI")
cursor.execute("""
CREATE TABLE IF NOT EXISTS SO_PHI (
farsideID INT AUTO_INCREMENT PRIMARY KEY,
tag VARCHAR(50),
filename VARCHAR(255) UNIQUE,
date_range VARCHAR(50) UNIQUE,
latest_date DATE,
caption TEXT,
file_creation_time DATE,
filepath VARCHAR(255) UNIQUE
)
""")
[docs]
def extracting_movies():
"""
Example:
>>> Table= extracting_movies()
"""
parent_folder="/scratch/seismo/gosip/farside/"
folders = os.listdir(parent_folder)
extracted_movies = []
for child_folder in folders:
if child_folder=="movies":
tag="movies"
movie_folder=parent_folder+child_folder
movie_files=os.listdir(movie_folder)
for filename in movie_files:
date_range= re.search(r"(\d{4}\.\d{2}\.\d{2}-\d{4}\.\d{2}\.\d{2})", filename)
if date_range:
date_range=date_range.group(1)
latest_date = re.search(r"(\d{4}\.\d{2}\.\d{2})$", date_range)
if latest_date:
latest_date=latest_date.group(1)
filepath=parent_folder+tag+"/"+filename
caption="none"
creation_time = os.path.getctime(filepath)
file_creation_time= datetime.fromtimestamp(creation_time).strftime('%Y-%m-%d %H:%M:%S')
extracted_movies.append((tag, filename, date_range, latest_date, caption, file_creation_time, filepath))
return extracted_movies
[docs]
def extracting_composite_maps():
"""
Example:
>>> Table= extracting_composite_maps()
"""
parent_folder="/scratch/seismo/gosip/farside/"
folders = os.listdir(parent_folder)
extracted_composite_maps = []
for child_folder in folders:
if child_folder=="composite_map":
tag="composite_map"
parent_folder_comp=parent_folder +tag
folders_comp = os.listdir(parent_folder_comp)
for file_year in folders_comp:
year = str(file_year)
child_folder_comp = parent_folder_comp+"/"+year
child_files = os.listdir(child_folder_comp)
for filename in child_files:
filepath = child_folder_comp+"/"+filename
date_range= re.search(r"(\d{4}\.\d{2}\.\d{2}_\d{2}:\d{2}:\d{2})", filename)
if date_range:
date_range=date_range.group(1)
latest_date = re.search(r"(\d{4}\.\d{2}\.\d{2})", date_range)
if latest_date:
latest_date=latest_date.group(1)
caption = re.search(r"(\d{2}:\d{2}:\d{2})", date_range)
if caption:
caption=caption.group(1)
creation_time = os.path.getctime(filepath)
file_creation_time= datetime.fromtimestamp(creation_time).strftime('%Y-%m-%d %H:%M:%S')
extracted_composite_maps.append((tag, filename, date_range, latest_date, caption, file_creation_time, filepath))
return extracted_composite_maps
[docs]
def extracting_xt_cut():
"""
Example:
>>> Table= extracting_xt_cut()
"""
parent_folder="/scratch/seismo/gosip/farside/"
folders = os.listdir(parent_folder)
extracted_composite_xt_cut = []
for child_folder in folders:
if child_folder=="lon_time_map":
tag="lon_time_map"
parent_folder_comp=parent_folder+tag
folders_comp = os.listdir(parent_folder_comp)
for file_year in folders_comp:
year = str(file_year)
child_folder_comp = parent_folder_comp+"/"+year
child_files = os.listdir(child_folder_comp)
for filename in child_files:
filepath = child_folder_comp+"/"+filename
date_range= re.search(r"(\d{4}\.\d{2}\.\d{2}_\d{2}:\d{2}:\d{2})", filename)
if date_range:
date_range=date_range.group(1)
latest_date = re.search(r"(\d{4}\.\d{2}\.\d{2})", date_range)
if latest_date:
latest_date=latest_date.group(1)
caption = re.search(r"(\d{2}:\d{2}:\d{2})", date_range)
if caption:
caption=caption.group(1)
creation_time = os.path.getctime(filepath)
file_creation_time= datetime.fromtimestamp(creation_time).strftime('%Y-%m-%d %H:%M:%S')
extracted_composite_xt_cut.append((tag, filename, date_range, latest_date, caption, file_creation_time, filepath))
return extracted_composite_xt_cut
[docs]
def extracting_synoptic_maps():
"""
Example:
>>> Table= extracting_synoptic_maps()
"""
parent_folder="/scratch/seismo/gosip/farside/"
folders = os.listdir(parent_folder)
extracted_synoptic_maps= []
for child_folder in folders:
if child_folder=="synoptic_map":
tag="synoptic_map"
parent_folder_synoptic=parent_folder+tag
folders_synoptic = os.listdir(parent_folder_synoptic)
for folder in folders_synoptic:
sub_file = str(folder)
child_folder_synoptic = parent_folder_synoptic+"/"+sub_file
child_synoptic = os.listdir(child_folder_synoptic)
for year_synoptic in child_synoptic:
year_synoptic = str(year_synoptic)
sub_sub_file = child_folder_synoptic +"/"+year_synoptic
child_sub_sub_file = os.listdir(sub_sub_file)
for filename in child_sub_sub_file:
date_range= re.search(r"(\d{4}\.\d{2}\.\d{2}_\d{2}:\d{2}:\d{2})", filename)
if date_range:
date_range=date_range.group(1)
latest_date = re.search(r"(\d{4}\.\d{2}\.\d{2})", date_range)
if latest_date:
latest_date=latest_date.group(1)
filepath=parent_folder_synoptic+"/"+ sub_file+"/"+ year_synoptic+"/"+filename
caption=sub_file
creation_time = os.path.getctime(filepath)
file_creation_time= datetime.fromtimestamp(creation_time).strftime('%Y-%m-%d %H:%M:%S')
extracted_synoptic_maps.append((tag, filename, date_range, latest_date, caption, file_creation_time, filepath))
return (extracted_synoptic_maps)
[docs]
def extracting_SO_PHI():
"""
Example:
>>> Table= extracting_SO_PHI()
"""
parent_folder="/scratch/seismo/gosip/farside/"
folders = os.listdir(parent_folder)
extracted_sophi = []
for child_folder in folders:
if child_folder=="SO_PHI":
tag="SO_PHI"
parent_folder_sophi=parent_folder +tag
folders_sophi = os.listdir(parent_folder_sophi)
for file_year in folders_sophi:
year = str(file_year)
child_folder_sophi = parent_folder_sophi+"/"+year
child_files = os.listdir(child_folder_sophi)
for filename in child_files:
filepath = child_folder_sophi+"/"+filename
date_range= re.search(r"(\d{4}\.\d{2}\.\d{2}_\d{2}:\d{2}:\d{2})", filename)
if date_range:
date_range=date_range.group(1)
latest_date = re.search(r"(\d{4}\.\d{2}\.\d{2})", date_range)
if latest_date:
latest_date=latest_date.group(1)
caption = re.search(r"(\d{2}:\d{2}:\d{2})", date_range)
if caption:
caption=caption.group(1)
creation_time = os.path.getctime(filepath)
file_creation_time= datetime.fromtimestamp(creation_time).strftime('%Y-%m-%d %H:%M:%S')
extracted_sophi.append((tag, filename, date_range, latest_date, caption, file_creation_time, filepath))
return(extracted_sophi)
if __name__ == "__main__":
T = [datetime.now()]
extracting_movies_val=extracting_movies()
T.append(datetime.now())
print(T[-1]-T[-2], 'extract movies')
extracting_comp_maps_val=extracting_composite_maps()
T.append(datetime.now())
print(T[-1]-T[-2], 'extract composite maps')
extracting_xt_cut_val = extracting_xt_cut()
T.append(datetime.now())
print(T[-1]-T[-2], 'extract xt cut')
extracting_synoptic_maps_val= extracting_synoptic_maps()
T.append(datetime.now())
print(T[-1]-T[-2], 'extract synoptic maps')
extracting_SO_PHI_val= extracting_SO_PHI()
T.append(datetime.now())
print(T[-1]-T[-2], 'extract SO/PHI')
try:
connection = connect_to_mysql()
cursor = connection.cursor()
create_table(cursor, overwrite=False)
connection.commit()
T.append(datetime.now())
print(T[-1]-T[-2], 'create tables')
cursor.executemany("""
INSERT IGNORE INTO farside_movies (tag, filename, date_range, latest_date, caption, file_creation_time, filepath)
VALUES ( %s, %s, %s, %s, %s, %s, %s)
""", extracting_movies_val)
connection.commit()
T.append(datetime.now())
print(T[-1]-T[-2], 'write to farside_movies')
cursor.executemany("""
INSERT IGNORE INTO farside_comp_maps (tag, filename, date_range, latest_date, caption, file_creation_time, filepath)
VALUES ( %s, %s, %s, %s, %s, %s, %s)
""", extracting_comp_maps_val)
connection.commit()
T.append(datetime.now())
print(T[-1]-T[-2], 'write to farside_comp_maps')
cursor.executemany("""
INSERT IGNORE INTO farside_xtcut_longmap (tag, filename, date_range, latest_date, caption, file_creation_time, filepath)
VALUES ( %s, %s, %s, %s, %s, %s, %s)
""", extracting_xt_cut_val)
connection.commit()
T.append(datetime.now())
print(T[-1]-T[-2], 'write to farside_xtcut_longmap')
cursor.executemany("""
INSERT IGNORE INTO synoptic_map (tag, filename, date_range, latest_date, caption, file_creation_time, filepath)
VALUES ( %s, %s, %s, %s, %s, %s, %s)
""", extracting_synoptic_maps_val)
connection.commit()
T.append(datetime.now())
print(T[-1]-T[-2], 'write to synoptic_map')
cursor.executemany("""
INSERT IGNORE INTO SO_PHI (tag, filename, date_range, latest_date, caption, file_creation_time, filepath)
VALUES ( %s, %s, %s, %s, %s, %s, %s)
""", extracting_SO_PHI_val)
connection.commit()
T.append(datetime.now())
print(T[-1]-T[-2], 'write to SO_PHI')
finally:
cursor.close()
connection.close()
T.append(datetime.now())
print(T[-1]-T[0], 'total elapsed time')
# 0:00:00.013719 create tables
# 0:00:00.002967 extract movies
# 0:00:01.555588 extract composite maps
# 0:00:00.029831 extract xt cut
# 0:00:00.059572 extract synoptic maps
# 0:00:00.033863 extract SO/PHI
# 0:00:00.006888 write to farside_movies
# 0:00:00.655665 write to farside_comp_maps
# 0:00:00.010167 write to farside_xtcut_longmap
# 0:00:00.019722 write to synoptic_map
# 0:00:00.014718 write to SO_PHI
# 0:00:02.402804 total elapsed time