Русский
Русский
English
Статистика
Реклама

Немного SQL алхимии

О популярной библиотеке SQLAlchemy для работы с разными СУБД из Python было написано довольно много статей. Предлагаю вашему вниманию обзор и сравнение запросов с использованием ORM и SQL подходов. Данное руководство будет интересно прежде всего начинающим разработчикам, поскольку позволяет быстро окунуться в создание и работу с SQLAlchemy, поскольку документация от разработчика SQLAlchemy на мой скромный взгляд тяжела для чтения.
image

Немного о себе: я также начинающий разработчик, прохожу обучение по курсу Python разработчик. Данный материал был составлен не в результате ДЗ, а в порядке саморазвития. Мой код может быть достаточно наивным, в связи с чем прошу не стесняться и свои замечания оставлять в комментариях. Если я вас еще не напугал, прошу под кат :)

Мы с вами разберем практический пример нормализации плоской таблицы, содержащей дублирующиеся данные, до состояния 3НФ (третьей нормальной формы).
Из вот такой таблицы:
Таблица с данными
image

сделаем вот такую БД:
Схема связей БД
image

Для нетерпеливых: код, готовый к запуску находится в этом репозитории. Интерактивная схема БД здесь. Шпаргалка по составлению ORM запросов находится в конце статьи.

Договоримся, что в тексте статьи мы будем использовать слово Таблица вместо Отношение, и слово Поле вместо Аттрибута. По заданию нам надо таблицу с музыкальными файлами поместить в БД, при этом устранив избыточность данных. В исходной таблице (формат CSV) имеются следующие поля (track, genre, musician, album, length, album_year, collection, collection_year). Связи между ними такие:
каждый музыкант может петь в нескольких жанрах, как и в одном жанре могут выступать несколько музыкантов (отношение многие ко многим).
в создании альбома могут участвовать один или несколько музыкантов (отношение многие ко многим).
трек принадлежит только одному альбому (отношение один ко многим)
треки могут в ходить в состав нескольких сборников (отношение многие ко многим)
трек может не входить ни в одну в коллекцию.

Для упрощения предположим что названия жанров, имена музыкантов, названия альбомов и коллекций не повторяются. Названия треков могут повторяться. В БД мы запроектировали 8 таблиц:
genres (жанры)
genres_musicians (промежуточная таблица)
musicians (музыканты)
albums_musicians (промежуточная таблица)
albums (альбомы)
tracks (треки)
collections_tracks (промежуточная таблица)
collections (коллекции)
* данная схема тестовая, взята из одного из ДЗ, в ней есть некоторые недостатки например нет связи треков с музыкантом, а также трека с жанром. Но для обучения это несущественно, и мы опустим этот недостаток.

Для теста я создал две БД на локальном Postgres: TestSQL и TestORM, доступ к ним: логин и пароль test. Давайте наконец писать код!

Создаем подключения и таблицы


Создаем подключения к БД
* код функций read_data и clear_db есть в репозитории.
DSN_SQL = 'postgresql://test:test@localhost:5432/TestSQL'    DSN_ORM = 'postgresql://test:test@localhost:5432/TestORM'    # Прочитаем данные из CSV в память в виде словаря.    DATA = read_data('data/demo-data.csv')    print('Connecting to DB\'s...')    # Мы будем работать с сессиями, поэтому создадим их раздельными для каждой БД.    engine_orm = sa.create_engine(DSN_ORM)    Session_ORM = sessionmaker(bind=engine_orm)    session_orm = Session_ORM()    engine_sql = sa.create_engine(DSN_SQL)    Session_SQL = sessionmaker(bind=engine_sql)    session_sql = Session_SQL()    print('Clearing the bases...')    # Удаляем все таблицы из БД перед заливкой содержимого. Используем только для учебы.    clear_db(sa, engine_sql)    clear_db(sa, engine_orm)


