search

mpope9 / nba-sql

:basketball: An application to build an NBA database backed by MySQL, Postgres, or SQLite
Apache License 2.0
171 stars 20 forks source link

UNIQUE Constraint Failed Error -- PGTT Table With Multiple Seasons #80

Closed crsabbagh closed 1 year ago

crsabbagh commented 2 years ago

Getting a UNIQUE constraint failed error when I am building a fresh database using the windows exe. It happened while loading seasonal data. I see that someone else reported a similar error and that a fix might be coming soon? Happy to test the fix when it gets posted.

Full error information:

Traceback (most recent call last): File "peewee.py", line 3144, in execute_sql sqlite3.IntegrityError: UNIQUE constraint failed: player_season.player_id, player_season.season_id, player_season.team_id

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "nba_sql.py", line 389, in File "gooey\python_bindings\gooey_decorator.py", line 134, in File "nba_sql.py", line 229, in main File "player_season.py", line 48, in populate_season File "general_requester.py", line 51, in populate File "db_utils.py", line 16, in insert_many File "db_utils.py", line 30, in __insert_many_sqlite File "peewee.py", line 1907, in inner File "peewee.py", line 1978, in execute File "peewee.py", line 2745, in _execute File "peewee.py", line 2474, in _execute File "peewee.py", line 3157, in execute File "peewee.py", line 3151, in execute_sql File "peewee.py", line 2917, in exit File "peewee.py", line 190, in reraise File "peewee.py", line 3144, in execute_sql peewee.IntegrityError: UNIQUE constraint failed: player_season.player_id, player_season.season_id, player_season.team_id

mpope9 commented 1 year ago

Hi. Pretty busy at work, but I'll try to get around to this soon.

crsabbagh commented 1 year ago

No problem! Thanks for your help.

mpope9 commented 1 year ago

Hey there, was able to take a look at this today. Did this occur with a fresh database? Was it triggered on a specific season or like on the current season?

crsabbagh commented 1 year ago

Yes, it happens when I try to create a fresh database. It successfully loads the team Table, player Table, player_game_log season Data. Then it successfully inserts the PlayerGameLog. But it then fails when loading Seasonal Data.

I'm not sure exactly which season is triggering the error. I tried loading all the seasons at once, and the UI doesn't say exactly which season it's working on, as far as I can tell. It happens very early on in the process though. I'll try to run individual season, and I'll let you know which specific ones fail.

Thanks!

crsabbagh commented 1 year ago

So after some testing, I've figured out the following: It runs successfully whenever I ask it to grab one season. But whenever I ask it to grab more than one season, it fails with this error. It seems to get the data for the first season just fine, but then runs into this error when it moves on to the second season in the list.

mpope9 commented 1 year ago

Ok thanks for this repro. I'll see if this is an issue with the cmdline client or if it's an issue with the older windows build this evening.

mpope9 commented 1 year ago

Interesting, this appears to error when loading the pgtt table for me:

peewee.IntegrityError: duplicate key value violates unique constraint "playergeneraltraditionaltotal_player_id_season_id_team_id"
DETAIL:  Key (player_id, season_id, team_id)=(203932, 2019, 1610612753) already exists

Thanks for the tips on narrowing this down! I actually usually skip this option. If you want the db to 'succeed', you can try to skip this table while loading.

Will work on a fix for this when I find some time.

mpope9 commented 1 year ago

Possible fix https://github.com/mpope9/nba-sql/pull/81

crsabbagh commented 1 year ago

Just tried the fix, and it works perfectly. Thanks!