CF8KcHCi

1import sqlite3
2from textwrap import dedent
3
4conn = sqlite3.connect(":memory:")
5cur = conn.cursor()
6
7table_actor = \
8"""
9CREATE TABLE IF NOT EXISTS actor (
10    actor_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
11    f_name TEXT,
12    l_name TEXT,
13    bday TEXT,
14    aka TEXT,
15    born_in TEXT,
16    citizenship TEXT
17);
18"""
19
20table_movie = \
21"""
22CREATE TABLE IF NOT EXISTS movie (
23    movie_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
24    title_eng TEXT,
25    title_ger TEXT,
26    year INTEGER
27);
28"""
29
30
31table_actor_movie = \
32"""
33CREATE TABLE IF NOT EXISTS actor_movie (
34    actor_id INTEGER NOT NULL,
35    movie_id INTEGER NOT NULL,
36    PRIMARY KEY (actor_id, movie_id)
37    FOREIGN KEY (actor_id) REFERENCES actor(actor_id),
38    FOREIGN KEY (movie_id) REFERENCES movie(movie_id)
39);
40"""
41
42cur.execute(dedent(table_actor))
43cur.execute(dedent(table_movie))
44cur.execute(dedent(table_actor_movie))
45
46cur.execute("INSERT INTO actor (f_name, l_name, bday) VALUES ('Thomas', 'Bremer', '27.04.1962')")
47cur.execute("INSERT INTO actor (f_name, l_name, bday) VALUES ('Dieter', 'Ohne', '14.11.2000')")
48cur.execute("INSERT INTO actor (f_name, l_name, bday) VALUES ('Otto', 'Wandel', '04.12.1934')")
49cur.execute("INSERT INTO actor (f_name, l_name, bday) VALUES ('Hans', 'Peter', '18.01.1996')")
50
51cur.execute("INSERT INTO movie (title_eng, title_ger, year) VALUES ('Good Movie', 'Guter Film', 2017)")
52cur.execute("INSERT INTO movie (title_eng, title_ger, year) VALUES ('Bad Movie', 'Schlechter Film', 2014)")
53cur.execute("INSERT INTO movie (title_eng, title_ger, year) VALUES ('Bad Movie2', 'Schlechter Film2', 2014)")
54cur.execute("INSERT INTO movie (title_eng, title_ger, year) VALUES ('Bad Movie3', 'Schlechter Film3', 2014)")
55cur.execute("INSERT INTO movie (title_eng, title_ger, year) VALUES ('Bad Movie4', 'Schlechter Film4', 2014)")
56cur.execute("INSERT INTO movie (title_eng, title_ger, year) VALUES ('Bad Movie5', 'Schlechter Film5', 2014)")
57cur.execute("INSERT INTO movie (title_eng, title_ger, year) VALUES ('Bad Movie6', 'Schlechter Film6', 2014)")
58cur.execute("INSERT INTO movie (title_eng, title_ger, year) VALUES ('Bad Movie7', 'Schlechter Film7', 2014)")
59
60cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (3, 2)")
61cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (3, 1)")
62cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (2, 1)")
63cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (1, 2)")
64cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (1, 4)")
65cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (1, 3)")
66cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (1, 6)")
67cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (1, 5)")
68cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (1, 7)")
69
70# cur.execute("SELECT MAX(actor_id) FROM actor")
71# print(cur.fetchall())
72#cur.execute("SELECT f_name, l_name, bday, title_ger FROM actor, movie INNER JOIN actor_movie am ON am.actor_id=actor.actor_id AND am.movie_id=movie.movie_id")
73#print(cur.fetchall())
74
75cur.execute("SELECT title_ger FROM movie, actor INNER JOIN actor_movie am ON am.actor_id = actor.actor_id AND am.movie_id = movie.movie_id WHERE actor.l_name = 'Bremer'")
76
77#cur.execute("SELECT * FROM actor")
78print(cur.fetchall())
79
80#cur.execute("SELECT * FROM actor_movie")
81#print(cur.fetchall())