Python Forum

I have not yet tried to use Python to interact with MySQL.

I run a little homework webpage for my classes. They do the homework, PHP marks it and saves the answers and the scores.

Once a week, I go to my webhost, open phpMyAdmin, then I do, as an example MySQL table:

Step 1:

SELECT studentnr, score FROM allstudentsAnswers20BE WHERE weeknr = 'Week2';

Step 2:

I export the resulting selection as a .csv file to my computer


(then I use Python to put the results in my timetable. I just need to get the .csv file first)

I would like to automate Step 1 and Step 2 using Python.

Could you please give me some tips about what Python modules can do this?

You can use the csv package for CSV write, see: https://docs.python.org/3/library/csv.html
specifically: https://docs.python.org/3/library/csv.html#csv.writer

For SQL, you might want to consider using sqlalchemy: https://pypi.org/project/SQLAlchemy/

Once a model written, the sane code can be used with little or no change for any other DBMS.
queries are a snap.
There is a simple tutorial you can examine here: https://python-forum.io/Thread-SqlAlchem...-Data-Load

Thanks!

csv I can handle with Python, I use it a lot.

It's just the interface Python MySQL I'm uncertain about!

I'll follow your links, thank you very much!

I the only sqlalchmy differences for mysql (my tutorial was for sqlite) are differences in datatype names.
See also: https://docs.sqlalchemy.org/en/14/dialects/mysql.html

I am not optimistic about my Python skills, so I was very pleased when this worked first time and sooo fast:

I just put it here in case some other poor soul needs to do this and is struggling like me!

Big Thank You to geeks for geeks!

import pymysql

clas = input('Which class? ')
weeknumber = input('What week number? ')

def mysqlRemote(clas, weeknr): 
        # To connect remote MySQL database 
        conn = pymysql.connect( 
            host='123.456.789.123', 
            user='myself',  
            password = 'fairdinkum', 
            db='mydatabase', 
            ) 
          
        cur = conn.cursor()
        
        # Select query 
        cur.execute(f"SELECT studentnr, score FROM allstudentsAnswers{clas} WHERE weeknr = '{weeknr}'") 
        output = cur.fetchall() 
          
        #for i in output: 
            #print(i) 
          
        # To close the connection 
        conn.close()
        return output

results = mysqlRemote(clas, weeknumber)

results appears to be a tuple of tuples, (studentnr, score) which I then put in a dictionary, then in Excel!!

Getting the results like this is much quicker than going to the webpage, getting Plesk started, exporting from phpMyAdmin, saving the csv as Excel!!

You shouldn't be using f-strings (or string concatenation) to construct your SQL statements, because of SQL injection. Use the correct placeholders to pass variable parts of the statement.