reading from csv file to loop and insert into mysql database

Question

I am working on a system that can send personalized messages to clients. I have a file with contacts and name. I want to upload the file then read the files. Before inserting to database, I want to add a message to it from a textbox in html templates. For instance, I will say: Dear [name], Thanks you. It will loop over the excel file and insert into database the contact and the message that has been generated like Dear Ronald, Thank you.

I have managed to add to upload the excel file and insert the contacts into the database tables. But I would not be able to connect with the texttbox message

Views.py
 def upload(request):
    template = 'sms/upload.html'
    prompt = {'order':'order of the CSV should be'}
    context = {}
    list1 =[]

    if request.method =="GET":
        return render(request, template, prompt)
    csv_file = request.FILES['file']
    if not csv_file.name.endswith('.csv'):
        messages.error(request,'This is not a csv file')
    data_set = csv_file.read().decode('UTF-8')
    io_string = io.StringIO(data_set)
    # row = csv.reader(io_string, delimiter=',', quotechar="|")
    # next(io_string)

    for column in csv.reader(io_string, delimiter=',', 
quotechar="|"):
        s = ''.join(column[0].split())
        p=f"{254}{s[-9:]}"

        _, created = Contact.objects.update_or_create(
            phone_numbers = p,
            first_name=column[1],
            last_name=column[2],
            email=column[3],
            author=request.user



        )

    context = {}
    # context1 = {list1}
    # print (context1)
    # print(column)


    return render(request, template, context)

#upload.html

 {% extends 'sms/base.html'%}

 {% block content%}




 {% if messages %}
   {% for message in messages %}

     <div class="">

      <strong>{{message|safe}}</strong>


     </div>

   {% endfor %}

 {%else%}
 <!-- {{order}} -->
 <form  method="post" enctype="multipart/form-data">
   {% csrf_token %}
   <!-- <label>Upload file</label> -->
   <input type="file" name="file">
  <p>Only accepts CSV file</p>
  <button type="submit">Upload</button>


</form>



 {% endif %}




<div class="col-sm-10">
  <h4>Send  message </h4>
  <div class="col-md-12">
   <form class="form-group" action="" method="POST">
   {% csrf_token %}

    Message: <br/>
    <textarea name="text_message" id="phone" class="form-control" 
 cols="14" rows="6" placeholder="Type your message here"> 

  <br/>


<br>
<input class="btn btn-secondary" type="submit" value="Send Now"/>
</form>

{% endblock content %}

I want to save the save the fields as variables and pass to the upload.html template then process to insert the message into database

Answer 1

For inserting data, first you have to read it properly. If you read csv data in list of dictionaries [{'name':'abc','contact':123},{'name':'xyz', 'contact':456}] iteration will be easy. Pandas is the best library for playing with data. You can use this code to take list of dictonaries

get_file = request.FILES['files']
df = pd.read_csv(get_file)
df = df.where((pd.notnull(df)), '')
    row = df.shape[0]
    col = df.shape[1]
    lista = [] 
    for r in range(row):
        aDict = {} 
        for c in range(col):
            v = df.iat[r,c]
            k = df.columns[c]
            aDict[k]=v
        lista.append(aDict)

Here lista returns list of dictionary Each dictionary in list represent row of (csv, excel). Then you can easily save this data in DB by column name lis['name'] After you can pass your context.

Note: You need to install pandas by

pip install pandas