Uploading data from API to a Postgres DB just stops with no error messages

Question

The strange behavior began when I've switched to the PostgreSQL, used sqllite3 before and there is were no problem like this. Just now recreated the sqllite3 db from the current state of my code. Just turned off the JsonFields that I used for one of the scripts to load to the Postrgre base. Still works.

Loading to the Postgre just stops at a random point on a random report, with no messages, command line do not react to the Ctrl + K command. I've checked the Postgre server, it's running all the time. When I stop the service after this "freezing" of the loading to the DB in the command line, sometimes it throws an error, sometimes is not and just nothing happening. I need to reopen the command line.

I’ve tried to record a screen video to illustrate this behavior. But unfortunately, my screen recording software do not work properly. You can see the code below, I'm not sure there is a problem in the code. Maybe something else needs to be done with the PostgreSQL setup? On the first day of using Postgre, I was able to load the script fully.

There is a screenshot with an example of how it looks in the command line - https://yadi.sk/i/i_LwZHCf_kYpyg

There is one of the scripts

import os
import urllib.request as urllib

from django.core.management.base import BaseCommand, CommandError
from django.core.files import File
from django.conf import settings

import requests
from tqdm import tqdm

from players.models import Skater
from players.models import Goalie


URL_PLAYERS = 'http://www.nhl.com/stats/rest/{}'
URL_PLAYERS_PICS = 'https://nhl.bamcontent.com/images/headshots/current/168x168/{}.jpg'
PL_TYPE1 = "goalies"
PL_TYPE2 = "skaters"
REP_TYPE1 = 'goaliesummary'
REP_TYPE2 = 'skatersummary'
REP_TYPE3 = 'realtime'
REP_TYPE4 = 'timeonice'
POSITIONS = ['G', 'D', 'C', 'LW', 'RW', 'L', 'R']
WING = 'W'

COUNTRIES = {
    'RUS': 'Russia',
    'CAN': 'Canada',
    'USA': 'USA',
    'CZE': 'Czech Republic',
    'CSK': 'Czech Republic',
    'CHE': 'Switzerland',
    'SWE': 'Sweden',
    'FIN': 'Finland',
    'DEU': 'Germany',
    'DNK': 'Denmark',
    'AUT': 'Austria',
    'FRA': 'France',
    'ITA': 'Italia',
    'NOR': 'Norway',
    'LVA': 'Latvia',
    'SVN': 'Slovenia',
    'SVK': 'Slovakia',
    'NLD': 'Netherlands',
    'AUS': 'Australia',
    'GBR': 'Great Britain',
    'KAZ': 'Kazachstan',
    'BGR': 'Bulgaria',
    'EST': 'Estonia',
    None: '—',
}

HEIGHT_CONVERT = {
    65: "5' 5\"",
    66: "5' 6\"",
    67: "5' 7\"",
    68: "5' 8\"",
    69: "5' 9\"",
    70: "5' 10\"",
    71: "5' 11\"",
    72: "6' 0\"",
    73: "6' 1\"",
    74: "6' 2\"",
    75: "6' 3\"",
    76: "6' 4\"",
    77: "6' 5\"",
    78: "6' 6\"",
    79: "6' 7\"",
    80: "6' 8\"",
    81: "6' 9\"",
    82: "6' 10\"",
}

POSITIONS = ['G', 'D', 'C', 'LW', 'RW', 'L', 'R']



