sunnyxuebuhui 2020-06-04
import pymysql conn=pymysql.connect(host=‘127.0.0.1‘,user=‘root‘,password=‘123‘,database=‘homework‘) #host是目标数据库ip地址,user是数据库授权用户,password是用户密码,database是数据库 cur=conn.cursor() #设置游标 其中括号内可以写cursor=pymysql.cursors.DictCorsor,表示查询的时候输出为字典形式 sql=‘select * from student;‘ #其中;可加可不加 cur.execute(sql) ret1=cur.fetchone() #从数据库取出一行数据 print(ret1) #(1, ‘男‘, 1, ‘理解‘) ret2=cur.fetchmany(10) #从数据库取出十行数据,注意,前面已经拿出了一个数据,所有会从第二个数据开始取,类似于迭代器 print(ret2) #((2, ‘女‘, 1, ‘钢蛋‘), (3, ‘男‘, 1, ‘张三‘), (4, ‘男‘, 1, ‘张一‘), (5, ‘女‘, 1, ‘张二‘), (6, ‘男‘, 1, ‘张四‘), (7, ‘女‘, 2, ‘铁锤‘), (8, ‘男‘, 2, ‘李三‘), (9, ‘男‘, 2, ‘李一‘), (10, ‘女‘, 2, ‘李二‘), (11, ‘男‘, 2, ‘李四‘)) ret3=cur.fetchall() #从数据库取出所有数据 print(ret3) #((12, ‘女‘, 3, ‘如花‘), (13, ‘男‘, 3, ‘刘三‘), (14, ‘男‘, 3, ‘刘一‘), (15, ‘女‘, 3, ‘刘二‘), (16, ‘男‘, 3, ‘刘四‘)) cur.close() conn.close() #rowcount:这是一个可读属性,可以查出操作了多少行 import pymysql conn=pymysql.connect(host=‘127.0.0.1‘,user=‘root‘,password=‘123‘,database=‘homework‘) cur=conn.cursor() sql=‘select * from student‘ cur.execute(sql) print(cur.rowcount) for i in range(cur.rowcount): print(cur.fetchone()) cur.close() conn.close() 16 (1, ‘男‘, 1, ‘理解‘) (2, ‘女‘, 1, ‘钢蛋‘) (3, ‘男‘, 1, ‘张三‘) (4, ‘男‘, 1, ‘张一‘) (5, ‘女‘, 1, ‘张二‘) (6, ‘男‘, 1, ‘张四‘) (7, ‘女‘, 2, ‘铁锤‘) (8, ‘男‘, 2, ‘李三‘) (9, ‘男‘, 2, ‘李一‘) (10, ‘女‘, 2, ‘李二‘) (11, ‘男‘, 2, ‘李四‘) (12, ‘女‘, 3, ‘如花‘) (13, ‘男‘, 3, ‘刘三‘) (14, ‘男‘, 3, ‘刘一‘) (15, ‘女‘, 3, ‘刘二‘) (16, ‘男‘, 3, ‘刘四‘)
import pymysql conn=pymysql.connect(host=‘127.0.0.1‘,user=‘root‘,password=‘123‘,database=‘homework‘) cur=conn.cursor() try: sql=‘insert into student values(17,"男","1","大黄");‘ #mysql执行语句 ,注意" "与‘‘的问题 cur.execute(sql) conn.commit() #这是区别于查询的地方,得提交命令 except Exception as e: print(e) conn.rollback() #若try中数据出现问题,将游标回滚至开始处,sql语句不生效 cur.close() conn.close()
import pymysql name=input("uesrname:") pwd=input("password:") conn=pymysql.connect(host=‘127.0.0.1‘,user=‘root‘,password=‘123‘,database=‘users‘) cur=conn.cursor() sql=‘select * from userinfo where name=%s and pwd=%s;‘ #这里不能使用"‘select * from userinfo where name=%s and pwd=%s‘%(name,pwd)",会产生sql注入问题 cur.execute(sql,(name,pwd)) print(cur.fetchone()) cur.close() conn.close()
#表的备份 #mysqldump -u用户 -p密码 -h目标数据库ip地址 库.表>存放的绝对地址 C:\Users\81533>mysqldump -uroot -p123 -h127.0.0.1 homework.student>C:\Users\81533\Desktop\mysql_data\student.sql #表的恢复 mysql>source C:\Users\81533\Desktop\mysql_data\student.sql
#库的备份 #mysqldump -uroot -p123 --databases 库名1,库名2,....(或者直接--all -databases:表示全部的库) >目标绝对路径 注意不加; C:\Users\81533>mysqldump -uroot -p123 --databases homework>C:\Users\81533\Desktop\mysql_data\homework.sql #库的恢复 mysql>source homework>C:\Users\81533\Desktop\mysql_data\homework.sql
begin; #开启事务 select * from student where id=1 forupdate; #for update表示将对这一块内容进行修改操作,所以数据库会对其上锁,组织其他用户对这一块内容进行操作 update student set age=18 where id=1; #对内容进行修改 commit; #提交事务,结束锁,其他用户可以修改该块内容