We help IT Professionals succeed at work.
Get Started

python 3 - creating csv files from python dictionary

jameskane
jameskane asked
on
1,009 Views
Last Modified: 2016-03-04
I have created an order entry dictionary which is dynamically updated from a student enrollment process.  There are 37 courses and around 300 students.  Each course is identified by a number 1, 2, 3 ... (used as key) and each student by unique ID number (45451, 333512 ,...etc)

We have a bulk sms system which we use to communicate to students by course - info on course changes etc... This requires the sms system to be fed with csv files by course number...  1.csv, 2.csv.............................37.csv.  The header for each csv will be
Nom, Prenom, civilites, mobile, email.

I have two problems. Firstly in generating a correctly formatted csv file and secondly to automatically create the csv file names from the course numbers.

I am attaching the page code -  function csvFileGenerator(d) is the one which should generate the csv's .  
Here is the output I get when I run this page

{1: [169450, 464860, 997638], 2: [1002], 3: [461640, 491485, 997638], 4: [1002, 1090], 5: [1002, 274240], 6: [464860, 997638], 7: [461640, 464860, 890419], 8: [464860], 9: [890419], 10: [272338, 524052], 11: [416757], 13: [1081], 14: [1003, 461640], 15: [1003, 997638], 16: [1003], 17: [1003, 1081], 18: [1003], 19: [1081, 980337], 20: [1235], 22: [1081, 491485, 980337], 23: [590314], 24: [158846, 659018], 25: [120474, 890419], 26: [890419], 27: [158846, 491485, 659018], 28: [980337], 29: [120474, 436329, 461640, 529495], 30: [1235, 120474, 158846, 436329, 461640, 659018, 890419], 33: [491485, 659018], 34: [1235, 491485], 35: [659018], 36: [1002, 120474], 37: [158846]}
[169450, 464860, 997638] this is members
1 this is activity number
'('SERRA', 'Annie', 'Mme', '0614010154', 'serrannie@sfr.fr')'
'('LE JAN', 'Frédéric', 'M', '0650501154', 'frederic.lejan@laposte.net')'
'('LE COZ', 'Anne-Marie', 'Mme', '0000000000', '')'
[1002] this is members
2 this is activity number
'('BIZIOU', 'Fiona', 'Mme', '0667799627', 'fionabiziou@gmail.com')'
[461640, 491485, 997638] this is members
3 this is activity number
'('SAPORTA', 'Evelyne', 'Mme', '0000000000', 'eva26saporta@free.fr')'
'('VIARD', 'Sylvette', 'Mme', '0671188317', '')'
'('LE COZ', 'Anne-Marie', 'Mme', '0000000000', '')'
[1002, 1090] this is members
4 this is activity number
'('BIZIOU', 'Fiona', 'Mme', '0667799627', 'fionabiziou@gmail.com')'
'('GIULIANI', 'Josette', 'Mme ', '0', None)'
[1002, 274240] this is members
5 this is activity number
'('BIZIOU', 'Fiona', 'Mme', '0667799627', 'fionabiziou@gmail.com')'
'('MERLINO', 'Enrica', 'Mme', '0695776546', 'merlino.enrica@gmail.com')'
[464860, 997638] this is members
6 this is activity number
'('LE JAN', 'Frédéric', 'M', '0650501154', 'frederic.lejan@laposte.net')'
'('LE COZ', 'Anne-Marie', 'Mme', '0000000000', '')'
[461640, 464860, 890419] this is members
7 this is activity number
'('SAPORTA', 'Evelyne', 'Mme', '0000000000', 'eva26saporta@free.fr')'
'('LE JAN', 'Frédéric', 'M', '0650501154', 'frederic.lejan@laposte.net')'
'('DALMET', 'Clotilde', 'Mlle', '0621650162', 'clotilde.dalmet@gmail.com')'
[464860] this is members
8 this is activity number
'('LE JAN', 'Frédéric', 'M', '0650501154', 'frederic.lejan@laposte.net')'
[890419] this is members
9 this is activity number
'('DALMET', 'Clotilde', 'Mlle', '0621650162', 'clotilde.dalmet@gmail.com')'
[272338, 524052] this is members
10 this is activity number
'('DELRIEU', 'Valerian', 'M', '0607376898', 'helene.cruciani@gmail.com')'
'('GHERING', 'Katty', 'Mme', '0612167545', '')'
[416757] this is members
11 this is activity number
'('TRASTOUR', 'Marie', 'Mlle', '0618070586', 'marie-louise.trastour@notaire.fr')'
[1081] this is members
13 this is activity number
'('FAUREL', 'Marie Odile', 'Mme', '0', None)'
[1003, 461640] this is members
14 this is activity number
'('Blanchard', 'Anita', 'Mme', '0', None)'
'('SAPORTA', 'Evelyne', 'Mme', '0000000000', 'eva26saporta@free.fr')'
[1003, 997638] this is members
15 this is activity number
'('Blanchard', 'Anita', 'Mme', '0', None)'
'('LE COZ', 'Anne-Marie', 'Mme', '0000000000', '')'
[1003] this is members
16 this is activity number
'('Blanchard', 'Anita', 'Mme', '0', None)'
[1003, 1081] this is members
17 this is activity number
'('Blanchard', 'Anita', 'Mme', '0', None)'
'('FAUREL', 'Marie Odile', 'Mme', '0', None)'
[1003] this is members
18 this is activity number
'('Blanchard', 'Anita', 'Mme', '0', None)'
[1081, 980337] this is members
19 this is activity number
'('FAUREL', 'Marie Odile', 'Mme', '0', None)'
'('DAMBRICOURT', 'Mireille', 'Mme', '0000000000', 'mireille.dambricourt@wanadoo.fr')'
[1235] this is members
20 this is activity number
'('SABONADIERE', 'Evelyne', 'Mme', '0', None)'
[1081, 491485, 980337] this is members
22 this is activity number
'('FAUREL', 'Marie Odile', 'Mme', '0', None)'
'('VIARD', 'Sylvette', 'Mme', '0671188317', '')'
'('DAMBRICOURT', 'Mireille', 'Mme', '0000000000', 'mireille.dambricourt@wanadoo.fr')'
[590314] this is members
23 this is activity number
'('LACHAUD', 'Gérard', 'M', '0609078570', 'lachaudg@wanadoo.fr')'
[158846, 659018] this is members
24 this is activity number
'('WILLSON', 'Maxime', 'M', '0651175777', 'rachelayala@gmail.com')'
'('FAYULA', 'Jean', 'M', '0686004287', 'flofane@orange.fr')'
[120474, 890419] this is members
25 this is activity number
'('MOUTTET', 'Gala', 'Mme', '0612174859', 'galamouttet@gmail.com')'
'('DALMET', 'Clotilde', 'Mlle', '0621650162', 'clotilde.dalmet@gmail.com')'
[890419] this is members
26 this is activity number
'('DALMET', 'Clotilde', 'Mlle', '0621650162', 'clotilde.dalmet@gmail.com')'
[158846, 491485, 659018] this is members
27 this is activity number
'('WILLSON', 'Maxime', 'M', '0651175777', 'rachelayala@gmail.com')'
'('VIARD', 'Sylvette', 'Mme', '0671188317', '')'
'('FAYULA', 'Jean', 'M', '0686004287', 'flofane@orange.fr')'
[980337] this is members
28 this is activity number
'('DAMBRICOURT', 'Mireille', 'Mme', '0000000000', 'mireille.dambricourt@wanadoo.fr')'
[120474, 436329, 461640, 529495] this is members
29 this is activity number
'('MOUTTET', 'Gala', 'Mme', '0612174859', 'galamouttet@gmail.com')'
'('POIRETTE', 'Marcelle', 'Mme', '0634445187', 'marcellepoirette@gmail.com')'
'('SAPORTA', 'Evelyne', 'Mme', '0000000000', 'eva26saporta@free.fr')'
'('CAVELLEC', 'Christine', 'Mme', '0000000000', '')'
[1235, 120474, 158846, 436329, 461640, 659018, 890419] this is members
30 this is activity number
'('SABONADIERE', 'Evelyne', 'Mme', '0', None)'
'('MOUTTET', 'Gala', 'Mme', '0612174859', 'galamouttet@gmail.com')'
'('WILLSON', 'Maxime', 'M', '0651175777', 'rachelayala@gmail.com')'
'('POIRETTE', 'Marcelle', 'Mme', '0634445187', 'marcellepoirette@gmail.com')'
'('SAPORTA', 'Evelyne', 'Mme', '0000000000', 'eva26saporta@free.fr')'
'('FAYULA', 'Jean', 'M', '0686004287', 'flofane@orange.fr')'
'('DALMET', 'Clotilde', 'Mlle', '0621650162', 'clotilde.dalmet@gmail.com')'
[491485, 659018] this is members
33 this is activity number
'('VIARD', 'Sylvette', 'Mme', '0671188317', '')'
'('FAYULA', 'Jean', 'M', '0686004287', 'flofane@orange.fr')'
[1235, 491485] this is members
34 this is activity number
'('SABONADIERE', 'Evelyne', 'Mme', '0', None)'
'('VIARD', 'Sylvette', 'Mme', '0671188317', '')'
[659018] this is members
35 this is activity number
'('FAYULA', 'Jean', 'M', '0686004287', 'flofane@orange.fr')'
[1002, 120474] this is members
36 this is activity number
'('BIZIOU', 'Fiona', 'Mme', '0667799627', 'fionabiziou@gmail.com')'
'('MOUTTET', 'Gala', 'Mme', '0612174859', 'galamouttet@gmail.com')'
[158846] this is members
37 this is activity number
'('WILLSON', 'Maxime', 'M', '0651175777', 'rachelayala@gmail.com')'
    its done</body>
        </html>

