<jupyter_start><jupyter_text>setting up an "acceptance test" by cleaning out the `01_files` workspace<jupyter_code>!rm -rf 01_files<jupyter_output><empty_output><jupyter_text>create `01_files` workspace and populate it with `tar` archive<jupyter_code>import os

# We create a working directory for this example.
os.makedirs("01_files", exist_ok=True)

# We create a "data" subdirectory for the images and the metadata tag files.
# os.makedirs("01_files/data", exist_ok=True)
data = "01_files/data"

# We create an "out" subdirectory for the processed images and the metadata catalog.
os.makedirs("01_files/out", exist_ok=True)
out  = "01_files/out" 
import requests
# import logging
# import http.client

# # To set up logging.
# # https://stackoverflow.com/questions/16337511/
# http.client.HTTPConnection.debuglevel = 1
# logging.basicConfig()
# logging.getLogger().setLevel(logging.DEBUG)
# requests_log = logging.getLogger("requests.packages.urllib3")
# requests_log.setLevel(logging.DEBUG)
# requests_log.propagate = True

# To access 
mwe_url = "https://github.com/coltongrainger/2020-02-05-mwe/raw/master/2020-02-05-mwe.tar.gz"
mwe_tar_archive = "01_files/2020-02-05-mwe.tar.gz"
res = requests.get(mwe_url)
if res.status_code == 200:
    with open(mwe_tar_archive, 'wb') as f:
        f.write(res.content)
import subprocess
retcode = subprocess.call(['tar', '-xvf', mwe_tar_archive, '-C', "01_files"])
if retcode == 0:
    print("Extracted successfully")
else:
    raise IOError('tar exited with code %d' % retcode)
os.remove(mwe_tar_archive)
for p in os.listdir(data):
    print(os.listdir(os.path.join(data, p)))<jupyter_output><empty_output><jupyter_text>catalog images under `data` directory<jupyter_code>%run -i "scripts/utils.py"
# We generate a fixed sequence for uuids.
get_fixed_seq()
import sys
sys.path.append('/glade/u/home/rdadata/lib/python/site-packages')

# get_exiftool()
import subprocess
import os
repo_dir = subprocess.Popen(['git', 'rev-parse', '--show-toplevel'], stdout=subprocess.PIPE).communicate()[0].rstrip().decode('utf-8')
sys.path.append(os.path.join(repo_dir, "dependencies/pyexiftool"))
import exiftool
normalized_catalog = get_normalized_catalog(data)
# We generate a metadata catalog (unnormalized) from the data directory.
catalog = unnormalize_catalog(normalized_catalog)
# We flatten the normalized catalog. 
# Each file in the data directory "has its own entry" in this catalog.
# We'll eventually ignore non-image files.
write_timestamped_catalog(catalog, out)
# We write this version of the metadata catalog to the output directory.
catalog = read_timestamped_catalog(out)
# We read in the most recent version of the metadata catalog from the out directory.<jupyter_output><empty_output><jupyter_text>rename images by `uuid`<jupyter_code>elementary_family = [c for c in catalog if c['media_type'].startswith("image")]
# We create a list of all the entries in the catalog that are image files.
import os
# We'll perform some file renames between the data directory and the out directory.

# We move all the images in the catalog to the output directory.
for member in elementary_family:
    os.rename(member['file_path'], os.path.join(out, member['uuid']))<jupyter_output><empty_output><jupyter_text>pick up catalog with `pandas`<jupyter_code>import pandas as pd

df = pd.DataFrame(catalog)
df = df[df['media_type'].str.contains("image")] 
# we only want to keep track of image files<jupyter_output><empty_output><jupyter_text>filter `DataFrame` for archives, platforms, documents, and images to insert into DB `images`<jupyter_code>import re
arc_df = df.filter(regex=("^archive"))
arc_df = arc_df.drop_duplicates()
arc_df.rename(columns=lambda x: re.sub('archive.','',x), inplace=True)
arc_df.to_dict('records')
plt_df = df.filter(regex=("^platform"))
plt_df = plt_df.drop_duplicates()
plt_df.rename(columns=lambda x: re.sub('platform.','',x), inplace=True)
plt_df.to_dict('records')
doc_df = pd.concat(
    [df.filter(regex=("^document")), 
     df.filter(items=["archive.host_country", "archive.name"]),
     df.filter(items=["platform.host_country", "platform.name"])
    ], axis=1
).drop_duplicates()
doc_df.rename(columns=lambda x: re.sub('document.', '', x), inplace=True)
doc_df.fillna("", inplace=True) # avoid NaNs<jupyter_output><empty_output><jupyter_text>declare and persist tables in `images`<jupyter_code>%run -i scripts/tables.py
# from sqlalchemy import create_engine
# engine = create_engine('mysql+mysqlconnector://user:pass@rda-db.ucar.edu/images')
# engine = create_engine('mysql+pymysql://user:pass@localhost/images')
# TODO read defaults extra file
metadata.drop_all(engine) # clean out the DB
metadata.create_all(engine) # reinitialize the canonical schema.
connection = engine.connect() # let's start working with these tables<jupyter_output><empty_output><jupyter_text>metadata insertion for archives<jupyter_code>from sqlalchemy import insert, select
ins = insert(archive)
rp = connection.execute(ins, arc_df.to_dict('records'))
# throws integrity error if run twice
s = select([archive])
rp = connection.execute(s)
for arc in rp:
    print(arc)<jupyter_output><empty_output><jupyter_text>metadata insertion for platforms<jupyter_code>ins = insert(platform)