Создаем таблицы классическим путем через SQL
* код функции read_query есть в репозитории. Тексты запросов также есть в репозитории.
print('\nPreparing data for SQL job...')    print('Creating empty tables...')    session_sql.execute(read_query('queries/create-tables.sql'))    session_sql.commit()    print('\nAdding musicians...')    query = read_query('queries/insert-musicians.sql')    res = session_sql.execute(query.format(','.join({f"('{x['musician']}')" for x in DATA})))    print(f'Inserted {res.rowcount} musicians.')    print('\nAdding genres...')    query = read_query('queries/insert-genres.sql')    res = session_sql.execute(query.format(','.join({f"('{x['genre']}')" for x in DATA})))    print(f'Inserted {res.rowcount} genres.')    print('\nLinking musicians with genres...')    # assume that musician + genre has to be unique    genres_musicians = {x['musician'] + x['genre']: [x['musician'], x['genre']] for x in DATA}    query = read_query('queries/insert-genre-musician.sql')    # this query can't be run in batch, so execute one by one    res = 0    for key, value in genres_musicians.items():        res += session_sql.execute(query.format(value[1], value[0])).rowcount    print(f'Inserted {res} connections.')    print('\nAdding albums...')    # assume that albums has to be unique    albums = {x['album']: x['album_year'] for x in DATA}    query = read_query('queries/insert-albums.sql')    res = session_sql.execute(query.format(','.join({f"('{x}', '{y}')" for x, y in albums.items()})))    print(f'Inserted {res.rowcount} albums.')    print('\nLinking musicians with albums...')    # assume that musicians + album has to be unique    albums_musicians = {x['musician'] + x['album']: [x['musician'], x['album']] for x in DATA}    query = read_query('queries/insert-album-musician.sql')    # this query can't be run in batch, so execute one by one    res = 0    for key, values in albums_musicians.items():        res += session_sql.execute(query.format(values[1], values[0])).rowcount    print(f'Inserted {res} connections.')    print('\nAdding tracks...')    query = read_query('queries/insert-track.sql')    # this query can't be run in batch, so execute one by one    res = 0    for item in DATA:        res += session_sql.execute(query.format(item['track'], item['length'], item['album'])).rowcount    print(f'Inserted {res} tracks.')    print('\nAdding collections...')    query = read_query('queries/insert-collections.sql')    res = session_sql.execute(query.format(','.join({f"('{x['collection']}', {x['collection_year']})" for x in DATA if x['collection'] and x['collection_year']})))    print(f'Inserted {res.rowcount} collections.')    print('\nLinking collections with tracks...')    query = read_query('queries/insert-collection-track.sql')    # this query can't be run in batch, so execute one by one    res = 0    for item in DATA:        res += session_sql.execute(query.format(item['collection'], item['track'])).rowcount    print(f'Inserted {res} connections.')    session_sql.commit()



По сути мы создаем пакетами справочники (жанры, музыкантов, альбомы, коллекции), а затем в цикле связываем остальные данные и строим вручную промежуточные таблицы. Запускаем код и видим что БД создалась. Главное не забыть вызывать commit() у сессии.

