Mysql
從 MySQL 表 1227 (42000) 讀取數據時出錯:訪問被拒絕;您需要(至少一個)SYSTEM_USER 權限才能執行此操作
我使用 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
為查詢啟用。