rp = connection.execute(ins, plt_df.to_dict('records'))
s = select([platform])
rp = connection.execute(s)
for plt in rp:
    print(plt)<jupyter_output><empty_output><jupyter_text>metadata insertion for documents<jupyter_code>from sqlalchemy import and_

def get_archive_fk(doc_dict): # add error handling
    s = select([archive.c.archive_id])
    s = s.where(and_(
        archive.c.name == doc_dict['archive.name'],
        archive.c.host_country == doc_dict['archive.host_country']
    ))
    s = s.limit(1) # should be unique anyways
    rp = connection.execute(s)
    result = rp.scalar() # is the parent id
    return result

def get_platform_fk(doc_dict): # add error handling
    s = select([platform.c.platform_id])
    s = s.where(and_(
        platform.c.name == doc_dict['platform.name'],
        platform.c.host_country == doc_dict['platform.host_country']
    ))
    s = s.limit(1) # should be unique anyways
    rp = connection.execute(s)
    result = rp.scalar() # is the parent id
    return result
for doc_dict in doc_df.to_dict('record'):
    arc_id = get_archive_fk(doc_dict)
    plt_id = get_platform_fk(doc_dict)
    for key in [
        'archive.host_country',
        'archive.name',
        'platform.host_country',
        'platform.name'
    ]:
        doc_dict.pop(key)
    ins = insert(document)
    rp = connection.execute(ins,
            doc_dict,
            archive_id = arc_id,
            platform_id = plt_id
    )
# throws an integrity error if run twice
s = select([document.c.id_within_archive_type, document.c.id_within_archive, document.c.contact_person])
s = s.where(document.c.start_date.between("1900-01-01", "2000-01-01"))
rp = connection.execute(s)
for res in rp:
    print(res)
s = select([document.c.id_within_archive_type, document.c.id_within_archive, document.c.contact_person])
s = s.where(document.c.start_date.between("1800-01-01", "1900-01-01"))
rp = connection.execute(s)
for res in rp:
    print(res)<jupyter_output><empty_output><jupyter_text>metadata insertion for images<jupyter_code>def get_document_fk(img_dict):
    s = select([document.c.document_id])
    s = s.where(and_(
        document.c.id_within_archive == img_dict['document.id_within_archive'],
        document.c.id_within_archive_type == img_dict['document.id_within_archive_type']
    ))
    s = s.limit(1) # should be unique anyways
    rp = connection.execute(s)
    result = rp.scalar() # is the parent id
    return result
img_df = df.filter(items=["uuid","media_type","document.id_within_archive","document.id_within_archive_type"])
for img_dict in img_df.to_dict('record'):
    doc_id = get_document_fk(img_dict)
    for key in ["document.id_within_archive","document.id_within_archive_type"]:
        img_dict.pop(key)
    img_dict['document_id'] = doc_id
    ins = insert(image, img_dict)
    print(ins.compile().params)
    rp = connection.execute(ins, img_dict)    
# throws an integrity error if run twice<jupyter_output><empty_output><jupyter_text>retrieval of images by uuid<jupyter_code>from IPython.display import Image, display
s = select([image.c.uuid, image.c.media_type])
s = s.order_by(image.c.uuid)
rp = connection.execute(s)

for img in rp:
    print(os.path.join(out, img.uuid), img.media_type)<jupyter_output><empty_output><jupyter_text>subsetting by date range<jupyter_code>columns = [image.c.uuid, image.c.media_type, 
           document.c.start_date, document.c.standardized_region_list]

twentieth_century = select(columns)
twentieth_century = twentieth_century.select_from(
    image.join(document)).where(
    document.c.start_date.between("1900-01-01", "1999-12-31"))

rp = connection.execute(twentieth_century).fetchall()

for img in rp:
    display(Image(
        filename=os.path.join(out, img.uuid),
        format=img.media_type.replace("image/","")
    ))
    for key in img.keys():
        print('{:>20}: {}'.format(key, img[key]))

columns = [image.c.uuid, image.c.media_type, 
           document.c.start_date, document.c.standardized_region_list]

nineteenth_century = select(columns)
nineteenth_century = nineteenth_century.select_from(
    image.join(document)).where(
    document.c.start_date.between("1800-01-01", "1899-12-31"))
nineteenth_century = nineteenth_century.order_by(document.c.start_date)

rp = connection.execute(nineteenth_century).fetchall()

for img in rp:
    display(Image(
        filename=os.path.join(out, img.uuid),
        format=img.media_type.replace("image/","")
    ))
    for key in img.keys():
        print('{:>20}: {}'.format(key, img[key]))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1311308249209748, 'fraction_numerical': 0.01345977363107984, 'mean_word_length': 4.998776758409786, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 55, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '611281', 'license': 'CC0-1.0', 'n_tokens_mistral': 3426, 'n_tokens_neox': 3307, 'n_words': 704, 'path': 'docs/01_Inserting_Images_with_SQLAlchemy.ipynb', 'repo_name': 'coltongrainger/imagearchive', 'size': '9807'}