Теперь пробуем сделать тоже самое, но с применением ORM подхода. Для того чтобы работать с ORM нам надо описать классы данных. Для этого мы создадим 8 классов (по одному на кажую таблицу).
Заголовок спойлера
Код скрипта объявления классов.
Base = declarative_base()class Genre(Base):    __tablename__ = 'genres'    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)    name = sa.Column(sa.String(20), unique=True)    # Объявляется отношение многие ко многим к Musician через промежуточную таблицу genres_musicians    musicians = relationship("Musician", secondary='genres_musicians')class Musician(Base):    __tablename__ = 'musicians'    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)    name = sa.Column(sa.String(50), unique=True)    # Объявляется отношение многие ко многим к Genre через промежуточную таблицу genres_musicians    genres = relationship("Genre", secondary='genres_musicians')    # Объявляется отношение многие ко многим к Album через промежуточную таблицу albums_musicians    albums = relationship("Album", secondary='albums_musicians')class GenreMusician(Base):    __tablename__ = 'genres_musicians'    # здесь мы объявляем составной ключ, состоящий из двух полей    __table_args__ = (PrimaryKeyConstraint('genre_id', 'musician_id'),)    # В промежуточной таблице явно указываются что следующие поля являются внешними ключами    genre_id = sa.Column(sa.Integer, sa.ForeignKey('genres.id'))    musician_id = sa.Column(sa.Integer, sa.ForeignKey('musicians.id'))class Album(Base):    __tablename__ = 'albums'    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)    name = sa.Column(sa.String(50), unique=True)    year = sa.Column(sa.Integer)    # Объявляется отношение многие ко многим к Musician через промежуточную таблицу albums_musicians    musicians = relationship("Musician", secondary='albums_musicians')class AlbumMusician(Base):    __tablename__ = 'albums_musicians'    # здесь мы объявляем составной ключ, состоящий из двух полей    __table_args__ = (PrimaryKeyConstraint('album_id', 'musician_id'),)    # В промежуточной таблице явно указываются что следующие поля являются внешними ключами    album_id = sa.Column(sa.Integer, sa.ForeignKey('albums.id'))    musician_id = sa.Column(sa.Integer, sa.ForeignKey('musicians.id'))class Track(Base):    __tablename__ = 'tracks'    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)    name = sa.Column(sa.String(100))    length = sa.Column(sa.Integer)    # Поскольку по полю album_id идет связь один ко многим, достаточно указать чей это внешний ключ    album_id = sa.Column(sa.Integer, ForeignKey('albums.id'))    # Объявляется отношение многие ко многим к Collection через промежуточную таблицу collections_tracks    collections = relationship("Collection", secondary='collections_tracks')class Collection(Base):    __tablename__ = 'collections'    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)    name = sa.Column(sa.String(50))    year = sa.Column(sa.Integer)    # Объявляется отношение многие ко многим к Track через промежуточную таблицу collections_tracks    tracks = relationship("Track", secondary='collections_tracks')class CollectionTrack(Base):    __tablename__ = 'collections_tracks'    # здесь мы объявляем составной ключ, состоящий из двух полей    __table_args__ = (PrimaryKeyConstraint('collection_id', 'track_id'),)    # В промежуточной таблице явно указываются что следующие поля являются внешними ключами    collection_id = sa.Column(sa.Integer, sa.ForeignKey('collections.id'))    track_id = sa.Column(sa.Integer, sa.ForeignKey('tracks.id'))


Нам достаточно создать базовый класс Base для декларативного стиля описания таблиц и унаследоваться от него. Вся магия отношений между таблицами заключается в правильном использовании relationship и ForeignKey. В коде указано в каком случае мы создаем какое отношение. Главное не забыть прописать relationship с обеих сторон связи многие ко многим.

Непосредственно создание таблиц с использованием ORM подхода происходит путем вызова:
Base.metadata.create_all(engine_orm)

И вот тут включается магия, буквально все классы, объявленные в коде через наследование от Base становятся таблицами. Сходу я не увидел как указать экземпляры каких классов надо создать сейчас, а какие отложить для создания позже (например в другой БД). Наверняка такой способ есть, но в нашем коде все классы-наследники Base инстанцируются одномоментно, имейте это ввиду.


Наполнение таблиц при использовании ORM подхода выглядит так:
Заполнение таблиц данными через ORM
    print('\nPreparing data for ORM job...')    for item in DATA:        # создаем жанры        genre = session_orm.query(Genre).filter_by(name=item['genre']).scalar()        if not genre:            genre = Genre(name=item['genre'])        session_orm.add(genre)        # создаем музыкантов        musician = session_orm.query(Musician).filter_by(name=item['musician']).scalar()        if not musician:            musician = Musician(name=item['musician'])        musician.genres.append(genre)        session_orm.add(musician)        # создаем альбомы        album = session_orm.query(Album).filter_by(name=item['album']).scalar()        if not album:            album = Album(name=item['album'], year=item['album_year'])        album.musicians.append(musician)        session_orm.add(album)        # создаем треки        # проверяем на существование трек не только по имени но и по альбому, так как имя трека по условию может        # быть не уникально        track = session_orm.query(Track).join(Album).filter(and_(Track.name == item['track'],                                                                 Album.name == item['album'])).scalar()        if not track:            track = Track(name=item['track'], length=item['length'])        track.album_id = album.id        session_orm.add(track)        # создаем коллекции, учитываем что трек может не входить ни в одну в коллекцию        if item['collection']:            collection = session_orm.query(Collection).filter_by(name=item['collection']).scalar()            if not collection:                collection = Collection(name=item['collection'], year=item['collection_year'])            collection.tracks.append(track)            session_orm.add(collection)        session_orm.commit()


