Sedikit tentang diri saya: Saya juga seorang pengembang pemula, saya mengambil kursus "Pengembang Python". Materi ini tidak disusun sebagai hasil dari penginderaan jauh, melainkan dalam urutan pengembangan diri. Kode saya mungkin cukup naif, oleh karena itu silakan tinggalkan komentar Anda di kolom komentar. Jika saya belum membuat Anda takut, tolong, di bawah luka :)
Kami akan menganalisis contoh praktis menormalkan tabel datar yang berisi data duplikat ke status 3NF ( bentuk normal ketiga ).
Dari tabel ini:
Tabel data
mari kita buat database seperti itu:
Diagram koneksi DB
Untuk yang tidak sabar: kode yang siap dijalankan ada di repositori ini . Skema database interaktif ada di sini . Lembar sontekan untuk menulis kueri ORM ada di akhir artikel.
Mari kita sepakati bahwa dalam teks artikel kita akan menggunakan kata "Tabel" bukan "Hubungan", dan kata "Bidang" bukan "Atribut". Dengan tugas, kita perlu menempatkan tabel dengan file musik di database, sambil menghilangkan redundansi data. Tabel asli (format CSV) berisi kolom-kolom berikut (trek, genre, musisi, album, durasi, album_year, collection, collection_year). Hubungan di antara mereka adalah sebagai berikut:
- setiap musisi dapat bernyanyi dalam beberapa genre, serta beberapa musisi dapat tampil dalam satu genre (hubungan banyak ke banyak)
- satu atau beberapa musisi dapat berpartisipasi dalam pembuatan album (hubungan banyak-ke-banyak)
- sebuah lagu hanya dimiliki oleh satu album (hubungan satu dengan banyak)
- trek dapat dimasukkan dalam beberapa koleksi (hubungan banyak-ke-banyak)
- trek mungkin tidak disertakan dalam koleksi apa pun.
Untuk mempermudah, katakanlah nama genre, nama artis, nama album, dan nama koleksi tidak diulang. Nama lagu dapat diulang. Kami telah mendesain 8 tabel dalam database:
- genre (genre)
- genres_musicians (tabel pementasan)
- musisi (musisi)
- album_musicians (tabel perantara)
- album (album)
- trek
- collections_tracks (tabel pementasan)
- collection (koleksi)
* Skema ini tes, diambil dari salah satu DZ, ada beberapa kekurangan - misalnya, tidak ada hubungan antara trek dan musisi, serta trek dengan genre. Tetapi ini tidak penting untuk belajar, dan kami akan menghilangkan kerugian ini.
Untuk pengujian, saya membuat dua database di Postgres lokal: "TestSQL" dan "TestORM", akses ke sana: uji login dan sandi. Akhirnya mari kita menulis beberapa kode!
Buat koneksi dan tabel
Buat koneksi ke database
* 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)
Kami membuat tabel dengan cara klasik menggunakan 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()
Faktanya, kami membuat direktori dalam paket (genre, musisi, album, koleksi), dan kemudian dalam satu lingkaran kami menautkan sisa data dan membuat tabel perantara secara manual. Jalankan kode dan lihat bahwa database telah dibuat. Hal utama adalah jangan lupa memanggil commit () pada sesi tersebut.
Sekarang kami mencoba melakukan hal yang sama, tetapi menggunakan pendekatan ORM. Untuk bekerja dengan ORM, kita perlu mendeskripsikan kelas data. Untuk ini, kami akan membuat 8 kelas (satu untuk setiap tabel).
Daftar kelas DB
.
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'))
Kita hanya perlu membuat basis kelas Basis untuk gaya deklaratif mendeskripsikan tabel dan mewarisinya. Semua keajaiban hubungan tabel terletak pada penggunaan hubungan dan ForeignKey yang benar. Kode menunjukkan dalam hal mana kita membuat hubungan yang mana. Hal utama adalah jangan lupa mendaftarkan hubungan di kedua sisi hubungan banyak-ke-banyak.
Membuat tabel secara langsung menggunakan pendekatan ORM dilakukan dengan memanggil:
Base.metadata.create_all(engine_orm)
Dan di sinilah keajaiban masuk, secara harfiah semua kelas yang dideklarasikan dalam kode melalui warisan dari Basis menjadi tabel. Langsung saja, saya tidak melihat bagaimana menentukan contoh kelas mana yang harus dibuat sekarang, dan mana yang harus ditunda untuk pembuatan nanti (misalnya, di database lain). Tentunya ada cara seperti itu, tetapi dalam kode kami semua kelas yang diwarisi dari Basis dibuat sekaligus, ingatlah ini.
Mengisi tabel menggunakan pendekatan ORM terlihat seperti ini:
Mengisi tabel dengan data melalui 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()
Anda harus mengisi setiap buku referensi (genre, musisi, album, koleksi) per bagian. Dalam kasus kueri SQL, dimungkinkan untuk menghasilkan penambahan data batch. Tetapi tabel perantara tidak perlu dibuat secara eksplisit; mekanisme internal SQLAlchemy bertanggung jawab untuk ini.
Kueri database
Pada penugasan, kita perlu menulis 15 query menggunakan teknik SQL dan ORM. Berikut adalah daftar pertanyaan yang diajukan untuk meningkatkan kesulitan:
- judul dan tahun rilis album yang dirilis pada 2018;
- judul dan durasi trek terpanjang;
- nama trek, durasinya tidak kurang dari 3,5 menit;
- judul koleksi yang diterbitkan dalam periode 2018-2020 inklusif;
- pemain yang namanya terdiri dari 1 kata;
- nama track yang mengandung kata "me".
- jumlah pemain di setiap genre;
- jumlah lagu yang termasuk dalam album 2019-2020;
- rata-rata panjang lagu untuk setiap album;
- semua artis yang belum merilis album pada tahun 2020;
- judul koleksi di mana artis tertentu hadir;
- nama album yang artisnya lebih dari 1 genre;
- nama trek yang tidak termasuk dalam koleksi;
- artis yang menulis trek terpendek (secara teoritis mungkin ada beberapa trek seperti itu);
- nama album yang berisi trek paling sedikit.
Seperti yang Anda lihat, pertanyaan di atas menyiratkan pemilihan sederhana dan tabel penggabungan, serta penggunaan fungsi agregat.
Di bawah ini disediakan solusi untuk masing-masing dari 15 kueri dalam dua opsi (menggunakan SQL dan ORM). Dalam kode, permintaan datang berpasangan untuk menunjukkan bahwa hasilnya identik pada keluaran konsol.
Permintaan dan deskripsi singkatnya
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----')
subquery = session_orm.query(distinct(Musician.name)).join(Musician.albums).filter(Album.year == 2020)
for item in session_orm.query(distinct(Musician.name)).filter(~Musician.name.in_(subquery)).order_by(
Musician.name.asc()):
print(f'{item}')
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}')
Bagi mereka yang tidak ingin mendalami membaca kode, saya akan mencoba menunjukkan bagaimana SQL "mentah" dan alternatifnya terlihat seperti ekspresi ORM, ayo pergi!
Lembar cheat untuk mencocokkan kueri SQL dan ekspresi ORM
1. judul dan tahun rilis album 2018:
SQL
select name
from albums
where year=2018
ORM
session_orm.query(Album).filter_by(year=2018)
2. judul dan durasi trek terpanjang:
SQL
select name, length
from tracks
order by length DESC
limit 1
ORM
session_orm.query(Track).order_by(Track.length.desc()).slice(0, 1)
3. nama trek, durasinya tidak kurang dari 3,5 menit:
SQL
select name, length
from tracks
where length >= 310
order by length DESC
ORM
session_orm.query(Track).filter(310 <= Track.length).order_by(Track.length.desc())
4. nama koleksi yang diterbitkan pada periode 2018-2020 inklusif:
SQL
select name
from collections
where (year >= 2018) and (year <= 2020)
ORM
session_orm.query(Collection).filter(2018 <= Collection.year, Collection.year <= 2020)
* perhatikan bahwa selanjutnya, pemfilteran ditentukan menggunakan filter, dan tidak menggunakan filter_by.
5. pelaksana yang namanya terdiri dari 1 kata:
SQL
select name
from musicians
where not name like '%% %%'
ORM
session_orm.query(Musician).filter(Musician.name.notlike('%% %%'))
6. nama track yang mengandung kata "me":
SQL
select name
from tracks
where name like '%%me%%'
ORM
session_orm.query(Track).filter(Track.name.like('%%me%%'))
7. Jumlah pemain di setiap genre:
SQL
select g.name, count(m.name)
from genres as g
left join genres_musicians as gm on g.id = gm.genre_id
left join musicians as m on gm.musician_id = m.id
group by g.name
order by count(m.id) DESC
ORM
session_orm.query(Genre).join(Genre.musicians).order_by(func.count(Musician.id).desc()).group_by(Genre.id)
8. jumlah lagu yang termasuk dalam album 2019-2020:
SQL
select t.name, a.year
from albums as a
left join tracks as t on t.album_id = a.id
where (a.year >= 2019) and (a.year <= 2020)
ORM
session_orm.query(Track, Album).join(Album).filter(2019 <= Album.year, Album.year <= 2020)
9. rata-rata panjang lagu untuk setiap album:
SQL
select a.name, AVG(t.length)
from albums as a
left join tracks as t on t.album_id = a.id
group by a.name
order 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. semua artis yang belum merilis album pada tahun 2020:
SQL
select distinct m.name
from musicians as m
where m.name not in (
select distinct m.name
from musicians as m
left join albums_musicians as am on m.id = am.musician_id
left join albums as a on a.id = am.album_id
where a.year = 2020
)
order by m.name
ORM
subquery = session_orm.query(distinct(Musician.name)).join(Musician.albums).filter(Album.year == 2020)
session_orm.query(distinct(Musician.name)).filter(~Musician.name.in_(subquery)).order_by(Musician.name.asc())
11. nama kompilasi yang menampilkan artis tertentu (Steve):
SQL
select distinct c.name
from collections as c
left join collections_tracks as ct on c.id = ct.collection_id
left join tracks as t on t.id = ct.track_id
left join albums as a on a.id = t.album_id
left join albums_musicians as am on am.album_id = a.id
left join musicians as m on m.id = am.musician_id
where 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. nama album yang artisnya ada lebih dari 1 genre:
SQL
select a.name
from albums as a
left join albums_musicians as am on a.id = am.album_id
left join musicians as m on m.id = am.musician_id
left join genres_musicians as gm on m.id = gm.musician_id
left join genres as g on g.id = gm.genre_id
group by a.name
having count(distinct g.name) > 1
order 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. Nama trek yang tidak termasuk dalam koleksi:
SQL
select t.name
from tracks as t
left join collections_tracks as ct on t.id = ct.track_id
where ct.track_id is null
ORM
session_orm.query(Track).outerjoin(Track.collections).filter(Collection.id == None)
* perhatikan bahwa meskipun ada peringatan di PyCharm, kondisi pemfilteran harus dibuat dengan cara ini, jika Anda menulisnya seperti yang disarankan oleh IDE ("Collection.id is None"), maka itu tidak akan berfungsi.
14. artis yang menulis trek terpendek (secara teoritis mungkin ada beberapa trek seperti itu):
SQL
select m.name, t.length
from tracks as t
left join albums as a on a.id = t.album_id
left join albums_musicians as am on am.album_id = a.id
left join musicians as m on m.id = am.musician_id
group by m.name, t.length
having 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. nama album yang berisi track paling sedikit:
SQL
select distinct a.name
from albums as a
left join tracks as t on t.album_id = a.id
where 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)
Seperti yang Anda lihat, pertanyaan di atas menyiratkan pemilihan sederhana dan tabel penggabungan, serta penggunaan fungsi agregat dan subkueri. Semua ini dapat dilakukan dengan SQLAlchemy baik dalam mode SQL maupun dalam mode ORM. Beragam operator dan metode memungkinkan Anda menjalankan kueri dengan kerumitan apa pun.
Saya harap materi ini akan membantu pemula dengan cepat dan efisien mulai menulis pertanyaan.