

In [5]:

    
%load_ext sql
%sql sqlite:///chinook.db









    



The sql extension is already loaded. To reload it, use:
  %reload_ext sql






    Out[5]:





'Connected: None@chinook.db'



In [2]:

    
import sqlite3



In [3]:

    
db = sqlite3.connect('chinook.db')

Задание 1

Написать функцию на языке Python, формирующую список всех исполнителей композиций жанра Рок. Список должен быть упорядочен в порядке убывания.



In [7]:

    
def task1():
    cursor = db.cursor()
    cursor.execute('''
    select distinct ar.Name
from tracks t 
  inner join albums al
    on t.albumid = al.albumid
  inner join artists ar
    on al.artistid = ar.artistid
  inner join genres g
    on t.genreid = g.genreid 
where g.name = 'Rock'
    ''')
    ar = cursor.fetchall()
    return [x[0] for x in  ar]



In [8]:

    
task1()









    Out[8]:





['AC/DC',
 'Accept',
 'Aerosmith',
 'Alanis Morissette',
 'Alice In Chains',
 'Audioslave',
 'Led Zeppelin',
 'Frank Zappa & Captain Beefheart',
 'Queen',
 'Kiss',
 'David Coverdale',
 'Deep Purple',
 'Santana',
 'Creedence Clearwater Revival',
 'Def Leppard',
 'Faith No More',
 'Foo Fighters',
 "Guns N' Roses",
 'Iron Maiden',
 'Jamiroquai',
 'Jimi Hendrix',
 'Joe Satriani',
 'Lenny Kravitz',
 'Marillion',
 'Men At Work',
 'Nirvana',
 'O Terço',
 'Ozzy Osbourne',
 'Page & Plant',
 "Paul D'Ianno",
 'Pearl Jam',
 'Pink Floyd',
 'R.E.M.',
 'Raul Seixas',
 'Red Hot Chili Peppers',
 'Rush',
 'Skank',
 'Soundgarden',
 'Stone Temple Pilots',
 'Terry Bozzio, Tony Levin & Steve Stevens',
 'The Cult',
 'The Doors',
 'The Police',
 'The Rolling Stones',
 'The Who',
 'U2',
 'Van Halen',
 'Velvet Revolver',
 'Dread Zeppelin',
 'Scorpions',
 'The Posies']

Задание 2

Написать функцию на языке Python, создающую таблицу Студентов Students(id, name, gpa). Ключ - id.



In [44]:

    
def task2():
    cursor=db.cursor()
    cursor.execute('''
    DROP TABLE IF EXISTS students''')
    cursor.execute('''
    CREATE TABLE Students(id INTEGERE PRIMARY KEY, name TEXT, gpa NUMBER(10,2))''')
    db.commit()



In [45]:

    
task2()

Проверим, что таблица создана



In [46]:

    
%%sql
select * 
from students









    



Done.






    Out[46]:





    
        id
        name
        gpa

Задание 3

Для созданной выше функции реализовть возможность добавления списка студентов вида [['Ivanov', 1.2], ['Petrov', 2.3]]. ID новых студентов должно начинаться с максимального ID в таблице + 1. (Например, если в таблице максимальный ID - 10, то у Петрова должно быть - 11, у Иванова - 12). Функция должна предполагать вставки списка любой ограниченной длины.

Получаем max(id) + 1



In [47]:

    
%%sql
select coalesce(max(id)+1, 1) as new_id from students









    



Done.






    Out[47]:





    
        new_id
    
    
        1



In [48]:

    
def task3(l_students):
    cursor = db.cursor()
    cursor.execute( '''
    SELECT COALESCE(MAX(ID)+1, 1) AS new_id FROM students''')
    new_id = cursor.fetchone()[0]
    for i, student in enumerate(l_students):
        cursor.execute('''
        INSERT INTO Students(id, name, gpa) VALUES(?,?,?)''', (new_id + i, student[0], student[1]))
        
    db.commit()



In [49]:

    
task3([['Ivanov', 3.2], ['Petrov', 4.2]])



