python3操作mysql数据库增删改查

缺乏、安全感 2022-09-23 13:56 266阅读 0赞

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