First time with mysql

[Pedroski55]

Of course you must first have a MySQL database and in that database a table.

My table names look like: allstudentsAnswers20BE

host is the ip of the remote or local server: host='123.456.789.123'
user is your user name in the that database: user='my_username'
password is the password for this user on that database: password = 'my_database_password',
db is the name of the database you want to access: db='my_database_name'

I have several different classes of students and each class has a table.

I need to collect the data in the 2 columns studentnr and score and write them to a dictionary.

So I pass clas and weeknr to the function below and it works just like magic!

So fast, if you blink, you missed it.

def mysqlRemoteCW(clas, weeknr): 
        # To connect remote MySQL database 
        conn = pymysql.connect( 
            host='123.456.789.123', 
            user='my_username',  
            password = 'my_database_password', 
            db='my_database_name', 
            ) 
          
        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 studentnr, score FROM allstudentsAnswers{clas}CW WHERE weeknr = %s"
        cur.execute(sql, (weeknr,))
        output = cur.fetchall() 
          
        #for i in output: 
            #print(i) 
          
        # To close the connection 
        conn.close()
        return output

results = mysqlRemoteCW(clas, weeknr)
# now put results in a dictionary

And yeah, work through a tutorial. MySQL takes some getting used to.

Good Luck!