Python, Tkinter : insert request doesn't work-CodePudding

i am beginner in python language and sql request. I don't know why but this code doesn't work. It say that my request is not good. Is it because of type of my variable ? I try several solutions and i saw that there is already several subjets related to my problem. But i don't find solutions.

Code of my class ProviderWindow :

    from tkinter import Tk, Label, Button, Entry, StringVar
    from GeStock_2 import provider


class ProviderWindow(Tk):
    def __init__(self):
        super().__init__()
        self.title("Provider")
        self.widgets_provider()

    def widgets_provider(self):
        # Input field for the new provider.
        name_provider = StringVar()
        provider_field = Entry(self, textvariable=name_provider)
        provider_field.grid(row=0, column=0)

        # Button to add a new provider.
        provider_add_button = Button(self, text="New provider")
        provider_add_button.grid(row=1, column=0, sticky="nesw")
        provider_add_button['command'] = lambda: call_function_insert_provider(name_provider)

def call_function_insert_provider(name_provider):
    new_provider = provider.Provider(name_provider)
    print(new_provider)
    print(str(new_provider))
    provider.Provider.insert_new_provider(new_provider)

Code of my class Provider :

from GeStock_2 import utils


class Provider(object):
    def __init__(self, name):
        self.name = name

    def insert_new_provider(self):
        conn = utils.connection()
        my_cursor = conn.cursor()
        print(self.name)
        print(str(self.name))
        my_cursor.execute("INSERT INTO `provider` (`id_prov`, `name_prov`) VALUES (NULL, ('"   str(self.name)   "')")
        cursor = my_cursor.fetchall()
        conn.commit()
        return cursor

Code of "utils" :

import mysql.connector

def connection():
    # function that to connect to database.
    conn = mysql.connector.connect(user='root', password='', host='localhost', database='gestock_2')
    return conn

Can you help me please ? Thank you

CodePudding user response：

When you construct the INSERT SQL statement first and print it out, like below:

sql = "INSERT INTO `provider` (`id_prov`, `name_prov`) VALUES (NULL, ('"   str(self.name)   "')"
print(sql)

Assume self.name has value David, then you will get from print(sql) as below:

INSERT INTO `provider` (`id_prov`, `name_prov`) VALUES (NULL, ('David')

There is extra ( after NULL, .

So the correct one is:

sql = "INSERT INTO `provider` (`id_prov`, `name_prov`) VALUES (NULL, '"   str(self.name)   "')"

However it is better to use placeholders to avoid SQL injection:

sql = "INSERT INTO `provider` (`id_prov`, `name_prov`) VALUES (NULL, %s)"
my_cursor.execute(sql, (self.name,))

Update: Seems like self.name is a StringVar, so self.name.get() should be used instead:

sql = "INSERT INTO `provider` (`id_prov`, `name_prov`) VALUES (NULL, %s)"
my_cursor.execute(sql, (self.name.get(),))