python 3 - creating csv files from python dictionary

jameskane
jameskane used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I can see few problems in your code. Firstly the core of the problem:
writer.writerow(
    [
        print("'{0}'".format(memberinfo))
    ]
)

Open in new window


When preparing the row to be written by the csv write, you correctly uses the list. Apparently, you want to try it with a single value first, so you want to put a single element into the row. However, you put the result of the print() function to -- the function returns None. Do not confuse it with what is displayed. Try simply:
writer.writerow([memberinfo])
#... or
writer.writerow([result[0]])

Open in new window

I suggest to try even the:
writer.writerow(result)

Open in new window

that should write all the info.

The second major problem is that you should open your output CSV file differently -- you need to add the newline='' argument to the open() function. Moreover, you should explicitly close the output file. Or even better, use the with construct when opening the file so that i will be closed automatically by the construct. Follow the doc example at https://docs.python.org/3/library/csv.html#csv.writer

The minor suggestion is to replace the first line by #!python3 when you are at Windows. The reason is that it will work also with the next Python 3.5 (that is out already). Its installer uses a different default location for installation.

I did not look for all the rest of your code. Let make working first what was described.
Walter RitzelSenior Software Engineer

Commented:
@pepr did a good job on pointing out your errors, but I'll do differently: let me share with you something that may speed you up on further development. Here is the generic code I use to write from a dictionary to a csv file.
import csv
import os

def WriteDictToCSV(fileToSave,csvHeader,dataToSave):
    try:
        with open(fileToSave, 'w') as csvfile:
            writer = csv.DictWriter(csvfile, fieldnames=csvHeader)
            writer.writeheader()
            for data in dataToSave:
                writer.writerow(data)
    except IOError as (errno, strerror):
            print("I/O error({0}): {1}".format(errno, strerror))    
    return            

csv_header = ['Row','Name','Country']
dData = [
    {'Row': 1, 'Name': 'Jose', 'Country': 'Brazil'},
    {'Row': 2, 'Name': 'Ben', 'Country': 'USA'},
    {'Row': 3, 'Name': 'Joao', 'Country': 'Brazil'},
    {'Row': 4, 'Name': 'Smith', 'Country': 'USA'},
    {'Row': 5, 'Name': 'John', 'Country': 'Brazil'},
    ]

Open in new window

@Walter: ... the newline='' :)
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Author

Commented:
Thanks very much pepr and walter for taking the time to look at this !

pepr
I have updated the code as per your suggestions, with the exception of closing the csv file, which I can't figure out how to do.  I tried writer.close(out3.csv) but get the following error
--------------------------------------------------------------------------------------------------------------------------------
 File &quot;c:\xampp2\htdocs\OFFICE_PYTHON\CSV\csv.cgi&quot;, line 99, in csvFileGenerator
    writer.close(out3.csv)
<B>AttributeError: &#x27;_csv.writer&#x27; object has no attribute &#x27;close&#x27;
</B></PRE>

------------------------------------------------------------------------------------------------------------------------------
However, when running the revised code (minus closing), out3.csv does does contain information :-

Nom,Prenom,civilites,mobile,email
"('WILLSON', 'Maxime', 'M', '0651175777', 'rachelayala@gmail.com')"

This relates to course 37  and to the third of the three adherents [1027, 127233, 158846]
The correct output should show the information for the 3 adherents, not just the last one.
That is my major problem # 1

My other major problem is that I need the code to generate not just 1 csv file - but 37 csv files  - each numbered uniquely by using its dictionary key. That is 1.csv ,  2.csv, 3.csv...........37.csv.
To achieve this I have obviously tried to replace out3.csv on line 92 of the code with a variable
which uses the actnum variable on line 86.  Unfortunately I can't figure out to do this either.


walter Thanks for the template - but don't see how to use it in this instance ?

James


 CURRENT CODE
#!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', newline=''))
            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([memberinfo])
            writer.close(out3.csv)
                        
            #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

Walter RitzelSenior Software Engineer

Commented:
James,
first you need to add the SELECT statement in your csvFileGenerator function to the dict Generator function.
Then, just change your code to call the function I've sent you.

I did not change the DictGenerator function as you can do better, but I already had added my function and how to call it in the example below.

#!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 WriteDictToCSV(fileToSave,csvHeader,dataToSave):
    try:
        with open(fileToSave, 'w') as csvfile:
            writer = csv.DictWriter(csvfile, fieldnames=csvHeader)
            writer.writeheader()
            for data in dataToSave:
                writer.writerow(data)
    except IOError as (errno, strerror):
            print("I/O error({0}): {1}".format(errno, strerror))    
    return 

#main program
if __name__== "__main__":
    try:
        htmlTop()
        db, cursor = connectDB()
        results = enrollments(db,cursor)
        results2 = ResultsbyActivity(results)
        d = DictGererator(results2)
        print(d)
        DictWriteToCSV('out3.csv',['Nom', 'Prenom', 'civilites', 'mobile', 'email'],d)
        csvFileGenerator(d)
        
        htmlTail()
    except:
        cgi.print_exception()

