CODEWITHSUNDEEP
pythonmysql-python
Hisone Nightmare
Hisone Nightmare

Reputation: 9

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

# -*- 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!

Upvotes: 0

Views: 745

Answers (2)

mortymacs
mortymacs

Reputation: 3736

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

Upvotes: 2

Germann Arlington
Germann Arlington

Reputation: 3353

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

Upvotes: 0

Related Questions