Create a function for writing to SQL data to csv

[Pedroski55]

Recently there was another question about a 60GB csv. Are you guys in the same class? What is a 60GB csv for?

How many rows would such a file have? Must be millions and millions! How many columns?

Not many people have 64GB ram. Get little bits at a time

Assuming your database table has a column id, a unique key, you can use that to get chunks of the table and write to a text file. If you get interrupted, or want to stop when the number of lines reaches a very large number, you can. Next time open it again append, or create another text file

This just gets 1 row at a time, but you could change that for fetchmany(size=1000) or whatever. You can directly open the result text file in Libre Office Calc as a spreadsheet.

import pymysql

savepath = '/home/pedro/myPython/pymysql/results.txt'

def mysqlRemoteHW(clas, weeknr, idnum): 
    # To connect remote MySQL database 
    conn = pymysql.connect( 
        host='123.456.789.123', 
        user='me',  
        password = 'secret', 
        db='allstudentsdb', 
        ) 
      
    cur = conn.cursor()

    # Select query 
    #cur.execute(f"SELECT studentnr, score FROM allstudentsAnswers{clas} WHERE weeknr = '{weeknr}'")
    #cursor.execute("SELECT spam FROM eggs WHERE lumberjack = ?", (lumberjack,))
    sql = f"SELECT id, studentnr, score FROM allstudentsAnswers{clas}HW WHERE weeknr = %s AND id = %s"
    cur.execute(sql, (weeknr, idnum,))
    output = cur.fetchone() 
      
    #for i in output: 
        #print(i) 
      
    # To close the connection 
    conn.close()
    return output

clas = input('Enter 21BE  ')
print('What week number scores are we getting from MySQL?')
weeknumber = input('Just enter a week number, like: Week1 or Week4 or Week11 ')

with open(savepath, 'a') as res:
    for i in range(510, 521):
        results = mysqlRemoteHW(clas, weeknumber, i)
        string = str(results[0]) + ',' + str(results[1]) + ',' + str(results[2]) + '\n'
        res.write(string)