Guided project: Designing and Creating SQL Database

Q&A DQ Courses
, ,
#1

Screen Link: https://app.dataquest.io/m/193/guided-project%3A-designing-and-creating-a-database/6/adding-the-team-and-game-tables

Your Code:

c1 = """
CREATE TABLE IF NOT EXISTS game(
    game_id TEXT PRIMARY KEY,
    date TEXT,
    number_of_game INTEGER,
    park_id TEXT,
    length_outs INTEGER,
    day BOOLEAN,
    completion TEXT,
    forefeit TEXT,
    protest TEXT,
    attendance INTEGER,
    legnth_minutes INTEGER,
    additional_info TEXT,
    acquisition_info TEXT,
    FOREIGN KEY (park_id) REFERENCES park(park_id)
);
"""

c2 = """
INSERT OR IGNORE INTO game
SELECT
    game_id,
    date,
    number_of_game,
    park_id,
    length_outs,
    CASE
        WHEN day_night = "D" THEN 1
        WHEN day_night = "N" THEN 0
        ELSE NULL
        END
        AS day,
    completion,
    forefeit,
    protest,
    attendance,
    length_minutes,
    additional_info,
    acquisition_info
FROM game_log;
"""

q = """
SELECT * FROM game
LIMIT 5;
"""

run_command(c1)
run_command(c2)
run_query(q)

What I expected to happen: I expected a Table called “game” to be created and values inserted in it.

What actually happened: I get an error

OperationalErrorTraceback (most recent call last)
<ipython-input-23-755c54a9cb88> in <module>()
     48 
     49 run_command(c1)
---> 50 run_command(c2)
     51 run_query(q)

<ipython-input-11-5892acdf2541> in run_command(c)
      9         conn.execute('PRAGMA foreign_keys = ON;')
     10         conn.isolation_level = None
---> 11         conn.execute(c)
     12 
     13 def show_tables():

OperationalError: no such table: main.par

Other details: I have throroughly perused my code and my tables are available.

#2

Hey, Obatemitope.

Can you please share your notebook and database file? I can’t tell what might be happening here with just the information you provided.

#3

Thanks Bruno for the prompt response.

But on the next screen, there was an instruction to run !rm mlb.db; mlb.db being the database I was creating.

After running the code, I got the expected output. (Though I still don’t understand what the code !rm mlb.db did).

#4

It wasn’t really an instruction, it was an observation in the Learn section:

For example, you might try running DELETE FROM park where park_id = "FOR01"; . If you get stuck, one option is to run !rm mlb.db in its own Jupyter cell to delete the database file so you can run all your cells to recreate the database files, tables and data.

What !rm mlb.db does is delete the database file.

Given that you didn’t share the files I asked for, I don’t understand if you still need help with your initial question.

Let me know if you still need help with any of this.

#5

Bruno,
I have found solution to the the first request.

Thanks a million.

1 Like