Database examples¶

In [ ]:

from chowda.db import engine
from chowda.models import SonyCiAsset, MediaFile
from sqlmodel import Session, select, col, func
from re import search, split

from chowda.db import engine from chowda.models import SonyCiAsset, MediaFile from sqlmodel import Session, select, col, func from re import search, split

Get single asset by ID¶

In [ ]:

with Session(engine) as session:
    results = session.get(SonyCiAsset, '42c85f4305f04a2d8240084a801061d0')

with Session(engine) as session: results = session.get(SonyCiAsset, '42c85f4305f04a2d8240084a801061d0')

Search with regex¶

In [ ]:

statement = select(SonyCiAsset).where(col(SonyCiAsset.name).op('REGEXP')('^\ufeff'))

with Session(engine) as session:
    results = session.exec(statement).all()

statement = select(SonyCiAsset).where(col(SonyCiAsset.name).op('REGEXP')('^\ufeff')) with Session(engine) as session: results = session.exec(statement).all()

Find Media Files with more than one asset¶

In [ ]:

duplicates = (
    select(MediaFile)
    .join(SonyCiAsset)
    .group_by(MediaFile.guid)
    .having(func.count(SonyCiAsset.id) > 1)
)

with Session(engine) as session:
    results = session.exec(duplicates).all()

duplicates = ( select(MediaFile) .join(SonyCiAsset) .group_by(MediaFile.guid) .having(func.count(SonyCiAsset.id) > 1) ) with Session(engine) as session: results = session.exec(duplicates).all()

Duplicates with matching checksums¶

In [ ]:

duplicates = (
    select(MediaFile)
    .join(SonyCiAsset)
    .group_by(MediaFile.guid)
    .having(func.count(SonyCiAsset.id) > 1)
)

matching_statement = duplicates.having(
    func.count(func.distinct(SonyCiAsset.md5Checksum)) == 1
)
unmatching_statement = duplicates.having(
    func.count(func.distinct(SonyCiAsset.md5Checksum)) > 1
)

with Session(engine) as session:
    matching = session.exec(matching_statement).all()
    unmatching = session.exec(unmatching_statement).all()

print(f'Matching: {len(matching)}, Unmatching: {len(unmatching)}')

duplicates = ( select(MediaFile) .join(SonyCiAsset) .group_by(MediaFile.guid) .having(func.count(SonyCiAsset.id) > 1) ) matching_statement = duplicates.having( func.count(func.distinct(SonyCiAsset.md5Checksum)) == 1 ) unmatching_statement = duplicates.having( func.count(func.distinct(SonyCiAsset.md5Checksum)) > 1 ) with Session(engine) as session: matching = session.exec(matching_statement).all() unmatching = session.exec(unmatching_statement).all() print(f'Matching: {len(matching)}, Unmatching: {len(unmatching)}')

In [ ]:

matching_dups_in_workspace = matching_statement.where(
    SonyCiAsset.folder['id'].as_string() == 'a1459cf719eb4a7cb2686663018be161'
)
matching_dups_not_in_workspace = matching_statement.where(
    SonyCiAsset.folder['id'].as_string() != 'a1459cf719eb4a7cb2686663018be161'
)

with Session(engine) as session:
    in_workspace_dups = session.exec(matching_dups_in_workspace).all()
    not_in_workspace_dups = session.exec(matching_dups_not_in_workspace).all()

print(f'In Workspace Duplicates: {len(in_workspace_dups)}')
print(f'Not in Workspace Duplicates: {len(not_in_workspace_dups)}')

matching_dups_in_workspace = matching_statement.where( SonyCiAsset.folder['id'].as_string() == 'a1459cf719eb4a7cb2686663018be161' ) matching_dups_not_in_workspace = matching_statement.where( SonyCiAsset.folder['id'].as_string() != 'a1459cf719eb4a7cb2686663018be161' ) with Session(engine) as session: in_workspace_dups = session.exec(matching_dups_in_workspace).all() not_in_workspace_dups = session.exec(matching_dups_not_in_workspace).all() print(f'In Workspace Duplicates: {len(in_workspace_dups)}') print(f'Not in Workspace Duplicates: {len(not_in_workspace_dups)}')

Aggregations¶

In [ ]:

folder_statement = matching_statement.having(
    # at least one related asset is in the 'Workspace' folder
    # func.bool_or(SonyCiAsset.folder['name'].as_string() == 'Workspace')

    # how many match
    func.count().filter(SonyCiAsset.folder['name'].as_string() == 'Workspace') > 0
)

with Session(engine) as session:
    matching = session.exec(folder_statement).all()
    len(matching)

folder_statement = matching_statement.having( # at least one related asset is in the 'Workspace' folder # func.bool_or(SonyCiAsset.folder['name'].as_string() == 'Workspace') # how many match func.count().filter(SonyCiAsset.folder['name'].as_string() == 'Workspace') > 0 ) with Session(engine) as session: matching = session.exec(folder_statement).all() len(matching)

Return extra fields with the results¶

In [ ]:

asset_count = func.count(SonyCiAsset.id).label('asset_count')
workspace_count = func.count().filter(
    SonyCiAsset.folder['name'].as_string() == 'Workspace'
).label('workspace_count')

# add_columns keeps the MediaFile entity and appends the aggregates
with_counts = folder_statement.add_columns(asset_count, workspace_count)

with Session(engine) as session:
    rows = session.exec(with_counts).all()

for media_file, assets, in_workspace in rows[:5]:
    print(media_file.guid, assets, in_workspace)

asset_count = func.count(SonyCiAsset.id).label('asset_count') workspace_count = func.count().filter( SonyCiAsset.folder['name'].as_string() == 'Workspace' ).label('workspace_count') # add_columns keeps the MediaFile entity and appends the aggregates with_counts = folder_statement.add_columns(asset_count, workspace_count) with Session(engine) as session: rows = session.exec(with_counts).all() for media_file, assets, in_workspace in rows[:5]: print(media_file.guid, assets, in_workspace)

Count assets per folder id¶

In [ ]:

folder_id = SonyCiAsset.folder['id'].as_string().label('folder_id')
folder_name = SonyCiAsset.folder['name'].as_string().label('folder_name')

folder_counts = (
    select(folder_id, folder_name, func.count().label('asset_count'))
    .group_by(folder_id, folder_name)
    .order_by(func.count().desc())
)

folders = []
with Session(engine) as session:
    for fid, name, count in session.exec(folder_counts):
        # print(f'{count:>8}  {fid}  {name}')
        folders.append((fid, name, count))
with open('folder_counts.csv', 'w') as f:
    for fid, name, count in folders:
        f.write(f'{fid},{name},{count}\n')

folder_id = SonyCiAsset.folder['id'].as_string().label('folder_id') folder_name = SonyCiAsset.folder['name'].as_string().label('folder_name') folder_counts = ( select(folder_id, folder_name, func.count().label('asset_count')) .group_by(folder_id, folder_name) .order_by(func.count().desc()) ) folders = [] with Session(engine) as session: for fid, name, count in session.exec(folder_counts): # print(f'{count:>8} {fid} {name}') folders.append((fid, name, count)) with open('folder_counts.csv', 'w') as f: for fid, name, count in folders: f.write(f'{fid},{name},{count}\n')

In [ ]: