103 lines
3.3 KiB
Python
103 lines
3.3 KiB
Python
import os
|
|
import sqlite3
|
|
from xdg.BaseDirectory import xdg_data_home
|
|
|
|
|
|
class Sqlite:
|
|
db_directory = os.path.join(xdg_data_home, 'lonestar.fm')
|
|
if not os.path.exists(db_directory):
|
|
os.makedirs(db_directory)
|
|
db = os.path.join(db_directory, 'scrobbles.db')
|
|
conn = sqlite3.connect(db)
|
|
cur = conn.cursor()
|
|
|
|
def close(self):
|
|
self.conn.close()
|
|
|
|
|
|
class Sync(Sqlite):
|
|
def __init__(self):
|
|
table = '''CREATE TABLE IF NOT EXISTS Tracks (
|
|
Date TEXT, Artist TEXT, Title TEXT, Album TEXT,
|
|
Duration INT, Timestamp INT, mbid TEXT)'''
|
|
self.cur.execute(table)
|
|
self.conn.commit()
|
|
|
|
def write_into(self,
|
|
date, artist, title, album,
|
|
duration, timestamp, mbid):
|
|
self.cur.execute(
|
|
'SELECT * FROM Tracks WHERE (Title=? AND Timestamp=?)',
|
|
(title, timestamp))
|
|
entry = self.cur.fetchone()
|
|
if not entry:
|
|
insert_query = '''INSERT INTO Tracks(
|
|
Date, Artist, Title, Album,
|
|
Duration, Timestamp, mbid) VALUES (?,?,?,?,?,?,?)'''
|
|
self.cur.execute(
|
|
insert_query,
|
|
(date, artist, title, album, duration, timestamp, mbid))
|
|
self.conn.commit()
|
|
else:
|
|
print("Item already exists!")
|
|
|
|
def select_all(self):
|
|
self.cur.execute('SELECT * FROM Tracks')
|
|
result = self.cur.fetchall()
|
|
return result
|
|
|
|
def drop_track(self, title, timestamp):
|
|
query = 'DELETE FROM Tracks WHERE (Title=? AND Timestamp=?)'
|
|
self.cur.execute(query, (title, timestamp))
|
|
self.conn.commit()
|
|
return True
|
|
|
|
|
|
class Compare(Sqlite):
|
|
def __init__(self):
|
|
tables = ['LastFM', 'LoneStar']
|
|
for table in tables:
|
|
self.create_table(table)
|
|
|
|
def create_table(self, table_name):
|
|
table = f'''CREATE TABLE IF NOT EXISTS {table_name} (
|
|
Artist TEXT, Title TEXT, Album TEXT, Timestamp INT)'''
|
|
self.cur.execute(table)
|
|
self.conn.commit()
|
|
|
|
def write_into(self, table_name, artist, title, album, timestamp):
|
|
self.cur.execute(
|
|
f'SELECT * FROM {table_name} WHERE (Title=? AND Timestamp=?)',
|
|
(title, timestamp))
|
|
entry = self.cur.fetchone()
|
|
if not entry:
|
|
query = f'''INSERT INTO {table_name} (
|
|
Artist, Title, Album, Timestamp) VALUES (?,?,?,?)'''
|
|
self.cur.execute(query, (artist, title, album, timestamp))
|
|
self.conn.commit()
|
|
else:
|
|
print("Item already exists!")
|
|
|
|
def compare(self):
|
|
# query = 'SELECT * FROM Lastfm EXCEPT SELECT * FROM Lonestar'
|
|
query = '''
|
|
CREATE TABLE Difference AS
|
|
SELECT * FROM LastFM
|
|
EXCEPT
|
|
SELECT * FROM LoneStar
|
|
'''
|
|
self.cur.execute(query)
|
|
self.conn.commit()
|
|
# result = self.cur.fetchall()
|
|
# return result
|
|
|
|
def read_difference(self):
|
|
query = 'SELECT * FROM Difference'
|
|
self.cur.execute(query)
|
|
result = self.cur.fetchall()
|
|
return result
|
|
|
|
def drop_table(self, table_name):
|
|
self.cur.execute(f'DROP TABLE {table_name}')
|
|
self.conn.commit()
|
|
|