import os import sqlite3 as dbapi import json import csv def ustvari_bazo_NN_skripta(ime_baze, ime_SQL, prepiši = False): '''Ustvari bazo nobelovih nagrajencev na 'ime_baze', tako, da izvedeš skripto SQL na 'ime_SQL'. Če datoteka 'ime_baze' že obstaja, upoštevaj prepiši! Če se baza ustvari na novo, vrni True, sicer False. ''' if os.path.exists(ime_baze) and not prepiši: return False pov = dbapi.connect(ime_baze) cur = pov.cursor() with open(ime_SQL) as dat: cur.executescript(dat.read()) cur.close() pov.commit() pov.close() return True def ustvari_bazo_NN_koda(ime_baze): '''Na ime_baze ustvari sqlite3 bazo.''' 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 def vpišiNagrajencaV2(cur, yr, subject, winner): #uporaba parametrov ? """Vpiši osebo z danimi podatki.""" cur.execute("""INSERT INTO nobel(yr, subject, winner) VALUES (?, ?, ?)""", (yr, subject, winner) ) return cur.lastrowid pov = dbapi.connect(ime_baze) cur = pov.cursor() cur.execute(UKAZ_ZA_CREATE_TABLE) pov.commit() #Vstavljanje podatkov v bazo for leto, podrocje, dobitnik in nagrajenci : kateri = vpišiNagrajencaV2(cur, yr = leto, subject = podrocje, winner = dobitnik) # Dejansko zapišemo v bazo pov.commit() cur.close() pov.close() def ustvari_bazo_NN_JSON(ime_baze, ime_json): '''Na ime_baze ustvari sqlite3 bazo. Podatki so na JSON datoteki ime_json ''' UKAZA_ZA_CREATE_TABLE = """ ---------------------------------------------------------------------- -- dobitniki Nobelovih nagrad ---------------------------------------------------------------------- DROP TABLE IF EXISTS nobel; CREATE TABLE nobel ( yr INTEGER, subject TEXT, winner TEXT ); """ # ====================================# def vpišiNagrajenca(cur, yr, subject, winner): """Vpiši osebo z danimi podatki.""" cur.execute("""INSERT INTO nobel(yr, subject, winner) VALUES (?, ?, ?)""", (yr, subject, winner) ) return cur.lastrowid pov = dbapi.connect(ime_baze) cur = pov.cursor() cur.executescript(UKAZA_ZA_CREATE_TABLE) pov.commit() #Vstavljanje podatkov v bazo with open(ime_json) as dat: podatki = json.load(dat) for leto, podrocje, dobitnik in podatki["rows"] : kateri = vpišiNagrajenca(cur, yr = leto, subject = podrocje, winner = dobitnik) # Dejansko zapišemo v bazo pov.commit() cur.close() pov.close() def ustvari_bazo_NN_CSV(ime_baze, ime_csv): '''Na ime_baze ustvari sqlite3 bazo. Podatki so na CSV datoteki ime_csv, ki ima glavo! Pozor - če baza že obstaja, se podatki dodajajo! ''' UKAZA_ZA_CREATE_TABLE = """ ---------------------------------------------------------------------- -- dobitniki Nobelovih nagrad ---------------------------------------------------------------------- DROP TABLE IF EXISTS nobel; CREATE TABLE nobel ( yr INTEGER, subject TEXT, winner TEXT ); """ # ====================================# def vpišiNagrajenca(cur, yr, subject, winner): """Vpiši osebo z danimi podatki.""" cur.execute("""INSERT INTO nobel(yr, subject, winner) VALUES (?, ?, ?)""", (yr, subject, winner) ) return cur.lastrowid pov = dbapi.connect(ime_baze) cur = pov.cursor() cur.executescript(UKAZA_ZA_CREATE_TABLE) pov.commit() #Vstavljanje podatkov v bazo with open(ime_csv) as dat: podatki = csv.reader(dat) # preskočimo glavo next(podatki) for leto, podrocje, dobitnik in podatki : kateri = vpišiNagrajenca(cur, yr = leto, subject = podrocje, winner = dobitnik) # Dejansko zapišemo v bazo pov.commit() cur.close() pov.close() def main(): ustvari_bazo_NN_skripta('novaNobel.db', 'nobel_ustvari.sql', True) ustvari_bazo_NN_koda('novaNobelKoda.db') ustvari_bazo_NN_JSON('novaNobelJSON.db', 'nobel_podatki.json') ustvari_bazo_NN_CSV('novaNobelCSV.db', 'nobel_podatki.csv') main()