= 'https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite'
url = Path('chinook.sqlite')
path if not path.exists(): urlsave(url, path)
Core
= f"sqlite:///{path}" connstr
conn_db
conn_db (connstr, **kwargs)
Connect to DB using url.URL()
params and return filled MetaData
= conn_db(connstr) db
ReadOnlyColumnCollection.__dir__
ReadOnlyColumnCollection.__dir__ ()
Default dir() implementation.
MetaData.__dir__
MetaData.__dir__ ()
Default dir() implementation.
' '.join(db.tables)
'Album Artist Customer Employee Genre Invoice InvoiceLine Track MediaType Playlist PlaylistTrack'
= db.Album a
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)]
MetaData.sql
MetaData.sql (statement, *args, **kwargs)
Execute statement
string and return DataFrame
of results (if any)
Connection.sql
Connection.sql (statement, nm='Row', *args, **kwargs)
Execute statement
string and return results (if any)
CursorResult.tuples
CursorResult.tuples (nm='Row')
Get all results as named tuples
= db.sql('select AlbumId,Title from Album')
rs 0] rs[
Row(AlbumId=1, Title='For Those About To Rock We Salute You')
Table.get
Table.get (where=None, limit=None)
Select from table, optionally limited by where
and limit
clauses
'F'), limit=5) a.get(a.c.Title.startswith(
[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
MetaData.close
MetaData.close ()
Close the connection
db.close()