Приходится поштучно заполнять каждый справочник (жанры, музыканты, альбомы, коллекции). В случае SQL запросов можно было генерировать пакетное добавление данных. Зато промежуточные таблицы в явном виде не надо создавать, за это отвечают внутренние механизмы SQLAlchemy.

Запросы к базам



По заданию нам надо написать 15 запросов используя обе техники SQL и ORM. Вот список поставленных вопросов в порядке возрастания сложности:
1. название и год выхода альбомов, вышедших в 2018 году;
2. название и продолжительность самого длительного трека;
3. название треков, продолжительность которых не менее 3,5 минуты;
4. названия сборников, вышедших в период с 2018 по 2020 год включительно;
5. исполнители, чье имя состоит из 1 слова;
6. название треков, которые содержат слово мой/my.
7. количество исполнителей в каждом жанре;
8. количество треков, вошедших в альбомы 2019-2020 годов;
9. средняя продолжительность треков по каждому альбому;
10. все исполнители, которые не выпустили альбомы в 2020 году;
11. названия сборников, в которых присутствует конкретный исполнитель;
12. название альбомов, в которых присутствуют исполнители более 1 жанра;
13. наименование треков, которые не входят в сборники;
14. исполнителя(-ей), написавшего самый короткий по продолжительности трек (теоретически таких треков может быть несколько);
15. название альбомов, содержащих наименьшее количество треков.
Как видите, вышеизложенные вопросы подразумевают как простую выборку так и с объединением таблиц, а также использование агрегатных функций.

Ниже предоставлены решения по каждому из 15 запросов в двух вариантах (используя SQL и ORM). В коде запросы идут парами, чтобы показать идентичность результатов на выводе в консоль.
Запросы и их краткое описание
    print('\n1. All albums from 2018:')    query = read_query('queries/select-album-by-year.sql').format(2018)    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Album).filter_by(year=2018):        print(item.name)    print('\n2. Longest track:')    query = read_query('queries/select-longest-track.sql')    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Track).order_by(Track.length.desc()).slice(0, 1):        print(f'{item.name}, {item.length}')    print('\n3. Tracks with length not less 3.5min:')    query = read_query('queries/select-tracks-over-length.sql').format(310)    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Track).filter(310 <= Track.length).order_by(Track.length.desc()):        print(f'{item.name}, {item.length}')    print('\n4. Collections between 2018 and 2020 years (inclusive):')    query = read_query('queries/select-collections-by-year.sql').format(2018, 2020)    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Collection).filter(2018 <= Collection.year,                                                     Collection.year <= 2020):        print(item.name)    print('\n5. Musicians with name that contains not more 1 word:')    query = read_query('queries/select-musicians-by-name.sql')    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Musician).filter(Musician.name.notlike('%% %%')):        print(item.name)    print('\n6. Tracks that contains word "me" in name:')    query = read_query('queries/select-tracks-by-name.sql').format('me')    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Track).filter(Track.name.like('%%me%%')):        print(item.name)    print('Ok, let\'s start serious work')    print('\n7. How many musicians plays in each genres:')    query = read_query('queries/count-musicians-by-genres.sql')    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Genre).join(Genre.musicians).order_by(func.count(Musician.id).desc()).group_by(            Genre.id):        print(f'{item.name}, {len(item.musicians)}')    print('\n8. How many tracks in all albums 2019-2020:')    query = read_query('queries/count-tracks-in-albums-by-year.sql').format(2019, 2020)    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Track, Album).join(Album).filter(2019 <= Album.year, Album.year <= 2020):        print(f'{item[0].name}, {item[1].year}')    print('\n9. Average track length in each album:')    query = read_query('queries/count-average-tracks-by-album.sql')    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Album, func.avg(Track.length)).join(Track).order_by(func.avg(Track.length)).group_by(            Album.id):        print(f'{item[0].name}, {item[1]}')    print('\n10. All musicians that have no albums in 2020:')    query = read_query('queries/select-musicians-by-album-year.sql').format(2020)    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Musician).join(Musician.albums).filter(Album.year != 2020).order_by(            Musician.name.asc()):        print(f'{item.name}')    print('\n11. All collections with musician Steve:')    query = read_query('queries/select-collection-by-musician.sql').format('Steve')    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Collection).join(Collection.tracks).join(Album).join(Album.musicians).filter(            Musician.name == 'Steve').order_by(Collection.name):        print(f'{item.name}')    print('\n12. Albums with musicians that play in more than 1 genre:')    query = read_query('queries/select-albums-by-genres.sql').format(1)    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    for item in session_orm.query(Album).join(Album.musicians).join(Musician.genres).having(func.count(distinct(            Genre.name)) > 1).group_by(Album.id).order_by(Album.name):        print(f'{item.name}')    print('\n13. Tracks that not included in any collections:')    query = read_query('queries/select-absence-tracks-in-collections.sql')    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    # Important! Despite the warning, following expression does not work: "Collection.id is None"    for item in session_orm.query(Track).outerjoin(Track.collections).filter(Collection.id == None):        print(f'{item.name}')    print('\n14. Musicians with shortest track length:')    query = read_query('queries/select-musicians-min-track-length.sql')    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    subquery = session_orm.query(func.min(Track.length))    for item in session_orm.query(Musician, Track.length).join(Musician.albums).join(Track).group_by(            Musician.id, Track.length).having(Track.length == subquery).order_by(Musician.name):        print(f'{item[0].name}, {item[1]}')    print('\n15. Albums with minimum number of tracks:')    query = read_query('queries/select-albums-with-minimum-tracks.sql')    print(f'############################\n{query}\n############################')    print('----SQL way---')    res = session_sql.execute(query)    print(*res, sep='\n')    print('----ORM way----')    subquery1 = session_orm.query(func.count(Track.id)).group_by(Track.album_id).order_by(func.count(Track.id)).limit(1)    subquery2 = session_orm.query(Track.album_id).group_by(Track.album_id).having(func.count(Track.id) == subquery1)    for item in session_orm.query(Album).join(Track).filter(Track.album_id.in_(subquery2)).order_by(Album.name):        print(f'{item.name}')


