Why name='%s' is not deleting my query but if i set to name='something' it deletes.



  • def button_click(self):
    #QString object
    QUERY_DELETE = self.Asset_tag.text()

        #Conditional statement starts here
        
        #Ends here
        
        con = mysql.connector.connect(user="root",password="admingelo",host="localhost",database="test")
        manager = con.cursor()
        
        delete_this_data = ("DELETE FROM test_table WHERE name='%s'")
    
        data = (str(QUERY_DELETE))
    
        manager.execute(delete_this_data,data)
    
        con.commit()
        print "Delete Successful!"

  • Qt Champions 2016

    How do you replace %s ?



  • @mrjj name='jenny' it delete the query but if i pass a string to my variable QUERY_DELETE nothings happen eg. QUERY_DELETE = 'jenny' and execute it nothing happens but if i manually set the name='jenny' it successfully delete the query


  • Qt Champions 2016

    So it sounds like the way you insert the data string into %s is wrong since
    it works if you do it manually.



  • @mrjj can you help me with that?


  • Qt Champions 2016

    @Gelo
    well I am not in python but in c++ you use positional variables.

    QSqlQuery query;
    int ok = query.prepare("SELECT firstname,num FROM person WHERE num=:thenum AND firstname=:thename AND lastname=:lastname");
    query.bindValue(":thenum", 4);
    query.bindValue(":thename", "Lars junior");
    query.bindValue(":lastname", "Gordon");
    query.exec();



  • Not an expert here since it's not C++ but try replacing '%s' with ?



  • Does this function work in your case ?
    http://doc.qt.io/qt-5/qstring.html#arg
    Then you can convert from QString to c++ str



  • @Bui-Trung i am using python


  • Qt Champions 2016

    @Gelo
    Hi
    Fast google tells me

    query_str = 'SELECT %s FROM track %s'%(select_string, where_string)
    self.query.prepare(query_str)

    That you might need to call prepare.
    Do you ?

    Also this looks like fine tut
    http://www.pythonstudio.us/pyqt-programming/executing-sql-queries.html



  • Sorry about it, i have not been ever using pyQt before, so can you find the same class of QString like the one i linked to?



  • @VRonin i am using mysql a "?" will not work but thank you !



  • @Gelo Ok, second try: %s instead of '%s' (I think %s automatically adds the 's)

    Did you try @mrjj 's solution?



  • @VRonin
    if you do a select * from table where something=%s , 'something' it will be replaced to select * from table where something=something, so if you have a string or char, you need to write it as

    • select * from table where something='%s' , 'something'
      or
    • select * from table where something=%s , '"something"'
      or the mysql connector will throw an exception
    cur.execute("select * from process where finished=%s",'K')
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
      File "/usr/lib/python2.7/site-packages/mysql/connector/cursor.py", line 507, in execute
        self._handle_result(self._connection.cmd_query(stmt))
      File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 722, in cmd_query
        result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
      File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 640, in _handle_result
        raise errors.get_exception(packet)
    mysql.connector.errors.ProgrammingError: 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 '%s' at line 1
    


  • @Gelo
    https://dev.mysql.com/doc/connector-python/en/connector-python-examples.html There you find some examples how to use mysql connector in python



  • @the_ def button_click(self):
    #QString object
    QUERY_DELETE = self.Asset_tag.text()
    data = QUERY_DELETE
    #Conditional statement starts here

        #Ends here
        
        con = mysql.connector.connect(user="root",password="admingelo",host="localhost",database="test")
        manager = con.cursor()
        
        delete_this_data = ("""DELETE FROM test_table WHERE name = %s""",str(data))
        manager.execute(delete_this_data)
        print data
        con.commit()
        print "Delete Successful!"
    

    Traceback (most recent call last):
    File "C:\Users\angelo\Desktop\IMS Delta EarthMoving Incorporated\QtDeleteQuery.py", line 39, in button_click
    manager.execute(delete_this_data)
    File "C:\Python27\lib\site-packages\mysql\connector\cursor.py", line 529, in execute
    stmt = operation.encode(self._connection.python_charset)
    AttributeError: 'tuple' object has no attribute 'encode'



  • @Gelo

    The error says exactly what the problem is.
    You are passing a tuple (something like (object, object) as a parameter to the cursor.execute()

    • What exactly is data and where do you get it from?
    • You dont need to use """ for a single line string
    • Even if you replace , with % in delete_this_data = ("""DELETE FROM test_table WHERE name = %s""",str(data)) it will not work as you have to put a string in ' ' inside a SQL statement

    (i have set data to "test" for this example)

    >>> data = "text"
    >>> delete_this_data = ("""DELETE FROM test_table WHERE name = %s""",str(data))
    >>> print delete_this_data
    ('DELETE FROM test_table WHERE name = %s', 'text')
    >>> print type(delete_this_data)
    <type 'tuple'>
    >>> delete_this_data = ("""DELETE FROM test_table WHERE name = %s"""%str(data))
    >>> print delete_this_data
    DELETE FROM test_table WHERE name = text
    >>> delete_this_data = ("""DELETE FROM test_table WHERE name = '%s'"""%str(data))
    >>> print delete_this_data
    DELETE FROM test_table WHERE name = 'text'
    
    


  • @the_ said in Why name='%s' is not deleting my query but if i set to name='something' it deletes.:

    delete_this_data = ("""DELETE FROM test_table WHERE name = %s"""%str(data))

    i got an error invalid syntax with %str(data)



  • @the_ said in Why name='%s' is not deleting my query but if i set to name='something' it deletes.:

    • What exactly is data and where do you get it from?

    And please remove the unneccessary " from a single line string



  • @the_ raise errors.get_exception(packet)
    mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'meratots' in 'where clause'



  • @the_ Thank you for this it helps me a lot! what does this do , i remove that and it works fine! Thank you men! your so awesome! :D


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.