0

I am trying to retrieve my data from MySQL to be used in python. But it prints out TypeError: 'NoneType' object is not iterable The server log shows that Access denied for user 'root'@'localhost' (using password: NO) This is the server log . Thanks in advance!

Edit: This is the python code

import pandas as pd

user, pw, host, db = 'root', '12345', '127.0.0.1', 'ca2database'
cnx = mysql.connector.connect(user=user, password=pw, host=host, database=db)
cursor = cnx.cursor()
df= pd.read_sql('SELECT * FROM student', con=cnx)
print(df)

cursor.close()
cnx.close()

and this is the data in the sql

Improve this question
6
  • thanks for answering, i have updated the code and show picture of the data in sql
    – Wolfeatspotatoes
    Commented Aug 5, 2019 at 3:56
  • So you are connecting localhost yet the arg passed equals 127.0.0.1? The MySQL security for root is host part specific usually so this may matter. Outside of the Python connection, can you access the MySQL instance as root and check security to see how it is configured? I'd guess your pw arg is null too. Unfortunately I don't know pandas very well either.
    – Vomit IT - Chunky Mess Style
    Commented Aug 5, 2019 at 4:05
  • I just tried cnx = mysql.connector.connect(user='root', password='12345', host='localhost', database='ca2database') But it still produces the same output. And i need the ' quotation if not it will say the variable is not defined.
    – Wolfeatspotatoes
    Commented Aug 5, 2019 at 4:16
  • The thing is it was working fine a week ago, but then i executed it again this morning and it returns nonetype error.
    – Wolfeatspotatoes
    Commented Aug 5, 2019 at 4:18
  • Here's an example of a way I've run MySQL queries on Windows machines using Python sort of how you are doing it.... pastebin.com/SkkfKvbu Although it is a dumbed down version and I'm sure I should be closing the cursor too so obviously it'll need fine tuned and tested more. Here's a reference link I used to build the logic if my notes are accurate: ianhowson.com/blog/a-quick-guide-to-using-mysql-in-python. Try using 127.0.0.1 instead of localhost and see if that makes any difference. Also, try all variations of those values with double quotes rather than single quotes too.
    – Vomit IT - Chunky Mess Style
    Commented Aug 5, 2019 at 4:41

1 Answer 1

Reset to default
0

There are some differences between 'localhost', '%' and '127.0.0.1'. Seems than Python use socket to connect to the database and '%' is preferred.

Add the following root variation (or just grant your database):

CREATE USER 'root'@'%' IDENTIFIED BY '<your password>';    
GRANT ALL PRIVILEGES on *.* TO 'root'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .