python3操作mysql数据库增删改查

python3.x 使用pymysql操作mysql，python2.x使用mysqldb操作mysql

#!/usr/bin/python3
import pymysql
import types

db=pymysql.connect("localhost","root","123456","python");

cursor=db.cursor()

#创建user表
cursor.execute("drop table if exists user")
sql="""CREATE TABLE IF NOT EXISTS `user` (
	  `id` int(11) NOT NULL AUTO_INCREMENT,
	  `name` varchar(255) NOT NULL,
	  `age` int(11) NOT NULL,
	  PRIMARY KEY (`id`)
	) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""

cursor.execute(sql)


#user插入数据
sql="""INSERT INTO `user` (`name`, `age`) VALUES
('test1', 1),
('test2', 2),
('test3', 3),
('test4', 4),
('test5', 5),
('test6', 6);"""

try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()
   
   
#更新
id=1
sql="update user set age=100 where id='%s'" % (id)
try:
	cursor.execute(sql)
	db.commit()
except:
	db.rollback()
	
#删除
id=2
sql="delete from user where id='%s'" % (id)
try:
	cursor.execute(sql)
	db.commit()
except:
	db.rollback()
	
	
#查询
cursor.execute("select * from user")

results=cursor.fetchall()

for row in results:
	name=row[0]
	age=row[1]
	#print(type(row[1])) #打印变量类型 <class 'str'>

	print ("name=%s,age=%s" % \
             (age, name))

执行结果

[root@mail pythonCode]# python3 test.py
name=test1,age=1
name=test3,age=3
name=test4,age=4
name=test5,age=5
name=test6,age=6