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

[A Former User]

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!"

[mrjj]

How do you replace %s ?

[A Former User]

@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

[mrjj]

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

[A Former User]

@mrjj can you help me with that?

[mrjj]

@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();

[VRonin]

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

[Bui Trung]

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

[A Former User]

@Bui-Trung i am using python

[mrjj]

@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

[Bui Trung]

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?

[A Former User]

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

[VRonin]

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

Did you try @mrjj 's solution?

[the_]

@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

[the_]

@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

[A Former User]

@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'

[the_]

@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'

[A Former User]

@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_]

@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

[A Former User]

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