import asyncpg
import datetime
import json
from dl2050utils.core import *
# https://www.psycopg.org
# https://magicstack.github.io/asyncpg/current/index.html
TBLS_EXCLUDE = ['refs', 'trans', 'enums', 'information_schema_catalog_name', 'check_constraint_routine_usage', 'applicable_roles',
'administrable_role_authorizations','collation_character_set_applicability', 'attributes', 'check_constraints',
'character_sets', 'collations', 'column_domain_usage', 'column_column_usage', 'column_privileges',
'column_udt_usage', 'columns', 'constraint_column_usage', 'schemata', 'constraint_table_usage',
'domain_constraints', 'sql_packages', 'domain_udt_usage', 'sequences', 'domains', 'enabled_roles',
'key_column_usage', 'parameters', 'referential_constraints', 'sql_features', 'role_column_grants',
'routine_privileges', 'role_routine_grants', 'routines', 'sql_implementation_info', 'sql_parts',
'sql_languages', 'sql_sizing', 'sql_sizing_profiles', 'table_constraints', 'table_privileges',
'role_table_grants', 'views', 'tables', 'transforms', 'triggered_update_columns', '_pg_foreign_servers',
'triggers', 'data_type_privileges', 'udt_privileges', 'role_udt_grants', 'usage_privileges', 'element_types',
'role_usage_grants', 'user_defined_types', '_pg_foreign_table_columns', 'view_column_usage', 'view_routine_usage',
'view_table_usage', 'foreign_server_options', 'column_options', '_pg_foreign_data_wrappers',
'foreign_data_wrapper_options', 'foreign_tables', 'foreign_data_wrappers', 'foreign_servers',
'foreign_table_options', 'user_mappings', 'user_mapping_options']
DRILLS = {
'tbls': {'canal': ['zona', 'subzona', 'agente']}
}
TYPES = {
'character varying': 'S',
'character': 'S',
'integer': 'I',
'real': 'P',
'double precision': 'F',
'money': 'C',
'date': 'D',
'timestamp with time zone': 'T',
'boolean': 'B',
'ARRAY': 'ARRAY',
'json': 'JSON',
'USER-DEFINED': 'ENUM'
}
Q_DROP = """
DROP TABLE flds;
DROP TABLE enums;
DROP TABLE refs;
DROP TABLE tbls;
DROP TYPE aligns;
"""
Q_CREATE = """
CREATE TYPE aligns AS ENUM ('Left', 'Center', 'Right');
CREATE TABLE tbls (
tbl varchar(64) primary key,
name varchar(64),
keys TEXT [],
descf varchar(64),
drills json
);
CREATE TABLE flds (
tbl varchar(64) NOT NULL references tbls(tbl),
fld varchar(64) NOT NULL,
name varchar(64),
type varchar(64),
size char(16),
frmt char(16),
ronly boolean,
align aligns,
PRIMARY KEY(tbl, fld)
);
CREATE TABLE refs (
tbl varchar(64) NOT NULL references tbls(tbl),
fld varchar(64) NOT NULL,
tbl_ref varchar(64) NOT NULL references tbls(tbl),
fld_ref varchar(64) NOT NULL
);
CREATE TABLE enums (
name varchar(64) NOT NULL,
values TEXT []
);
"""
Q_TBLS = """
SELECT table_name FROM information_schema.tables;
"""
Q_FLDS_ = f"""
SELECT table_name as tbl, column_name as fld, column_name as name, data_type as type, character_maximum_length as size
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name IN
"""
Q_KEYS = """
SELECT tc.table_name as tbl, c.column_name as fld
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name)
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema
AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
WHERE constraint_type = 'PRIMARY KEY';
"""
Q_REFS = """
SELECT
tc.table_name as tbl,
kcu.column_name as fld,
ccu.table_name AS tbl_ref,
ccu.column_name AS fld_ref
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' and tc.constraint_name ='flds_tbl_fkey';
"""
Q_ENUMS = """
select t.typname as ename,
e.enumlabel as evalue
from pg_type t
join pg_enum e on t.oid = e.enumtypid
join pg_catalog.pg_namespace n ON n.oid = t.typnamespace;
"""
async def build_meta(db):
for q in Q_DROP.split(";"): await db.query(q)
for q in Q_CREATE.split(";"): await db.query(q)
tbls = await db.query(Q_TBLS)
tbls_list = [e['table_name'] for e in tbls
if e['table_name'] not in TBLS_EXCLUDE and not e['table_name'].startswith('pg') and not e['table_name'].startswith('_pg')]
keys = await db.query(Q_KEYS)
tbls = [{'tbl': e, 'name': e, 'keys': [e1['fld'] for e1 in keys if e1['tbl']==e], 'drills': DRILLS[e] if e in DRILLS else {}}
for e in tbls_list]
await db.insert_rows('tbls', tbls, delete=False)
TBLS_LIST = ', '.join([f'\'{e}\'' for e in tbls_list])
Q_FLDS = f'{Q_FLDS_} ({TBLS_LIST});'
flds = await db.query(Q_FLDS)
for fld in flds:
fld['name'] = fld['name'].title()
fld['type'] = TYPES[fld['type']]
fld['align'] = 'Left' if fld['type'] in ['S', 'D', 'T'] else 'Right'
await db.insert_rows('flds', flds, delete=True)
enums = await db.query(Q_ENUMS)
enums = [{'name': e, 'values': [e1['evalue'] for e1 in enums if e1['ename']==e]} for e in set([e['ename'] for e in enums])]
await db.insert_rows('enums', enums, delete=True)
refs = await db.query(Q_REFS)
await db.insert_rows('refs', refs, delete=True)
async def update_meta(db, meta):
for tbl in meta['tables']:
if 'fields' not in meta['tables'][tbl]: continue
for fld in meta['tables'][tbl]['fields']:
d = {'tbl': tbl, 'fld': fld}
for attr in meta['tables'][tbl]['fields'][fld]:
d[attr] = meta['tables'][tbl]['fields'][fld][attr]
res = await db.update('flds', ['tbl', 'fld'], d)
async def get_meta(db):
tbls = await db.select('tbls')
if tbls is None: return None
flds = await db.select('flds')
if flds is None: return None
meta = {'tables': {e['tbl']: {'table': e, 'fields': {e1['fld']: e1 for e1 in flds if e1['tbl']==e['tbl']}} for e in tbls}}
return meta
def fix_types(d):
for k in d.keys():
if isinstance(d[k], str): d[k] = d[k].strip()
if isinstance(d[k], datetime.date):
d[k] = d[k].strftime("%Y-%m-%d %H:%M:%S") if isinstance(d[k], datetime.datetime) else d[k].strftime("%Y-%m-%d")
return d
def strip(e):
if type(e) != str: return e
e = e.replace('\'', '')
e.replace('\"', '')
e.replace('\n', ' ')
return e
def get_repr(e):
if type(e)==list:
items = [f'"{str(e1)}"' for e1 in e]
return f"'{{{' ,'.join(items)}}}'"
if type(e)==dict: return f"'{json.dumps(e)}'"
return f"'{strip(e)}'"
class DB():
def __init__(self, cfg, log):
self.cfg, self.LOG = cfg, log
try:
passwd = self.cfg["db"]["passwd"]
except Exception as e:
passwd = 'rootroot'
self.url = f'postgres://postgres:{passwd}@db:5432/postgres'
async def startup(self, min_size=5, max_size=20):
try:
self.pool = await asyncpg.create_pool(self.url, min_size=min_size, max_size=max_size)
except Exception as e:
self.LOG.log(4, 0, label='DBPG', label2='startup', msg=str(e))
return True
self.LOG.log(2, 0, label='DBPG', label2='startup', msg='CONNECTED (POOL)')
return False
async def build_m
没有合适的资源?快使用搜索试试~ 我知道了~
温馨提示
![preview](https://csdnimg.cn/release/downloadcmsfe/public/img/white-bg.ca8570fa.png)
共14个文件
py:11个
readme:1个
cfg:1个
![preview-icon](https://csdnimg.cn/release/downloadcmsfe/public/img/scale.ab9e0183.png)
资源分类:Python库 所属语言:Python 资源全名:dl2050utils-1.0.17.tar.gz 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
资源推荐
资源详情
资源评论
![gz](https://img-home.csdnimg.cn/images/20210720083447.png)
![gz](https://img-home.csdnimg.cn/images/20210720083447.png)
![bz2](https://img-home.csdnimg.cn/images/20210720083646.png)
![bz2](https://img-home.csdnimg.cn/images/20210720083646.png)
![gz](https://img-home.csdnimg.cn/images/20210720083447.png)
![gz](https://img-home.csdnimg.cn/images/20210720083447.png)
![gz](https://img-home.csdnimg.cn/images/20210720083447.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
收起资源包目录
![package](https://csdnimg.cn/release/downloadcmsfe/public/img/package.f3fc750b.png)
![folder](https://csdnimg.cn/release/downloadcmsfe/public/img/folder.005fa2e5.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![folder](https://csdnimg.cn/release/downloadcmsfe/public/img/folder.005fa2e5.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
![file-type](https://csdnimg.cn/release/download/static_files/pc/images/minetype/UNKNOWN.png)
共 14 条
- 1
资源评论
![avatar-default](https://csdnimg.cn/release/downloadcmsfe/public/img/lazyLogo2.1882d7f4.png)
![avatar](https://profile-avatar.csdnimg.cn/277f6345dca0446498fbbc03843436aa_qq_38161040.jpg!1)
挣扎的蓝藻
- 粉丝: 13w+
- 资源: 15万+
上传资源 快速赚钱
我的内容管理 展开
我的资源 快来上传第一个资源
我的收益
登录查看自己的收益我的积分 登录查看自己的积分
我的C币 登录后查看C币余额
我的收藏
我的下载
下载帮助
![voice](https://csdnimg.cn/release/downloadcmsfe/public/img/voice.245cc511.png)
![center-task](https://csdnimg.cn/release/downloadcmsfe/public/img/center-task.c2eda91a.png)
安全验证
文档复制为VIP权益,开通VIP直接复制
![dialog-icon](https://csdnimg.cn/release/downloadcmsfe/public/img/green-success.6a4acb44.png)