QVhCQJcw

1import sqlite3
2
3con = sqlite3.connect('firmy.db')
4
5con.row_factory = sqlite3.Row
6
7cur = con.cursor()
8
9cur.execute('DROP TABLE IF EXISTS firma;')
10
11cur.execute("""
12CREATE TABLE IF NOT EXISTS firma (
13id INTEGER PRIMARY KEY ASC,
14nazwa varchar(250) NOT NULL,
15nip varchar(10) NOT NULL
16wlasciciel_id integer not null
17FOREIGN KEY (wlasciciel_id) REFERENCES wlasciciel(id)
18)""")
19
20cur.executescript("""
21DROP TABLE IF EXISTS wlasciciel;
22CREATE TABLE IF NOT EXISTS wlasciciel (
23id INTEGER PRIMARY KEY ASC,
24imie varchar(250) NOT NULL,
25nazwisko varchar(250) NOT NULL,
26)""")
27
28cur.execute('INSERT INTO wlasciciel VALUES(NULL, ?, ?);', ('Tomasz', 'Maciejczyk'))
29cur.execute('INSERT INTO wlasciciel VALUES(NULL, ?, ?);', ('Kamil', 'Banaszak'))
30
31cur.execute('SELECT id FROM wlasciciel WHERE imie = ? and nazwisko = ?', ('Tomasz', 'Maciejczyk'))
32wlasciciel_id1 = cur.fetchone()[0]
33cur.execute('SELECT id FROM wlasciciel WHERE imie = ? and nazwisko = ?', ('Kamil', 'Banaszak'))
34wlasciciel_id2 = cur.fetchone()[0]
35
36firma = (
37  (None, "SoloWin Corporation", "9876543210", wlasciciel_id1),
38  (None, "Biedronka", "8876543210", wlasciciel_id2),
39  )
40  
41cur.executemany('INSERT INTO klient VALUES(?, ?, ?, ?)', firma)
42
43con.commit()
44
45def czytajdane():
46    cur.execute('SELECT firma.id, imie, nazwisko, firma.nazwa, firma.nip, FROM firma, wlasciciel WHERE firma.wlasciciel_id = wlasciciel.id')
47    firmy = cur.fetchall()
48    
49    for firmas in firmy:
50        print(firmas['id'], firmas['imie'], firmas['nazwisko'], firmas['nazwa'], firmas['nip'])
51        print(" ")
52      
53czytajdane() 
54
55def usunfirme():
56    print("usuwam firme")
57    print(" ")
58    print(" ")
59    cur.execute('DELETE FROM firma WHERE id = ?', (2))
60    usunfirme()
61    
62def dodajfirme():
63    print(" ")
64    cur.execute('INSERT INTO firma VALUES(?, ?, ?, ?)', (None, 'Netto', '988887654', 2))
65    dodajfirme()
66
67dodajfirme()
68usunfirme()
69con.close()