Для тех, кому не хочется погружаться в чтение кода, я попробую показать как выглядит сырой SQL и его альтернатива в ORM выражении, поехали!

Шпаргалка по сопоставлению SQL запросов и ORM выражений



1. название и год выхода альбомов, вышедших в 2018 году:
SQL
select namefrom albumswhere year=2018

ORM
session_orm.query(Album).filter_by(year=2018)


2. название и продолжительность самого длительного трека:
SQL
select name, lengthfrom tracksorder by length DESClimit 1

ORM
session_orm.query(Track).order_by(Track.length.desc()).slice(0, 1)


3. название треков, продолжительность которых не менее 3,5 минуты:
SQL
select name, lengthfrom trackswhere length >= 310order by length DESC

ORM
session_orm.query(Track).filter(310 <= Track.length).order_by(Track.length.desc())


4. названия сборников, вышедших в период с 2018 по 2020 год включительно:
SQL
select namefrom collectionswhere (year >= 2018) and (year <= 2020)

ORM
session_orm.query(Collection).filter(2018 <= Collection.year, Collection.year <= 2020)

* обратите внимание что здесь и далее фильтрация задается уже с использованием filter, а не с использованием filter_by.

5. исполнители, чье имя состоит из 1 слова:
SQL
select namefrom musicianswhere not name like '%% %%'

ORM
session_orm.query(Musician).filter(Musician.name.notlike('%% %%'))


6. название треков, которые содержат слово мой/my:
SQL
select namefrom trackswhere name like '%%me%%'

ORM
session_orm.query(Track).filter(Track.name.like('%%me%%'))


7. количество исполнителей в каждом жанре:
SQL
select g.name, count(m.name)from genres as gleft join genres_musicians as gm on g.id = gm.genre_idleft join musicians as m on gm.musician_id = m.idgroup by g.nameorder by count(m.name) DESC

ORM
session_orm.query(Genre).join(Genre.musicians).order_by(func.count(Musician.id).desc()).group_by(Genre.id)


8. количество треков, вошедших в альбомы 2019-2020 годов:
SQL
select t.name, a.yearfrom albums as aleft join tracks as t on t.album_id = a.idwhere (a.year >= 2019) and (a.year <= 2020)

ORM
session_orm.query(Track, Album).join(Album).filter(2019 <= Album.year, Album.year <= 2020)


