1. 自定义 Vanna 对象
class MyVanna(ChromaDB_VectorStore, OpenAI_Chat):
def __init__(self, client=None, config=None):
ChromaDB_VectorStore.__init__(self, config=config)
OpenAI_Chat.__init__(self, client=client, config=config)
2. 配置模型
refresh_token = 'sk-xxx'
我的云服务器的地址
my_llm_base_url = 'https://api.deepseek.com/'
my_llm_name = 'deepseek-chat'
client = OpenAI(
api_key=refresh_token,
base_url=my_llm_base_url,
default_headers={"Authorization": f"Bearer {refresh_token}", "Content-Type": "application/json"}
)
3. 创建vanna对象 绑定模型
vn = MyVanna(client=client, config={"model": my_llm_name, })
4.连接数据库
vn.connect_to_postgres(host='192.168.2.162', dbname='dbname', user='postgres', password='test', port='5001')
5.训练ddl, 训练question ,sql
vn.train(ddl="""
CREATE TABLE if not exists prod_inst_acct_info (
ID INT8 NOT NULL DEFAULT nextval('prod_inst_acct_info_id_seq'::regclass),
prod_id INT8,
prod_inst_id INT8 NOT NULL,
bill_ym CHAR ( 6 ) COLLATE pg_catalog.DEFAULT,
cust_ym CHAR ( 6 ) COLLATE pg_catalog.DEFAULT,
belong_ym CHAR ( 6 ) COLLATE pg_catalog.DEFAULT,
org_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
trust_flag CHAR ( 1 ) COLLATE pg_catalog.DEFAULT DEFAULT '0' :: BPCHAR,
trust_org_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
cust_id INT8,
acct_id INT8,
order_nbr VARCHAR ( 50 ) COLLATE pg_catalog.DEFAULT,
acc_num VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
contract_nbr VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
rmb_all NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_all NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_port_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_port_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_main_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_main_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_local_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_local_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_port_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_port_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_main_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_main_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_local_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_local_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_loop_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_loop_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_loop_b NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_loop_b_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_inter NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_inter_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_oppo NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_oppo_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_port_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_port_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_main_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_main_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_local_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_local_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_port_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_port_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_main_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_main_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_local_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_local_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_loop_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_loop_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_loop_b NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_loop_b_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_inter NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_inter_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_oppo NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_oppo_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
org_port_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
dis_port_a NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_main_a NUMERIC ( 19, 6 ) DEFAULT 0.000000,
org_local_a NUMERIC ( 19, 6 ) DEFAULT 0.000000,
dis_local_a NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_port_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
dis_port_z NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_main_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
org_local_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
dis_local_z NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_loop_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
org_loop_b NUMERIC ( 19, 4 ) DEFAULT 0.0000,
dis_loop NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_inter NUMERIC ( 19, 4 ) DEFAULT 0.0000,
dis_inter NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_oppo NUMERIC ( 19, 4 ) DEFAULT 0.0000,
cal_prod_inst_id INT8,
bill_type CHAR ( 1 ) COLLATE pg_catalog.DEFAULT DEFAULT '0' :: BPCHAR,
cal_org_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
notice_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
us_pay_money NUMERIC ( 19, 4 ) DEFAULT 0.0000,
pay_money NUMERIC ( 19, 4 ) DEFAULT 0.0000,
pay_flag CHAR ( 1 ) COLLATE pg_catalog.DEFAULT DEFAULT '0' :: BPCHAR,
taxes NUMERIC ( 19, 6 ),
tax_rate NUMERIC ( 10, 6 ) DEFAULT 0.000000,
belong_org INT8,
NO_PAY_MONEY NUMERIC ( 19, 4 ) DEFAULT 0.0000,
NO_US_PAY_MONEY NUMERIC ( 19, 4 ) DEFAULT 0.0000,
prov_id VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
latn_id VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
cust_region VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
create_org_id VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
cycle_type VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
fee_cycle VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
control_dep VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
begin_rent_cd VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
stop_rent_cd VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
industry_type VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
control_type VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
account VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
EXT_PROD_INST_ID VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
PRIMARY KEY ( ID )
);
COMMENT ON COLUMN prod_inst_acct_info.ID IS '记录ID';
COMMENT ON COLUMN prod_inst_acct_info.prod_id IS '产品编码';
COMMENT ON COLUMN prod_inst_acct_info.prod_inst_id IS '产品实例ID';
COMMENT ON COLUMN prod_inst_acct_info.bill_ym IS '发生年月';
COMMENT ON COLUMN prod_inst_acct_info.cust_ym IS '客户应收年月';
COMMENT ON COLUMN prod_inst_acct_info.belong_ym IS '帐期计费年月';
COMMENT ON COLUMN prod_inst_acct_info.org_id IS '收款单位';
COMMENT ON COLUMN prod_inst_acct_info.trust_flag IS '是否代收费';
COMMENT ON COLUMN prod_inst_acct_info.trust_org_id IS '委托收款单位';
COMMENT ON COLUMN prod_inst_acct_info.cust_id IS '客户编号';
COMMENT ON COLUMN prod_inst_acct_info.acct_id IS '帐户编号';
COMMENT ON COLUMN prod_inst_acct_info.order_nbr IS '流水号';
COMMENT ON COLUMN prod_inst_acct_info.acc_num IS '电路代号';
COMMENT ON COLUMN prod_inst_acct_info.contract_nbr IS '合同号';
COMMENT ON COLUMN prod_inst_acct_info.rmb_all IS '应收金额';
COMMENT ON COLUMN prod_inst_acct_info.us_all IS '应收金额美元';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_a IS 'A端端口费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_a_amount IS 'A端端口费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_a IS 'A端代维费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_a_amount IS 'A端代维费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_a IS 'A端本地月租';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_a_amount IS 'A端本地月租调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_z IS 'Z端端口费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_z_amount IS 'Z端端口费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_z IS 'Z端代维费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_z_amount IS 'Z端代维费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_z IS 'Z端本地月租费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_z_amount IS 'Z端本地月租费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_a IS '国内A长途费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_a_amount IS '国内A长途费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_b IS '国内B长途费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_b_amount IS '国内B长途费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_inter IS '国际长途费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_inter_amount IS '国际长途费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_oppo IS '国际对端金额';
COMMENT ON COLUMN prod_inst_acct_info.rmb_oppo_amount IS '国际对端金额调整';
COMMENT ON COLUMN prod_inst_acct_info.us_port_a IS 'A端端口费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_port_a_amount IS 'A端端口费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_a IS 'A端代维费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_a_amount IS 'A端代维费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_a IS 'A端本地月租美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_a_amount IS 'A端本地月租调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_port_z IS 'Z端端口费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_port_z_amount IS 'Z端端口费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_z IS 'Z端代维费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_z_amount IS 'Z端代维费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_z IS 'Z端本地月租费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_z_amount IS 'Z端本地月租费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_a IS '国内A长途费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_a_amount IS '国内A长途费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_b IS '国内B长途费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_b_amount IS '国内B长途费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_inter IS '国际长途费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_inter_amount IS '国际长途费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_oppo IS '国际对端金额美元';
COMMENT ON COLUMN prod_inst_acct_info.us_oppo_amount IS '国际对端金额调整美元';
COMMENT ON COLUMN prod_inst_acct_info.org_port_a IS 'A端端口费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_port_a IS 'A端端口优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_main_a IS 'A端代维费报价';
COMMENT ON COLUMN prod_inst_acct_info.org_local_a IS 'A端本地月租报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_local_a IS 'A端本地月租优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_port_z IS 'Z端端口费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_port_z IS 'Z端端口优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_main_z IS 'Z端代维费报价';
COMMENT ON COLUMN prod_inst_acct_info.org_local_z IS 'Z端本地月租费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_local_z IS 'Z端本地优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_loop_a IS '国内A长途费报价';
COMMENT ON COLUMN prod_inst_acct_info.org_loop_b IS '国内B长途费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_loop IS '国内长途优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_inter IS '国际长途费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_inter IS '国际长途优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_oppo IS '国际对端金额报价';
COMMENT ON COLUMN prod_inst_acct_info.cal_prod_inst_id IS '当期计费资料ID';
COMMENT ON COLUMN prod_inst_acct_info.bill_type IS '收费类型(1--一次性费用，2-- 月租费用)';
COMMENT ON COLUMN prod_inst_acct_info.cal_org_id IS '作帐单位';
COMMENT ON COLUMN prod_inst_acct_info.notice_id IS '通知单ID';
COMMENT ON COLUMN prod_inst_acct_info.us_pay_money IS '销帐金额(US)';
COMMENT ON COLUMN prod_inst_acct_info.pay_money IS '销帐金额(RMB)';
COMMENT ON COLUMN prod_inst_acct_info.pay_flag IS '销帐标识';
COMMENT ON COLUMN prod_inst_acct_info.taxes IS '税金';
COMMENT ON COLUMN prod_inst_acct_info.tax_rate IS '税率，ICT时显示NULL值';
COMMENT ON COLUMN prod_inst_acct_info.belong_org IS '部门ID';
-- 新加的扩展字段
COMMENT ON COLUMN prod_inst_acct_info.NO_US_PAY_MONEY IS '未销账金额美元';
COMMENT ON COLUMN prod_inst_acct_info.NO_PAY_MONEY IS '未销账金额';
COMMENT ON COLUMN prod_inst_acct_info.latn_id IS '本地网代码';
COMMENT ON COLUMN prod_inst_acct_info.prov_id IS '省份代码';
COMMENT ON COLUMN prod_inst_acct_info.cust_region IS '客户营销责任地';
COMMENT ON COLUMN prod_inst_acct_info.create_org_id IS '发起局编码';
COMMENT ON COLUMN prod_inst_acct_info.cycle_type IS '收费周期';
COMMENT ON COLUMN prod_inst_acct_info.fee_cycle IS '费用周期';
COMMENT ON COLUMN prod_inst_acct_info.control_dep IS '管控部门';
COMMENT ON COLUMN prod_inst_acct_info.begin_rent_cd IS '起租系数';
COMMENT ON COLUMN prod_inst_acct_info.stop_rent_cd IS '退租系数';
COMMENT ON COLUMN prod_inst_acct_info.industry_type IS '行业类型';
COMMENT ON COLUMN prod_inst_acct_info.control_type IS '直管类型';
COMMENT ON COLUMN prod_inst_acct_info.account IS '接入号';
COMMENT ON COLUMN prod_inst_acct_info.EXT_PROD_INST_ID IS 'CRM实例ID';
""")
vn.train(ddl="""CREATE TABLE if not exists idc_province_report (
id int8 NOT NULL DEFAULT nextval('idc_province_report_id_seq'::regclass), -- 主键 ID, 使用序列实现自增
province VARCHAR(255), -- 省份名称
province_code text COLLATE pg_catalog.default, -- 省份编码
total_rack_count INT, -- 总机架数量
total_bandwidth_count INT, -- 总带宽数量
dynamic_traffic NUMERIC(18,2), -- 动态流量
fixed_traffic NUMERIC(18,2), -- 固定流量
total_revenue NUMERIC(18,2), -- 权责总收入
managed_host_total_revenue NUMERIC(18,2), -- 主机托管权责总收入
idc_bandwidth_total_revenue NUMERIC(18,2), -- IDC带宽权责总收入
other_products_total_revenue NUMERIC(18,2), -- 其他产品权责总收入
one_time_fee NUMERIC(18,2), -- 一次性费用
rack_slot_count INT, -- 机位数量
rack_slot_revenue NUMERIC(18,2), -- 机位收入
rack_count INT, -- 机架数量
rack_revenue NUMERIC(18,2), -- 机架收入
power_count INT, -- 电力数量
power_revenue NUMERIC(18,2), -- 电力收入
auxiliary_area_count INT, -- 工作附属区数量
auxiliary_area_revenue NUMERIC(18,2), -- 工作附属区收入
machine_room_count INT, -- 机房数量
machine_room_revenue NUMERIC(18,2), -- 机房收入
bgp_dynamic_traffic NUMERIC(18,2), -- BGP动态流量
bgp_dynamic_revenue NUMERIC(18,2), -- BGP动态收入
bgp_fixed_traffic NUMERIC(18,2), -- BGP固定流量
bgp_fixed_revenue NUMERIC(18,2), -- BGP固定收入
static_protocol_dynamic_traffic NUMERIC(18,2), -- 静态协议动态流量
static_protocol_dynamic_revenue NUMERIC(18,2), -- 静态协议动态收入
static_protocol_fixed_traffic NUMERIC(18,2), -- 静态协议固定流量
static_protocol_fixed_revenue NUMERIC(18,2), -- 静态协议固定收入
shared_port_traffic NUMERIC(18,2), -- 共享端口流量
shared_port_revenue NUMERIC(18,2), -- 共享端口收入
ip_broadcast_and_bgp_activation_revenue NUMERIC(18,2), -- IP地址广播及BGP开通收入
ip_rental_revenue NUMERIC(18,2), -- IP出租收入
idc_value_added_service_revenue NUMERIC(18,2), -- IDC增值业务收入
trust_flag CHAR(1), -- 信任标志 (0: 非委托, 1: 委托),
bill_ym text COLLATE pg_catalog.default, -- 计费年月
latn_id varchar(32) COLLATE pg_catalog.default, --本地网代码
prov_id varchar(32) COLLATE pg_catalog.default, -- 省份代码
batch_num_ym text COLLATE pg_catalog.default, -- 批次年月便于数据清理
PRIMARY KEY (id) -- 设置主键
);
-- Add comments for each column
COMMENT ON COLUMN idc_province_report.province IS '省份名称';
COMMENT ON COLUMN idc_province_report.province_code IS '省份编码';
COMMENT ON COLUMN idc_province_report.total_rack_count IS '总机架数量';
COMMENT ON COLUMN idc_province_report.total_bandwidth_count IS '总带宽数量';
COMMENT ON COLUMN idc_province_report.dynamic_traffic IS '动态流量';
COMMENT ON COLUMN idc_province_report.fixed_traffic IS '固定流量';
COMMENT ON COLUMN idc_province_report.total_revenue IS '权责总收入';
COMMENT ON COLUMN idc_province_report.managed_host_total_revenue IS '主机托管权责总收入';
COMMENT ON COLUMN idc_province_report.idc_bandwidth_total_revenue IS 'IDC带宽权责总收入';
COMMENT ON COLUMN idc_province_report.other_products_total_revenue IS '其他产品权责总收入';
COMMENT ON COLUMN idc_province_report.one_time_fee IS '一次性费用';
COMMENT ON COLUMN idc_province_report.rack_slot_count IS '机位数量';
COMMENT ON COLUMN idc_province_report.rack_slot_revenue IS '机位收入';
COMMENT ON COLUMN idc_province_report.rack_count IS '机架数量';
COMMENT ON COLUMN idc_province_report.rack_revenue IS '机架收入';
COMMENT ON COLUMN idc_province_report.power_count IS '电力数量';
COMMENT ON COLUMN idc_province_report.power_revenue IS '电力收入';
COMMENT ON COLUMN idc_province_report.auxiliary_area_count IS '工作附属区数量';
COMMENT ON COLUMN idc_province_report.auxiliary_area_revenue IS '工作附属区收入';
COMMENT ON COLUMN idc_province_report.machine_room_count IS '机房数量';
COMMENT ON COLUMN idc_province_report.machine_room_revenue IS '机房收入';
COMMENT ON COLUMN idc_province_report.bgp_dynamic_traffic IS 'BGP动态流量';
COMMENT ON COLUMN idc_province_report.bgp_dynamic_revenue IS 'BGP动态收入';
COMMENT ON COLUMN idc_province_report.bgp_fixed_traffic IS 'BGP固定流量';
COMMENT ON COLUMN idc_province_report.bgp_fixed_revenue IS 'BGP固定收入';
COMMENT ON COLUMN idc_province_report.static_protocol_dynamic_traffic IS '静态协议动态流量';
COMMENT ON COLUMN idc_province_report.static_protocol_dynamic_revenue IS '静态协议动态收入';
COMMENT ON COLUMN idc_province_report.static_protocol_fixed_traffic IS '静态协议固定流量';
COMMENT ON COLUMN idc_province_report.static_protocol_fixed_revenue IS '静态协议固定收入';
COMMENT ON COLUMN idc_province_report.shared_port_traffic IS '共享端口流量';
COMMENT ON COLUMN idc_province_report.shared_port_revenue IS '共享端口收入';
COMMENT ON COLUMN idc_province_report.ip_broadcast_and_bgp_activation_revenue IS 'IP地址广播及BGP开通收入';
COMMENT ON COLUMN idc_province_report.ip_rental_revenue IS 'IP出租收入';
COMMENT ON COLUMN idc_province_report.idc_value_added_service_revenue IS 'IDC增值业务收入';
COMMENT ON COLUMN idc_province_report.trust_flag IS '信任标志 (0: 非信任, 1: 信任)';
COMMENT ON COLUMN idc_province_report.bill_ym IS '计费年月';
COMMENT ON COLUMN idc_province_report.batch_num_ym IS '批次年月便于数据清理';
COMMENT ON COLUMN idc_province_report.latn_id IS '本地网代码';
COMMENT ON COLUMN idc_province_report.prov_id IS '省份代码';
""")
vn.train(question="全国2024年8月税金最多的两个省份是？", sql="SELECT PROV_ID, SUM(TAXES) AS `T2024年8月税金` FROM `prod_inst_acct_info` WHERE CUST_YM = '202408' GROUP BY PROV_ID ORDER BY `T2024年8月税金` DESC LIMIT 2")
vn.train(question="教育行业2024年3月A端端口费是多少？", sql="SELECT SUM(RMB_PORT_A) AS `T2024年3月A端端口费` FROM `prod_inst_acct_info` WHERE CUST_YM = '202403'AND INDUSTRY_TYPE = 'CT13'")
vn.train(question="湖北省2024年6月总机架数量环比是多少？", sql="SELECT tmp.m_data, tmp.pre_m_data, ( ( tmp.m_data - tmp.pre_m_data ) / tmp.m_data ) * 100 AS `环比` FROM ( SELECT ( SELECT SUM ( COALESCE ( TOTAL_RACK_COUNT, 0 ) ) AS pre_m_data FROM idc_province_report WHERE BILL_YM = '202405' AND PROV_ID = '8420000' ) AS pre_m_data, ( SELECT SUM ( TOTAL_RACK_COUNT ) AS m_data FROM idc_province_report WHERE BILL_YM = '202406' AND PROV_ID = '8420000' ) AS m_data ) tmp")
vn.train(question="湖北省2024年8月总机架数量是多少？", sql=" select SUM ( TOTAL_RACK_COUNT ) as `机架数量`, PROV_ID as `省份代码` , bill_ym as `账期` from idc_province_report where bill_ym = '202408' and PROV_ID = '8420000' GROUP BY PROV_ID, bill_ym")
6.启动flask web
from vanna.flask import VannaFlaskApp
app = VannaFlaskApp(vn,
logo="https://xxx/logo2.png",
allow_llm_to_see_data=True,
summarization=True,
title=" chatbi",
subtitle="sql 生成器")
app.run(port = 6060, host = '0.0.0.0' , debug = True)
7.源码
from vanna.flask.auth import AuthInterface
import flask
class SimplePassword(AuthInterface):
def __init__(self, users: dict):
self.users = users
def get_user(self, flask_request) -> any:
return flask_request.cookies.get('user')
def is_logged_in(self, user: any) -> bool:
return user is not None
def override_config_for_user(self, user: any, config: dict) -> dict:
return config
def login_form(self) -> str:
return '''
<div class="p-4 sm:p-7">
<div class="text-center">
<h1 class="block text-2xl font-bold text-gray-800 dark:text-white">Sign in</h1>
<p class="mt-2 text-sm text-gray-600 dark:text-gray-400">
</p>
</div>
<div class="mt-5">
<form action="/auth/login" method="POST">
<div class="grid gap-y-4">
<div>
<label for="email" class="block text-sm mb-2 dark:text-white">Email address</label>
<div class="relative">
<input type="email" id="email" type="email" name="email" class="py-3 px-4 block w-full border border-gray-200 rounded-lg text-sm focus:border-blue-500 focus:ring-blue-500 disabled:opacity-50 disabled:pointer-events-none dark:bg-slate-900 dark:border-gray-700 dark:text-gray-400 dark:focus:ring-gray-600" required aria-describedby="email-error">
<div class="hidden absolute inset-y-0 end-0 pointer-events-none pe-3">
<svg class="size-5 text-red-500" width="16" height="16" fill="currentColor" viewBox="0 0 16 16" aria-hidden="true">
<path d="M16 8A8 8 0 1 1 0 8a8 8 0 0 1 16 0zM8 4a.905.905 0 0 0-.9.995l.35 3.507a.552.552 0 0 0 1.1 0l.35-3.507A.905.905 0 0 0 8 4zm.002 6a1 1 0 1 0 0 2 1 1 0 0 0 0-2z"/>
</svg>
</div>
</div>
<p class="hidden text-xs text-red-600 mt-2" id="email-error">Please include a valid email address so we can get back to you</p>
</div>
<div>
<div class="flex justify-between items-center">
<label for="password" class="block text-sm mb-2 dark:text-white">Password</label>
</div>
<div class="relative">
<input type="password" id="password" name="password" class="py-3 px-4 block w-full border border-gray-200 rounded-lg text-sm focus:border-blue-500 focus:ring-blue-500 disabled:opacity-50 disabled:pointer-events-none dark:bg-slate-900 dark:border-gray-700 dark:text-gray-400 dark:focus:ring-gray-600" required aria-describedby="password-error">
<div class="hidden absolute inset-y-0 end-0 pointer-events-none pe-3">
<svg class="size-5 text-red-500" width="16" height="16" fill="currentColor" viewBox="0 0 16 16" aria-hidden="true">
<path d="M16 8A8 8 0 1 1 0 8a8 8 0 0 1 16 0zM8 4a.905.905 0 0 0-.9.995l.35 3.507a.552.552 0 0 0 1.1 0l.35-3.507A.905.905 0 0 0 8 4zm.002 6a1 1 0 1 0 0 2 1 1 0 0 0 0-2z"/>
</svg>
</div>
</div>
<p class="hidden text-xs text-red-600 mt-2" id="password-error">8+ characters required</p>
</div>
<button type="submit" class="w-full py-3 px-4 inline-flex justify-center items-center gap-x-2 text-sm font-semibold rounded-lg border border-transparent bg-blue-600 text-white hover:bg-blue-700 disabled:opacity-50 disabled:pointer-events-none">Sign in</button>
</div>
</form>
</div>
</div>
'''
def login_handler(self, flask_request) -> str:
email = flask_request.form['email']
password = flask_request.form['password']
# Find the user and password in the users dict
for user in self.users:
if user["email"] == email and user["password"] == password:
response = flask.make_response('Logged in as ' + email)
response.set_cookie('user', email)
# Redirect to the main page
response.headers['Location'] = '/'
response.status_code = 302
return response
else:
return 'Login failed'
def callback_handler(self, flask_request) -> str:
user = flask_request.args['user']
response = flask.make_response('Logged in as ' + user)
response.set_cookie('user', user)
return response
def logout_handler(self, flask_request) -> str:
response = flask.make_response('Logged out')
response.delete_cookie('user')
return response
from openai import OpenAI
import os
from vanna.openai import OpenAI_Chat
from vanna.chromadb import ChromaDB_VectorStore
from SimplePassword import SimplePassword
os.environ['api_base'] = 'https:
# 使用自己的llm模型及所需配置
refresh_token = 'sk-xxx'
我的云服务器的地址
my_llm_base_url = 'https:
# my_llm_name = 'glm-4'
my_llm_name = 'deepseek-chat'
# 初始化客户端
client = OpenAI(
api_key=refresh_token,
base_url=my_llm_base_url,
default_headers={"Authorization": f"Bearer {refresh_token}", "Content-Type": "application/json"}
)
class MyVanna(ChromaDB_VectorStore, OpenAI_Chat):
def __init__(self, client=None, config=None):
ChromaDB_VectorStore.__init__(self, config=config)
OpenAI_Chat.__init__(self, client=client, config=config)
# 使用自定义的大模型及vanna提供的向量库
vn = MyVanna(client=client, config={"model": my_llm_name, })
vn.connect_to_postgres(host='192.168.2.162', dbname='JTBILL', user='postgres', password='Tianyuan@2020', port='5001')
vn.train(ddl="""
CREATE TABLE if not exists prod_inst_acct_info (
ID INT8 NOT NULL DEFAULT nextval('prod_inst_acct_info_id_seq'::regclass),
prod_id INT8,
prod_inst_id INT8 NOT NULL,
bill_ym CHAR ( 6 ) COLLATE pg_catalog.DEFAULT,
cust_ym CHAR ( 6 ) COLLATE pg_catalog.DEFAULT,
belong_ym CHAR ( 6 ) COLLATE pg_catalog.DEFAULT,
org_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
trust_flag CHAR ( 1 ) COLLATE pg_catalog.DEFAULT DEFAULT '0' :: BPCHAR,
trust_org_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
cust_id INT8,
acct_id INT8,
order_nbr VARCHAR ( 50 ) COLLATE pg_catalog.DEFAULT,
acc_num VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
contract_nbr VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
rmb_all NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_all NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_port_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_port_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_main_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_main_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_local_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_local_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_port_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_port_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_main_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_main_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_local_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_local_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_loop_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_loop_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_loop_b NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_loop_b_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_inter NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_inter_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_oppo NUMERIC ( 19, 4 ) DEFAULT 0.0000,
rmb_oppo_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_port_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_port_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_main_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_main_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_local_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_local_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_port_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_port_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_main_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_main_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_local_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_local_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_loop_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_loop_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_loop_b NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_loop_b_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_inter NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_inter_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_oppo NUMERIC ( 19, 4 ) DEFAULT 0.0000,
us_oppo_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
org_port_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
dis_port_a NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_main_a NUMERIC ( 19, 6 ) DEFAULT 0.000000,
org_local_a NUMERIC ( 19, 6 ) DEFAULT 0.000000,
dis_local_a NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_port_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
dis_port_z NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_main_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
org_local_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
dis_local_z NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_loop_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
org_loop_b NUMERIC ( 19, 4 ) DEFAULT 0.0000,
dis_loop NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_inter NUMERIC ( 19, 4 ) DEFAULT 0.0000,
dis_inter NUMERIC ( 10, 6 ) DEFAULT 0.000000,
org_oppo NUMERIC ( 19, 4 ) DEFAULT 0.0000,
cal_prod_inst_id INT8,
bill_type CHAR ( 1 ) COLLATE pg_catalog.DEFAULT DEFAULT '0' :: BPCHAR,
cal_org_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
notice_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
us_pay_money NUMERIC ( 19, 4 ) DEFAULT 0.0000,
pay_money NUMERIC ( 19, 4 ) DEFAULT 0.0000,
pay_flag CHAR ( 1 ) COLLATE pg_catalog.DEFAULT DEFAULT '0' :: BPCHAR,
taxes NUMERIC ( 19, 6 ),
tax_rate NUMERIC ( 10, 6 ) DEFAULT 0.000000,
belong_org INT8,
NO_PAY_MONEY NUMERIC ( 19, 4 ) DEFAULT 0.0000,
NO_US_PAY_MONEY NUMERIC ( 19, 4 ) DEFAULT 0.0000,
prov_id VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
latn_id VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
cust_region VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
create_org_id VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
cycle_type VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
fee_cycle VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
control_dep VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
begin_rent_cd VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
stop_rent_cd VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
industry_type VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
control_type VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
account VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
EXT_PROD_INST_ID VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
PRIMARY KEY ( ID )
);
COMMENT ON COLUMN prod_inst_acct_info.ID IS '记录ID';
COMMENT ON COLUMN prod_inst_acct_info.prod_id IS '产品编码';
COMMENT ON COLUMN prod_inst_acct_info.prod_inst_id IS '产品实例ID';
COMMENT ON COLUMN prod_inst_acct_info.bill_ym IS '发生年月';
COMMENT ON COLUMN prod_inst_acct_info.cust_ym IS '客户应收年月';
COMMENT ON COLUMN prod_inst_acct_info.belong_ym IS '帐期计费年月';
COMMENT ON COLUMN prod_inst_acct_info.org_id IS '收款单位';
COMMENT ON COLUMN prod_inst_acct_info.trust_flag IS '是否代收费';
COMMENT ON COLUMN prod_inst_acct_info.trust_org_id IS '委托收款单位';
COMMENT ON COLUMN prod_inst_acct_info.cust_id IS '客户编号';
COMMENT ON COLUMN prod_inst_acct_info.acct_id IS '帐户编号';
COMMENT ON COLUMN prod_inst_acct_info.order_nbr IS '流水号';
COMMENT ON COLUMN prod_inst_acct_info.acc_num IS '电路代号';
COMMENT ON COLUMN prod_inst_acct_info.contract_nbr IS '合同号';
COMMENT ON COLUMN prod_inst_acct_info.rmb_all IS '应收金额';
COMMENT ON COLUMN prod_inst_acct_info.us_all IS '应收金额美元';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_a IS 'A端端口费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_a_amount IS 'A端端口费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_a IS 'A端代维费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_a_amount IS 'A端代维费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_a IS 'A端本地月租';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_a_amount IS 'A端本地月租调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_z IS 'Z端端口费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_z_amount IS 'Z端端口费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_z IS 'Z端代维费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_z_amount IS 'Z端代维费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_z IS 'Z端本地月租费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_z_amount IS 'Z端本地月租费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_a IS '国内A长途费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_a_amount IS '国内A长途费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_b IS '国内B长途费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_b_amount IS '国内B长途费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_inter IS '国际长途费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_inter_amount IS '国际长途费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_oppo IS '国际对端金额';
COMMENT ON COLUMN prod_inst_acct_info.rmb_oppo_amount IS '国际对端金额调整';
COMMENT ON COLUMN prod_inst_acct_info.us_port_a IS 'A端端口费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_port_a_amount IS 'A端端口费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_a IS 'A端代维费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_a_amount IS 'A端代维费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_a IS 'A端本地月租美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_a_amount IS 'A端本地月租调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_port_z IS 'Z端端口费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_port_z_amount IS 'Z端端口费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_z IS 'Z端代维费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_z_amount IS 'Z端代维费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_z IS 'Z端本地月租费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_z_amount IS 'Z端本地月租费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_a IS '国内A长途费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_a_amount IS '国内A长途费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_b IS '国内B长途费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_b_amount IS '国内B长途费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_inter IS '国际长途费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_inter_amount IS '国际长途费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_oppo IS '国际对端金额美元';
COMMENT ON COLUMN prod_inst_acct_info.us_oppo_amount IS '国际对端金额调整美元';
COMMENT ON COLUMN prod_inst_acct_info.org_port_a IS 'A端端口费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_port_a IS 'A端端口优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_main_a IS 'A端代维费报价';
COMMENT ON COLUMN prod_inst_acct_info.org_local_a IS 'A端本地月租报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_local_a IS 'A端本地月租优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_port_z IS 'Z端端口费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_port_z IS 'Z端端口优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_main_z IS 'Z端代维费报价';
COMMENT ON COLUMN prod_inst_acct_info.org_local_z IS 'Z端本地月租费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_local_z IS 'Z端本地优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_loop_a IS '国内A长途费报价';
COMMENT ON COLUMN prod_inst_acct_info.org_loop_b IS '国内B长途费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_loop IS '国内长途优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_inter IS '国际长途费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_inter IS '国际长途优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_oppo IS '国际对端金额报价';
COMMENT ON COLUMN prod_inst_acct_info.cal_prod_inst_id IS '当期计费资料ID';
COMMENT ON COLUMN prod_inst_acct_info.bill_type IS '收费类型(1--一次性费用，2-- 月租费用)';
COMMENT ON COLUMN prod_inst_acct_info.cal_org_id IS '作帐单位';
COMMENT ON COLUMN prod_inst_acct_info.notice_id IS '通知单ID';
COMMENT ON COLUMN prod_inst_acct_info.us_pay_money IS '销帐金额(US)';
COMMENT ON COLUMN prod_inst_acct_info.pay_money IS '销帐金额(RMB)';
COMMENT ON COLUMN prod_inst_acct_info.pay_flag IS '销帐标识';
COMMENT ON COLUMN prod_inst_acct_info.taxes IS '税金';
COMMENT ON COLUMN prod_inst_acct_info.tax_rate IS '税率，ICT时显示NULL值';
COMMENT ON COLUMN prod_inst_acct_info.belong_org IS '部门ID';
-- 新加的扩展字段
COMMENT ON COLUMN prod_inst_acct_info.NO_US_PAY_MONEY IS '未销账金额美元';
COMMENT ON COLUMN prod_inst_acct_info.NO_PAY_MONEY IS '未销账金额';
COMMENT ON COLUMN prod_inst_acct_info.latn_id IS '本地网代码';
COMMENT ON COLUMN prod_inst_acct_info.prov_id IS '省份代码';
COMMENT ON COLUMN prod_inst_acct_info.cust_region IS '客户营销责任地';
COMMENT ON COLUMN prod_inst_acct_info.create_org_id IS '发起局编码';
COMMENT ON COLUMN prod_inst_acct_info.cycle_type IS '收费周期';
COMMENT ON COLUMN prod_inst_acct_info.fee_cycle IS '费用周期';
COMMENT ON COLUMN prod_inst_acct_info.control_dep IS '管控部门';
COMMENT ON COLUMN prod_inst_acct_info.begin_rent_cd IS '起租系数';
COMMENT ON COLUMN prod_inst_acct_info.stop_rent_cd IS '退租系数';
COMMENT ON COLUMN prod_inst_acct_info.industry_type IS '行业类型';
COMMENT ON COLUMN prod_inst_acct_info.control_type IS '直管类型';
COMMENT ON COLUMN prod_inst_acct_info.account IS '接入号';
COMMENT ON COLUMN prod_inst_acct_info.EXT_PROD_INST_ID IS 'CRM实例ID';
""")
vn.train(ddl="""CREATE TABLE if not exists idc_province_report (
id int8 NOT NULL DEFAULT nextval('idc_province_report_id_seq'::regclass), -- 主键 ID, 使用序列实现自增
province VARCHAR(255), -- 省份名称
province_code text COLLATE pg_catalog.default, -- 省份编码
total_rack_count INT, -- 总机架数量
total_bandwidth_count INT, -- 总带宽数量
dynamic_traffic NUMERIC(18,2), -- 动态流量
fixed_traffic NUMERIC(18,2), -- 固定流量
total_revenue NUMERIC(18,2), -- 权责总收入
managed_host_total_revenue NUMERIC(18,2), -- 主机托管权责总收入
idc_bandwidth_total_revenue NUMERIC(18,2), -- IDC带宽权责总收入
other_products_total_revenue NUMERIC(18,2), -- 其他产品权责总收入
one_time_fee NUMERIC(18,2), -- 一次性费用
rack_slot_count INT, -- 机位数量
rack_slot_revenue NUMERIC(18,2), -- 机位收入
rack_count INT, -- 机架数量
rack_revenue NUMERIC(18,2), -- 机架收入
power_count INT, -- 电力数量
power_revenue NUMERIC(18,2), -- 电力收入
auxiliary_area_count INT, -- 工作附属区数量
auxiliary_area_revenue NUMERIC(18,2), -- 工作附属区收入
machine_room_count INT, -- 机房数量
machine_room_revenue NUMERIC(18,2), -- 机房收入
bgp_dynamic_traffic NUMERIC(18,2), -- BGP动态流量
bgp_dynamic_revenue NUMERIC(18,2), -- BGP动态收入
bgp_fixed_traffic NUMERIC(18,2), -- BGP固定流量
bgp_fixed_revenue NUMERIC(18,2), -- BGP固定收入
static_protocol_dynamic_traffic NUMERIC(18,2), -- 静态协议动态流量
static_protocol_dynamic_revenue NUMERIC(18,2), -- 静态协议动态收入
static_protocol_fixed_traffic NUMERIC(18,2), -- 静态协议固定流量
static_protocol_fixed_revenue NUMERIC(18,2), -- 静态协议固定收入
shared_port_traffic NUMERIC(18,2), -- 共享端口流量
shared_port_revenue NUMERIC(18,2), -- 共享端口收入
ip_broadcast_and_bgp_activation_revenue NUMERIC(18,2), -- IP地址广播及BGP开通收入
ip_rental_revenue NUMERIC(18,2), -- IP出租收入
idc_value_added_service_revenue NUMERIC(18,2), -- IDC增值业务收入
trust_flag CHAR(1), -- 信任标志 (0: 非委托, 1: 委托),
bill_ym text COLLATE pg_catalog.default, -- 计费年月
latn_id varchar(32) COLLATE pg_catalog.default, --本地网代码
prov_id varchar(32) COLLATE pg_catalog.default, -- 省份代码
batch_num_ym text COLLATE pg_catalog.default, -- 批次年月便于数据清理
PRIMARY KEY (id) -- 设置主键
);
-- Add comments for each column
COMMENT ON COLUMN idc_province_report.province IS '省份名称';
COMMENT ON COLUMN idc_province_report.province_code IS '省份编码';
COMMENT ON COLUMN idc_province_report.total_rack_count IS '总机架数量';
COMMENT ON COLUMN idc_province_report.total_bandwidth_count IS '总带宽数量';
COMMENT ON COLUMN idc_province_report.dynamic_traffic IS '动态流量';
COMMENT ON COLUMN idc_province_report.fixed_traffic IS '固定流量';
COMMENT ON COLUMN idc_province_report.total_revenue IS '权责总收入';
COMMENT ON COLUMN idc_province_report.managed_host_total_revenue IS '主机托管权责总收入';
COMMENT ON COLUMN idc_province_report.idc_bandwidth_total_revenue IS 'IDC带宽权责总收入';
COMMENT ON COLUMN idc_province_report.other_products_total_revenue IS '其他产品权责总收入';
COMMENT ON COLUMN idc_province_report.one_time_fee IS '一次性费用';
COMMENT ON COLUMN idc_province_report.rack_slot_count IS '机位数量';
COMMENT ON COLUMN idc_province_report.rack_slot_revenue IS '机位收入';
COMMENT ON COLUMN idc_province_report.rack_count IS '机架数量';
COMMENT ON COLUMN idc_province_report.rack_revenue IS '机架收入';
COMMENT ON COLUMN idc_province_report.power_count IS '电力数量';
COMMENT ON COLUMN idc_province_report.power_revenue IS '电力收入';
COMMENT ON COLUMN idc_province_report.auxiliary_area_count IS '工作附属区数量';
COMMENT ON COLUMN idc_province_report.auxiliary_area_revenue IS '工作附属区收入';
COMMENT ON COLUMN idc_province_report.machine_room_count IS '机房数量';
COMMENT ON COLUMN idc_province_report.machine_room_revenue IS '机房收入';
COMMENT ON COLUMN idc_province_report.bgp_dynamic_traffic IS 'BGP动态流量';
COMMENT ON COLUMN idc_province_report.bgp_dynamic_revenue IS 'BGP动态收入';
COMMENT ON COLUMN idc_province_report.bgp_fixed_traffic IS 'BGP固定流量';
COMMENT ON COLUMN idc_province_report.bgp_fixed_revenue IS 'BGP固定收入';
COMMENT ON COLUMN idc_province_report.static_protocol_dynamic_traffic IS '静态协议动态流量';
COMMENT ON COLUMN idc_province_report.static_protocol_dynamic_revenue IS '静态协议动态收入';
COMMENT ON COLUMN idc_province_report.static_protocol_fixed_traffic IS '静态协议固定流量';
COMMENT ON COLUMN idc_province_report.static_protocol_fixed_revenue IS '静态协议固定收入';
COMMENT ON COLUMN idc_province_report.shared_port_traffic IS '共享端口流量';
COMMENT ON COLUMN idc_province_report.shared_port_revenue IS '共享端口收入';
COMMENT ON COLUMN idc_province_report.ip_broadcast_and_bgp_activation_revenue IS 'IP地址广播及BGP开通收入';
COMMENT ON COLUMN idc_province_report.ip_rental_revenue IS 'IP出租收入';
COMMENT ON COLUMN idc_province_report.idc_value_added_service_revenue IS 'IDC增值业务收入';
COMMENT ON COLUMN idc_province_report.trust_flag IS '信任标志 (0: 非信任, 1: 信任)';
COMMENT ON COLUMN idc_province_report.bill_ym IS '计费年月';
COMMENT ON COLUMN idc_province_report.batch_num_ym IS '批次年月便于数据清理';
COMMENT ON COLUMN idc_province_report.latn_id IS '本地网代码';
COMMENT ON COLUMN idc_province_report.prov_id IS '省份代码';
""")
vn.train(question="全国2024年8月税金最多的两个省份是？", sql="SELECT PROV_ID, SUM(TAXES) AS `T2024年8月税金` FROM `prod_inst_acct_info` WHERE CUST_YM = '202408' GROUP BY PROV_ID ORDER BY `T2024年8月税金` DESC LIMIT 2")
vn.train(question="教育行业2024年3月A端端口费是多少？", sql="SELECT SUM(RMB_PORT_A) AS `T2024年3月A端端口费` FROM `prod_inst_acct_info` WHERE CUST_YM = '202403'AND INDUSTRY_TYPE = 'CT13'")
vn.train(question="湖北省2024年6月总机架数量环比是多少？", sql="SELECT tmp.m_data, tmp.pre_m_data, ( ( tmp.m_data - tmp.pre_m_data ) / tmp.m_data ) * 100 AS `环比` FROM ( SELECT ( SELECT SUM ( COALESCE ( TOTAL_RACK_COUNT, 0 ) ) AS pre_m_data FROM idc_province_report WHERE BILL_YM = '202405' AND PROV_ID = '8420000' ) AS pre_m_data, ( SELECT SUM ( TOTAL_RACK_COUNT ) AS m_data FROM idc_province_report WHERE BILL_YM = '202406' AND PROV_ID = '8420000' ) AS m_data ) tmp")
vn.train(question="湖北省2024年8月总机架数量是多少？", sql=" select SUM ( TOTAL_RACK_COUNT ) as `机架数量`, PROV_ID as `省份代码` , bill_ym as `账期` from idc_province_report where bill_ym = '202408' and PROV_ID = '8420000' GROUP BY PROV_ID, bill_ym")
from vanna.flask import VannaFlaskApp
app = VannaFlaskApp(vn,
# auth=SimplePassword(users=[{"email": "admin@example.com", "password": "password"}]),
logo="https://tydic.com/images/logo2.png",
allow_llm_to_see_data=True,
summarization=True,
title="迪科chatbi",
subtitle="sql 生成器")
app.run(port = 6060, host = '0.0.0.0' , debug = True)
