0

I am connected to the MySQL database in Python using the (MySQL Connector) Python module in jupyter notebook. I can perform operations for example creating a table or reading data from a table. In the following code, I want to create a view but before creating a view first I want to make sure that the database doesn't have the view if it has I want to drop it.

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")

But when I run this query from jupyter notebook I am getting the following error.

Error reading data from MySQL table 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

Please help me with this how to solve this problem. Thank you in advance.

2
  • 1
    Have you tried running these statements in the MySQL CLI while connecting as the same user?
    – mustaccio
    Jul 17 at 18:52
  • No I did not tried to run this in MySQL CLI. Jul 17 at 19:12
1

You have multiple queries so you need to use

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

Use CREATE OR REPLACE to atomically replace the view.

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

So you don't need to enable multi=True for the query.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.