Error 1064 (42000) when executing UPDATE SQL

[gratiszzzz]

Good afternoon:

I am trying to run an UPDATE query in SQL from python. I think the statement is correct, but it constantly returns an error. I do not know what to do...

---------------------------------------------------------
UPDATE ps_product_lang SET description = '--', name = 'Ratón óptico Standard USB' WHERE id_product = 278335 AND id_lang = 1;
---------------------------------------------------------
this is the sql i am using


Can somebody help me?

Thank you so much!

[gratiszzzz]

sql_actualizar_ficha_es = "UPDATE ps_product_lang SET description = '--', name = '" + titulo + "' WHERE id_product = " + id_producto + " AND id_lang = 1;"
    sql_actualizar_ficha_pt = "UPDATE ps_product_lang SET description = '--', name = '" + titulo_pt + "' WHERE id_product = " + id_producto + " AND id_lang = 2;"
    sql_registrar_id_producto = "INSERT INTO fichas_actualizadas (id_product) VALUES ('" + id_producto + "');"   

    connection = create_connection("######", "######", "########", "######") #
    
    consulta1 = execute_query(connection, sql_actualizar_ficha_es) 
    consulta2 = execute_query(connection, sql_actualizar_ficha_pt)

buran write May-22-2023, 07:47 AM:
Please, use proper tags when post code, traceback, output, etc. This time I have added tags for you.
See BBcode help for more info.

[ibreeden]

Which database are you using? Please show us your program and the complete error message.
You are calling execute_query(). Where is it defined?

[gratiszzzz]

Which database are you using? Please show us your program and the complete error message.
You are calling execute_query(). Where is it defined?

Hi ibreeden!

I am using MariaDB.

def create_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        
    except Error as e:
        falla_conexion = 1
        print("lo que esta fallando es la conexion")
       

    return connection

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("La consulta se ejecutó correctamente.")

        return 1
    except Error as e:
        print("Hubo un error al ejecutar la consulta: " + str(e))
        return 0
    finally:
        cursor.close()

def execute_read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return len(result)
    except Error as e:
        print("The error consulta numerica occurred")



sql_actualizar_ficha_es = "UPDATE ps_product_lang SET description = '--', name = '" + titulo + "' WHERE id_product = " + id_producto + " AND id_lang = 1;"
    sql_actualizar_ficha_pt = "UPDATE ps_product_lang SET description = '--', name = '" + titulo_pt + "' WHERE id_product = " + id_producto + " AND id_lang = 2;"
    sql_registrar_id_producto = "INSERT INTO fichas_actualizadas (id_product) VALUES ('" + id_producto + "');"   

    connection = create_connection("?????", "¿¿¿¿¿", "???????", "???????") 
    
    consulta1 = execute_query(connection, sql_actualizar_ficha_es) 
    consulta2 = execute_query(connection, sql_actualizar_ficha_pt) 

    print("-----------------------------------------")
    print(sql_actualizar_ficha_es)
    if consulta1 == 1 and consulta2 == 1 :
        consulta3 = execute_query(connection, sql_registrar_id_producto) 

The error output that appears when running the script is:

Hubo un error al ejecutar la consulta: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
Hubo un error al ejecutar la consulta: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
-----------------------------------------
UPDATE ps_product_lang SET description = '--', name = 'Teclado Value con cable' WHERE id_product = 278333 AND id_lang = 1;

Something that really catches my attention is that if I take the same sql query that I'm trying to execute in the script with the error and paste it into phpmyadmin, it runs without any problem.

I am completely lost.....

Thank you so much!

[ibreeden]

That is weird, everything seems to be correct.
Perhaps you should not use the semicolon ( ; ) at the end. The semicolon is primarily used as a statement terminator in client mode. I think you do not need it in this case because the string just ends which is a clear ending of the query.

[gratiszzzz]

Yeah. I had also thought about it and tried with ";" and without ";". But nothing, it looks like the same error... I'll keep testing until I find the error...

Thank you very much for your help. If I find the solution I will reply with it!

[gratiszzzz]

Solved!!!!!! It was the mixture of 2 errors:

On the one hand, the product_id was a text string that had characters that were not numbers.

On the other hand I had forgotten to use connection.commit() in the function execute_query()

Finally solved... Thank you very much!

[buran]

You shouldn't be using string concatenation to construct SQL statements