Python 3 mysql export to csv

[elliott0184]

Hi there,
I have been creating an attendance system (Clocking in and out machine) for the company i work for. I have been storing all the data in a mysql server on the local sever at work. I am now trying to get it to write all the information for a particular month tp a csv and then store it on a network shared area.
This is the section of code i have for getting the data;

print('Print to CSV File')
        printmonth = input('Month to Export to CSV (eg 2 for feb, 11 for nov)')
        export = "SELECT staff_name, date_, clock_in, clock_out WHERE Month_ = %s"
        cursor.execute(export, (printmonth))
        results4 = cursor.fetchall()
            if not cursor.rowcount:
                print('Error #4')
                print('No Log on this date found')
                time.sleep(2)
                restart_program()
            else:
                  ##this is where the print function will go.
            print('printed')

This is what im thinking for the print function;

rows = cursor.fetchall()
fp = open('/tmp/file.csv', 'w') ##different path but you get the idea
myFile = csv.writer(fp, lineterminator='\n')
myFile.writerows(rows)
fp.close()

my main question is would this work. Also how could i name the file in the month being exported ie 2/2017.csv?
I have never used any of the csv functions before and before doing this code i had never done any coding either so be nice with me.

Thankyou in advance for the help

[zivoni]

With right indentation and probably with (printmonth,) instead of (printmonth) it should work, but you need to write your results4, not rows, the cursor will be empty after first fetchall(). You can even pass a cursor directly to csv.writer without using fetchall() at all - just myFile.writerows(cursor).

You can prepare your filename with string formatting (% or .format()) :

filename = "/tmp/data_{:d}_{:02d}.csv".format(2017, 2)

fp = open(filename, 'w')

[buran]

You need to specify also the year, not just the month. Once you have data for more than one year it will be a problem if you don't store also the year in the DB. Whether it will be a separate column, just the year or you will store full date (e.g. last day of the month) is your choice and db-design issue. For the file name you would probably use datetime.strftime()

[joe_anonimist]

Another option would be to export your data directly from your sql query. Here is a nice tutorial http://www.mysqltutorial.org/mysql-export-table-to-csv/