Open in new window

The problem is that you do open('out3.csv', 'w', newline='') and pass it immediately to the csv writer. The open() returns a file object. If you want to close the file explicitly, you need to store the reference to the file object and use that object .close() method to close the file in turn.

So, instead of
writer = csv.writer(open('out3.csv', 'w', newline=''))

Open in new window

you should use
f = open('out3.csv', 'w', newline='')
writer = csv.writer(f)
...
f.close()

Open in new window

The writer is actually a helper object that writes into f for you. It does not have any .close() method. The close belongs to the file object.

The code like writer = csv.writer(open('out3.csv', 'w', newline='')) is actually wrong. You loose the access to the wrapped file object, and you have to rely on implicit close done by OS/runtime when the program terminates. It may look like a nice and quick hack for tiny ad-hoc utilities, but... Think about the fact that the process (the running program) may limit the number of open files (and it actually does). It may be rather big number (like 512 for a C runtime). The OS may even restrict it. This means, you really want to close the file when you finished to use it -- especially when writing to it.

Anyway, using the pairs open()/close() is error prone. This is the reason why the with construct was introduced. Using the with you tell the guard to correctly finish the work with the wrapped object (here the file object). And because the file object knows the protocol used by the guard, it calls its .close() method when hinted by the guard.

Notice also, that--besides you call the close on the wrong object--you call the close inside the for loop. It should be called after the for loop finished (indentation problem). This is the kind of error that may happen when you do not use the with construct.

The part of the error is that you also open the output file during each loop cycle. This is the reason why you observe only the last record in the output file. I suggest to define precisely what query should produce what output. As Walter suggested, you should have a function or method that gets all the necessary inputs to produce a single output file. Try to describe your problem by word in steps to make it clear what should be done -- that is top-down analysis. Then the parts of the solution can be implemented using partly top-down and partly bottom-up approach.

Author

Commented:
pepr, I understand the mistake I have made with file opening and closing - I have fixed it as you will see in the updated code.  I used the older approach as I feel more comfortable with it at this moment - I see this approach as simple 'wrapping' the code which generates the file content with opening and closing statements.

That leaves the problem of generating the set of csv files --- 1.csv, 2.csv.......37.csv.  A convenient way to do this would be to replace out3.csv in line 90 with VAIABLE.csv.  I have tried actnum.csv and each.csv, but neither works. Is there a way of doing this ?

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")
        f = open('out3.csv', 'w', newline='')        
        for each in members: 
            writer = csv.writer(f)
            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([memberinfo])
        f.close()
                        
            #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

#!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', newline=''))
            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([memberinfo])
            writer.close(out3.csv)
                        
            #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

Author

Commented:
Thanks pepr,
I figured out how to use the varible key to name the  csv files.  So, adding that to your great help in sorting out how to open and close files   properly -  all is working  perfectly.

james
You should move the line 92 writer = csv.writer(f) in front of the for loop. You want to open the file once, and you also want to wrap it once using the csv.writer object. Your code may work, because you actually wrap the f by another and yet another object -- which is redundant and leads to inefficiency.

My experience is that it is always better to store the file name to a variable first and then to use the variable for passing the file name to the open() function. One reason is the situation you observed. You can later easily modify/generate the filename. Another reason is that often, when the code gets bigger, you snip that part of code and put it as the body of a function. Then the variable with the file name may be converted to the argument of the function.

It is a good thing to think more about identifiers. The each is less meaningful than key or shorter k. If the key is actually a activity number, why not using the actnum in the for loop? Also, there is the foreach keyword or construct in other languages. Your for each in d may be a bit confusing for some people. Any confusion that can be avoided should be avoided.

You can use textwrap.dedent() for nicer way to construct the formatted SQL commands. Notice the backslash after the opening tripple quote (prefer the single quotes unless the convention says otherwise). The textwrap.dedent removes the indentation the same as at the first line from all the lines in the multiline string. This way, the SQL command can be nicely indented under the cursor.execute, but the SQL command without the indentation will be passed to the database engine:

import textwrap
...
def csvFileGenerator(d):
    for actnum in d:
        members = d[actnum]
        print(members, 'this is members')
        print(actnum, 'this is activity number')
        fname = 'out{}.csv'.format(actnum)
        f = open(fname, 'w', newline='')        
        writer = csv.writer(f)
        writer.writerow(['Nom', 'Prenom', 'civilites', 'mobile', 'email'])            
        for mID in members: 
            cursor.execute(textwrap.dedent('''\
                SELECT Nom, Prenom, civilites, mobile, email
                FROM members2
                WHERE memberID = "{}"
                '''.format(mID)))
            results = cursor.fetchall()            
            memberinfo = results[0]    # should only a single record be extracted for one memberID?
            writer.writerow([memberinfo])  # should not these [ ] be left out?
        f.close()

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial