Btukfyl

i'm Marcello from italy, i'm new in python and django. I ask a question to get info for my project in django.

I've created a project that have two tables, my problem is that how get data automatically from csv, i will create an ftp server that stored csv.txt and update the tables (for example every day).

Now i update with Django import / export manually. I would like that django read csv files and update db then delete.

Thank you very much for all your support!!!

my models.py:

from django.db import models

from django.urls import reverse







class AnagraficaCliente(models.Model):

    codice_cliente = models.CharField(max_length=20, primary_key=True, 

                                      null=False, unique=True)

    ragione_sociale = models.CharField(max_length=40)

    ragione_sociale_dest = models.CharField(max_length=40)

    nome = models.CharField(max_length=20, blank=True)

    cognome = models.CharField(max_length=20, blank=True)

    ragione_sociale = models.CharField(max_length=20)

    indirizzo = models.TextField(blank=True)

    cap = models.CharField(max_length=5, blank=True)

    piva = models.CharField(max_length=11, blank=True)

    vatnumber = models.CharField(max_length=13, blank=True)

    ragione_sociale_dest = models.CharField(max_length=40)

    indirizzo_dest = models.TextField(null=True)



    def __str__(self):

        #return self.ragione_sociale + " " + self.codice_cliente

        return self.ragione_sociale_dest + " " + self.indirizzo_dest 



    class Meta:

        verbose_name = "AnagraficaCliente"

        verbose_name_plural = "AnagraficaClienti"









class Tracking(models.Model):

    track = models.CharField(max_length=10, null=False, unique=True, 

                             primary_key=True)

    passaggio1 = models.CharField(max_length=50, blank=True)

    passaggio2 = models.CharField(max_length=50, blank=True)

    passaggio3 = models.CharField(max_length=50, blank=True)

    passaggio4 = models.CharField(max_length=50, blank=True)

    passaggio5 = models.CharField(max_length=50, blank=True)

    consegna = (

        ('C', 'Consegnato'),

        ('N', 'Non consegnato'),

    )

    consegnato = models.CharField(max_length=1, choices=consegna)



    #consegnato = models.BooleanField(blank=True)

    #esito = models.CharField(max_length=10, blank=True)

    flag = models.CharField(max_length=2, blank=True)

    AnagraficaCliente = models.ForeignKey(AnagraficaCliente, 

    on_delete=models.CASCADE, related_name='trackings')



    def __str__(self):

        return self.track



    class Meta:

        verbose_name = "Tracking"

        verbose_name_plural = "Trackings"

I make a script that read from trackingtest.csv and store in result_str_csv_anag_index_8_list then read from anagraficatest.csv and insert in mysql, after this,i get a query from table tracking_anagraficacliente (
previously entered in mysql previously) and i get last field data to store in result_str_query_anag_index_8_list then compare result_str_csv_anag_index_8_list in result_str_query_anag_index_8_list, if foreign key is correct.

All of this work with a cron in linux but i'd like to implement inside django (in backend) with alert, (for example button red or green if the insert work fine).

I hope to explain well what I have done, i'm beginner ;)

Thank you.

#!/usr/bin/python3



import csv

import MySQLdb, time



sql_anag= """

        INSERT INTO tracking_anagraficacliente 

        (codice_cliente,nome,cognome,ragione_sociale,indirizzo,cap,piva,vatnumber,ragione_sociale_dest,indirizzo_dest)

        VALUES 

        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) 

        ON DUPLICATE KEY UPDATE 

                                                    -- no primary key  aggiornata



        nome   = VALUES(nome),

        cognome   = VALUES(cognome),

        ragione_sociale   = VALUES(ragione_sociale),

        indirizzo   = VALUES(indirizzo),

        cap   = VALUES(cap),

        piva   = VALUES(piva),

        vatnumber   = VALUES(vatnumber),

        ragione_sociale_dest   = VALUES(ragione_sociale_dest),

        indirizzo_dest   = VALUES(indirizzo_dest);

        """

    sql_track= """

        INSERT INTO tracking_tracking 



        VALUES 

        (%s, %s, %s, %s, %s, %s, %s, %s, %s) 

        ON DUPLICATE KEY UPDATE 

                                                    -- no primary key  aggiornata



        passaggio1   = VALUES(passaggio1),

        passaggio2   = VALUES(passaggio2),

        passaggio3   = VALUES(passaggio3),

        passaggio4   = VALUES(passaggio4),

        passaggio5   = VALUES(passaggio5),

        consegnato   = VALUES(consegnato),

        flag   = VALUES(flag),

        AnagraficaCliente_id  = VALUES(AnagraficaCliente_id);

        """

query_id_anag = """ select codice_cliente from tracking_anagraficacliente; """

#query_id_track = """ select AnagraficaCliente_id from tracking_tracking; """





db = MySQLdb.connect(

        host="localhost",    # your host

        user="xxxxxxx",         # your username

        passwd="xxxxxx",  # your password

        db="lm_cms")        # name of the data base



cur = db.cursor()



with open('trackingtest.csv') as csvfile: #

readCSVQuery = csv.reader(csvfile, delimiter=',') 

next (readCSVQuery) 

list_csv_tracking =  

for i in readCSVQuery: 

    trackAnagraficaId = i[8]

    list_csv_tracking.append(trackAnagraficaId) 

result_str_csv_track_index_8 = str(list_csv_tracking).replace(",", 

"").replace("[", "").replace("]", "") 

result_str_csv_anag_index_8_list = result_str_csv_track_index_8.split(' ') 

print ("Risultato CSV Tracking Foreign 

Key",result_str_csv_anag_index_8_list)



def insert_anagrafica():

    with open('anagraficatest.csv') as csvfile: #read CSV anagraficatest

        readCSV = csv.reader(csvfile, delimiter=',')

        next (readCSV) # Jump first line

        for row_anag in readCSV:

            try:

                cur.execute (sql_anag, (row_anag)) #Insert data on mysql

                db.commit() # Commit your changes in the database

                print(row_anag)

            except (MySQLdb.Error, MySQLdb.Warning) as e: #Exception

                print(e)

                db.rollback() # Rollback in case there is any error

                db.close()



def query_anag():

    cur.execute(query_id_anag) #Execute query to get codice_cliente from tracking_anagraficacliente

    results_query_id_anag = cur.fetchall() # Get all data from query and assign to results_query_id_anag

    result_query_anag = str(results_query_id_anag).replace("(", "").replace(")", "").replace(",", "")# Delete ( ) , from results_query_id_anag and convert to string

    result_str_query_anag_index_8_list = result_query_anag.split(' ') # Convert in string from result_query_anag to result_str_query_anag_index_8_list

    print ("Risultato Query anagrafica Codice cliente  ",result_str_query_anag_index_8_list)

    return result_str_query_anag_index_8_list #return value



def insert_tracking():

    with open('trackingtest.csv') as csvfile: #Read Csv

        readCSV = csv.reader(csvfile, delimiter=',') #Read csv file

        next (readCSV) #Jump first line

        #print (results_query_id_anag)

        #compare if AnagraficaClienteID in trackingtest.csv is  present in mysql tracking_aanagraficacliente Codice Cliente

        #,if present make the insert  sql_track else no

        for i in result_str_csv_anag_index_8_list: #compare element lists in result_str_csv_anag_index_8_list is present in result_str_query_anag_index_8_list

            if i in result_str_query_anag_index_8_list:

                for row_track in readCSV: # Read all the line in trackingtest.csv

                    try:

                        #print(row_track[0])

                        #print(row_track)



                        cur.execute (sql_track, (row_track)) #Insert data trackingtest.csv in tracking_tracking

                        db.commit() # Commit your changes in the database

                        print ("Insert effettuata con successo ", row_track)

                    except (MySQLdb.Error, MySQLdb.Warning) as e:

                        print (e, "Riga non inserita: ",row_track)

                        #db.close() # disconnect from server

            else:

                print ("Attenzione, una o più righe non sono state inserite")





insert_anagrafica()

time.sleep(2)

(result_str_query_anag_index_8_list) = query_anag() #value passed from def query_anag() to insert_tracking

time.sleep(2)

insert_tracking()