Android-Charts技术交流QQ群现已开通,欢迎加入。群号:170987350
Android-Charts技术社区网站http://faq.android-charts.com/

[Python]PostgreSQL字典/JSON类型递归自展开

July 15th, 2019 No comments

PostgreSql 习惯上会将特殊数据类型的各个节点按字典/JSON类型存储
程序中需要获得完整的数据信息的时候,需要对这个节点进行自展开。

以下使用global id方式进行展开,一般适用于SQL+NoSQL结合的系统使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import sys, os
import numpy as np
 
def get_object_by_gid(id):
	for dict in data["json"]:
		if dict["gid"] == id:
	return dict.copy()
 
def self_exact_node(key):
	dict = get_object_by_gid(key)
	for k,v in dict.items():
		if k == "sub_item" :
			item_arr = []
			for id in v["gids"]:
				item_arr.append(self_exact_node(id))
			v["item_arr"] = item_arr.copy()
	return dict
 
def demo():
	data_exact = data.copy()
	for d in data_exact["json"]:
		d = self_exact_node(d["gid"])
 
def main():
	demo()
 
if __name__ == '__main__’:
	sys.exit(main())

其他玩法

1
2
3
4
5
6
7
8
# Global ID方式
"gid": "大分类2:中分类2:子分类2",
# 复合ID
"id":{"l1_cat":"大分类1","l2_cat":"中分类2","l3_cat":"子分类2"}
# 链表式
"chain":{"next_gid":"","pre_gid":"","head_gid":"","tail_gid":""}
# 二叉树式
"btree":{"next_sibling":"","child":”"}

Read more…

Categories: 语言编程 Tags: , , ,

[Python]使用OpenCV实现伪彩色和热力图

July 15th, 2019 No comments

使用applyColorMap可以对单个通道的图像进行伪彩色处理和热力图
OpenCV的定义了12种colormap常数,选择一个需要的即可
cv2.applyColorMap(heatmap_g, cv2.COLORMAP_JET)
图像可以使用addWeighted进行叠加处理
cv2.addWeighted(heatmap_img, alpha, merge_img, 1-alpha, 0, merge_img) # 将热度图覆盖到原图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
def heatmap_overlay(image,heatmap):
    # 灰度化heatmap
    heatmap_g = heatmap.astype(np.uint8)
    # 热力图伪彩色
    heatmap_color = cv2.applyColorMap(heatmap_g, cv2.COLORMAP_JET)
    # overlay热力图
    merge_img = image.copy()
    heatmap_img = heatmap_color.copy()
    overlay = image.copy()
    alpha = 0.25 # 设置覆盖图片的透明度
    #cv2.rectangle(overlay, (0, 0), (merge_img.shape[1], merge_img.shape[0]), (0, 0, 0), -1) # 设置蓝色为热度图基本色
    cv2.addWeighted(overlay, alpha, merge_img, 1-alpha, 0, merge_img) # 将背景热度图覆盖到原图
    cv2.addWeighted(heatmap_img, alpha, merge_img, 1-alpha, 0, merge_img) # 将热度图覆盖到原图
    return merge_img

参考:
https://blog.csdn.net/u013381011/article/details/78341861

Categories: 语言编程 Tags: ,

[Python]使用OpenCV实现图像和视频转换操作

July 15th, 2019 No comments

将视频按FPS拆解成单张图片
使用cv2.VideoCapture
cv2.VideoCapture(video_path)
计算FPS使用,注意部分压缩视频FPS存在丢帧情况,需要进行跳帧处理
fps = int(vidcap.get(cv2.CAP_PROP_FPS))

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
def video_split():
  video_path = 'test/video/video_01.mp4'
  video_name = video_path[:-4]
  vidcap = cv2.VideoCapture(video_path)
  success,image = vidcap.read()
  fps = int(vidcap.get(cv2.CAP_PROP_FPS))
  count = 0
  while success:
    image = image_process(image)
    cv2.imwrite("%s/%d.jpg" % (video_name, count), image)
    #if count % fps == 0:
    #    cv2.imwrite("%s/%d.jpg" % (video_name, int(count / fps)), image)
    print('Process %dth seconds: ' % int(count / fps), success)
    success,image = vidcap.read()
    count += 1

Read more…

Categories: 语言编程 Tags: ,

[Python]使用OpenCV进行轮廓检索

July 15th, 2019 No comments

对遮罩层进行轮廓检索并合并到图像上
第一步使用高斯模糊GaussianBlur模糊边缘像素
第二步使用Canny侦测边界,丢弃部分散点
最后使用findContours找到外框

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#对遮罩层进行轮廓检索并合并到图像上
def drawMaskContoursOverImage(image,mask):
    # convert colorspace
    gray = cv2.cvtColor(mask, cv2.COLOR_BGR2GRAY)
    #image = cv2.cvtColor(image, cv2.COLOR_GRAY2BGR)
 
    # 3*3 GaussianBlur
    # gray = cv2.GaussianBlur(gray, (3, 3), 0)
    # canny detect edge
    gray = cv2.Canny(gray, 100, 300)
 
    ret, thresh = cv2.threshold(gray, 127, 255, cv2.THRESH_BINARY)
 
    # binary是最后返回的二值图像
    #findContours()第一个参数是源图像、第二个参数是轮廓检索模式,第三个参数是轮廓逼近方法
    #输出是轮廓和层次结构,轮廓是图像中所有轮廓的python列表,每个单独的轮廓是对象边界点的(x,y)坐标的Numpy数组
    binary, contours, hierarchy = cv2.findContours(thresh, cv2.RETR_TREE, cv2.CHAIN_APPROX_SIMPLE)
    cv2.drawContours(image, contours, -1, (0, 255, 0), 1)
 
    # 写图像
    cv2.imwrite('%s.masked.png'%pair[0],image,[int(cv2.IMWRITE_PNG_COMPRESSION),3])

参考
https://blog.csdn.net/dz4543/article/details/80655067

Categories: 语言编程 Tags: ,

PostgreSQL使用PL/SQL和游标实现按日期批量执行

March 26th, 2019 No comments

现有的DWH系统的是按天创建数据表的,使得定期维护变得麻烦,例如每个月底需要将按当月产生的临时表archive。

方式1.批量生成SQL,按固定的日期值生成一堆SQLSQL生成方法多样。但是需要确认全部sql是否正确。

方式2.编写PL/SQL function,使用游标方式批量执行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
drop function fun_datecursor(from_date text,to_date text,cond text);
create or replace function fun_datecursor(from_date text,to_date text,cond text) returns text
as $ 
declare
    cur_date refcursor;
    buffer text := '';
    var_day date;
begin
open cur_date for execute 'SELECT generate_series('''|| from_date ||'''::date,'''|| to_date ||'''::date,'''|| cond ||''')'; 
    loop  --开始循环
        fetch cur_date into var_day;  --将游标指定的值赋值给变量
        if found then 
             ---------------------------------------
             --任意的逻辑,或调用其他function
             ---------------------------------------
            buffer:= buffer || to_char(var_day,'yyyyMMdd'); 
           else 
            exit; 
            end if; 
    end loop;  --结束循环
close cur_date;  --关闭游标
return buffer;
end
$ language plpgsql;

执行function

1
select fun_datecursor('2008-03-01','2008-03-31','1 day’);
Categories: 零敲碎打 Tags: , ,

PostgreSQL创建ReadOnly只读用户

March 26th, 2019 No comments

PostgreSQL可以通过schema和table级别对数据表进行只读控制
一般会使用PostgreSQL创建只读用户,然后给予相应的只读权限方式实现

通过使用

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建readonly_user用户,密码为readonly_password
create user readonly_user with encrypted password 'readonly_password';
-- 设置readonly_user用户为只读事务
alter user readonly_user set default_transaction_read_only=on;
-- 授予usage权限给到readonly_user用户
grant usage on schema "public" to readonly_user;
-- 将默认"public"schema下新建表的读取权限授予给readonly_user
alter default privileges in schema "public" grant select on tables to readonly_user;
-- 授予select权限给到readonly_user用户
grant select on all tables in schema "public" to readonly_user;
grant select on all sequences in schema "public" to readonly_user;
-- 允许readonly_user用户连接到指定数据库
grant connect on database splrp_dev to readonly_user;

注意:
已有的数据表进行readonly设置,可以通过

1
2
3
4
5
-- 授予usage权限给到readonly_user用户
grant usage on schema "public" to readonly_user;
-- 授予select权限给到readonly_user用户
grant select on all tables in schema "public" to readonly_user;
grant select on all sequences in schema "public" to readonly_user;

对于将来创建的新表,则需要通过

1
2
-- 将默认"public"schema下新建表的读取权限授予给readonly_user
alter default privileges in schema "public" grant select on tables to readonly_user;

可以讲以后创建的table也赋予readonly_user只读权限。

Categories: 零敲碎打 Tags: ,

[VBA]Base64编码和Base64解码

March 26th, 2019 No comments

VBA实现Base64编码和Base64解码,用于处理加密的URL非常方便。

VBA Base64 编码/加密函数:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
'VBA Base64 编码/加密函数:
Function Base64Encode(StrA As String) As String                                  'Base64 编码
    On Error GoTo over                                                          '排错
    Dim buf() As Byte, length As Long, mods As Long
    Dim Str() As Byte
    Dim i, kk As Integer
    kk = Len(StrA) - 1
    ReDim Str(kk)
    For i = 0 To kk
        Str(i) = Asc(Mid(StrA, i + 1, 1))
    Next i
    Const B64_CHAR_DICT = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/="
    mods = (UBound(Str) + 1) Mod 3   '除以3的余数
    length = UBound(Str) + 1 - mods
    ReDim buf(length / 3 * 4 + IIf(mods <> 0, 4, 0) - 1)
    For i = 0 To length - 1 Step 3
        buf(i / 3 * 4) = (Str(i) And &HFC) / &H4
        buf(i / 3 * 4 + 1) = (Str(i) And &H3) * &H10 + (Str(i + 1) And &HF0) / &H10
        buf(i / 3 * 4 + 2) = (Str(i + 1) And &HF) * &H4 + (Str(i + 2) And &HC0) / &H40
        buf(i / 3 * 4 + 3) = Str(i + 2) And &H3F
    Next
    If mods = 1 Then
        buf(length / 3 * 4) = (Str(length) And &HFC) / &H4
        buf(length / 3 * 4 + 1) = (Str(length) And &H3) * &H10
        buf(length / 3 * 4 + 2) = 64
        buf(length / 3 * 4 + 3) = 64
    ElseIf mods = 2 Then
        buf(length / 3 * 4) = (Str(length) And &HFC) / &H4
        buf(length / 3 * 4 + 1) = (Str(length) And &H3) * &H10 + (Str(length + 1) And &HF0) / &H10
        buf(length / 3 * 4 + 2) = (Str(length + 1) And &HF) * &H4
        buf(length / 3 * 4 + 3) = 64
    End If
    For i = 0 To UBound(buf)
        Base64Encode = Base64Encode + Mid(B64_CHAR_DICT, buf(i) + 1, 1)
    Next
over:
End Function

Read more…

Categories: 零敲碎打 Tags: , ,