我使用 jupyter notebook 中的 (MySQL Connector) Python 模块连接到 Python 中的 MySQL 数据库。我可以执行操作,例如创建表或从表中读取数据。在下面的代码中,我想创建一个视图,但在首先创建视图之前,我想确保数据库没有视图,如果我想删除它。
import mysql.connector as con
try:
connection = con.connect(host = 'localhost',
database='mydb',
user='***', # my user name is also correct
password='****') # my password is correct
create_view_query = """
drop view if exists chicago_public;
create view chicago_public as
select NAME_OF_SCHOOL as school_name,Safety_Icon as afety_rating,
Family_Involvement_Icon as family_rating,
Environment_Icon as environment_rating,
Instruction_Icon as instruction_rating,
Leaders_Icon as leaders_rating,
Teachers_Icon as teacher_rating
from CHICAGO_PUBLIC_SCHOOLS;
"""
cursor = connection.cursor()
cursor.execute(create_view_query)
print("View created succussfully.")
except con.Error as e:
print("Error reading data from MySQL table",e)
finally:
if connection.is_connected():
connection.close()
cursor.close()
print("MySQL connection is closed")
但是,当我从 jupyter notebook 运行此查询时,我收到以下错误。
从 MySQL 表 1227 (42000) 读取数据时出错:访问被拒绝;您需要(至少一个)SYSTEM_USER 权限才能执行此操作
请帮助我解决这个问题。先感谢您。
您有多个查询,因此您需要使用
使用CREATE OR REPLACE以原子方式替换视图。
所以你不需要
multi=True
为查询启用。