DGeGf7TC

1## simple demo script for showing how to connect to an sqlite DB 
2# from Python, and run a simple SQL query 
3
4# import the python library for SQLite 
5import sqlite3 
6
7# connect to the database file, and create a connection object(location of cursor)
8db_connection = sqlite3.connect('restaurants.db')
9
10# create a database cursor object, which allows us to perform SQL on the database. 
11db_cursor = db_connection.cursor()
12
13### Add your favorite 3 restaurants in Kreuzberg or Neukölln to the database 
14# additional restaurant row insert
15db_cursor.execute("""
16                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
17                    VALUES ('4', 'Imren', '3', '2')
18                    """)
19db_cursor.execute("""
20                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
21                    VALUES ('5', 'Burgermeister', '1', '1')
22                    """)
23db_cursor.execute("""
24                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
25                    VALUES ('6', 'Somethingfancy', '3', '4')
26                    """)
27
28# Add a new neighborhood to the database, 
29db_cursor.execute("""
30                    INSERT INTO neighborhoods (ID, NAME)
31                    VALUES ('5', 'Schoeneberg')
32                    """)
33# and add 3 restaurants to it. 
34db_cursor.execute("""
35                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
36                    VALUES ('7', 'Chinese', '5', '3')
37                    """)
38db_cursor.execute("""
39                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
40                    VALUES ('8', 'Korean', '5', '1')
41                    """)
42db_cursor.execute("""
43                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
44                    VALUES ('9', 'Japanese', '5', '2')
45                    """)
46
47# Create a new table for people, which contains their favorite restaurant. 
48# Add yourself to this table, and add Adam as well. 
49# His favorite is Curry 36.
50
51# Adding new table 'favorites' with values 'ID, NAME, RESTAURANTS_ID'
52db_cursor.execute("""
53                    CREATE TABLE IF NOT EXISTS "favorites" (
54                    "ID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
55                    "NAME" TEXT NOT NULL,
56                    "RESTAURANTS_ID" INTEGER NOT NULL REFERENCES "restaurants" ("NAME")
57                    );
58                    """)
59
60# Adding people on the 'favorites'
61db_cursor.execute("""
62                    INSERT INTO favorites (ID, NAME, RESTAURANTS_ID)
63                    VALUES ('1', 'Adam', '1')
64                    """)
65
66db_cursor.execute("""
67                    INSERT INTO favorites (ID, NAME, RESTAURANTS_ID)
68                    VALUES ('2', 'Jongwoo', '4')
69                    """)
70
71db_cursor.execute("""
72                    INSERT INTO favorites (ID, NAME, RESTAURANTS_ID)
73                    VALUES ('3', 'Soyoon', '3')
74                    """)
75
76
77'''
78db_cursor.execute("""SELECT * from restaurants
79                   INNER JOIN neighborhoods ON restaurants.NEIGHBORHOOD_ID=neighborhoods.ID
80                   WHERE neighborhoods.NAME="Kreuzberg"
81                   """) 
82'''
83
84
85#showing all the info of restaurants
86db_cursor.execute("SELECT * from restaurants")
87
88#showing all the info of neighborhoods
89#db_cursor.execute("SELECT * from neighborhoods")
90
91
92# * means collect every att from restaurant db
93
94# store the result in a local variable. 
95# this will be a list of tuples, where each tuple represents a row in the table
96list_restaurants = db_cursor.fetchall() # getting readable format in a tuple
97
98print("list_restaurants contents:")
99print(list_restaurants)
100
101db_connection.close()
102# print out the list of tuple; immutable db