9. средняя продолжительность треков по каждому альбому:
SQL
select a.name, AVG(t.length)from albums as aleft join tracks as t on t.album_id = a.idgroup by a.nameorder by AVG(t.length)

ORM
session_orm.query(Album, func.avg(Track.length)).join(Track).order_by(func.avg(Track.length)).group_by(Album.id)


10. все исполнители, которые не выпустили альбомы в 2020 году:
SQL
select distinct m.namefrom musicians as mleft join albums_musicians as am on m.id = am.musician_idleft join albums as a on a.id = am.album_idwhere not a.year = 2020order by m.name

ORM
session_orm.query(Musician).join(Musician.albums).filter(Album.year != 2020).order_by(Musician.name.asc())


11. названия сборников, в которых присутствует конкретный исполнитель (Steve):
SQL
select distinct c.namefrom collections as cleft join collections_tracks as ct on c.id = ct.collection_idleft join tracks as t on t.id = ct.track_idleft join albums as a on a.id = t.album_idleft join albums_musicians as am on am.album_id = a.idleft join musicians as m on m.id = am.musician_idwhere m.name like '%%Steve%%'order by c.name

ORM
session_orm.query(Collection).join(Collection.tracks).join(Album).join(Album.musicians).filter(Musician.name == 'Steve').order_by(Collection.name)


12. название альбомов, в которых присутствуют исполнители более 1 жанра:
SQL
select a.namefrom albums as aleft join albums_musicians as am on a.id = am.album_idleft join musicians as m on m.id = am.musician_idleft join genres_musicians as gm on m.id = gm.musician_idleft join genres as g on g.id = gm.genre_idgroup by a.namehaving count(distinct g.name) > 1order by a.name

ORM
session_orm.query(Album).join(Album.musicians).join(Musician.genres).having(func.count(distinct(Genre.name)) > 1).group_by(Album.id).order_by(Album.name)


13. наименование треков, которые не входят в сборники:
SQL
select t.namefrom tracks as tleft join collections_tracks as ct on t.id = ct.track_idwhere ct.track_id is null

ORM
session_orm.query(Track).outerjoin(Track.collections).filter(Collection.id == None)

* обратите внимание что несмотря на предупреждение в PyCharm надо именно так составлять условие фильтрации, если написать как предлагает IDE (Collection.id is None) то оно работать не будет.

14. исполнителя(-ей), написавшего самый короткий по продолжительности трек (теоретически таких треков может быть несколько):
SQL
select m.name, t.lengthfrom tracks as tleft join albums as a on a.id = t.album_idleft join albums_musicians as am on am.album_id = a.idleft join musicians as m on m.id = am.musician_idgroup by m.name, t.lengthhaving t.length = (select min(length) from tracks)order by m.name

ORM
subquery = session_orm.query(func.min(Track.length))session_orm.query(Musician, Track.length).join(Musician.albums).join(Track).group_by(Musician.id, Track.length).having(Track.length == subquery).order_by(Musician.name)


15. название альбомов, содержащих наименьшее количество треков:
SQL
select distinct a.namefrom albums as aleft join tracks as t on t.album_id = a.idwhere t.album_id in (    select album_id    from tracks    group by album_id    having count(id) = (        select count(id)        from tracks        group by album_id        order by count        limit 1    ))order by a.name

ORM
subquery1 = session_orm.query(func.count(Track.id)).group_by(Track.album_id).order_by(func.count(Track.id)).limit(1)subquery2 = session_orm.query(Track.album_id).group_by(Track.album_id).having(func.count(Track.id) == subquery1)session_orm.query(Album).join(Track).filter(Track.album_id.in_(subquery2)).order_by(Album.name)


Как видите, вышеизложенные вопросы подразумевают как простую выборку так и с объединением таблиц, а также использование агрегатных функций и подзапросов. Все это реально сделать с SQLAlchemy как в режиме SQL так и в режиме ORM. Разноообразие операторов и методов позволяет выполнить запрос наверное любой сложности.

Надеюсь данный материал поможет избавиться начинающим быстро и эффективно начать составлять запросы.
Источник: habr.com
К списку статей
Опубликовано: 06.12.2020 02:07:34
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Python

Sql

Python 3

Sqlalchemy

Orm

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru