read_init_data.py

import os
import sys, inspect

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'api.settings')
import django
django.setup()

from django.apps import apps

from api.models import *
import pandas as pd

# todo: replace file links with media upload server
# todo: Refactor read Functions into one Function
# todo: maybe convert ids also to int beforehand
# todo: add support for logo file upload

def getDFfromXLXS(filePath):
    df = pd.read_excel(filePath, sheet_name=None)['Sheet1']

    # delete first row (the row which explains in which format the data should be)
    df = df.drop(0)

    #strip all newlines and whitespaces in beginning and end
    cols = df.select_dtypes(object).columns
    df[cols] = df[cols].apply(lambda x: x.str.strip('\n') if type(x)==str else x)
    df = df.applymap(lambda x: x.strip() if type(x)==str else x)

    #convert PLZ to int 
    if 'Postleitzahl' in df:
        df['Postleitzahl'] = pd.to_numeric(df['Postleitzahl'], errors='coerce') #to float
        df['Postleitzahl'] = df['Postleitzahl'].fillna(0) #fill missing values with nan 
        df = df.astype({'Postleitzahl':'int'}) # convert to int

    #replace NaN Values with empty String
    df = df.fillna('')

    #find columns that are not ID
    data = df.columns
    for d in data:
        if 'ID' in d: data = data.drop(d)

    #drop rows that are empty
    for index, row in df.iterrows():
        if not any((row[d] for d in data)):
            df = df.drop(index)

    #change dateformat
    try: 
        df['Datum'] = pd.to_datetime(df.Datum, format='%d.%m.%Y')
        df['Datum'] = df['Datum'].dt.strftime('%Y-%m-%d')
    except AttributeError: pass

    return df

def delete_db():
    modelMembers = inspect.getmembers(sys.modules[__name__], inspect.isclass)

    print('clear db')
    for model in modelMembers:
        try:
            if model[1].objects.all().count() > 0:
                model[1].objects.all().delete()
        except:
            pass

def addMultiValueAttribute(cell, column, cellName = 'entry', tableName = 'table'):
    for ID in str(cell).split(","):
        if ID in ('nan', ''): continue
        ID = int(float(ID.strip()))
        try:
            column.add(ID)
        except:
            print(f"Warning: {cellName} with ID {ID} doesn't exist! {tableName} is beeing created without that {cellName}.")

def insertAdress(state, street, zip, city, country, id = None):
    #check if some value is nan     todo: check if necessary
    try:
        if [x for x in (street, zip, city, country) if x == '']: #
            print(f's:{street}, p:{zip}, c:{city}, co:{country}')
            return
    except:
        pass

    #search for address, if it already exists
    query = Address.objects.filter(state=state, street=street,
                                   zip=zip, city=city, country=country)
    if query: return query[0]
    
    # else insert it in db
    address = Address(id=id, state=state, street=street,
                      zip=zip, city=city, country=country)
    address.save()
    return address


def readUser(df):
    for index, row in df.iterrows():
        #check if it already exists
        query = User.objects.filter(title=row['Titel'], 
                                    firstname=row['Vorname'],
                                    lastname=row['Nachname'],
                                    email=row['Email'],
                                    position=row['Position'])
        if query.exists(): continue


        #check if new entry overrides old entry on same ID
        try:
            old = User.objects.get(id = row['UserID'])
            print(old)
            print(f"Warning: Override existing {old.firstname} {old.lastname} with {row['Vorname']} {row['Nachname']}")
        except User.DoesNotExist:
            pass

        user = User(id=row['UserID'],
                    title=row['Titel'], 
                    firstname=row['Vorname'],
                    lastname=row['Nachname'],
                    email=row['Email'],
                    position=row['Position'],
                    photo=row['Foto'])
        user.save()

def readEvents(df):
    for index, row in df.iterrows():
        #check if it already exists
        query = ResearchNetworkEvent.objects.filter(name=row['Name'], 
                                                    photo=row['Foto'],
                                                    date=row['Datum'])
        if query.exists(): continue


        #check if new entry overrides old entry on same ID
        try:
            old = ResearchNetworkEvent.objects.get(id = row['EventID'])
            print(f"Warning: Override existing {old.name} with {row['Name']}")
        except ResearchNetworkEvent.DoesNotExist:
            pass

        event = ResearchNetworkEvent(id=row['EventID'],
                                     name=row['Name'], 
                                     photo=row['Foto'],
                                     date=row['Datum'])
        event.save()

def readNews(df):
    for index, row in df.iterrows():
        #check if it already exists
        query = ResearchNetworkNews.objects.filter(title=row['Titel'], 
                                                   photo=row['Foto'],
                                                   description=row['Beschreibung'],
                                                   eyecatcher=['Eyecatcher'])
        if query.exists(): continue


        #check and warn if new entry overrides old entry on same ID
        try:
            old = ResearchNetworkNews.objects.get(id = row['NewsID'])
            print(f"Warning: Override existing {old.title} with {row['Titel']}")
        except ResearchNetworkNews.DoesNotExist:
            pass

        news = ResearchNetworkNews(id=row['NewsID'],
                                   title=row['Titel'], 
                                   photo=row['Foto'],
                                   description=row['Beschreibung'],
                                   eyecatcher=['Eyecatcher'])
        news.save()

def readProjects(df):
    for index, row in df.iterrows():
        #search if it already exists
        query = ResearchProject.objects.filter(name=row['Projektname'], 
                                               description=row['Projektbeschreibung'])
        if query.exists():
            print(f"Warning: Found duplicate Network Entry under ID {query[0].id} and {int(row['ProjektID'])}")
            continue

        #check if new entry overrides some old entry on same ID
        try:
            old = ResearchProject.objects.get(id = row['ProjektID'])
            print(f"Warning: Override existing {old.name} with {row['Projektname']}")
        except ResearchProject.DoesNotExist:
            pass
        
        project = ResearchProject(id=row['ProjektID'],
                                  name=row['Projektname'], 
                                  description=row['Projektbeschreibung'])
        project.save()

def readClusters(df):
    for index, row in df.iterrows():
        #search if it already exists
        query = ResearchCluster.objects.filter(name=row['Clustername'], 
                                               description=row['Clusterbeschreibung'])
        if query.exists():
            print(f"Warning: Found duplicate Cluster Entry under ID {query[0].id} and {int(row['ClusterID'])}")
            continue

        #check if new entry overrides some old entry on same ID
        try:
            old = ResearchCluster.objects.get(id = row['ClusterID'])
            print(f"Warning: Override existing {old.name} with {row['Clustername']}")
        except ResearchCluster.DoesNotExist:
            pass
        
        project = ResearchCluster(id=row['ClusterID'],
                                  name=row['Clustername'], 
                                  description=row['Clusterbeschreibung'])
        project.save()

def readNetworks(df):
    for index, row in df.iterrows():
        #check if it already exists
        query = ResearchNetwork.objects.filter(name=row['Name'],
                                               shortcut=row['Kuerzel'], 
                                               description=row['Information'],
                                               research_description=row['Forschung'])
        if query.exists():
            print(f"Warning: Found duplicate Network Entry under ID {query[0].id} and {int(row['NetworkID'])}")
            continue

        #check if new entry overrides old entry on same ID
        try:
            old = ResearchNetwork.objects.get(id = row['NetworkID'])
            print(f"Warning: Override existing {old.name} ({old.shortcut}) with {row['Name']} ({row['Kuerzel']})")
        except ResearchNetwork.DoesNotExist:
            pass

        address = insertAdress(state=row['Bundesland'], street=row['Strasse'], 
                               zip=row['Postleitzahl'], city=row['Ort'], country=row['Land'])

        rn = ResearchNetwork(id=row['NetworkID'],
                             name=row['Name'],
                             shortcut=row['Kuerzel'], 
                             description=row['Information'],
                             research_description=row['Forschung'],
                             logo=row['Logo'],
                             address = address)
        rn.save()
        
        addMultiValueAttribute(cell = row['EventIDs'], column = rn.events, cellName = 'Event', tableName = 'ResearchNetwork '+ rn.name + rn.shortcut)
        addMultiValueAttribute(cell = row['NewsIDs'], column = rn.news, cellName = 'News', tableName = 'ResearchNetwork ' + rn.name + rn.shortcut)

def readProfiles(df):
    for index, row in df.iterrows():
        address = insertAdress(state=row['Bundesland'], street=row['Strasse'], 
                               zip=row['Postleitzahl'], city=row['Ort'], country=row['Land'])

        institute = None
        if row['Organisationsname'] and str(row['Organisationsname']).strip() != '':
            try:
                institute = Institute.objects.get(title=row['Organisationsname'])
            except Institute.DoesNotExist:
                print(f"No institute found for '{row['Organisationsname']}' - profile '{row['Institutsname']}' will have no institute")

        cp = CompetenceProfile(author = None,
                               title=row['Institutsname'],
                               organization=row['Organisationsname'],
                               description=row['Zusammenfassung'],
                               shortcut=row['Kuerzel'],
                               research_focus=row['Forschungsfokus'],
                               publications = row['Veroeffentlichungen'],
                               logo = row['Logo'],
                               address = address,
                               institute = institute)
        cp.save()

        addMultiValueAttribute(cell = row['ProjektIDs'], 
                               column = cp.research_projects, 
                               cellName = 'Project', tableName = 'CompetenceProfile '+ cp.shortcut)
        addMultiValueAttribute(cell = row['RepresentativeIDs'], column = cp.representatives, 
                               cellName = 'User', tableName = 'CompetenceProfile '+ cp.shortcut)
        addMultiValueAttribute(cell = row['NetworkIDs'],
                               column = cp.research_networks,
                               cellName = 'Network', tableName = 'CompetenceProfile '+ cp.shortcut)
        addMultiValueAttribute(cell = row['ClusterIDs'], 
                               column = cp.research_clusters, 
                               cellName = 'Cluster', tableName = 'CompetenceProfile '+ cp.shortcut)


def readInstitutes(df):
    for index, row in df.iterrows():

        if not row['Organisationsname'] or str(row['Organisationsname']).strip() == '':
            continue

        address = insertAdress(state=row['Bundesland'], street=row['Strasse'], 
                               zip=row['Postleitzahl'], city=row['Ort'], country=row['Land'])

        institute = Institute(title=row['Organisationsname'],
                             shortcut=row['institute_short'], 
                             description=row['Zusammenfassung'],
                             logo=row['Logo'],
                             address=address)
        institute.save()

def synchronize_primary_keys():
    with django.db.connection.cursor() as cursor:
        # Get a list of all installed app models
        app_models = apps.get_models()

        print(app_models)
        print()

        for model in app_models:
            if model._meta.managed and model._meta.app_label == 'api':  # Check if the model is managed by Django and if it is from our app
                table_name = model._meta.db_table
                primary_key_column = model._meta.pk.column

                query = f"SELECT MAX({primary_key_column}) FROM {table_name};"

                cursor.execute(query)
                max_id = cursor.fetchone()[0]

                print(f"Table: {table_name}, PK: {primary_key_column}, MAX ID: {max_id}")

                if max_id is not None:
                        sequence_name = f"{table_name}_{primary_key_column}_seq"
                        update_sequence_query = f"SELECT setval('{sequence_name}', {max_id});"
                        cursor.execute(update_sequence_query)
                        print(f"Sequence {sequence_name} updated to {max_id}")
                else:
                    print(f"No records in {table_name}")


if __name__ == '__main__':
    print("Starting Init_data script...")
    delete_db()

    FOLDER = "init_data"

    readUser    (getDFfromXLXS(FOLDER + '/User.xlsx'))
    readNews    (getDFfromXLXS(FOLDER + '/News.xlsx'))
    readEvents  (getDFfromXLXS(FOLDER + '/Events.xlsx'))
    readNetworks(getDFfromXLXS(FOLDER + '/Research_Networks.xlsx'))
    readProjects(getDFfromXLXS(FOLDER + '/Research_Projects.xlsx'))
    readClusters(getDFfromXLXS(FOLDER + '/Research_Clusters.xlsx'))
    readInstitutes(getDFfromXLXS(FOLDER + '/CompetenceProfile.xlsx'))

    readProfiles(getDFfromXLXS(FOLDER + '/CompetenceProfile.xlsx'))


    #sync up primary key sequences for all tables
    print("synchronizing primary keys now")
    synchronize_primary_keys()