python-study-41
pymysql
#pip3 install pymysql 先安装第三方模块 #本质：套接字客户端 import pymysql #模块 #发起连接拿到套接字对象 client=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='egon123', database='db6', #服务端必须先创建好数据库 charset='utf8' ) #拿到一个能提交sql语句的游标对象 #游标 接收用户传入的sql语句 send发给服务端 #底层就是send cursor=client.cursor() sql='insert into t1 values(1,"egon");' #一条sql语句 try: #捕获异常 和mysql的事务一样 res=cursor.execute(sql) #给游标提交语句execute 提交但没执行，此时数据库看不到插入的数据 print(res) #cursor提交是有返回值的 返回值是影响的行数 # cursor.execute(sql) # cursor.execute(sql) # cursor.execute(sql) client.commit() #真正执行（增删改行为必须要commit），无法回退了，防止异常的 except Exception: client.rollback() #捕获到异常 回滚到没执行的状态 应用在转账交易操作方面 cursor.close() #关闭游标 client.close() #关闭链接 回收资源
#pip3 install pymysql 第三方模块 先安装 import pymysql client=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='egon123', database='db6', charset='utf8' ) cursor=client.cursor() #1 # sql='insert into t1 values(3,"alex"),(4,"lxx");' 批量写入数据库 这是一种方式 但是写死了 #2 #多个用户写入数据库的方法 # userinfo=[ # (3,"alex"), # (4,"lxx"), # (5,"yxx") # ] # for user in userinfo: 遍历写入用execute这种麻烦 可以用executemany 一样的功能 # sql='insert into t1 values(%s,"%s");' %(user[0],user[1]) 注意加引号 # # print(sql) # cursor.execute(sql) #3 # userinfo=[ # (3,"alex"), # (4,"lxx"), # (5,"yxx") # ] # sql='insert into t1 values(%s,%s);' 批量 # cursor.executemany(sql,userinfo) 本质就是for循环 注意插入的个数要一一对应 cursor.execute('delete from t1 where id=3;') #删除操作 client.commit() cursor.close() client.close()
# 提交查询语句并且拿到查询结果 import pymysql client=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='egon123', database='db6', charset='utf8' ) #如果不加的话 从管道中拿的是元组 加上就会有标题 变成字典列表形式 cursor=client.cursor(pymysql.cursors.DictCursor) #查询 sql='select * from user where id > 3' rows=cursor.execute(sql) #异步 sql语句传给服务端 就取执行其他事情 # print(rows) # print(cursor.fetchall()) #从管道中拿到执行的结果 拿完 # print(cursor.fetchall()) # print(cursor.fetchone()) #拿一条 # print(cursor.fetchone()) # print(cursor.fetchone()) # print(cursor.fetchmany(2)) #拿指定的条数 # print(cursor.fetchone()) #类似指针的概念 scroll # print(cursor.fetchall()) # # cursor.scroll(0,mode='absolute') # 绝对位置移动 指针移动到头部 从头再查一遍 # # cursor.scroll(1,mode='absolute') # 绝对位置移动 指针移动到下一条 # print(cursor.fetchall()) # print(cursor.fetchone()) # cursor.scroll(2,mode='relative') # 相对当前位置移动 跳过两条 # print(cursor.fetchone()) cursor.close() client.close()
#pip3 install pymysql import pymysql client=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='egon123', database='db5', charset='utf8' ) cursor=client.cursor(pymysql.cursors.DictCursor) #返回值以列表中字典形式返回，sql语句的执行结果 #mysql中写好存储过程在python程序中调用 参数一个给n一个给res res=cursor.callproc('p4',(3,111)) #set @_p4_0 = 3; set @_p4_1 = 111 # print(res) 返回值就是参数 没什么用 #fetchall 类似于从管道中取值 一次取完 指针移动到末尾 print(cursor.fetchall()) cursor.execute('select @_p4_1;') #提交查看标识的sql语句 print(cursor.fetchone()) #查看最后的执行的结果 cursor.close() client.close()
# #pip3 install pymysql # import pymysql # # client=pymysql.connect( # host='127.0.0.1', # port=3306, # user='root', # password='egon123', # database='db6', # charset='utf8' # ) # # cursor=client.cursor() #思路：1 输入用户名、密码 2、拼接sql 3、是否有返回值 判断是否验证成功 # #查询 不需要commit 登录验证 从数据库拿数据验证 登录 # inp_user=input('输入账号名: ').strip() # inp_pwd=input('输入密码: ').strip() # #注意传入密码 要用密文 一般数据库中存的都是密文 所以明文对不上 # 密文长度很长 注意设计表的时候要建好长度 不然会超过限制 密文存不全 # # sql='select id from user where name = "%s" and pwd = password("%s");' %(inp_user,inp_pwd) # sql='select id from user where name = "%s" and pwd = "%s";' %(inp_user,inp_pwd) # print(sql) # rows=cursor.execute(sql) #如果name pwd都正确的话 就会返回一个值 # if rows: 如果返回了id值 说明用户名 密码都正确 # print('\033[45m登陆成功\033[0m') # else: # print('\033[46m用户名或密码错误\033[0m') # # cursor.close() # client.close() # #sql注入：通过mysql的一些特殊的符号，来改变sql语句的运行逻辑，从而不需要账号密码 都能登录 #小知识 -- mysql中的注释 #问题1：egon" -- xxxx 密码不用输入 也能登录成功 #'select id from user where name = "%s" and pwd = password("%s");' %(inp_user,inp_pwd) #'select id from user where name = "egon" -- xxxx" and pwd = password("%s");' %(inp_user,inp_pwd) #问题2：xxx" or 1=1 -- hello 密码不写 也能登录 #'select id from user where name = "xxx" or 1=1 -- hello" and pwd = password("%s");' %(inp_user,inp_pwd) #解决方法：早期的网站很多这种问题，现在的网站一般都不允许用特殊符号了，就是为了避免这种问题 #1 前端检测，不允许带特殊符号，全部过滤掉，就不会改变sql的逻辑执行 发给应用程序 # pymysql已经解决这个问题了 看后面 #2 不用前端浏览器，跳过检测，所以应用程序也要检测一遍 # 解决 sql注入问题 不自己拼接sql语句了 #pip3 install pymysql # import pymysql # # client=pymysql.connect( # host='127.0.0.1', # port=3306, # user='root', # password='egon123', # database='db6', # charset='utf8' # ) # # cursor=client.cursor() # #查询 # inp_user=input('输入账号名: ').strip() # inp_pwd=input('输入密码: ').strip() # # sql='select id from user where name = %s and pwd = %s;' # rows=cursor.execute(sql,(inp_user,inp_pwd)) #这一步就自动过滤掉了 # if rows: # print('\033[45m登陆成功\033[0m') # else: # print('\033[46m用户名或密码错误\033[0m') # # cursor.close() # client.close()
navicat
一个桌面版的连接服务端的软件
解压即可使用
mysql服务端的功能
开发中用不到，面试要用，需要了解一下
这里面的功能在python程序里都可以实现。
mysql是想把应用程序解放出来
实际应用中还是在程序中实现较多
# 强调 #1、字段名不能重复 #2、视图是为了简化查询的sql语句，不应该修改视图中的记录 create view emp2dep as select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id = dep.id; 视图就是虚拟表 从多个表中查出来的 视图保存：只有一张表，没有数据，相当于存储的sql语句 数据来自于其他表
增删改没有查会触发触发器的执行 CREATE TABLE cmd ( id INT PRIMARY KEY auto_increment, USER CHAR (32), priv CHAR (10), cmd CHAR (64), sub_time datetime, #提交时间 success enum ('yes', 'no') #0代表执行失败 ); CREATE TABLE errlog ( id INT PRIMARY KEY auto_increment, err_id int ); #创建触发器 检测写入的记录 如果有错的 就向errlog中也写入一份记录 delimiter $$ （也可以用//） #改变sql的结束符 让分号变成无意义 CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW BEGIN if NEW.success = 'no' then #NEW: cmd新的记录被封装成了对象 insert into errlog(err_id) values(NEW.id); #满足条件就插入到另外一张表 end if; END $$ 结束 delimiter ; #还原成分号作为结束符 INSERT INTO cmd ( USER, priv, cmd, sub_time, success ) VALUES ('egon','0755','ls -l /etc',NOW(),'yes'), ('egon','0755','cat /etc/passwd',NOW(),'no'), ('egon','0755','useradd xxx',NOW(),'no'), ('egon','0755','ps aux',NOW(),'yes');
#transaction:事务，交易中使用 # 事务可以包含一系列的sql语句，事务的执行具有原子性 #1、原子性： #包含多条sql语句要么都执行成功，要么都执行不成功 #2、回滚 create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('wsb',1000), ('egon',1000), ('ysb',1000); #创建事务 回滚 start transaction; try: update user set balance=900 where id=1; update user set balance=1010 where id=2; update user set balance=1090 where id=3; commit; #只要commit就不可逆了 在这之前都可以rollback回滚 except Exception: rollback; #pymysql 模块默认开启事务 #mysql 也可以实现事务
创建在某个库下面 # 无参 delimiter $$ create procedure p1() BEGIN select * from blog; END $$ delimiter ; create table s1( id int, name varchar(20), gender char(6), email varchar(50) ); mysql中调用：call p1(); delimiter $$ create procedure p2() BEGIN declare n int default 1; --声明一个变量 while (n < 100) do insert into s1 values(n,concat('egon',n),'male',concat('egon',n,'@163.com')); set n=n+1; --自增 end while; END $$ delimiter ; mysql:call p2(); 调用 # 有参 delimiter $$ create procedure p3( in n int, 接收外部传入的值 out res int 用来返回的 #inout x int 这种既能接收也能返回 ) BEGIN select * from blog where id > n; set res = 0; 返回值 用来标识执行成功与否 END $$ delimiter ; # 直接在mysql中调用存储过程： 有参的调用 mysql> set @x=111; 设置变量 mysql> call p3(3,@x); x传给res mysql> select @x; 查询变量 +------+ | @x | +------+ | 0 | +------+ 1 row in set (0.00 sec) # 在python中调用： cursor.callproc('p4',(3,111)) #set @_p4_0 = 3; set @_p4_1 = 111 print(cursor.fetchall()) cursor.execute('select @_p4_1;') print(cursor.fetchone())
date_formate(sub_time,'%Y-%m') 切割时间 函数要和sql语句结合使用，不能单独调用
1 条件语句 if 2 循环语句 while repeat loop
应用程序与数据库的结合开发方案： 方案一： 配两个人 一个人写应用程序 一个写sql 但是人之间的沟通成本问题 扩展问题等 应用程序： mysql：编写存储过程 方案二： 应用程序：原生sql mysql： 方案三： 应用程序：ORM(类/对象 --->原生sql) 类-表 对象-记录 mysql： 执行效率： 方案一 > 方案二 -> 方案三 开发效率： 方案一 > 方案三 -> 方案二 我们以后用方案三 存储过程：把视图，触发器，流程控制，等功能 分装成接口提供给应用程序使用
