193-5 Guided Project: Designing and Creating a Database Phyton SQL

Hi, my code produces an error and I don’t know what is wrong with it. I checked the solution, and even tried to copy it from the solution notebook but still produce the foreign key mismatch error.

Code as follows: errors at the bottom
Code for appearance_type:

with sqlite3.connect('mlb.db') as conn:
    c='DROP TABLE IF EXISTS appearance_type;'
    run_command_del(c)
    appearance.to_sql('appearance_type', conn, index=False, if_exists = 'append')

Code for person_appearance is copied from solution
https://github.com/dataquestio/solutions/blob/master/Mission193Solutions.ipynb

This is my error message:

OperationalErrorTraceback (most recent call last)
<ipython-input-41-9b62c94a7243> in <module>()
    158 """
    159 
--> 160 run_command(c2)

<ipython-input-13-12da1162681e> in run_command(c)
     13         conn.execute('PRAGMA foreign_keys = ON;')
     14         conn.isolation_level = None
---> 15         conn.execute(c)
     16 def show_tables():
     17     df = ''' 

**OperationalError: foreign key mismatch - "person_appearance" referencing "appearance_type"**

Can you help me deduce why is my code getting this error?

1 Like

Hey, Maria. It’s very hard to trace guided project issues without access to the notebook. Can you please attach it to your question?

Moreover, this guided project has an additional hurdle: the fact that it uses an external file as both output and input (the database file).

If you delete that file (or rename it if you want to save your work) and run the solution again, it should work.
If you want to understand why you’re running into issues even if the solution works, then please also share your database.

Basics.ipynb (260.5 KB)

Please find attached. Thank you!

I tried downloading the csv files but cannot for some reasons.

What CSV files? Do you mean the database file that I asked for? That’s actually not a CSV, it’s a file that judging by your notebook is called mlb.db.

I suspect you’re working in the Dataquest app instead of locally. If you are, if you click on the button on top that says Download, you should find your database file there.

Anyway, back to your question. Judging by the code you shared above, you’re getting an error on the cell whose code is what’s below.

with sqlite3.connect('mlb.db') as conn:
    c='DROP TABLE IF EXISTS appearance_type;'
    run_command_del(c)
    appearance.to_sql('appearance_type', conn, index=False, if_exists = 'append')

From a clean slate, this runs fine on my end. Did you do what I suggested about deleting the database file?

Despite this, I still got the same kind of error later on, so that wouldn’t have solved the problem anyway.

The error occurs because you created person_appearance with a foreign key (appearance_type_id) on the table appearance_type:

CREATE TABLE person_appearance (
    appearance_id INTEGER PRIMARY KEY,
    person_id TEXT,
    team_id TEXT,
    game_id TEXT,
    appearance_type_id,
    FOREIGN KEY (person_id) REFERENCES person(person_id),
    FOREIGN KEY (team_id) REFERENCES team(team_id),
    FOREIGN KEY (game_id) REFERENCES game(game_id),
    FOREIGN KEY (appearance_type_id) REFERENCES appearance_type(appearance_type_id)
);

However, running run_query("PRAGMA table_info(appearance_type);") yields

cid name type notnull dflt_value pk
0 0 appearance_type_id TEXT 0 None 0
1 1 name TEXT 0 None 0
2 2 category TEXT 0 None 0

See that zero on the pk column of the row corresponding to apperance_type_id? It says that appearance_type_id is not a primary key. For this reason, your code eventually fails.

You’ll need to create apperance_type with appearance_type_id as a primary key or make do without that (for example by not using apperance_type_id as a foreign key).

1 Like

Thank you, now it works!!

Great! {}{}{}{}{}{}

Hi! I’m having a problem downloading the project. The download button on top seems like not working, when I click it nothing happens. Can you please help me with that?
Thanks in advance.

@gulden Hey.

This seems to be a platform issue and unrelated to this question. I suggest you ask your question in another post and tag it as Dataquest platform.

Please also add details regarding what is it exactly you’re trying to download. The download button should work and there’s definitely an issue here, but it if happens to be the case that you’re trying to download something specific, we may find a workaround for you.

Thanks!

1 Like

Please, how di i get the game_log and others downloaded to my PC. Also, how do i upload all tables into DBdesigner.net in order to construct my schema?

Thanks for your help