Understanding the Photos database
Photos (and iPhotos as well as Aperture) stores data about the photos in the Photos library in a sqlite3 database file inside the Photos Library.photoslibrary folder.
osxphotos currently supports the following versions of the Photos database and associated MacOS version:
- Photos 2.0 (MacOS 10.12)
- Photos 3.0 (MacOS 10.13)
- Photos 4.0 (MacOS 10.14)
- Photos 5.0 (MacOS 10.15)
In Photos 2.0 - 4.0, the database format is very similar between versions. Photos 5.0 appeared to be a complete rewrite with a completely different database structure.
In Photos 2.0 - 4.0, the sqlite3 database is located in:
~/Pictures/Photos Library.photoslibrary/database/photos.db
In Photos 5.0, this file also exists but contains only metadata data about the library itself (e.g. which version of Photos created the library) and the actual photo metadata is located in:
~/Pictures/Photos Library.photoslibrary/database/Photos.sqlite
osxphotos reads photos.db (which is present in all versions of Photos) then reads Photos.sqlite if the library is a Photos 5 library.
To read the library version, osxphotos PhotosDB calls self._get_db_version() which queries photos.db using the following sql statement to find the library version:
SELECT value from LiGlobals where LiGlobals.keyPath is 'libraryVersion'
The version is a 4-digit integer, the first digit of which signifies which version of Photos created the library. The versions I've been able to determine from testing are:
- Photos 2.0 (10.12.6) == 2622
- Photos 3.0 (10.13.6) == 3301
- Photos 4.0 (10.14.5) == 4016
- Photos 4.0 (10.14.6) == 4025
- Photos 5.0 (10.15.0) == 6000
Most of what is described in the following comes from reverse engineering what Photos is doing or looking at the code of others who have done related work. I had to make many, many assumptions and some of them are surely wrong. If you discover any errors or have additional information to add I welcome edits to the wiki or opening an issue!
Dates & times in the database are stored as a MacOS time value which is the number of seconds since 2001-01-01 00:00:00. To work with these in python, you'll need add the number of seconds since the Unix time epoch of 1970-01-01 00:00:00. I do this by computing a time delta thusly:
td = (datetime(2001, 1, 1, 0, 0) - datetime(1970, 1, 1, 0, 0)).total_seconds()The NSDate values can also be "parsed" directly with sqlite's datetime function, e.g.
datetime(ZCREATIONDATE, 'unixepoch', '31 years')Additional details about changes from Photos 5 to Photos 6 database structure are here
Additional details about the Photos 5 database structure are here.
Additional details about the Photos 4 database structure are here.