Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Key (pos_team)=(JAX) is not present in table "team". #194

Open
pla1 opened this issue Sep 18, 2016 · 27 comments
Open

Key (pos_team)=(JAX) is not present in table "team". #194

pla1 opened this issue Sep 18, 2016 · 27 comments

Comments

@pla1
Copy link

pla1 commented Sep 18, 2016

Getting the following from nfldb-update. TIA, PLA

/usr/local/bin/nfldb-update

-------------------------------------------------------------------------------

STARTING NFLDB UPDATE AT 2016-09-18 17:56:02.147214

Connecting to nfldb... done.

Setting timezone to UTC... done.

Locking write access to tables... done.

Updating season phase, year and week... done.

Bulk inserting data for 2 games...

Sending batch of data to database.

Traceback (most recent call last):

File "/usr/local/bin/nfldb-update", line 39, in <module>
nfldb.update.run(**vars(args))

File "/usr/local/lib/python2.7/dist-packages/nfldb/update.py", line 535, in run
doit()

File "/usr/local/lib/python2.7/dist-packages/nfldb/update.py", line 525, in doit
update_games(db, batch_size=batch_size)

File "/usr/local/lib/python2.7/dist-packages/nfldb/update.py", line 397, in update_games
bulk_insert_game_data(cursor, scheduled, batch_size=batch_size)

File "/usr/local/lib/python2.7/dist-packages/nfldb/update.py", line 221, in bulk_insert_game_data
do()

File "/usr/local/lib/python2.7/dist-packages/nfldb/update.py", line 188, in do
nfldb.db._big_insert(cursor, table, bulk[table])

File "/usr/local/lib/python2.7/dist-packages/nfldb/db.py", line 356, in _big_insert
% (table, insert_fields, values))

File "/usr/local/lib/python2.7/dist-packages/psycopg2/extras.py", line 223, in execute
return super(RealDictCursor, self).execute(query, vars)

psycopg2.IntegrityError: insert or update on table "play" violates foreign key constraint "play_pos_team_fkey"

DETAIL:  Key (pos_team)=(JAX) is not present in table "team".
@adamvonderhaar
Copy link

Having the same issue.

@pla1
Copy link
Author

pla1 commented Sep 18, 2016

@vhaar1 I did the following as a work-around.

insert into team values('JAX','Jacksonville', 'Jaguars');

@adamvonderhaar
Copy link

That worked great. Thanks.

@leighlondon
Copy link

Are you up to date with nfldb? There's a ton of recent issues with manually inserted data (like that) causing issues down the line (e.g. #186).

@pla1
Copy link
Author

pla1 commented Sep 19, 2016

@leighlondon I am up-to-date. I tried an upgrade prior to posting this issue.

sudo pip install nfldb --upgrade

The JAX insert into the team table was just a work-around. There was data coming into the play table. You can see it with this query:

select * from play where pos_team = 'JAX';

@mlloyd5
Copy link

mlloyd5 commented Sep 19, 2016

Hi all,

I am receiving the same error. This is on a new install of nfldb

$ pip freeze beautifulsoup4==4.5.1 enum34==1.1.6 httplib2==0.9.2 nfldb==0.2.17 nflgame==1.2.20 psycopg2==2.6.2 pytz==2016.6.1
Then I tried the workaround @hpadmanabhan mentions in nflgame issue 201

I performed the update:
update team set team_id = 'JAX' where team_id = 'JAC';

I changed the lines in nflgame\__init__.py and nfldb\team.py
From: ['JAC', 'Jacksonville', 'Jaguars', 'Jacksonville Jaguars', 'JAX'],
To: ['JAX', 'Jacksonville', 'Jaguars', 'Jacksonville Jaguars', 'JAC'],

Then I ran nfldb-update --player-interval 0

Which returns a different error, this time JAC:
psycopg2.IntegrityError: insert or update on table "play" violates foreign key constraint "play_pos_team_fkey" DETAIL: Key (pos_team)=(JAC) is not present in table "team".

@joshutt
Copy link

joshutt commented Sep 19, 2016

I had the same problem and did the manual insert to resolve it. It looks like the real problem is that this week the game table is using JAC but the play table is using JAX. But in previous weeks it's JAC in both places. That is why just doing an update doesn't fix it, but just switches where it finds the problem.

In my case I added JAX to the team table, so that I have both JAC and JAX. Then on my side of the application wrote some code to convert JAX to JAC.

But all of this is a workaround and it appears that there will have to be a master adjustment to account for both JAC or JAX coming from GameCenter. (I suspect that ARI or ARZ could be a potential problem in the future as well)

