python version is 3.5, MySQL version is 5.7.21,
create database: py4e
create table: PymySqlTest,
install pymysql
pip3 install pymysql
prompt
Requirement already satisfied: pymysql in /Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages
install pymysql via Preference- > my project->.. Intepreter in the pycharm project
enable permissions to create table, in mysqldb
GRANT ALL PRIVILEGES ON PY4E.* TO "root"@"localhost" IDENTIFIED BY "pswd" WITH GRANT OPTION;
SHOW GRANTS FOR "root"@"localhost";
FLUSH PRIVILEGES;
Test in Pycharm
-sharp!/usr/bin/env python3
-sharp GRANT ALL PRIVILEGES ON tablename.* TO "root"@"localhost" IDENTIFIED BY "root" WITH GRANT OPTION;
-sharp FLUSH PRIVILEGES;
import pymysql
-sharp Connect to the database
connection = pymysql.connect(host="127.0.0.1",
user="root",
password="root",
db="db",
charset="utf8mb4",
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
-sharp Create a new record
sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ("webmaster@python.org", "very-secret"))
-sharp connection is not autocommit by default. So you must commit to save
-sharp your changes.
connection.commit()
with connection.cursor() as cursor:
-sharp Read a single record
sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
cursor.execute(sql, ("webmaster@python.org",))
result = cursor.fetchone()
print(result)
finally:
connection.close()
Hover on execution file pymysql does not indicate an error
execute file prompt error:
pymysql.err.OperationalError: (1045, "Access denied for user "root"@"localhost" (using password: YES)")
solve?