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()

 

posted @ 2018-06-12 09:12  巨兽~墨菲特  阅读(130)  评论(0)    收藏  举报