Mysql

從 MySQL 表 1227 (42000) 讀取數據時出錯:訪問被拒絕;您需要(至少一個)SYSTEM_USER 權限才能執行此操作

  • July 18, 2021

我使用 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 權限才能執行此操作

請幫助我解決這個問題。先感謝您。

您有多個查詢,因此您需要使用

cursor.execute(create_view_query,(), multi=True)

使用CREATE OR REPLACE以原子方式替換視圖。

create_view_query = """
create or replace view chicago_public as
...

所以你不需要multi=True為查詢啟用。

引用自:https://dba.stackexchange.com/questions/295811