Python and SQL mastery

[Pedroski55]

Maybe you are over-thinking this. It is not an arcane and esoteric mystery. If you can use SQL commands on the command line, or in say, phpMyAdmin, then you can use them in Python.

Think Nike: just do it!

I just do simple stuff, collecting homework or online classwork, but it works like this (and very fast):

import pymysql

# this 1 should get Monday's online class work, then Thursday's CW
def mysqlRemoteCW(clas, weeknr): 
    # To connect remote MySQL database 
    conn = pymysql.connect( 
        host='123.456.789.123',
        # listening on the default port, no need to set the server access port
        #port=123456,
        user='myuser',  
        password = 'mypassword', 
        db='mydbname', 
        )       
    cur = conn.cursor()   
    # Select query     
    sql = f"SELECT studentnr, score FROM allstudentsAnswers{clas}CW WHERE weeknr = %s"
    cur.execute(sql, (weeknr,))
    output = cur.fetchall()      
    # To close the connection 
    conn.close()
    return output

clas = input('Enter 20BE or 20EAP or 21BE  ')
weeknumber = input('Just enter a week number, like: Week1_M or Week4_M or Week11_T ')
results = mysqlRemoteCW(clas, weeknumber)
studentsScoresM = {}
# read the results in
for result in results:
    studentsScoresM[result[0]] = result[1]
# put the results in Excel