Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python and MySQL
#1
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?
Reply
#2
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
Pedroski55 likes this post
Reply
#3
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!
Reply
#4
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
Reply
#5
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!!
Reply
#6
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.
buran likes this post
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Mysql and mysql.connector error lostintime 2 612 Oct-03-2023, 10:25 PM
Last Post: lostintime
  Mysql error message: Lost connection to MySQL server during query tomtom 6 15,686 Feb-09-2022, 09:55 AM
Last Post: ibreeden
Question Debian 11 Bullseye | Python 3.9.x | pip install mysql-connector-python-rf problems BrandonKastning 4 6,574 Feb-05-2022, 08:25 PM
Last Post: BrandonKastning
  Python and MySql ogautier 8 3,250 May-20-2021, 11:10 PM
Last Post: Pedroski55
  Python MySQL ogautier 0 2,084 Sep-03-2020, 03:54 PM
Last Post: ogautier
  Insert into mysql through python LaKhWaN 0 1,922 Aug-26-2020, 04:54 AM
Last Post: LaKhWaN
  Python mysql query help please tduckman 4 4,245 Mar-13-2020, 03:42 PM
Last Post: Marbelous
  mysql db connection using python sunstar20 13 11,883 Aug-04-2018, 09:08 AM
Last Post: vishalhule
  A combination of Python and MySql xgrzeniu 2 3,929 Mar-28-2018, 06:50 AM
Last Post: xgrzeniu
  ssh + mysql connection python 3.4.3 desudesu 9 12,988 May-25-2017, 03:19 PM
Last Post: desudesu

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020