Python Forum
Python and MySQL - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Python and MySQL (/thread-32944.html)

Python and MySQL - Pedroski55 - Mar-18-2021

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?

RE: Python and MySQL - Larz60+ - Mar-18-2021

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-SqlAlchemy-Tutorial-Basic-Model-database-Creation-and-Data-Load

RE: Python and MySQL - Pedroski55 - Mar-18-2021

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!

RE: Python and MySQL - Larz60+ - Mar-19-2021

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

RE: Python and MySQL - Pedroski55 - Mar-22-2021

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!!

RE: Python and MySQL - ndc85430 - Mar-23-2021

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.