class Command(BaseCommand):

    def import_player(self, player, index):
        # print(player["playerId"])
        # print(player["playerName"])
        id_ = player["playerId"]
        if player['playerDraftOverallPickNo'] is None:
            player['playerDraftOverallPickNo'] = '—'
            player['playerDraftYear'] = '—'

        if player['playerBirthStateProvince'] is None:
            player['playerBirthStateProvince'] = ''

        if player['playerPositionCode'] in POSITIONS[5:]:
            player['playerPositionCode'] += WING

        defaults = {
            'name': player["playerName"],
            'team_abbr': player['playerTeamsPlayedFor'][-3:],
            'position_abbr': player["playerPositionCode"],
            'height': HEIGHT_CONVERT[player["playerHeight"]],
            'weight': player["playerWeight"],
            'birth_date': player["playerBirthDate"],
            'birth_city': player["playerBirthCity"],
            'birth_state': player["playerBirthStateProvince"],
            'birth_country': COUNTRIES[player["playerBirthCountry"]],
            'nation': COUNTRIES[player["playerNationality"]],
            'draft_year': player["playerDraftYear"],
            'draft_number': player["playerDraftOverallPickNo"],
            'games': player["gamesPlayed"],
        }

        if player["playerPositionCode"] == POSITIONS[0]:
            defaults_g = {
                'wins': player["wins"],
                'losses': player["losses"],
                'ot_losses': player["otLosses"],
                'goals_against_av': player["goalsAgainstAverage"],
                'saves_perc': player["savePctg"],
                'saves': player["saves"],
                'shotouts': player["shutouts"],
            }
            defaults = {**defaults, **defaults_g}
            player_obj, created = Goalie.objects.update_or_create(nhl_id=id_, defaults=defaults)

            if self.pic_missing(player_obj):
                self.upload_pic(player_obj)

        else:
            if index == 1:
                defaults_s = {
                    'goals': player["goals"],
                    'goals_avg': player["goals"] / player["gamesPlayed"],
                    'assists': player["assists"],
                    'assists_avg': player["assists"] / player["gamesPlayed"],
                    'points': player["points"],
                    'points_avg': player["points"] / player["gamesPlayed"],
                    'plus_minus': player["plusMinus"],
                    'plus_minus_avg': player["plusMinus"] / player["gamesPlayed"],
                    'penalty_min': player["penaltyMinutes"],
                    'penalty_min_avg': player["penaltyMinutes"] / player["gamesPlayed"],
                    'shots': player["shots"],
                    'shots_avg': player["shots"] / player["gamesPlayed"],
                    'pp_points': player["ppPoints"],
                    'pp_points_avg': player["ppPoints"] / player["gamesPlayed"],
                    'sh_points': player["shPoints"],
                    'sh_points_avg': player["shPoints"] / player["gamesPlayed"],
                }
                defaults = {**defaults, **defaults_s}
                player_obj, created = Skater.objects.update_or_create(nhl_id=id_, defaults=defaults)

                if self.pic_missing(player_obj):
                    self.upload_pic(player_obj)

            elif index == 2:
                defaults = {
                    'hits': player["hits"],
                    'hits_avg': player["hits"] / player["gamesPlayed"],
                    'blocks': player["blockedShots"],
                    'blocks_avg': player["blockedShots"] / player["gamesPlayed"],
                    'faceoff_wins': player["faceoffsWon"],
                    'faceoff_wins_avg': player["faceoffsWon"] / player["gamesPlayed"],
                }
                player_obj, created = Skater.objects.update_or_create(nhl_id=id_, defaults=defaults)

            elif index == 3:
                defaults = {
                    'time_on_ice': time_from_sec(player["timeOnIcePerGame"]),
                    'time_on_ice_pp': time_from_sec(player["ppTimeOnIcePerGame"]),
                    'time_on_ice_sh': time_from_sec(player["shTimeOnIcePerGame"]),
                }
                player_obj, created = Skater.objects.update_or_create(nhl_id=id_, defaults=defaults)


    def pic_missing(self, player_obj):
        file_name = f'{player_obj.slug}.jpg'
        file_path = os.path.join(settings.MEDIA_ROOT, 'players_pics', file_name)
        # player_obj.image.path != file_path can be unnecessary in production
        return not player_obj.image or player_obj.image.path != file_path or not os.path.isfile(file_path)


    def upload_pic(self, player_obj):
        content = urllib.urlretrieve(URL_PLAYERS_PICS.format(player_obj.nhl_id))
        pic = File(open(content[0], 'rb'))
        name_pattern = f'{player_obj.slug}.jpg'
        player_obj.image.save(name=name_pattern, content=pic)


    def handle(self, *args, **options):
        lst = [[REP_TYPE1, PL_TYPE1], [REP_TYPE2, PL_TYPE2], [REP_TYPE3, PL_TYPE2], [REP_TYPE4, PL_TYPE2]]
        for index, item in enumerate(lst):
            print(f'\n Uploading from {item[0]} report')
            data = players_resp(item[0], item[1]).json()["data"]

            for player in tqdm(data):
                self.import_player(player, index)


def time_from_sec(time):
    min_, sec = divmod(time, 60)
    min_ = int(min_)
    sec = str(int(sec)).zfill(2)
    return f'{min_}:{sec}'.rjust(5, '0')


def players_resp(rep_type, pl_type):
    params = {
        'isAggregate': 'false',
        'reportType': 'season',
        'isGame': 'false',
        'reportName': rep_type,
        'cayenneExp': 'gameTypeId=2 and seasonId=20182019',
    }

    return requests.get(URL_PLAYERS.format(pl_type), params=params)

models.py