Open in new window


You will see in the code that I am only generating one csv file (out3.csv) - this is because I do not know how to utilize the variable actnum to create a variable csv file name. So, right now, out3.csv just keeps getting revised with each loop.  In addition, there is no csv content created, bar the header.
------------------------------------------------------------------------------------------------
Nom,Prenom,civilites,mobile,email

""
------------------------------------------------------------------------------------------------

Hopefully you can help with this  - I'm afraid I am struggling with the python csv import.

Thanks


#!C:\Python34\python.exe
import json
import traceback
import cgi,cgitb
import mysql.connector as conn
import collections
import datetime
import shelve
import copy
import csv
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>

            <style>
            body {
        font-size: 100%; /*to support em text sizing*/
        margin-top: 0;

            }

            #backgrd_repeat_image {
        background-image: url(images/background.png);
        background-position: left top;
        background-repeat: repeat;
        height: 1cm;	/*padding-top: 15px;*/
            }


            </style>


                <meta charset="UTF-8">
                <title> Welcomme</title>

                <link href="registration.css" rel="stylesheet" type="text/css">


        </head>
        """)

def htmlTail():

    print("""    its done</body>
        </html>""")

def connectDB():
    db=conn.connect(host='localhost' ,user='root' ,passwd='844cheminduplan' ,db='python_office')
    cursor = db.cursor()
    return db, cursor 

def enrollments(db,cursor):
    sql = "select memberID,T1ytdlatest,T2ytdlatest,T3ytdlatest  from registrations;"
    cursor.execute(sql)
    results=cursor.fetchall()
    return results

def ResultsbyActivity(results):
    results2 = []
    for each in results:
        test = eval(each[1]) + eval(each[3]) + eval(each[2])
        test = list(set(test))
        member = each[0]
        test = [member] + test
        results2.append(test)
    return results2

def DictGererator(results2):
    d={}
    for each in results2:
        memberID = each[0]
        subresults = each[1:]
        #print(subresults, "this is subresults")
        for x in subresults:
            key = x
            #print(key, "this is key")
            if key not in d:
                d[key] = []
            d[key].append(memberID)
    return d     

def csvFileGenerator(d):
    for each in d:
        actnum = each
        members = d[each]
        print(members, "this is members")
        print(actnum, "this is activity number")
                
        for each in members: 
            writer = csv.writer(open("out3.csv", 'w'))
            writer.writerow(['Nom', 'Prenom', 'civilites', 'mobile', 'email'])            
            cursor.execute("""select Nom, Prenom, civilites, mobile, email from members2 WHERE memberID = "%s" """
            %(each))             
            results = cursor.fetchall()
            memberinfo = results[0]
            writer.writerow([print("'{0}'".format(memberinfo))])
            
                        
            #SELECT Nom, Prenom, civilites, mobile, email from members2 
            #WHERE memberID = x 
            #INTO OUTFILE "C:/csv/actnum.csv"
            #FIELDS ENCLOSED BY '"' 
            #TERMINATED BY ';' 
            #ESCAPED BY '"' 
            #LINES TERMINATED BY '\r\n'""" %(each))
            



#main program
if __name__== "__main__":
    try:
        htmlTop()
        db, cursor = connectDB()
        results = enrollments(db,cursor)
        results2 = ResultsbyActivity(results)
        d = DictGererator(results2)
        print(d)
        csvFileGenerator(d)
        
        htmlTail()
    except:
        cgi.print_exception()

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE