163 lines
6.5 KiB
Python
163 lines
6.5 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""
|
|
添加缺失的 ODS 列到数据库
|
|
"""
|
|
import psycopg2
|
|
|
|
DSN = 'postgresql://local-Python:Neo-local-1991125@100.64.0.4:5432/LLZQ-test'
|
|
|
|
# 缺失字段定义：表名 -> [(列名, 类型, 注释)]
|
|
MISSING_COLUMNS = {
|
|
'billiards_ods.settlement_records': [
|
|
('electricityadjustmoney', 'NUMERIC(18,2)', '电费调整金额'),
|
|
('electricitymoney', 'NUMERIC(18,2)', '电费金额'),
|
|
('mervousalesamount', 'NUMERIC(18,2)', '商户券销售额'),
|
|
('plcouponsaleamount', 'NUMERIC(18,2)', '平台券销售额'),
|
|
('realelectricitymoney', 'NUMERIC(18,2)', '实际电费金额'),
|
|
('settlelist', 'JSONB', '结算明细列表'),
|
|
],
|
|
'billiards_ods.recharge_settlements': [
|
|
('electricityadjustmoney', 'NUMERIC(18,2)', '电费调整金额'),
|
|
('electricitymoney', 'NUMERIC(18,2)', '电费金额'),
|
|
('mervousalesamount', 'NUMERIC(18,2)', '商户券销售额'),
|
|
('plcouponsaleamount', 'NUMERIC(18,2)', '平台券销售额'),
|
|
('realelectricitymoney', 'NUMERIC(18,2)', '实际电费金额'),
|
|
('settlelist', 'JSONB', '结算明细列表'),
|
|
],
|
|
'billiards_ods.table_fee_transactions': [
|
|
('activity_discount_amount', 'NUMERIC(18,2)', '活动折扣金额'),
|
|
('order_consumption_type', 'INT', '订单消费类型'),
|
|
('real_service_money', 'NUMERIC(18,2)', '实际服务费金额'),
|
|
],
|
|
'billiards_ods.assistant_service_records': [
|
|
('assistantteamname', 'TEXT', '助教团队名称'),
|
|
('real_service_money', 'NUMERIC(18,2)', '实际服务费金额'),
|
|
],
|
|
'billiards_ods.group_buy_redemption_records': [
|
|
('assistant_service_share_money', 'NUMERIC(18,2)', '助教服务分摊金额'),
|
|
('assistant_share_money', 'NUMERIC(18,2)', '助教分摊金额'),
|
|
('coupon_sale_id', 'BIGINT', '优惠券销售ID'),
|
|
('good_service_share_money', 'NUMERIC(18,2)', '商品服务分摊金额'),
|
|
('goods_share_money', 'NUMERIC(18,2)', '商品分摊金额'),
|
|
('member_discount_money', 'NUMERIC(18,2)', '会员折扣金额'),
|
|
('recharge_share_money', 'NUMERIC(18,2)', '充值分摊金额'),
|
|
('table_service_share_money', 'NUMERIC(18,2)', '台费服务分摊金额'),
|
|
('table_share_money', 'NUMERIC(18,2)', '台费分摊金额'),
|
|
],
|
|
'billiards_ods.table_fee_discount_records': [
|
|
('area_type_id', 'BIGINT', '区域类型ID'),
|
|
('charge_free', 'BOOLEAN', '是否免费'),
|
|
('site_table_area_id', 'BIGINT', '门店台区ID'),
|
|
('site_table_area_name', 'TEXT', '门店台区名称'),
|
|
('sitename', 'TEXT', '门店名称'),
|
|
('table_name', 'TEXT', '台桌名称'),
|
|
('table_price', 'NUMERIC(18,2)', '台桌价格'),
|
|
('tenant_name', 'TEXT', '租户名称'),
|
|
],
|
|
'billiards_ods.member_stored_value_cards': [
|
|
('able_share_member_discount', 'BOOLEAN', '是否可共享会员折扣'),
|
|
('electricity_deduct_radio', 'NUMERIC(18,4)', '电费扣减比例'),
|
|
('electricity_discount', 'NUMERIC(18,4)', '电费折扣'),
|
|
('electricitycarddeduct', 'BOOLEAN', '电费卡扣'),
|
|
('member_grade', 'INT', '会员等级'),
|
|
('principal_balance', 'NUMERIC(18,2)', '本金余额'),
|
|
('rechargefreezebalance', 'NUMERIC(18,2)', '充值冻结余额'),
|
|
],
|
|
'billiards_ods.member_profiles': [
|
|
('pay_money_sum', 'NUMERIC(18,2)', '累计支付金额'),
|
|
('person_tenant_org_id', 'BIGINT', '人员租户组织ID'),
|
|
('person_tenant_org_name', 'TEXT', '人员租户组织名称'),
|
|
('recharge_money_sum', 'NUMERIC(18,2)', '累计充值金额'),
|
|
('register_source', 'TEXT', '注册来源'),
|
|
],
|
|
'billiards_ods.member_balance_changes': [
|
|
('principal_after', 'NUMERIC(18,2)', '变动后本金'),
|
|
('principal_before', 'NUMERIC(18,2)', '变动前本金'),
|
|
('principal_data', 'TEXT', '本金变动数据'),
|
|
],
|
|
'billiards_ods.group_buy_packages': [
|
|
('is_first_limit', 'BOOLEAN', '是否首单限制'),
|
|
('sort', 'INT', '排序'),
|
|
('tenantcouponsaleorderitemid', 'BIGINT', '租户券销售订单项ID'),
|
|
],
|
|
'billiards_ods.store_goods_master': [
|
|
('commodity_code', 'TEXT', '商品编码'),
|
|
('not_sale', 'BOOLEAN', '是否停售'),
|
|
],
|
|
'billiards_ods.assistant_cancellation_records': [
|
|
('tenant_id', 'BIGINT', '租户ID'),
|
|
],
|
|
'billiards_ods.store_goods_sales_records': [
|
|
('coupon_share_money', 'NUMERIC(18,2)', '优惠券分摊金额'),
|
|
],
|
|
'billiards_ods.payment_transactions': [
|
|
('tenant_id', 'BIGINT', '租户ID'),
|
|
],
|
|
'billiards_ods.site_tables_master': [
|
|
('order_id', 'BIGINT', '订单ID'),
|
|
],
|
|
'billiards_ods.tenant_goods_master': [
|
|
('not_sale', 'BOOLEAN', '是否停售'),
|
|
],
|
|
}
|
|
|
|
|
|
def get_existing_columns(conn, schema, table):
|
|
"""获取表已有的列"""
|
|
sql = """
|
|
SELECT column_name
|
|
FROM information_schema.columns
|
|
WHERE table_schema = %s AND table_name = %s
|
|
"""
|
|
with conn.cursor() as cur:
|
|
cur.execute(sql, (schema, table))
|
|
return {row[0].lower() for row in cur.fetchall()}
|
|
|
|
|
|
def add_column(conn, full_table, col_name, col_type, comment):
|
|
"""添加列"""
|
|
schema, table = full_table.split('.')
|
|
sql = f'ALTER TABLE {full_table} ADD COLUMN IF NOT EXISTS "{col_name}" {col_type}'
|
|
comment_sql = f"COMMENT ON COLUMN {full_table}.\"{col_name}\" IS '{comment}'"
|
|
|
|
with conn.cursor() as cur:
|
|
cur.execute(sql)
|
|
cur.execute(comment_sql)
|
|
conn.commit()
|
|
print(f" [OK] 添加列: {col_name} ({col_type})")
|
|
|
|
|
|
def main():
|
|
conn = psycopg2.connect(DSN)
|
|
|
|
print("=" * 80)
|
|
print("添加缺失的 ODS 列")
|
|
print("=" * 80)
|
|
|
|
total_added = 0
|
|
total_skipped = 0
|
|
|
|
for full_table, columns in MISSING_COLUMNS.items():
|
|
schema, table = full_table.split('.')
|
|
print(f"\n处理表: {full_table}")
|
|
|
|
existing = get_existing_columns(conn, schema, table)
|
|
|
|
for col_name, col_type, comment in columns:
|
|
if col_name.lower() in existing:
|
|
print(f" [跳过] 列已存在: {col_name}")
|
|
total_skipped += 1
|
|
else:
|
|
add_column(conn, full_table, col_name, col_type, comment)
|
|
total_added += 1
|
|
|
|
conn.close()
|
|
|
|
print("\n" + "=" * 80)
|
|
print(f"完成: 添加 {total_added} 列, 跳过 {total_skipped} 列")
|
|
print("=" * 80)
|
|
|
|
|
|
if __name__ == '__main__':
|
|
main()
|