8ApfL3GS

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)")
53
54cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (3, 2)")
55cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (3, 1)")
56cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (2, 1)")
57cur.execute("INSERT INTO actor_movie (actor_id, movie_id) VALUES (1, 1)")
58
59# cur.execute("SELECT MAX(actor_id) FROM actor")
60# print(cur.fetchall())
61cur.execute("SELECT f_name, l_name, title_ger FROM actor, movie INNER JOIN actor_movie am ON am.actor_id=actor.actor_id AND am.movie_id=movie.movie_id")
62print(cur.fetchall())
63
64#cur.execute("SELECT * FROM actor")
65#print(cur.fetchall())
66
67#cur.execute("SELECT * FROM actor_movie")
68#print(cur.fetchall())