@kennysushi
Copy link

Hi @pla1 , sorry if this is obvious, but where did you place this work around? In one of the .py files?

I did the following as a work-around:
insert into team values('JAX','Jacksonville', 'Jaguars');

@pla1
Copy link
Author

pla1 commented Sep 19, 2016

@kennysushi I executed the insert statement after logging-in on the server via the psql command:

psql -U nfldb nfldb

@mlloyd5
Copy link

mlloyd5 commented Sep 20, 2016

Ok so here's my workaround. I reset my install, but I didn't like the idea of keeping that inserted row in the teams table so I changed all occurrences of 'JAX' in the database to 'JAC' and then deleted the row from the teams table. Queries below:
Run on the database: insert into team values ('JAX','Jacksonville','Jaguars')
Run nfldb-update

If you'd like, you can verify that only the play table needs to be updated:

SELECT COUNT(*) from play where pos_team = 'JAX';
SELECT COUNT(*) from drive where pos_team = 'JAX';
SELECT COUNT(*) from game where home_team = 'JAX';
SELECT COUNT(*) from game where away_team = 'JAX';
SELECT COUNT(*) from player where team = 'JAX';
SELECT COUNT(*) from play_player where team = 'JAX';

Update play table:
UPDATE play SET pos_team = 'JAC' WHERE pos_team = 'JAX'

Delete 'JAX' value from team:
DELETE FROM team WHERE team_id = 'JAX'

@ghost
Copy link

ghost commented Sep 20, 2016

@pla1 did you get this resolved? thanks @mlloyd5 your manual workaround worked for me

@BurntSushi
Copy link
Owner

I hope to have this fixed before the next game.

I think the best thing to do is to settle on JAC/JAX, and do the translation inside nflgame when the JSON is parsed. On the next update, I'll include a DB migration that syncs the entire database with the correct team name (something similar to what @mlloyd5 did manually).

@mrg1999
Copy link

mrg1999 commented Sep 25, 2016

OK, I'm lost.
Where do I insert the
insert into team values ('JAX','Jacksonville','Jaguars')
I added it to the nfldb -update script and received an error.
Added it to the config.ini script and received an error.
I'm using the Windows install.
Or should I just wait for the upgrade??
Thanks.

@challgren
Copy link

You would need to do that query in postgres the database, if you are not a
database administrator, it would be wise to wait for an update.

On Sep 25, 2016 6:17 PM, "mrg1999" [email protected] wrote:

OK, I'm lost.
Where do I insert the
insert into team values ('JAX','Jacksonville','Jaguars')
I added it to the nfldb -update script and received an error.
Added it to the config.ini script and received an error.
I'm using the Windows install.
Or should I just wait for the upgrade??
Thanks.


You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#194 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAFbTQ7_V_Mo9dGr1CcMESl14ROBK_hIks5qtwD0gaJpZM4KAB6h
.

@chppppp
Copy link

chppppp commented Sep 26, 2016

I seem to get this error with LA as well. Just notifying.

psycopg2.IntegrityError: insert or update on table "play" violates foreign key constraint "play_pos_team_fkey"
DETAIL:  Key (pos_team)=(LA) is not present in table "team".

@Deom23
Copy link

Deom23 commented Oct 5, 2016

When is this going to be fixed?

@kennysushi
Copy link

I used the work around below and it's been working fine since then. I assume at some point I'll have to roll back. IIRC you must navigate to the folder where "psql" is located (usually the PostgreSQL folder) via cmd.exe and then run the insert statement via:

I executed the insert statement after logging-in on the server via the psql command:

psql -U nfldb nfldb

@clesiemo3
Copy link

the JAX insert (insert into team values ('JAX','Jacksonville','Jaguars')) does the trick. A better solution would be for the update script to check if team's (or other necessary ref data) don't exist and add them prior to inserting transactional data.

It might be up to the end user whether to update older records to match or to query by name (e.g. name = 'Jaguars') to get both sets of data. All depends on your use case I suppose.

@mrg1999
Copy link

mrg1999 commented Oct 9, 2016

KennySushi and mlloyd5, thank you very much for your help.
My postgres and python programming abilities are lacking. I used them to retrieve the info from the nfldb database and import the scores/stats into an excel vba program that I spent about 6 months developing and was working flawlessly until the JAC/JAZ episode.
My excel program does an nfldb upgrade at the beginning of the season, at the transition from pre-season to regular and to post-season. It also does an upgrade once per month during the regular season so I had to include your Insert, PLAY, UPDATE and JAX DELETE each time the upgrade is performed.
So far, my program is working well due to your help.

