archery查询SQL

-- 查询用户id
select * from sql_users where username='xxx'

-- 查询拥有的权限   数据库级别：priv_type=1     表级别： priv_type=2
select * from query_privileges where user_name='xxx' and priv_type=2;

-- 查询所有资源组
select * from  resource_group

-- 查询某个用户所在的组
select c.username,c.display,a.group_name
from resource_group a  
inner join  sql_users_resource_group b  on a.group_id = b.resourcegroup_id
inner join sql_users c on b.users_id = c.id
where  c.username='xxx'

-- 查询某个资源组所有的成员
select c.username,c.display,a.group_name
from resource_group a  
inner join  sql_users_resource_group b  on a.group_id = b.resourcegroup_id
inner join sql_users c on b.users_id = c.id
where  a.group_name='xxx'

-- 查询某个用户对应的组
select a.username,a.display,c.name
from sql_users a inner join  sql_users_groups b on a.id = b.users_id
inner join  auth_group c on  b.group_id = c.id
where a.username='xxx'

-- 查询某个组所有成员
select a.username,a.display,c.name
from sql_users a inner join  sql_users_groups b on a.id = b.users_id
inner join  auth_group c on  b.group_id = c.id
where c.name = 'PM'


-- 查询某个工单【 id 是页面上看到的 https://archery.bndxqc.com/detail/36823/ 】
select * from  sql_workflow  where id = 36823
-- sql_workflow.status
workflow_finish           已正常结束   
workflow_autoreviewwrong  自动审核不通过
workflow_abort            人工终止流程
workflow_exception        执行有异常
workflow_review_pass      审核通过
workflow_queuing          排队中
workflow_executing        执行中
workflow_manreviewing     等待审核人审核    

-- 手工修改异常工单为 人工终止流程
update   sql_workflow set status='workflow_abort'  where id in (44747);


Archery表对应关系
resource_group      资源组
sql_users             用户
auth_group               组
sql_users_groups     用户组   
sql_instance          实例 


-- 工单日志  workflow_id=37664  是页面上看到的 https://archery.bndxqc.com/detail/37664/
select * from workflow_log  where audit_id in (select audit_id from workflow_audit where workflow_id=37664);

-- 修改 工单日志=>操作人
update  workflow_log  set operator='xxx',operator_display='xxx'
where audit_id in (select audit_id from workflow_audit where workflow_id=47940)
and  operator='xxx';


10.8.1.4:3316_dba@archery 10:24:54>select group_id,group_name from  resource_group order by 2,1;      

  

--某个资源组的PM权限有哪些用户
select a.display,c.name,f.group_name  from 
sql_users a 
inner join sql_users_groups b on a.id =b.users_id
inner join auth_group c  on b.group_id = c.id 
left join (select e.group_name,d.users_id,d.resourcegroup_id  from sql_users_resource_group d left join resource_group e on d.resourcegroup_id = e.group_id  ) f on a.id=f.users_id 
where   c.name='PM' and f.resourcegroup_id='69';



--用户的组和资源组权限查询
select a.display,c.name,f.group_name  from 
sql_users a 
inner join sql_users_groups b on a.id =b.users_id
inner join auth_group c  on b.group_id = c.id 
left join (select e.group_name,d.users_id  from sql_users_resource_group d left join resource_group e on d.resourcegroup_id = e.group_id  ) f on a.id=f.users_id 
where   a.username='xxx';

-- 修改已经申请的结果集
select * from query_privileges_apply where apply_id=7628;
update  query_privileges_apply  set limit_num=20  where apply_id=7628;

-- 查找回滚SQL， 10.21.52.209   mysql  --login-path=root -A 
-- 查找库名
select SCHEMA_NAME  from   information_schema.SCHEMATA  where SCHEMA_NAME like '%saas_xqy_contract%';

-- 表名是工单的表名；打开F2，查看 sequence 就是回滚SQL   opid_time
select rollback_statement from  xqy_sell_consume_log   where opid_time='1712816284_12690737_00000002';

-- 导出： 
import pandas as pd
import pymysql

# MySQL连接信息
connection = pymysql.connect(
    host="10.21.52.209",
    user="arch_wt",
    password="2uID@8SNF2OhGecg",
    database="al_cn_south_1_mysql_rds_myhuaweicloud_com_6033_saas_xqy_contract",
    port=3316
)

# SQL查询语句
query = "select rollback_statement from xqy_sell_consume_log where opid_time='1712816284_12690737_00000002'"

# 执行查询并将结果保存为DataFrame对象
df = pd.read_sql(query, connection)

# 保存DataFrame为CSV文件
df.to_csv("46439-xqy_sell_consume_log.csv", index=False, header=False)