troubleshooting Question

python 3 - creating csv files from python dictionary

Avatar of jameskane
jameskane asked on
Python
9 Comments1 Solution1016 ViewsLast Modified:
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

ASKER CERTIFIED SOLUTION
pepr

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 9 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 9 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004