mysql入门第三天_pymysql操作
1 import pymysql 2 3 4 #链接数据库 5 #参数1：mysql服务器所在主机 6 7 8 db=pymysql.connect('localhost','root','123,'kaige') 9 10 #创建一个cursor对象 11 cursor=db.cursor() 12 13 sql="select * from student limit 0,3;" 14 15 #执行sql语句 16 17 # cursor.execute(sql) 18 # #获取返回的信息 19 # data=cursor.fetchone() 20 # for i in data: 21 # print(i) 22 23 #检查表是否存在，如果存在则删除 24 # cursor.execute("drop table if EXISTS bandcard") 25 #建表 26 # sql='create table bandcard(id int auto_increment primary key,money int not null)' 27 28 # cursor.execute(sql) 29 sql='insert into bandcard VALUES (0,100),(0,200),(0,300),(0,400),(0,500)' 30 try: 31 cursor.execute(sql) 32 db.commit() 33 except: 34 #如果提交失败，回滚到上一次的数据 35 db.rollback() 36 37 finally: 38 cursor.close() 39 40 #断开 41 # cursor.close() 42 db.close()
数据库更新数据操作：
1 import pymysql 2 3 db=pymysql.connect("192.168.0.100","root","123","kaige") 4 5 cursor=db.cursor() 6 7 sql="update bandcard set money=1000 WHERE id=1" 8 9 try: 10 cursor.execute(sql) 11 db.commit() 12 13 except: 14 db.rollback() 15 16 finally: 17 cursor.close() 18 19 db.close()
结果：
更新后： 
数据库删除操作：
1 import pymysql 2 3 db=pymysql.connect("192.168.0.100","root","123","kaige") 4 5 cursor=db.cursor() 6 7 # sql="update bandcard set money=1000 WHERE id=1" 8 sql="delete from bancard WHERE money=200" 9 try: 10 cursor.execute(sql) 11 db.commit() 12 13 except: 14 db.rollback() 15 16 finally: 17 cursor.close() 18 19 db.close()
查看：
1 import pymysql 2 3 db=pymysql.connect("192.168.0.100","root","123","kaige") 4 5 cursor=db.cursor() 6 7 # sql="update bandcard set money=1000 WHERE id=1" 8 # sql="delete from bandcard WHERE money=200" 9 #查询操作： 10 ''' 11 fetchone() 接受返回一行 12 13 14 fetchall() 接受全部的返回的行 15 16 17 rowcount:是一个只读属性，返回execute()方法影响的行数 18 ''' 19 20 sql='select * from bandcard where money>300' 21 22 23 try: 24 cursor.execute(sql) 25 # db.commit() 26 data=cursor.fetchall() 27 28 for i in data: 29 print(i[0],">>>",i[1]) 30 31 except: 32 db.rollback() 33 34 finally: 35 cursor.close() 36 37 db.close()
