CODEWITHSUNDEEP
pythonmysql
Cmag
Cmag

Reputation: 15750

python mysqldb delete row

for some reason, this is bombing out:

print tbl,record
statmt="DELETE FROM '%s' WHERE email LIKE '%s'" %(tbl,record)
print statmt
self.cursor.execute(statmt)

error:

maillist_frogs [email protected]
DELETE FROM 'maillist_frogs' WHERE email LIKE '[email protected]'
Traceback (most recent call last):
  File "./compare.py", line 123, in <module>
    main()
  File "./compare.py", line 117, in main
    remove_mailgust = sql_mailgust.removeRow ("maillist_frogs",x)
  File "./compare.py", line 81, in removeRow
    self.cursor.execute(statmt)
  File "build/bdist.macosx-10.6-intel/egg/MySQLdb/cursors.py", line 174, in execute
  File "build/bdist.macosx-10.6-intel/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
_mysql_exceptions.ProgrammingError: (1064, "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 ''maillist_frogs' WHERE email LIKE '[email protected]'' at line 1")

Thanks!! :)

Upvotes: 2

Views: 9941

Answers (3)

Nelson Hoover
Nelson Hoover

Reputation: 159

I was having a similar problem just now.

After further research, I realized I'd forgotten to do a connection.commit() after the delete, or, as I found somewhere else, you could just do cursor.execute("set autocommit = 1") before doing any other data operations to have them automatically committed right away, if you don't need control over transactions commits.

This could have been the trouble with Cmag's code, though without seeing more, it's hard to tell.

Upvotes: 8

Thomas Orozco
Thomas Orozco

Reputation: 55197

You have two issues:

The first one, that is causing your crash, is that you quoted your table name, using regular quotes. If you want to quote table names, you should use backquotes (`).

Second issue, you're 'Not doing it right'. You should not create your queries using string formatting, but instead let Python MySQLdb's cursor.execute do it for you, properly.

To do so, try the following:

statmt = "DELETE FROM %s WHERE email LIKE %s" 
self.cursor.execute(statmt, (tbl, record))

By the way, using string formatting on MySQLdb queries exposes you to SQL injection in case you're going to use your application on the Web or something. You should not be using string formatting to create queries.

Upvotes: 1

Cmag
Cmag

Reputation: 15750

def removeRow(self,tbl,record):
        """ Remove specific record """
        record = record[0]
        statmt="DELETE FROM %s WHERE email LIKE '%s'" %(tbl,record)
        self.cursor.execute(statmt)

Upvotes: 2

Related Questions