Import photo metadata from Apple Photos into SQLite

[simonw]

Faces, albums, locations, that kind of thing.

[simonw]

I'm going to use osxphotos for this.

Since I've already got code to upload photos and insert them into a table based on their sha256 hash, my first go at this will be to import data using the tool and foreign-key it to the sha256 hash in the existing table.

[simonw]

Command will be this:

$ photos-to-sqlite apple-photos photos.db

This will populate a apple_photos table with the data imported by the osxphotos library, plus the calculated sha256.

[simonw]

For locations: I'll add place_x columns for all of these:

(Pdb) photo.place.address._asdict()
{'street': None, 'sub_locality': None, 'city': 'Loreto', 'sub_administrative_area': 'Loreto', 'state_province': 'BCS', 'postal_code': None, 'country': 'Mexico', 'iso_country_code': 'MX'}

[simonw]

To get the taken date in UTC:

from datetime import timezone
(Pdb) photo.date.astimezone(timezone.utc).isoformat()
'2018-02-13T20:21:31.620000+00:00'
(Pdb) photo.date.astimezone(timezone.utc).isoformat().split(".")
['2018-02-13T20:21:31', '620000+00:00']
(Pdb) photo.date.astimezone(timezone.utc).isoformat().split(".")[0]
'2018-02-13T20:21:31'
(Pdb) photo.date.astimezone(timezone.utc).isoformat().split(".")[0] + "+00:00"
'2018-02-13T20:21:31+00:00'

[simonw]

Albums have UUIDs:

(Pdb) photo.album_info[0].__dict__
{'_uuid': '17816791-ABF3-447B-942C-9FA8065EEBBA', '_db': osxphotos.PhotosDB(dbfile='/Users/simon/Pictures/Photos Library.photoslibrary/database/photos.db'), '_title': 'Geotaggable Photos geotagged'}

[simonw]

Record burst_uuid as a column:

(Pdb) with_bursts[0]._info["burstUUID"]
'703FAA23-57BF-40B4-8A33-D9CEB143391B'

[simonw]

Reading this source code is really useful for figuring out how to store a photo in a DB table: https://github.com/RhetTbull/osxphotos/blob/7444b6d173918a3ad2a07aefce5ecf054786c787/osxphotos/photoinfo.py

[simonw]

Needs documentation.