Python操作MySQL数据库（检索/删除数据库中数据）

最新推荐文章于 2024-05-30 10:26:22 发布

转载最新推荐文章于 2024-05-30 10:26:22 发布 · 1k 阅读

3 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/2428313/blog/492520

文章标签：

#数据库 #python #开发工具

2019独角兽企业重金招聘Python工程师标准>>>

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)

转载于:https://my.oschina.net/u/2428313/blog/492520