Load data from a dict (as loaded from directory of YAMLs) into database

def load_from_dict(db, data, metadata):
    """Load data from a dict (as loaded from directory of YAMLs) into database
    """
    # The ORM overhead is too high for this kind of bulk load,
    # so drop down to SQLAlchemy Core.
    # This tries to do minimize the number of SQL commands by loading entire
    # tables at once

    if metadata['version'] != 2:
        raise ValueError('Can only load version 2')

    try:
        bind = db.get_bind(tables.Event)
    except (ValueError, AttributeError):
        pass
    else:
        if bind.dialect.name == 'sqlite':
            db.execute('PRAGMA foreign_keys = ON')

    with bulk_inserter(db) as insert:

        # Load speakers

        speaker_slugs = set()

        for series_slug, series in data['series'].items():
            for event_slug, event in series['events'].items():
                for talk in event.get('talks'):
                    for speaker in talk.get('speakers', ()):
                        if speaker not in speaker_slugs:
                            speaker_slugs.add(speaker)
                            insert(tables.Speaker, {
                                'slug': speaker,
                                'name': speaker,
                            })

        venue_ids = {}

        # Load cities, and their venues

        for city_slug, city in data['cities'].items():
            city_data = city['city']
            insert(tables.City, {
                'slug': city_slug,
                'name': city_data['name'],
                'latitude': city_data['location']['latitude'],
                'longitude': city_data['location']['longitude'],
                '_source': city_data['_source'],
            })

            for venue_slug, venue in city.get('venues', {}).items():
                venue_ids[city_slug, venue_slug] = insert(tables.Venue, {
                    'city_slug': city_slug,
                    'slug': venue_slug,
                    'name': venue['name'],
                    'address': venue.get('address'),
                    'latitude': venue['location']['latitude'],
                    'longitude': venue['location']['longitude'],
                    'notes': venue.get('notes'),
                })


        # Load series, their events, and everything underneath

        for series_slug, series_dir in data['series'].items():

            series = series_dir['series']
            recurrence = series.get('recurrence')
            if recurrence:
                rrule_str = recurrence['rrule']
                rrule.rrulestr(rrule_str)  # check rrule syntax
                recurrence_attrs = {
                    'recurrence_rule': rrule_str,
                    'recurrence_scheme': recurrence['scheme'],
                    'recurrence_description_cs': recurrence['description']['cs'],
                    'recurrence_description_en': recurrence['description']['en'],
                }
            else:
                recurrence_attrs = {
                    'recurrence_rule': None,
                    'recurrence_scheme': None,
                    'recurrence_description_cs': None,
                    'recurrence_description_en': None,
                }
            insert(tables.Series, {
                'slug': series_slug,
                'name': series['name'],
                'home_city_slug': series.get('city'),
                'description_cs': series['description']['cs'],
                'description_en': series['description']['en'],
                'organizer_info': json.dumps(series.get('organizer-info', ())),
                **recurrence_attrs,
            })

            for event_slug, event in series_dir['events'].items():
                venue_slug = event.get('venue')
                city_slug = event['city']
                if venue_slug:
                    venue_id = venue_ids[city_slug, venue_slug]
                else:
                    venue_id = None

                start = make_full_datetime(event['start'])
                end = event.get('end')
                if end is None:
                    end = start.replace(hour=23, minute=59, second=59)
                event_id = insert(tables.Event, {
                    'name': event['name'],
                    'number': event.get('number'),
                    'topic': event.get('topic'),
                    'description': event.get('description'),
                    'date': start.date(),
                    'start_time': start.time(),
                    'end': end,
                    'all_day': event.get('all_day', False),
                    'series_slug': series_slug,
                    'city_slug': city_slug,
                    'venue_id': venue_id,
                    '_source': event['_source']
                })

                for i, talk in enumerate(event.get('talks', ())):
                    talk_id = insert(tables.Talk, {
                        'event_id': event_id,
                        'index': i,
                        'title': talk['title'],
                        'description': talk.get('description'),
                        'is_lightning': talk.get('lightning', False),
                    })

                    for i, speaker in enumerate(talk.get('speakers', ())):
                        assert speaker in speaker_slugs
                        insert(tables.TalkSpeaker, {
                            'talk_id': talk_id,
                            'index': i,
                            'speaker_slug': speaker,
                        })

                    for i, link in enumerate([
                            *({'talk': u} for u in talk.get('urls', ())),
                            *talk.get('coverage', {})]):
                        for kind, url in link.items():
                            insert(tables.TalkLink, {
                                'talk_id': talk_id,
                                'index': i,
                                'url': url,
                                'kind': kind,
                            })

                for i, url in enumerate(event.get('urls', ())):
                    insert(tables.EventLink, {
                        'event_id': event_id,
                        'index': i,
                        'url': url,
                    })