from django.db import models
from django.contrib.auth.models import User
from django.contrib.postgres.fields import JSONField
from django.utils.text import slugify
from players.storage import OverwriteStorage


class Player(models.Model):
    name = models.CharField(max_length=128, default='nme')
    nhl_id = models.IntegerField(unique=True, default=25)
    slug = models.SlugField()
    image = models.ImageField(upload_to='players_pics', storage=OverwriteStorage(), max_length=None)
    team_abbr = models.CharField(max_length=128, default='')
    team_name = models.CharField(max_length=128, default='')
    position_abbr = models.CharField(max_length=128, default='')
    position_name = models.CharField(max_length=128, default='')
    height = models.CharField(max_length=128, default='')
    weight = models.IntegerField(default=25)
    birth_date = models.CharField(max_length=128, default='')
    birth_city = models.CharField(max_length=128, default='')
    birth_state = models.CharField(max_length=128, default='')
    birth_country = models.CharField(max_length=128, default='')
    nation = models.CharField(max_length=128, default='')
    draft_year = models.CharField(max_length=128, default='')
    draft_number = models.CharField(max_length=128, default='')
    pl_number = models.IntegerField(null=True, default=25)
    age = models.IntegerField(null=True, default=25)
    roster_status = models.CharField(max_length=128, default='')
    captain = models.BooleanField(default=False)
    alt_captain = models.BooleanField(default=False)
    sbs_stats = JSONField(default=dict)
    career_stats = JSONField(default=dict)
    gamelog_stats = JSONField(default=dict)
    games = models.IntegerField(default=25)

    def __str__(self):
        return f'{self.name}'

    def save(self, *args, **kwargs):
        is_new = self.pk is None
        if is_new:
            self.slug = slugify(self.name)
        super(Player, self).save(*args, **kwargs)

    class Meta:
        abstract = True


class Skater(Player):
    favorite = models.ManyToManyField(User, related_name='favorite_s', blank=True)
    goals = models.IntegerField(default=10)
    goals_avg = models.FloatField(default=10)
    assists = models.IntegerField()
    assists_avg = models.FloatField(default=10)
    points = models.IntegerField()
    points_avg = models.FloatField(default=10)
    plus_minus = models.IntegerField()
    plus_minus_avg = models.FloatField(default=10)
    penalty_min = models.IntegerField()
    penalty_min_avg = models.FloatField(default=10)
    shots = models.IntegerField()
    shots_avg = models.FloatField(default=10)
    hits = models.IntegerField(null=True)
    hits_avg = models.FloatField(default=10, null=True)
    blocks = models.IntegerField(null=True)
    blocks_avg = models.FloatField(default=10, null=True)
    faceoff_wins = models.IntegerField(null=True)
    faceoff_wins_avg = models.FloatField(default=10, null=True)
    pp_points = models.IntegerField()
    pp_points_avg = models.FloatField(default=10)
    sh_points = models.IntegerField()
    sh_points_avg = models.FloatField(default=10)
    time_on_ice = models.CharField(max_length=128, default='')
    time_on_ice_pp = models.CharField(max_length=128, default='')
    time_on_ice_sh = models.CharField(max_length=128, default='')


class Goalie(Player):
    favorite = models.ManyToManyField(User, related_name='favorite_g', blank=True)
    wins = models.IntegerField(default=10)
    losses = models.IntegerField()
    ot_losses = models.IntegerField()
    goals_against_av = models.FloatField()
    saves_perc = models.FloatField()
    saves = models.IntegerField()
    shotouts = models.IntegerField()


class Team(models.Model):
    name = models.CharField(max_length=128, default='')
    nhl_id = models.IntegerField(unique=True, default=25)
    abbr = models.CharField(max_length=128, default='')
    slug = models.SlugField()
    image = models.ImageField(upload_to='teams_logos', storage=OverwriteStorage(), max_length=None)
    arena_name = models.CharField(max_length=128, default='')
    arena_location = models.CharField(max_length=128, default='')
    division = models.CharField(max_length=128, default='')
    conference = models.CharField(max_length=128, default='')
    off_site = models.URLField(max_length=128, default='')
    nhl_debut = models.CharField(max_length=128, default='')

    def __str__(self):
        return f'{self.name}'

    def save(self, *args, **kwargs):
        is_new = self.pk is None
        if is_new:
            self.slug = slugify(self.name)
        super(Team, self).save(*args, **kwargs)

settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'nhl_web_app_2',
        'USER': 'postgres',
        'PASSWORD': '***',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

views.py

from itertools import chain

