Core

url = 'https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite'
path = Path('chinook.sqlite')
if not path.exists(): urlsave(url, path)

connstr = f"sqlite:///{path}"

---

source

conn_db

 conn_db (connstr, **kwargs)

Connect to DB using url.URL() params and return filled MetaData

db = conn_db(connstr)

---

source

ReadOnlyColumnCollection.__dir__

 ReadOnlyColumnCollection.__dir__ ()

Default dir() implementation.

---

source

MetaData.__dir__

 MetaData.__dir__ ()

Default dir() implementation.

' '.join(db.tables)

'Album Artist Customer Employee Genre Invoice InvoiceLine Track MediaType Playlist PlaylistTrack'

a = db.Album

list(a.c)

[Column('AlbumId', INTEGER(), table=<Album>, primary_key=True, nullable=False),
 Column('Title', NVARCHAR(length=160), table=<Album>, nullable=False),
 Column('ArtistId', INTEGER(), ForeignKey('Artist.ArtistId'), table=<Album>, nullable=False)]

---

source

MetaData.sql

 MetaData.sql (statement, *args, **kwargs)

Execute statement string and return DataFrame of results (if any)

---

source

Connection.sql

 Connection.sql (statement, nm='Row', *args, **kwargs)

Execute statement string and return results (if any)

---

source

CursorResult.tuples

 CursorResult.tuples (nm='Row')

Get all results as named tuples

rs = db.sql('select AlbumId,Title from Album')
rs[0]

Row(AlbumId=1, Title='For Those About To Rock We Salute You')

---

source

Table.get

 Table.get (where=None, limit=None)

Select from table, optionally limited by where and limit clauses

a.get(a.c.Title.startswith('F'), limit=5)

[Row(AlbumId=1, Title='For Those About To Rock We Salute You', ArtistId=1),
 Row(AlbumId=7, Title='Facelift', ArtistId=5),
 Row(AlbumId=60, Title='Fireball', ArtistId=58),
 Row(AlbumId=88, Title='Faceless', ArtistId=87),
 Row(AlbumId=99, Title='Fear Of The Dark', ArtistId=90)]

This is the query that will run behind the scenes:

print(a.select().where(a.c.Title.startswith('F')).limit(5))

SELECT "Album"."AlbumId", "Album"."Title", "Album"."ArtistId" 
FROM "Album" 
WHERE ("Album"."Title" LIKE :Title_1 || '%')
 LIMIT :param_1

---

source

MetaData.close

 MetaData.close ()

Close the connection

db.close()