Svw15NwP

1import xml.etree.ElementTree as ET
2import sqlite3
3
4dbConnection = sqlite3.connect('iTunesLibrary.sqlite') # create a Cursor object
5dbOpen = dbConnection.cursor() # call this object to execute() method to perform SQL commands. It is like open() a file as a FileHandler
6
7dbOpen.executescript('''
8DROP TABLE IF EXISTS Artist;
9DROP TABLE IF EXISTS Album;
10DROP TABLE IF EXISTS Genre;
11DROP TABLE IF EXISTS Track;
12
13CREATE TABLE Artist (
14    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
15    name TEXT UNIQUE
16);
17CREATE TABLE Album (
18    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
19    artist_id INTEGER,
20    title TEXT UNIQUE
21);
22
23CREATE TABLE Genre (
24    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
25    name TEXT UNIQUE
26);
27
28CREATE TABLE Track (
29   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
30   title TEXT UNIQUE,
31   album_id INTEGER,
32   genre_id INTEGER
33   );
34''')
35
36fileInput = input('Enter a xml file: ')
37if len(fileInput) < 1: fileInput = 'Library.xml'
38#print(fileInput)
39#print(type(fileInput))
40#print('====================================================================')
41
42fileParsed = ET.parse(fileInput)
43#print(fileParsed)
44#print(type(fileParsed))
45#print('====================================================================')
46
47# Create a list with number of items
48dataList = fileParsed.findall('dict/dict/dict')
49#print(dataList)
50#print(type(dataList))
51#print('Dict count:', len(dataList))
52#print('====================================================================')
53
54def lookup(list, key): #key is a string
55    #print(key)
56    index = 0
57    while True:
58        #print(list[index].tag)
59        if len(list) <= index:
60            break
61
62        #list[index] points to a list
63        if list[index].tag == 'key' and list[index].text == key:
64            #print(list[index].text, list[index + 1].text)
65            return list[index + 1].text
66
67        index += 1
68
69
70#artist = lookup (dataList[0], 'Artist') #E.G.
71#i = 4 #E.G.
72#print(dataList[0]) #E.G.
73#print(len(dataList[0])) #E.G.
74#print(dataList[0][i]) #E.G.
75#print(type(dataList[0][i])) #E.G.
76#print(dataList[0][i].text) #E.G.
77#print(dataList[0][i+1].text) #E.G.
78
79for list in dataList:
80    #print(type(list))
81    if lookup(list, 'Artist') is None or lookup(list, 'Album') is None or lookup(list, 'Genre') is None:
82        continue
83    #print(list)
84    title = lookup (list, 'Name')
85    artist = lookup (list, 'Artist')
86    album = lookup (list, 'Album')
87    genre = lookup (list, 'Genre')
88    #print(artist, album, genre)
89
90    #############################################################################################
91    ## IGNORE means that if we choose parametr 'UNIQUE' setting (line 15) when creating table, it ignores any repetions, otherwise it leads to Traceback
92    ## Notice that we let the database take care of keeping us from “double-inserting” a relationship by creating the table with a uniqueness constraint and then adding OR IGNORE to our INSERT statement.
93    dbOpen.execute('INSERT OR IGNORE INTO Artist (name) VALUES (?)', (artist, )) #(artist, ) is a tuple
94
95
96    ## 'fetch' only works binding with SELECT. Moreover, it is important to add 'WHERE" clause in otder to retreive all the values, otherwise it fetches only one value'
97    ## fetchone() method to retrieve a single matching row, or call fetchall() to get a list of the matching rows.
98    ## dbOpen.execute('SELECT name FROM Artist') #E.G. TRY IT
99    ## dbOpen.execute('SELECT name FROM Artist WHERE name = ?', (artist, )) #E.G. TRY IT
100    ## artist_name = dbOpen.fetchone() #E.G. fetch only works binding with SELECT. Moreover, it is important to add 'WHERE" clause in otder to retreive all the values, otherwise it fetches only one value'
101    #print(artist_name)
102    dbOpen.execute('SELECT id FROM Artist WHERE name = ?', (artist, ))
103    ## artist_id = dbOpen.fetchone() #E.G.
104    artist_id = dbOpen.fetchone()[0] # fetch us выбрать [] brakets use to extract 0 index value from TURPLE (TRY PRINTS BELOW)
105    #print(artist_id)
106    #print(type(artist_id))
107    #print('====================================================================')
108    #############################################################################################
109
110    dbOpen.execute('INSERT OR IGNORE INTO Album (artist_id, title) VALUES (?, ?)', (artist_id, album)) ## line 103 artist_id moves as an argument into the line 109
111    dbOpen.execute('SELECT id FROM Album WHERE title = ?', (album, ))
112    album_id = dbOpen.fetchone()[0]
113    #############################################################################################
114
115    dbOpen.execute('INSERT OR IGNORE INTO Genre (name) VALUES (?)', (genre, )) #(artist, ) is a tuple
116    dbOpen.execute('SELECT id FROM Genre WHERE name = ?', (genre, ))
117    genre_id = dbOpen.fetchone()[0]
118    #############################################################################################
119
120    dbOpen.execute('INSERT OR REPLACE INTO Track (title, album_id, genre_id) VALUES (?, ?, ?)', (title, album_id, genre_id))
121    dbOpen.execute('SELECT Track.title, Artist.name, Album.title, Genre.name FROM Track JOIN Artist JOIN Album JOIN Genre ON Album.artist_id = Artist.id and Track.album_id = Album.id and Track.genre_id = Genre.id ORDER BY Artist.name LIMIT 3')
122    data = dbOpen.fetchall()
123    print(data)
124    ## commit() method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connection
125    dbConnection.commit()