Dúvida sobre SQLite

grand0rbiter

Giga Chad
Registrado
UPDATE: RESOLVIDO

To criando um aplicativo python que guarda as músicas que ouço em um banco sqlite.
Criei três tabelas:

Python:
    sql_create_tracks_table = """ CREATE TABLE IF NOT EXISTS tracks (
                                      id integer PRIMARY KEY,
                                      title text NOT NULL,
                                      album_id integer,
                                      plays integer DEFAULT 0,
                                      FOREIGN KEY (album_id) REFERENCES albums (id)
                                  );"""

    sql_create_albums_table = """ CREATE TABLE IF NOT EXISTS albums (
                                      id integer PRIMARY KEY,
                                      title text NOT NULL,
                                      artist_id integer,
                                      plays integer DEFAULT 0,
                                      FOREIGN KEY (artist_id) REFERENCES artists (id)
                                  );"""

    sql_create_artists_table = """ CREATE TABLE IF NOT EXISTS artists (
                                       id integer PRIMARY KEY,
                                       name text NOT NULL,
                                       plays integer DEFAULT 0
                                   );"""

Como eu faço pro SQLite inserir o item caso ele não exista e caso ele exista, incrementar o plays com +1? Sei que tem o INSERT OR REPLACE, mas não entendo bulhufas de banco de dados e to perdidão nas minhas buscas.

Por enquanto as funções de inserir estão assim:

Python:
def create_tracks(conn, track):
    """ insert current song into the database """
    sql = """ INSERT INTO tracks(title, album_id, plays) VALUES(?,?,?) """
    cur = conn.cursor()
    cur.execute(sql, track)
    conn.commit()
    return cur.lastrowid


def create_albums(conn, album):
    """ insert or replace current album into the database """
    sql = """ INSERT INTO albums(title, artist_id, plays) VALUES(?,?,?) """
    cur = conn.cursor()
    cur.execute(sql, album)
    conn.commit()
    return cur.lastrowid


def create_artists(conn, artist):
    """ insert current artist into the database """
    sql = """ INSERT INTO artists(name, plays) VALUES(?,?) """
    cur = conn.cursor()
    cur.execute(sql, artist)
    conn.commit()
    return cur.lastrowid
--- Post duplo é unido automaticamente: ---

Consegui, migos.

Python:
def create_tracks(conn, track):
    """ insert current song into the database """
    sql = """ INSERT INTO tracks(title, album_name, plays) VALUES(?,?,?) ON CONFLICT(title) DO UPDATE SET plays=plays+1"""
    cur = conn.cursor()
    cur.execute(sql, track)
    conn.commit()
    return cur.lastrowid


def create_albums(conn, album):
    """ insert or replace current album into the database """
    sql = """ INSERT INTO albums(title, artist_name, plays) VALUES(?,?,?) ON CONFLICT(title) DO UPDATE SET plays=plays+1"""
    cur = conn.cursor()
    cur.execute(sql, album)
    conn.commit()
    return cur.lastrowid


def create_artists(conn, artist):
    """ insert current artist into the database """
    sql = """ INSERT INTO artists(name, plays) VALUES(?, 1) ON CONFLICT(name) DO UPDATE SET plays=plays+1"""
    cur = conn.cursor()
    cur.execute(sql, artist)
    conn.commit()
    return cur.lastrowid

Tirei o id integer PRIMARY KEY das tabelas e deixei os nomes do artista, musica e album como PRIMARY KEY.
 
Última edição:
Só uma sugestão, tu não precisa colocar plays na tabela album e nem artista, pois a partir do momento que tu insere na tabela tracks tu já tem essa informação.

Alem disso tu poderia tirar o play da tabela tracks e colocar um campo do tipo date time, dessa forma alem de saber quantas vezes a musica foi tocada, tu ainda tem o histórico com a data e hora.
 
Só uma sugestão, tu não precisa colocar plays na tabela album e nem artista, pois a partir do momento que tu insere na tabela tracks tu já tem essa informação.

Alem disso tu poderia tirar o play da tabela tracks e colocar um campo do tipo date time, dessa forma alem de saber quantas vezes a musica foi tocada, tu ainda tem o histórico com a data e hora.
Sou muito newbie em banco de dados então to perdidasso, mas eu fiz exatamente isso agora. Tirei o plays do artista e album e deixei só no tracks. Só to tendo dificuldade em como compilar um Top 10 artistas mais tocados.

SELECT artist_name, plays FROM tracks GROUP BY artist_name ORDER BY plays DESC LIMIT 10

O código acima não funciona pois um artista que foi tocado 9 vezes, porém 6 vezes em um cd e 3 vezes em outro, mostra só que foi tocado 6 vezes.

UPDATE: usei SUM(plays) e parece que agora funcionou

PS 1: acho que vou partir pra sua sugestão de date time, mas só utilizar data. Hora não faço questão, é pra ser algo mais básico. Data em SQL seria guardada como? Uma string tipo 20211012?

PS 2: conhece algum curso de SQL pra aprender como banco de dados funciona? Desde como construir um usando boas práticas até os comandos?
 
Última edição:
Não saberia te indicar um curso , mas qualquer um básico já vai te ajudar muito.

No teu caso tu precisa unir a tabela tracks com a tabela artist, mas pra isso é preciso passar pelo album.

então ficaria assim:

select * from tracks t, albums a, artists s
where t.album_id = a.album_id
and a.artist_id = s.artist_id

Essa consulta vai retornar todos os campos das 3 tabelas, dai tu pode escolher o campo que quiser colocando o alias tabela junto com o nome do campo (t.title).

Agora que tens todos os dados da track, album e artista tu pode começar a filtrar ou fazer calculos em cima deles.

Tu poderia fazer a essa consulta "SELECT artist_name, plays FROM tracks GROUP BY artist_name ORDER BY plays DESC LIMIT 10" do seguinte modo

select s.artist_name, sum(t.plays) soma from tracks t, albums a, artists s
where t.album_id = a.album_id
and a.artist_id = s.artist_id
group by ar.artist_name order by 2 desc

Agora em relação a essa consulta (apesar de achar que ela esta errada).

SELECT artist_name, plays FROM tracks GROUP BY artist_name ORDER BY plays DESC LIMIT 10

Tu precisa colocar um sum(plays) pra dizer que vais agrupar por artista e somar a coluna play, dessa forma ele te dá o total de musica tocada por artista.

SELECT artist_name, sum(plays) soma FROM tracks GROUP BY artist_name ORDER BY plays DESC LIMIT 10
 

Users who are viewing this thread

Voltar
Topo