In [50]:

    
%%sql
SELECT * 
FROM Students

Задание 4

Добавить таблицу Факультетов Faculties(fac_id, name). Для таблицы Students добавить новое поле fac_id с внещним ключом на таблицу факультетов.



In [51]:

    
def task4():
    cursor = db.cursor()
    cursor.execute('''DROP TABLE IF EXISTS faculties''')
    cursor.execute('''CREATE TABLE faculties(fac_id INTEGER PRIMARY KEY, name TEXT)''')
    cursor.execute('''ALTER TABLE students ADD fac_id INTEGER REFERENCES faculties(fac_id)''')
    db.commit()



In [52]:

    
task4()



In [53]:

    
%%sql 
select * 
from faculties









    



Done.






    Out[53]:





    
        fac_id
        name



In [54]:

    
%%sql 
select * 
from Students









    



Done.






    Out[54]:





    
        id
        name
        gpa
        fac_id
    
    
        1
        Ivanov
        3.2
        None
    
    
        2
        Petrov
        4.2
        None

Задание 5

Написать функцию, осуществляющую обновления всех факультетов у каждого студента. Функция должна выводить информацию о студенту, приглашение на вход для обновления факультета и обновление факультета. При возникновение вставки функция должна обрабатывать исключение, и продолжать работу.

Для начала добавим в таблицу факультетов пару записей



In [55]:

    
%%sql
INSERT INTO faculties(fac_id, name)
VALUES (1, 'IT'), (2, 'KIB'), (3, 'Math')









    



3 rows affected.






    Out[55]:





[]



In [56]:

    
%%sql 
select * 
from faculties



In [57]:

    
a = input('1 {}', '2')









    



---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-57-eff2ef7e1f37> in <module>()
----> 1 a = input('1 {}', '2')

TypeError: raw_input() takes from 1 to 2 positional arguments but 3 were given



In [79]:

    
def task5():
    cursor = db.cursor()
    cursor.execute('Select id, name, gpa from Students')
    a = cursor.fetchall()
    for x in a:
        print("Введите факультет для студента {} с id = {} и gpa = {}".format(x[1], x[0], x[2]))
        fac_name = input()
        cursor.execute("SELECT fac_id from faculties where name = ?", (fac_name, ))
        # Проверяем есть ли такая запись
        try:
            fac_id = cursor.fetchone()[0]
        except TypeError:
            continue
        cursor.execute("Update students set fac_id = ? where id = ?", (fac_id, x[0],))
    db.commit()



In [80]:

    
task5()









    



Введите факультет для студента Ivanov с id = 1 и gpa = 3.2
IT
Введите факультет для студента Petrov с id = 2 и gpa = 4.2
KIB



In [81]:

    
%%sql 
SELECT * 
FROM students



In [82]:

    
task5()









    



Введите факультет для студента Ivanov с id = 1 и gpa = 3.2
Math
Введите факультет для студента Petrov с id = 2 и gpa = 4.2
IS



In [83]:

    
%%sql 
SELECT * 
FROM Students

Задание 6

Написать функцию, осущетсвляющую перевод части учеников на новый факультет. На входе: Имя факультета, Список студентов для перехода на новый факультет. На выходе Добавление новой записи в таблицу факультетов, Обновление записей в таблице студентов.



In [84]:

    
def task6(fac_name, l_id):
    cursor = db.cursor()
    cursor.execute( '''
    SELECT COALESCE(MAX(fac_id)+1, 1) AS new_fac_id FROM faculties''')
    new_id = cursor.fetchone()[0]
    cursor.execute('''
    INSERT INTO faculties(fac_id, name) VALUES(?,?)''', (new_id, fac_name,))
    for x in l_id:
        cursor.execute('''
        Update students set fac_id = ? where id = ?''', (new_id, x, ))
    
    db.commit()



In [86]:

    
task6('Hist', [1])



In [87]:

    
%%sql 
select * 
from students



In [ ]:



In [ ]: