Android-Charts技术交流QQ群现已开通,欢迎加入。群号:170987350

PostgreSQL创建ReadOnly只读用户

March 26th, 2019 No comments

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

通过使用

-- 创建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设置,可以通过

-- 授予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;

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

-- 将默认"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 编码/加密函数:

'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: , ,

解决Python Error ‘TSaslClientTransport’ object has no attribute ‘trans’

November 12th, 2018 No comments

解决Python Error ‘TSaslClientTransport’ object has no attribute ‘trans’
原因应该是thrift和impyla包版本的问题

sudo pip uninstall thrift
sudo pip uninstall impyla
sudo pip install thrift==0.9.3
sudo pip install impyla==0.13.8

参考
http://community.cloudera.com/t5/Interactive-Short-cycle-SQL/Python-Error-TSaslClientTransport-object-has-no-attribute/m-p/58033

Categories: 系统管理 Tags: ,

RQAlpha Docker容器化Dockerfile

November 12th, 2018 No comments

最近需要使用RQAlpha进行国内的量化分析工作,并对RQAlpha进行了容器化处理,
方便使用docker和ks进行容器化管理

1.基于 jupyter/minimal-notebook jupyter的官方镜像,默认使用python3
2.使用Anaconda的mini-conda进行环境管理,可以自由切换python27和python3运行环境
3.使用pip安装了其他非conda管理的包内容

pip install bs4 cx-Oracle docopt future hdfs pyecharts PyMySQL raven typing lxml

4.使用编译方式安装了TA-lib,暂时为32bit版本,安装了国内流行的tushare

pip install tushare TA-lib

5.将matplotlib.pyplot自动导入,同时配合安装了国内流行的pyechart
6.安装了HDFS,cx-Oracle,PyMySQL等常用的数据连接包

可以在以下位置找到
https://github.com/limccn/rqalpha/tree/master/docker

Read more…

Categories: 系统管理 Tags: ,

RQAlpha BUG Issue#219

November 12th, 2018 No comments

Pingback https://github.com/ricequant/rqalpha/issues/219

Hello, RQAlpha Team

RQAlpha is really a effective tool for price back-testing.

I found something wrong when using command `# rqalpha plot someresult.pkl` to plot my back-testing result. It came out a blank window. I tried to solve this problem and found something interesting.

in `rqalpha/rqalpha/mod/rqalpha_mod_sys_analyser/plot.py`, line 52-53

 portfolio = result_dict["portfolio"]
 benchmark_portfolio = result_dict.get("benchmark_portfolio")

 print portfolio.index
 print benchmark_portfolio.index

by printing portfolio.index and benchmark_portfolio.index , I found an unreasonable difference.

![image](https://user-images.githubusercontent.com/4476941/33165321-59a13c02-d071-11e7-80e1-b41a5fbdd6db.png)

According to https://github.com/pandas-dev/pandas/issues/8614 says, matplotlib can not plotting when DatetimeIndex is created by pandas > 0.15 .

I found a temporary way to solve this problem. and finally plotting was working functionally.

Use `index.to_pydatetime()` to explicitly convert `DatetimeIndex` type index to Python `Datetime` type.

For example: modify `rqalpha/rqalpha/mod/rqalpha_mod_sys_analyser/plot.py`, line 152
`ax.plot(portfolio[“unit_net_value”] – 1.0, label=_(u”strategy”), alpha=1, linewidth=2, color=red)`
to
`ax.plot(index.to_pydatetime(),portfolio[“unit_net_value”] – 1.0, label=_(u”strategy”), alpha=1, linewidth=2, color=red)`

Although it is not the best way to solve this problem, I know you can find the best one finally.
And I hope these information may help you.

Thanks.

Categories: 算法研究, 系统管理 Tags:

PostgreSQL查询表和index占用空间大小

November 12th, 2018 No comments

PostgreSQL查询表和index占用空间大小

PostgreSQL表和index占用空间大小信息存储在
information_schema.tables中
通过SQL可以查询到相应的统计数据

--查出单个表的大小
SELECT pg_size_pretty(pg_relation_size('TABLENAME'));

查出表大小按大小含Index

-- 查出表大小按大小含Index
SELECT
"table_name",
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
TABLE_NAME,
SUBSTRING("table_name",1,10) AS short_name,
pg_table_size(TABLE_NAME) AS table_size,
pg_indexes_size(TABLE_NAME) AS indexes_size,
pg_total_relation_size(TABLE_NAME) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || TABLE_NAME || '"') AS TABLE_NAME
FROM information_schema.tables
) AS all_tables
WHERE all_tables.table_name LIKE '%TABLENAME%'
ORDER BY total_size DESC
) AS pretty_sizes

Read more…

Nginx HTTP站点使用301跳转HTTPS

November 12th, 2018 No comments

方法一,使用Return方式,适用于全站HTTPS

server_name  www.lidaren.com lidaren.com;
return       301 https://www.lidaren.com$request_uri;

方法二,rewirte方式转发特定目录,适用于子目录HTTPS化
location 使用 ·/· 根目录则全站跳转
location 使用 ·/XXX目录· 子目录则跳转指定子目录

location / {
    rewrite ^(.*) https://www.lidaren.com$1 permanent
}

完整参考

server {
    listen       80;
    server_name  www.lidaren.com lidaren.com;
 
# 方法一,使用Return方式,适用于全站HTTPS
    return       301 https://www.lidaren.com$request_uri;
 
# 方法二,rewirte方式转发特定目录,适用于子目录HTTPS化
# location 使用/根目录则全站跳转
#    location / {
#        rewrite ^(.*) https://www.lidaren.com$1 permanent
#    }
}
Categories: 系统管理, 零敲碎打 Tags: