How to delete a mysql row without using field names when using python and mysqldb?

Question

# -*- coding: utf-8 -*-

import re
import sys
import MySQLdb
from getpass import getpass

reload(sys)
sys.setdefaultencoding('utf-8')

conn = MySQLdb.connect(host, user, passwd, db, charset = 'utf-8')
cur = conn.cursor()
cur.execute("show tables")
tablenames = [i[0] for i in cur.fetchall()]

cur.execute("SELECT * FROM %s" % tablenames)
rows = cur.fetchall()
for row in rows:
    x = re.compile(r"\bhello\b")
    p = x.search(str(row))
    if p:
    cur.execute("DELETE FROM %s WHERE " % t) # how to delete this row

conn.close()

Using the code above, I would like to search the table rows with regular expressions, and search for the keyword "hello".

If it's matched, I'd like to delete the row which for loop in rows that fetched all.

How can I write the delete statement when the regular expression found the row?

Thanks very much!

That's not a row, it's a tablename (as indicated by your variable name) Do you want to DROP TABLE it or DELETE FROM all the rows in it, or something else? — moopet
– moopet, Commented Sep 20, 2012 at 16:46
that "where" line i'm not finished, i want to delete one row that fit my search condition, delete one row that i checking. — Hisone Nightmare
– Hisone Nightmare, Commented Sep 21, 2012 at 0:58

mortymacs · Accepted Answer · 2013-08-14 09:07:19Z

2

you can use LIKE in your SQL:

DELETE FROM `table` WHERE `content` LIKE "%value%"

and important thing after executing your sql is commit:

a = cur.execute(sql)
con.commit()

answered Aug 14, 2013 at 9:07

mortymacs

3,7865 gold badges34 silver badges58 bronze badges

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

Comments

Germann Arlington · Accepted Answer · 2012-09-28 08:50:40Z

0

If as I suspect you want to delete rows which meet some criteria than you don't need the select at all.
DELETE FROM tableName WHERE columnName [=, !=, <, >, LIKE] columnValue
If you need regex match than read this: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

edited Sep 28, 2012 at 8:50

answered Sep 20, 2012 at 16:53

Germann Arlington

3,3532 gold badges19 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to delete a mysql row without using field names when using python and mysqldb?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related