@slash-zero
Copy link

Hi! I just did a fresh install, new DB and all, and got the error reported in this issue. I opted for the simple insert statement as a work-around for now. Has nflgame been updated to do the auto translation form JAC to JAX? Is there a plan to get this fixed in a way that won't break existing installations?

Best regards.

@mrg1999
Copy link

mrg1999 commented Oct 23, 2016

What I did was create 3 different scripts and save them in the Python27 folder:

Create a script named Insert_JAX.sql with the following code:
INSERT into team values('JAX','Jacksonville', 'Jaguars');

Then create a script named NewDB.pywith the following code:
import os
os.chdir("c:\\Python27\\Scripts")
execfile("nfldb-update")

Then create another script named Delete_JAX.sql:
UPDATE play SET pos_team = 'JAC' WHERE pos_team = 'JAX';
DELETE FROM team WHERE team_id = 'JAX'

My program code runs as thus:
psql -U nfldb -f C:\Python27\Insert_JAX.sql
Python C:\Python27\NewDB.py
psql -U nfldb -f C:\Python27\Delete_JAX.sql

This seems to be the best fix for now and I suspect it wont hurt anything to keep the code if/when a fix is implemented.
One note is that this script will need to be re-run any time you upgrade nfldb

@ghost
Copy link

ghost commented Nov 28, 2016

@mrg1999
This is probably obvious, but for those unaware, you can throw those into a .bat file as well and then run the single file. Adding a pause at the end will make sure there aren't any errors. If anyone is using Anaconda the paths will be "C:\Anaconda2" and "C;\Anaconda2\Scripts" respectively.

@andr3w321
Copy link

Here's the script I'm using. Hopefully @BurntSushi finds the time for a permanent fix soon!

psql -U nfldb nfldb -c "INSERT into team values('JAX','Jacksonville', 'Jaguars');"
nfldb-update
psql -U nfldb nfldb -c "UPDATE play SET pos_team = 'JAC' WHERE pos_team = 'JAX';"
psql -U nfldb nfldb -c "DELETE FROM team WHERE team_id = 'JAX';"

I'm running ubuntu and saved it as ~/.local/bin/nfldb-update-script.sh Make it executuable with chmod +x nfldb-update-script.sh and then instead of running nfldb-update run nfldb-update-script.sh from anywhere.

@mrg1999
Copy link

mrg1999 commented Feb 9, 2017

Looking forward to next season...
Will the JAX/JAC issue be resolved?
Will the Rams issue be resolved?
Will the Raiders move to Las Vegas create a new issue?
(This isn't a dig at BurntSushi but rather questions about GameCenter)
For one segment of my program that reports score changes, penalties etc. of games in progress I'd like to run nfldb-update at a 30 second interval:
C:\Python27\scripts python nfldb-update --Interval 30
But would I need to also run the
INSERT into team values('JAX','Jacksonville', 'Jaguars');
and
UPDATE play SET pos_team = 'JAC' WHERE pos_team = 'JAX';
DELETE FROM team WHERE team_id = 'JAX'
with each update?
And can I incorporate these lines into the nfldb-update script if so?

@ghost
Copy link

ghost commented Aug 2, 2017

Hi - Would this still be an issue on a new install? Seeding the database with the file at the bottom of the readme and then running nfldb-update yields this error. Am I doing something wrong or is that expected?

Thanks!

$ pip freeze                                                                                                                                                                                            
beautifulsoup4==4.6.0                                                                           
dnspython==1.14.0
docopt==0.6.2
enum34==1.1.6
fuse-python==0.2.1
fusepy==2.0.4
httplib2==0.10.3
mercurial==3.9.1
nfldb==0.2.17
nflgame==1.2.20
psycopg2==2.7.3
pycrypto==2.6.1
pytz==2017.2

After somehow missing mrg1999's comment, that's what I ended up doing. Subsequent run of nflupdate, ran fine.

@kevin2107
Copy link

kevin2107 commented Aug 6, 2017

@verchirl you need to do what @mylloyd5 said and update your database with the entry JAX in the team table. then run the nfldb-update script. Have you used postgres before? If not I can give you a more detailed walk through.

@ghost
Copy link

ghost commented Aug 15, 2017

@kevin2107 - Thanks Kevin. I ended up doing the table insert/update/delete. I was just worried I had installed improperly and was supposed to receive the fix by other (python package?) means.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests