python删除mysql数据库_Python操作MySQL数据库（检索/删除数据库中数据）

1、准备安装Python的mysql模块"MySQLdb"和Python表格模块“prettytable”(之前文章已介绍，在此不在赘述)

[root@lnmp ~]# python

Python 2.6.6 (r266:84292, Jan 22 2014, 09:42:36)

[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2

Type "help", "copyright", "credits" or "license" for more information.

>>> import MySQLdb

>>> from prettytable import PrettyTable                # 确保模块已安装

2、程序主体

[root@lnmp ~]# vim xt_forbidacc_del.py

#!/usr/bin/python

import os,sys,MySQLdb

from prettytable import PrettyTable

hostip = '127.0.0.1'

username = 'root'

passwd = 'redhat'

db = 'mysql'

try:

acc = raw_input('Please input accname:').strip()

conn=MySQLdb.connect(hostip,username,passwd,db,port=3306)

cur=conn.cursor()

cur.execute("select host,user,password from user where user='%s'" %acc)

jilu = cur.fetchone()

if jilu == None:

print '######## No such user:\033[33;3m%s\033[0m'%acc

sys.exit()

else:

x = PrettyTable()

x.field_names = ['host','user','password']

x.padding_width = 1

x.add_row([jilu[0],jilu[1],jilu[2]])

print x

sure = raw_input('Are you sure delete \033[33;3m%s\033[0m:[y/n]' %acc).strip()

if sure == 'y' or sure == 'Y':

cur.execute("delete from user where user='%s'" %acc)

conn.commit()

else:

cur.close()

conn.close()

sys.exit()

cur.close()

conn.close()

except MySQLdb.Error,e:

print "Mysql Error %d: %s" % (e.args[0], e.args[1])

程序测试

[root@lnmp ~]# mysql -uroot -predhat

mysql> select host,user,password from mysql.user;                # 删除前数据库数据

+-----------+------+-------------------------------------------+

| host      | user | password                                  |

+-----------+------+-------------------------------------------+

| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| lnmp      | root |                                           |

| 127.0.0.1 | root |                                           |

| localhost |      |                                           |

| lnmp      |      |                                           |

| localhost | shaw | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

+-----------+------+-------------------------------------------+

6 rows in set (0.00 sec)

[root@lnmp ~]# python xt_forbidacc_del.py                         # 删除账号“shaw”

Please input accname:shaw

+-----------+------+-------------------------------------------+

|    host   | user |                  password                 |

+-----------+------+-------------------------------------------+

| localhost | shaw | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

+-----------+------+-------------------------------------------+

Are you sure delete shaw:[y/n]y

[root@lnmp ~]# mysql -uroot -predhat

mysql> select host,user,password from mysql.user;                # 删除后数据库数据

+-----------+------+-------------------------------------------+

| host      | user | password                                  |

+-----------+------+-------------------------------------------+

| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| lnmp      | root |                                           |

| 127.0.0.1 | root |                                           |

| localhost |      |                                           |

| lnmp      |      |                                           |

+-----------+------+-------------------------------------------+

5 rows in set (0.01 sec)