思绪来得快 也去得快 偶尔在这里停留

Blog

首页博客
07
Oct
0

获取变量名自己的名字

import inspect
import re
def varname(p):

for line in inspect.getframeinfo(inspect.currentframe().f_back)[3]:
    m = re.search(r'\bvarname\s*\(\s*([A-Za-z_][A-Za-z0-9_]*)\s*\)', line)
    if m:
        return m.group(1)
16
Aug
0

上一行数据减去下一行数据

Oracle

--1.创建测试表
create table tmp as
select 1 id, 20 percent from dual union all
select 1 id, 30 percent from dual union all
select 1 id, 50 percent from dual union all
select 2 id, 20 percent from dual union all
select 2 id, 20 percent from dual union all
select 2 id, 20 percent from dual union all
select 2 id, 20 percent from dual;
--2.SQL实现
select id,100-sum(percent)over(partition by id order by rownum) percent
from tmp;

SQL SERVER

select b.相减字段-a.相减字段 from(select from (select Row_Number() over ( order by 排序字段 ) as RN , from Table Name) where rn%2=0) a
inner join
(select from (select Row_Number() over ( order by 排序字段 ) as RN , from Table Name) where rn%2<>0) b on a.rn=b.rn-1

mysql

select * from
(select (@i:=@i+1) as no,id from job_log ,(select @i:=0)t where job_id =131) as t1
left join
(select (@k:=@k+1) as no,id from job_log ,(select @k:=0)t where job_id =131) as t2
on (t1.no = t2.no-1)
order by t1.no

11
Aug
0

安装uwsgi出错

错误1:
centos 6.5
python 3.6
core/routing.o: In function `uwsgi_route_condition_regexp':

routing.c:(.text+0x365c): undefined reference to `pcre_free_study'
routing.c:(.text+0x3680): undefined reference to `pcre_free_study'
collect2: ld 返回 1
*** error linking uWSGI ***

解决方法:
需要先御载pcre-devel

yum remove pcre-devel
pip3 install uwsgi 
08
Aug
0

Let's Encrypt(https证书)

支持

python2.7
git

证书申请

#获取letsencrypt
git clone https://github.com/letsencrypt/letsencrypt
#进入letsencrypt目录
cd letsencrypt
#生成证书
./letsencrypt-auto certonly --standalone --email 123@123.com -d 123.com -d www.123.com

证书保存位置

/etc/letsencrypt/live/123.com/

文件说明

cert.pem - Apache服务器端证书
chain.pem - Apache根证书和中继证书
fullchain.pem - Nginx所需要ssl_certificate文件
privkey.pem - 安全证书KEY文件

Nginx配置

#打开linux配置文件,找到HTTPS 443端口配置的server
ssl_certificate /etc/letsencrypt/live/123.com/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/live/123.com/privkey.pem;

续证书有效期(默认只有90天)

./letsencrypt-auto certonly --renew-by-default --email 123@123.com -d 123.com -d www.123.com
16
Jul
0

mysql 触发器里使用游标

--存储过程
CREATE DEFINER = 'root'@'10.200.%.%'
PROCEDURE geely-itil-pc-test.StatisticStore()
BEGIN
-- 创建接收游标数据的变量
DECLARE c int;
DECLARE n varchar(20);
-- 创建总数变量
DECLARE total int DEFAULT 0;
-- 创建结束标志变量
DECLARE done int DEFAULT FALSE;
-- 创建游标
DECLARE cur CURSOR FOR
SELECT

id

FROM message_option;
-- 指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 设置初始值
SET total = 0;
-- 打开游标
OPEN cur;
-- 开始循环游标里的数据
read_loop:
LOOP

-- 根据游标当前指向的一条数据
FETCH cur INTO c;
-- 判断游标的循环是否结束
IF done THEN
  LEAVE read_loop;    -- 跳出游标循环
END IF;
-- 获取一条数据时,将count值进行累加操作,这里可以做任意你想做的操作,
SET total = total + c;

-- 结束游标循环
END LOOP;
-- 关闭游标
CLOSE cur;

-- 输出结果
SELECT

total;

END

-- 普通执行SQL语句
DROP TRIGGER IF EXISTS group_state_update_user_logout;
delimiter ;;
CREATE TRIGGER group_state_update_user_logout AFTER UPDATE ON operation_group FOR EACH ROW BEGIN

DECLARE cid integer DEFAULT 0;
DECLARE done int DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT  user_id  FROM user_group WHERE group_id = new.id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

IF (old.state <> new.state) then

OPEN cur;
read_loop:
LOOP
  FETCH cur INTO cid;
  IF done THEN
    LEAVE read_loop;
  END IF;
  UPDATE user_cache set last_check_time = '2017-01-01' WHERE user_id = cid;
END LOOP;
CLOSE cur;

END IF;
END
;;
delimiter ;

-- 生成SQL语句并执行 (函数和触发器暂时不支持动态语句,只能在存储过程中使用)

set exesql = CONCAT('UPDATE user_cache set last_check_time = \'2017-01-01\' WHERE user_id IN(',ids,')');
prepare stmt from @exesql;  -- 编译语句 
EXECUTE stmt; -- 执行语句 
EXECUTE prod USING ids; -- 执行语句 ids 替换?参数
DEALLOCATE prepare prod; -- 释放解析和执行sql语句