How to create Stored Procedures mysql with python from aqruivos . sql

Question

I have some files .sql where I keep a procedure in each file.

• An example, procedure1.sql:

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_teste$$

CREATE DEFINER=`db`@`%` PROCEDURE sp_teste ()
BEGIN

UPDATE tb_empresas SET emp_descricao = `TESTE` WHERE emp_codigo = 1;

END$$

DELIMITER ;

Now I would like to create a Python program that reads each of these files .sql and create the procedure, if it does not exist.

The program can currently open and read each file, without problems, but when I give the command cursor.execute(data), get the bug:

1064 (42000): You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'DELIMITER $$$ USE python_mysql$$ DROP PROCEDURE IF EXISTS sp_test$$ CREATE DE' at line 1.

I’m not using the function callproc, 'cause I don’t want to call "execute" to procedure, only raise it if it doesn’t exist. Any hint?

Here is the code I’m using: (The procedures are in a folder called 'scripts', at the root where the python code is.

from tkinter import *
import os
#import MySQLdb
from mysql.connector import MySQLConnection, Error

window = Tk()
window.title("Instalador de Scripts")
window.geometry('350x200')

# Linha 1
lbl1 = Label(window, text="Servidor")
lbl1.grid(column=0, row=0)
txt1 = Entry(window,width=20)
txt1.grid(column=1, row=0)

# Linha 2
lbl2 = Label(window, text="Porta")
lbl2.grid(column=0, row=1)
txt2 = Entry(window,width=20)
txt2.grid(column=1, row=1)
txt2.insert(END, '3306')

# Linha 3
lbl3 = Label(window, text="Usuario")
lbl3.grid(column=0, row=2)
txt3 = Entry(window,width=20)
txt3.grid(column=1, row=2)

# Linha 4
lbl4 = Label(window, text="Senha")
lbl4.grid(column=0, row=3)
txt4 = Entry(window,width=20)
txt4.grid(column=1, row=3)


# Linha 5
lbl5 = Label(window, text="Banco")
lbl5.grid(column=0, row=4)
txt5 = Entry(window,width=20)
txt5.grid(column=1, row=4)
txt5.insert(END, 'conference')


def verifica():
    contador = 6
    for root, dirs, files in os.walk("./scripts"):  
        for filename in files:
            txt = Entry(window,width=20)
            txt.grid(column=1, row=contador)
            txt.insert(END, filename)
            contador = contador+1
            print(filename)


def roda():
    contador = 6
    for root, dirs, files in os.walk("./scripts"):  
        for filename in files:
            print(filename)
            filename = "./scripts/" + filename
            with open(filename, 'r') as file:
                data = file.read()
                print("Dados: " + str(data) + " FIM dos dados**********")

                con = MySQLConnection(
                    host="X",
                    user="Y",
                    passwd="Z",
                    database='W'
                )
                print(con)
                #con.select_db('conference_teste')
                cursor = con.cursor()

                try:
                    cursor.execute(data)
                except Exception as e:
                    print("Nao rolou o execute: " + str(e))


btn_verifica = Button(window, text="Verifica", command=verifica)
btn_verifica.grid(column=2, row=5)

btn_roda = Button(window, text="Roda", command=roda)
btn_roda.grid(column=3, row=5)

window.mainloop()

Answer 1

I figured out what was causing the mistake. The answer is in this LINK.

I changed my code to:

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_teste$$

CREATE DEFINER=`dbw`@`%` PROCEDURE sp_teste ()
BEGIN

UPDATE tb_empresas SET emp_descricao = `ATRI RP TESTE` WHERE emp_codigo = 1;

END$$

DELIMITER ;

for:

CREATE DEFINER=`dbw`@`%` PROCEDURE sp_teste ()
BEGIN

UPDATE tb_empresas SET emp_descricao = `ATRI RP TESTE` WHERE emp_codigo = 1;

END

I only removed the DELIMITER and 'DROP PROCEDURE IF EXISTS sp_teste$$' from the code. Checking if the process already exists I’m doing 'on the outside':

# Busca todas as procedures existentes
cursor.execute('SHOW PROCEDURE STATUS')
procedures = cursor.fetchall()
# Para cada procedure que existe
for p in procedures:
    print(p[1])