python mysqldb delete row

Question

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!! :)

How about this: statmt="DELETE FROM %s WHERE email LIKE '%s'" %(tbl,record). — M.Babcock
– M.Babcock, Commented Jan 1, 2012 at 19:50
If I replace self.cursor.execute("DELETE FROM %s.email WHERE email LIKE'%s'", tbl,record) then I get TypeError: execute() takes at most 3 arguments (4 given) — Cmag
– Cmag, Commented Jan 1, 2012 at 19:50

Nelson Hoover · Accepted Answer · 2017-09-21 18:49:04Z

8

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.

edited Sep 21, 2017 at 18:49

answered Mar 25, 2014 at 15:54

Nelson Hoover

1794 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cmag · Accepted Answer · 2012-01-01 19:55:04Z

2

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)

answered Jan 1, 2012 at 19:55

Cmag

15.9k26 gold badges97 silver badges147 bronze badges

Comments

Thomas Orozco · Accepted Answer · 2012-01-01 20:38:05Z

1

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.

answered Jan 1, 2012 at 20:38

Thomas Orozco

55.6k12 gold badges120 silver badges120 bronze badges

4 Comments

Cmag Over a year ago

fixed: paste.pocoo.org/show/528559, problem now is that the script just loops, no sql records ever get deleted

Thomas Orozco Over a year ago

But no exceptions get raised? The cursor.execute call should return the number of rows deleted, could you print that and check it? Can you double-check that the records do exist in the DB?

Cmag Over a year ago

the script at first compares the databases, so yes, records exist for sure

Thomas Orozco Over a year ago

Well, I tried using your code, it works for me, so your problem is probably somewhere in the DB, did you double-check it? Do you have the necessary permissions? Maybe you could try doing it using MySQL's CLI? Are you sure remove, and x[0] are what you expect? It seems strange that the cursor.execute call would return None given that if no rows are deleted, it returns 0, is everything OK there?

Collectives™ on Stack Overflow

python mysqldb delete row

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related