Create Views in the database from function

Question

I am developing a simple application where you will need to create a views in Postgresql through Python function. Until then it seems a simple code, but what I did it does not create the views in the database and also does not present error to my despair.

The function is:

 def cria_view(self):
    cursor = self.connectBanco()    
     sql = "CREATE OR REPLACE VIEW espacial.teste AS SELECT *FROM espacial.fx_pedestre;"

     cursor.execute(sql)

Here I call the method:

instancia_sql.cria_view()

With this same SQL create the view by Postgresql Query, but by the Python function does not create, but also does not give error. Anyone knows why? I am missing something?

Answer 1

It may be necessary to use the method MySQLConnection.commit to confirm the current transaction and save the changes.

conexao = MySQLdb.connect(host = "localhost", user = "root", passwd  = "pass", db = "dbname")
cursor = self.connectBanco()    

sql = "CREATE OR REPLACE VIEW espacial.teste AS SELECT * FROM espacial.fx_pedestre;"
cursor.execute(sql)

conexao.commit()

Note: Assuming that the function self.connectBanco returns an object cursor, cannot be used cursor.commit(), if you prefer to change this, in the function self.connectBanco do something like this:

def connectBanco(self):
    conexao = MySQLdb.connect(host = "localhost", ...)

    # Códigos...
    return conexao

In the method cria_view you can do so:

def cria_view(self):
    conexao = self.connectBanco()
    cursor  = conexao.cursor()

    sql = "CREATE OR REPLACE VIEW espacial.teste AS SELECT * FROM espacial.fx_pedestre;"

    cursor.execute(sql)
    conexao.commit()