import sqlite3 as dbAPI

# Stavki, s katerimi naredimo tabelo

UKAZ_ZA_CREATE_TABLE = """

----------------------------------------------------------------------
-- dobitniki Nobelovih nagrad
----------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS nobel (
  yr INTEGER,
  subject TEXT,
  winner TEXT
);

"""

nagrajenci = [    # seznam nagrajencev v obliki naborov (trojk)

(2008 ,'Chemistry',     'Martin Chalfie'),
(2008 , 'Chemistry',    'Osamu Shimomura'),
(2008 , 'Chemistry',    'Roger Y. Tsien'),
(2008 , 'Economics',    'Paul Krugman'),
(2008 , 'Literature',   'Jean-Marie Gustave Le ClA©zio'),
(2008 , 'Medicine',     'FranA§oise BarrA©-Sinoussi'),
(2008 , 'Medicine',     'Luc Montagnier'),
(2008 , 'Medicine',     'Harald zur Hausen'),
(2008 , 'Peace',    'Martti Ahtisaari'),
(2008 , 'Physics',  'Makoto Kobayashi'),
(2008 , 'Physics',  'Toshihide Maskawa'),
(2008 , 'Physics',  'Yoichiro Nambu'),
(2007 , 'Chemistry',    'Gerhard Ertl'),
(2007 , 'Economics',    'Leonid Hurwicz'),
(2007 , 'Economics',    'Eric S. Maskin'),
(2007 , 'Economics',    'Roger B. Myerson'),
(2007 , 'Literature',   'Doris Lessing'),
(2007 , 'Medicine',     'Mario R. Capecchi'),
(2007 , 'Medicine',     'Sir Martin J. Evans'),
(2007 , 'Medicine',     'Oliver Smithies'),
(2007 , 'Peace',    'Intergovernmental Panel on Climate Change'),
(2007 , 'Peace',    'Al Gore'),
(2007 , 'Physics',  'Albert Fert'),
(2007 , 'Physics',  'Peter GrA1nberg'),
(2006 , 'Chemistry',    'Roger D. Kornberg'),
(2006 , 'Economics',    'Edmund S. Phelps'),
(2006 , 'Literature',   'Orhan Pamuk'),
(2006 , 'Medicine',     'Andrew Z. Fire'),
(2006 , 'Medicine',     'Craig C. Mello'),
(2006 , 'Peace',    'Grameen Bank'),
(2006 , 'Peace',    'Muhammad Yunus'),
(2006 , 'Physics',  'John C. Mather'),
(2006 , 'Physics',  'George F. Smoot'),
(2005 , 'Chemistry',    'Yves Chauvin'),
(2005 , 'Chemistry',    'Robert H. Grubbs'),
(2005 , 'Chemistry',    'Richard R. Schrock'),
(2005 , 'Economics',    'Robert J. Aumann'),
(2005 , 'Economics',    'Thomas C. Schelling'),
(2005 , 'Literature',   'Harold Pinter'),
(2005 , 'Medicine',     'Barry J. Marshall'),
(2005 , 'Medicine',     'J. Robin Warren'),
(2005 , 'Peace',    'International Atomic Energy Agency'),
(2005 , 'Peace',    'Mohamed ElBaradei'),
(2005 , 'Physics',  'Roy J. Glauber'),
(2005 , 'Physics',  'John L. Hall'),
(2005 , 'Physics',  'Theodor W. HA¤nsch'),
(2004 , 'Chemistry',    'Aaron Ciechanover'),
(2004 , 'Chemistry',    'Avram Hershko'),
(2004 , 'Chemistry',    'Irwin Rose'),
(2004 , 'Economics',    'Finn E. Kydland'),
(2004 , 'Economics',    'Edward C. Prescott'),
(2004 , 'Literature',   'Elfriede Jelinek'),
(2004 , 'Medicine',     'Richard Axel'),
(2004 , 'Medicine',     'Linda B. Buck'),
(2004 , 'Peace',    'Wangari Maathai'),
(2004 , 'Physics',  'David J. Gross'),
(2004 , 'Physics',  'H. David Politzer'),
(2004 , 'Physics',  'Frank Wilczek'),
(2003 , 'Chemistry',    'Peter Agre'),
(2003 , 'Chemistry',    'Roderick MacKinnon'),
(2003 , 'Economics',    'Robert F. Engle III'),
(2003 , 'Economics',    'Clive W.J. Granger'),
(2003 , 'Literature',   'J. M. Coetzee'),
(2003 , 'Medicine',     'Paul C. Lauterbur'),
(2003 , 'Medicine',     'Sir Peter Mansfield'),
(2003 , 'Peace',    'Shirin Ebadi'),
(2003 , 'Physics',  'Alexei A. Abrikosov'),
(2003 , 'Physics',  'Vitaly L. Ginzburg'),
(2003 , 'Physics',  'Anthony J. Leggett'),
(2002 , 'Chemistry',    'John B. Fenn'),
(2002 , 'Chemistry',    'Koichi Tanaka'),
(2002 , 'Chemistry',    'Kurt WA1thrich'),
(2002 , 'Economics',    'Daniel Kahneman'),
(2002 , 'Economics',    'Vernon L. Smith'),
(2002 , 'Literature',   'Imre KertA©sz'),
(2002 , 'Medicine',     'Sydney Brenner'),
(2002 , 'Medicine',     'H. Robert Horvitz'),
(2002 , 'Medicine',     'John E. Sulston'),
(2002 , 'Peace',    'Jimmy Carter'),
(2002 , 'Physics',  'Raymond Davis Jr.'),
(2002 , 'Physics',  'Riccardo Giacconi'),
(2002 , 'Physics',  'Masatoshi Koshiba'),
(2001 , 'Chemistry',    'William S. Knowles'),
(2001 , 'Chemistry',    'Ryoji Noyori'),
(2001 , 'Chemistry',    'K. Barry Sharpless'),
(2001 , 'Economics',    'George A. Akerlof'),
(2001 , 'Economics',    'A. Michael Spence'),
(2001 , 'Economics',    'Joseph E. Stiglitz'),
(2001 , 'Literature',   'V. S. Naipaul'),
(2001 , 'Medicine',     'Leland H. Hartwell'),
(2001 , 'Medicine',     'Tim Hunt'),
(2001 , 'Medicine',     'Sir Paul Nurse'),
(2001 , 'Peace',    'United Nations'),
(2001 , 'Peace',    'Kofi Annan'),
(2001 , 'Physics',  'Eric A. Cornell'),
(2001 , 'Physics',  'Wolfgang Ketterle'),
(2001 , 'Physics',  'Carl E. Wieman'),
(2000 , 'Chemistry',    'Alan Heeger'),
(2000 , 'Chemistry',    'Alan G. MacDiarmid'),
(2000 , 'Chemistry',    'Hideki Shirakawa'),
(2000 , 'Economics',    'James J. Heckman'),
(2000 , 'Economics',    'Daniel L. McFadden'),
(2000 , 'Literature',   'Gao Xingjian'),
(2000 , 'Medicine',     'Arvid Carlsson'),
(2000 , 'Medicine',     'Paul Greengard'),
(2000 , 'Medicine',     'Eric R. Kandel'),
(2000 , 'Peace',    'Kim Dae-jung'),
(2000 , 'Physics',  'Zhores I. Alferov'),
(2000 , 'Physics',  'Jack S. Kilby'),
(2000 , 'Physics',  'Herbert Kroemer'),
(1999 , 'Chemistry',    'Ahmed Zewail'),
(1999 , 'Economics',    'Robert A. Mundell'),
(1999 , 'Literature',   'GA1nter Grass'),
(1999 , 'Medicine',     'GA1nter Blobel'),
(1999 , 'Peace',    'MA©decins Sans FrontiA¨res'),
(1999 , 'Physics',  'Gerardus Hooft'),
(1999 , 'Physics',  'Martinus J.G. Veltman'),
(1998 , 'Chemistry',    'Walter Kohn'),
(1998 , 'Chemistry',    'John Pople'),
(1998 , 'Economics',    'Amartya Sen'),
(1998 , 'Literature',   'JosA© Saramago'),
(1998 , 'Medicine',     'Robert F. Furchgott'),
(1998 , 'Medicine',     'Louis J. Ignarro'),
(1998 , 'Medicine',     'Ferid Murad'),
(1998 , 'Peace',    'John Hume'),
(1998 , 'Peace',    'David Trimble'),
(1998 , 'Physics',  'Robert B. Laughlin'),
(1998 , 'Physics',  'Horst L. StA¶rmer'),
(1998 , 'Physics',  'Daniel C. Tsui')
]