import requests
from django.http import HttpResponse, HttpResponseRedirect
from django.shortcuts import render, get_object_or_404

from . import services
from .models import Skater
from .models import Goalie
from .models import Team


def home(request):
    return render(request, 'players/home.html', {'title': 'Home'})


def about(request):
    return render(request, 'players/about.html', {'title': 'About'})


def players(request):
    user = request.user
    context = {
        'goalies': Goalie.objects.all(),
        'skaters': Skater.objects.all(),
        'favorites_g': user.favorite_g.all(),
        'favorites_s': user.favorite_s.all(),
    }
    return render(request, 'players/players.html', context)


def skaters_averages(request):
    user = request.user
    context = {
        'skaters': Skater.objects.all(),
        'favorites_s': user.favorite_s.all(),
    }
    return render(request, 'players/skaters_averages.html', context)


def search(request):
    if 'q' in request.GET and request.GET['q']:
        q = request.GET['q']
        result_s = Skater.objects.filter(name__icontains=q)
        result_g = Goalie.objects.filter(name__icontains=q)
        result_list = result_s.values_list('name', 'nhl_id', 'slug', 'favorite').union(result_g.values_list('name', 'nhl_id', 'slug', 'favorite'))

        context = {
            'players': result_list,
            'query': q,
        }
        return render(request, 'players/search_results.html', context)
    else:
        return HttpResponse('Please submit a search term.')


def player_detail(request, slug, nhl_id):
    bio = services.season_stats(nhl_id)
    player = services.get_player(nhl_id)
    context = {
        'is_favorite': services.is_favorite(request, nhl_id),
        'player': player,
        'bio': bio,
        'stats': bio['stats'][0]['splits'][0]['stat'],
        'total': services.career_stats(nhl_id),
        'sbs_stats': services.sbs_stats(nhl_id),
        'last_gms': player.gamelog_stats[:5],
        'countries': services.COUNTRIES,
        'team': services.TEAM_ABBR,
    }

    return render(request, 'players/player_detail.html', context)


def player_gamelog(request, slug, nhl_id):

    context = {
        'player': services.get_player(nhl_id),
    }
    return render(request, 'players/player_gamelog.html', context)


def player_favorite(request, slug, nhl_id):
    player = services.get_player(nhl_id)
    if player.favorite.filter(id=request.user.id).exists():
        player.favorite.remove(request.user)
    else:
        player.favorite.add(request.user)
    return HttpResponseRedirect(request.META.get('HTTP_REFERER', '/'))


def favorites(request):
    user = request.user
    context = {
        'favorites_g': user.favorite_g.all(),
        'favorites_s': user.favorite_s.all(),
    }
    return render(request, 'players/favorites.html', context)


def teams(request):
    context = {
        'teams': Team.objects.all(),
    }
    return render(request, 'players/teams.html', context)


def team_detail(request, slug, team_id):
    user = request.user
    skaters = Skater.objects.filter(team_abbr=services.TEAM_ABBR[team_id])
    context = {
        'goalies': Goalie.objects.filter(team_abbr=services.TEAM_ABBR[team_id]),
        'skaters': [
            {
                'type': 'Defencemen',
                'list': skaters.filter(position_abbr=services.POSITIONS[1]),
                'table_id': 'tab6',
            },
            {
                'type': 'Forwards',
                'list': skaters.filter(position_abbr__in=services.POSITIONS[2:]),
                'table_id': 'tab7',
            }
            ],
        'favorites_g': user.favorite_g.all(),
        'favorites_s': user.favorite_s.all(),
        'team': get_object_or_404(Team, nhl_id=team_id)
    }

    return render(request, 'players/team_detail.html', context)

Answer 1

I guess that something has to do with the quotes or the single quotes that are used in Json structure. I have no clue from django and how it works, it might be useful if you use some postgres json functions.

Please give more info about the version and how the queries are performed. Try use transaction controls (BEGIN,COMMIT,ROLLBACK) in order to execute your block of code as a transaction.

Answer 2

The problem was solved by uninstalling NetWorx. Bandwidth monitoring and data usage reports program for Windows.

The answer was found kind of accidentally because the same NetWorx program was causing APPCRASH for Python ~ every 3 hours when I used Jupyter Notebook. I found the solution in this answer . I am using Windows 7 by the way.

After deleting NetWorx Jupyter was working just fine. And then I realized that it could be the same reason for Postgres to behave strangely. Since the NetWorx was not in any real use at this moment, just to occasionally look at traffic charts(every 1-2 months) for fun.