Exporting Album metadata

[thomasjmalloy]

I'm trying to get my wife off of Photos. She has about 50K images/videos taking up about 250GB of data. Photos regularly finds fault with its database and takes about 20 yours to repair it. I successfully exported all of the images and video using the Photos export command and imported them into Lightroom Classic. But, that left behind metadata, the majority of which is Album structure. Enough background ...

I installed osxphotos on my Mac (Sonoma). I ran the info command on (a copy of) the Photos database. To my surprise it reported and album-count of 0. I inspected the database file, Photos.sqlite, using "DB Browser for SQLite". What I found is that the ZALBUMLIST table is almost empty (7 records to be precise). There is another table, Z_27ALBUMLISTS, that contains 781 records. That number is high relative to the number of albums Photos reports: 41 albums and 34 folders. The info command also undercounts the number of photos (24,866). Photos thinks there are 48,049 photos and 1,180 videos. There is a Z_ASSETS table with 51,162 records and a Z_28ASSETS table with 40,560 records.

So, I'm way over my head. Are the tables, Z_27ALBUMLISTS and Z_28ASSETS some kind of legacy information from iPhotos or older versions of Photos? Does osxphotos interpret them. Do they even have anything to do with the inconsistencies I am seeing.

Finally, should I bother to export the database using {folder_album} template or some such or is the info output likely correct; i.e. osxphotos is not aware of any albums.

Thanks in advance for any advice. I'm willing (try to) install from the repository and poke around in the code (I use PyCharm when I can as an IDE) if you think it would shed light on anything.

Tom Malloy

[RhetTbull]

Z_28ASSETS is used to associate assets (photos and videos) with albums. ZASSETS is the master list of all assets in the library. The albums are stored in the ZGENERICALBUM table.

The info command also undercounts the number of photos (24,866). Photos thinks there are 48,049 photos and 1,180 videos.

The photo + video count reported by osxphotos info should be close to those reported in Photos. There are sometimes differences due to hidden photos, deleted photos, etc. that OSXPhotos reports but Photos does not. There are also certain "syndicated" photos reported by OSXPhotos that don't show in the Photos count (for example, photos shared in Messages but not yet imported to the library). But in general the numbers should be close.

There's a more detailed "counts" script that can be run via:

osxphotos run https://raw.githubusercontent.com/RhetTbull/osxphotos/main/examples/counts.py

Try that to see what it says regarding total photos and albums.

Photos regularly finds fault with its database and takes about 20 yours to repair it.

My guess is the Photos database is corrupted and that is why OSXPhotos is reporting different numbers and cant' find the albums.

Does Photos show the albums you expect and are the photos you expect to be in those albums in the right albums?

If Photos shows the albums and associated photos correctly, try the following:

Run osxphotos repl

This opens an interactive REPL environment with your photos loaded. In Photos, select a photo that you know is in at least one album. Then in the REPL, at the >>> prompt, type the following

p = Photo(uuid=selected[0].uuid)
[a.title for a in p.albums]

Does this print the names of the albums correctly? (It may be slow as this will use AppleScript to interact with Photos). If this works, then it would not be hard to write a simple python script that outputs the album(s) for each photo.

What is desired end-state? You've imported the photos into Lightroom already so what do you intend to do with the album data (or any other metadata)? Are you wanting to re-export the images with the metadata? Somehow apply the metadata in Lightroom?

If you're willing to share the Photos.sqlite file (and associated -wal and -shm files) I would be happy to take a look at it to see if I can figure out why OSXPhotos isn't reading it correctly. (e.g. send me a dropbox or iCloud link to osxphotos.py@gmail.com). I will caution that this database will contain potentially sensitive data such as locations (including addresses), person names, captions, etc. but it does not contain any actual images in case that is of concern to you.

[RhetTbull]

One additional note: the ZGENERICALBUM table will include shared albums, albums in the trash, and some special albums used by Photos internally, etc. so it's count may be greater than than the current number of albums shown in Photos.

[RhetTbull]

Does this command produce any output? If so, do you see any of the expected albums listed? (look for 'title': 'Album title' in the output)

osxphotos debug-dump --dump _dbalbum_details

[thomasjmalloy]

Thank. you so much for your advice and offer to help! I am on childcare duty with my 4 year old grandson most of today and tomorrow but I will do all the diagnosis you describe as well as share the database with you as soon as I come up for air.

To answer you high level question, the desired end state is to create a Lightroom Classic environment with as much of the information in the Photos library as possible. I'm happy to re-export from osxphotos if that is the way to achieve that goal. If, in the process, we figured out what was wrong with the Photos database that would be icing on the cake.