# ====================================#

def vpišiNagrajenca(cur, **kwargs): #uporaba poimenovanih parametrov
    """Vpiši osebo z danimi podatki."""
    cur.execute("""INSERT INTO nobel(yr, subject, winner)
                   VALUES (:yr, :subject, :winner)""",
                kwargs)
    return cur.lastrowid
    
   
# --------- Glavni program ----------
# Delamo s podatkovno bazo, ki je/bo shranjena
# v datoteki Nobel.db

db = dbAPI.connect('nobelBaza.db')
c = db.cursor()

try:  # če bo kaj narobe z bazo ....
    # Naredimo tabelo, če je še ni
    c.execute(UKAZ_ZA_CREATE_TABLE)

    #Vstavljanje podatkov v bazo

    # Vnesemo podatke

    for leto, podrocje, dobitnik in nagrajenci :
        kateri = vpišiNagrajenca(c,
                    yr = leto,
                    subject = podrocje,
                    winner = dobitnik)
        if kateri % 10 == 0 :
            print(kateri, end = ":")
    print()

    # Dejansko zapišemo v bazo
    db.commit()

    # Izberemo vse nagrajence od 2006 dalje
    c.execute("SELECT * FROM nobel WHERE yr > 2005")

    # Gremo vrstico po vrstico čez rezultate SELECT
    for vrstica in c:
        print("Nobelovec: %s" % str(vrstica))
except :
    print("Prišlo je do težav, a zapiram bazo ...")
# Kurzorja ne potrebujemo več, ga zapremo
c.close()
# Tudi povezave do baze ne
db.close()