{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import os\n", "from decimal import Decimal, ROUND_HALF_UP\n", "\n", "# 设置 Decimal 的四舍五入模式和小数位数\n", "decimal_rounding = ROUND_HALF_UP\n", "decimal_places = Decimal('0.0000001')\n", "\n", "os.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"] = \"./gcp-service-account.json\"" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
providerregionregion_nameis_public
0awsap-northeast-1Tokyo1
1awsap-northeast-2Seoul1
2awsap-northeast-3Osaka0
3awsap-south-1Mumbai1
4awsap-southeast-1Singapore1
5awsap-southeast-2Sydney0
6awseu-central-1Frankfurt1
7awssa-east-1São Paulo1
8awsus-east-1N.Virginia1
9awsap-southeast-3Jakarta0
10awsus-west-2Oregon1
11gcpasia-east1Taiwan1
12gcpasia-northeast1Tokyo1
13gcpasia-northeast2Osaka0
14gcpasia-southeast1Singapore1
15gcpasia-southeast2Jakarta1
16gcpus-central1Iowa1
17gcpus-east4N.Virginia1
18gcpus-west1Oregon1
\n", "
" ], "text/plain": [ " provider region region_name is_public\n", "0 aws ap-northeast-1 Tokyo 1\n", "1 aws ap-northeast-2 Seoul 1\n", "2 aws ap-northeast-3 Osaka 0\n", "3 aws ap-south-1 Mumbai 1\n", "4 aws ap-southeast-1 Singapore 1\n", "5 aws ap-southeast-2 Sydney 0\n", "6 aws eu-central-1 Frankfurt 1\n", "7 aws sa-east-1 São Paulo 1\n", "8 aws us-east-1 N.Virginia 1\n", "9 aws ap-southeast-3 Jakarta 0\n", "10 aws us-west-2 Oregon 1\n", "11 gcp asia-east1 Taiwan 1\n", "12 gcp asia-northeast1 Tokyo 1\n", "13 gcp asia-northeast2 Osaka 0\n", "14 gcp asia-southeast1 Singapore 1\n", "15 gcp asia-southeast2 Jakarta 1\n", "16 gcp us-central1 Iowa 1\n", "17 gcp us-east4 N.Virginia 1\n", "18 gcp us-west1 Oregon 1" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "# Reorganizing the data to make each row a tuple representing a region's information\n", "data = [\n", " (\"aws\", \"ap-northeast-1\", \"Tokyo\", 1),\n", " (\"aws\", \"ap-northeast-2\", \"Seoul\", 1),\n", " (\"aws\", \"ap-northeast-3\", \"Osaka\", 0),\n", " (\"aws\", \"ap-south-1\", \"Mumbai\", 1),\n", " (\"aws\", \"ap-southeast-1\", \"Singapore\", 1),\n", " (\"aws\", \"ap-southeast-2\", \"Sydney\", 0),\n", " (\"aws\", \"eu-central-1\", \"Frankfurt\", 1),\n", " (\"aws\", \"sa-east-1\", \"São Paulo\", 1),\n", " (\"aws\", \"us-east-1\", \"N.Virginia\", 1),\n", " (\"aws\", \"ap-southeast-3\", \"Jakarta\", 0),\n", " (\"aws\", \"us-west-2\", \"Oregon\", 1),\n", " (\"gcp\", \"asia-east1\", \"Taiwan\", 1),\n", " (\"gcp\", \"asia-northeast1\", \"Tokyo\", 1),\n", " (\"gcp\", \"asia-northeast2\", \"Osaka\", 0),\n", " (\"gcp\", \"asia-southeast1\", \"Singapore\", 1),\n", " (\"gcp\", \"asia-southeast2\", \"Jakarta\", 1),\n", " (\"gcp\", \"us-central1\", \"Iowa\", 1),\n", " (\"gcp\", \"us-east4\", \"N.Virginia\", 1),\n", " (\"gcp\", \"us-west1\", \"Oregon\", 1)\n", "]\n", "\n", "# Converting the list of tuples into a DataFrame\n", "region_list = pd.DataFrame(data, columns=[\"provider\", \"region\", \"region_name\", \"is_public\"])\n", "region_list" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
providerregionregion_nameprice_typepriceunitperiodis_public
0awsus-west-2Oregonticdc0.130700rcuhour1
1awsus-east-1N.Virginiaticdc0.130700rcuhour1
2awsap-south-1Mumbaiticdc0.139300rcuhour1
3awsap-southeast-1Singaporeticdc0.162300rcuhour1
4awsap-northeast-2Seoulticdc0.158800rcuhour1
...........................
67gcpus-central1Iowadual-region-backup0.190997GBmonth1
68gcpus-east4N.Virginiadual-region-backup0.219999GBmonth1
69gcpus-west1Oregondual-region-backup0.190997GBmonth1
70awsap-southeast-2Sydneyticdc0.162300rcuhour0
71awsap-southeast-3Jakartaticdc0.162300rcuhour0
\n", "

72 rows × 8 columns

\n", "
" ], "text/plain": [ " provider region region_name price_type price unit \\\n", "0 aws us-west-2 Oregon ticdc 0.130700 rcu \n", "1 aws us-east-1 N.Virginia ticdc 0.130700 rcu \n", "2 aws ap-south-1 Mumbai ticdc 0.139300 rcu \n", "3 aws ap-southeast-1 Singapore ticdc 0.162300 rcu \n", "4 aws ap-northeast-2 Seoul ticdc 0.158800 rcu \n", ".. ... ... ... ... ... ... \n", "67 gcp us-central1 Iowa dual-region-backup 0.190997 GB \n", "68 gcp us-east4 N.Virginia dual-region-backup 0.219999 GB \n", "69 gcp us-west1 Oregon dual-region-backup 0.190997 GB \n", "70 aws ap-southeast-2 Sydney ticdc 0.162300 rcu \n", "71 aws ap-southeast-3 Jakarta ticdc 0.162300 rcu \n", "\n", " period is_public \n", "0 hour 1 \n", "1 hour 1 \n", "2 hour 1 \n", "3 hour 1 \n", "4 hour 1 \n", ".. ... ... \n", "67 month 1 \n", "68 month 1 \n", "69 month 1 \n", "70 hour 0 \n", "71 hour 0 \n", "\n", "[72 rows x 8 columns]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from sqlalchemy import create_engine\n", "\n", "# 数据库参数\n", "username = 'VrZ2gT88Be1w1tS.root'\n", "password = 'RptuDt6C0D16Uy6s' # 或者从安全的地方获取密码\n", "host = 'gateway01.ap-northeast-1.prod.aws.tidbcloud.com'\n", "port = 4000\n", "database = 'tidbcloud'\n", "ssl_ca = '/etc/ssl/cert.pem'\n", "# 创建数据库连接\n", "engine = create_engine(f\"mysql+pymysql://{username}:{password}@{host}:{port}/{database}?ssl_ca={ssl_ca}&ssl_verify_cert=true\")\n", "# SQL 查询\n", "query = \"select provider, region, region_name, price_type, price, unit, period, is_valid as is_public from dedicated_other_price;\"\n", "# 执行查询并将结果保存到 DataFrame\n", "pc_other_pring_df = pd.read_sql(query, engine)\n", "pc_other_pring_df" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
providerregionprice_typepriceunitperiodregion_nameis_public
0awsap-northeast-1backup0.0253456GBmonthTokyo1
1awsap-northeast-1dual-region-backup0.2179999GBmonthTokyo1
2awsap-northeast-2backup0.0253456GBmonthSeoul1
3awsap-northeast-2dual-region-backup0.2179999GBmonthSeoul1
4awsap-northeast-3dual-region-backup0.2179999GBmonthOsaka0
5awsap-south-1backup0.0253456GBmonthMumbai1
6awsap-south-1dual-region-backup0.2180014GBmonthMumbai1
7awsap-southeast-1backup0.0253456GBmonthSingapore1
8awsap-southeast-1dual-region-backup0.2179999GBmonthSingapore1
9awsap-southeast-2backup0.0250025GBmonthSydney0
10awsap-southeast-2dual-region-backup0.2179999GBmonthSydney0
11awsap-southeast-3backup0.0253456GBmonthJakarta0
12awseu-central-1backup0.0248419GBmonthFrankfurt1
13awseu-central-1dual-region-backup0.2129994GBmonthFrankfurt1
14awssa-east-1backup0.0405004GBmonthSão Paulo1
15awssa-east-1dual-region-backup0.3519987GBmonthSão Paulo1
16awsus-east-1backup0.0233162GBmonthN.Virginia1
17awsus-east-1dual-region-backup0.1999981GBmonthN.Virginia1
18awsus-west-2backup0.0233162GBmonthOregon1
19awsus-west-2dual-region-backup0.1999981GBmonthOregon1
20gcpasia-east1backup0.0202794GBmonthTaiwan1
21gcpasia-east1dual-region-backup0.1909972GBmonthTaiwan1
22gcpasia-northeast1backup0.0233162GBmonthTokyo1
23gcpasia-northeast1dual-region-backup0.2200001GBmonthTokyo1
24gcpasia-northeast2backup0.0233162GBmonthOsaka0
25gcpasia-northeast2dual-region-backup0.2200001GBmonthOsaka0
26gcpasia-southeast1backup0.0202794GBmonthSingapore1
27gcpasia-southeast1dual-region-backup0.1909972GBmonthSingapore1
28gcpasia-southeast2backup0.0230023GBmonthJakarta1
29gcpus-central1backup0.0202794GBmonthIowa1
30gcpus-central1dual-region-backup0.1909972GBmonthIowa1
31gcpus-east4backup0.0233162GBmonthN.Virginia1
32gcpus-east4dual-region-backup0.2199986GBmonthN.Virginia1
33gcpus-west1backup0.0202794GBmonthOregon1
34gcpus-west1dual-region-backup0.1909972GBmonthOregon1
\n", "
" ], "text/plain": [ " provider region price_type price unit period \\\n", "0 aws ap-northeast-1 backup 0.0253456 GB month \n", "1 aws ap-northeast-1 dual-region-backup 0.2179999 GB month \n", "2 aws ap-northeast-2 backup 0.0253456 GB month \n", "3 aws ap-northeast-2 dual-region-backup 0.2179999 GB month \n", "4 aws ap-northeast-3 dual-region-backup 0.2179999 GB month \n", "5 aws ap-south-1 backup 0.0253456 GB month \n", "6 aws ap-south-1 dual-region-backup 0.2180014 GB month \n", "7 aws ap-southeast-1 backup 0.0253456 GB month \n", "8 aws ap-southeast-1 dual-region-backup 0.2179999 GB month \n", "9 aws ap-southeast-2 backup 0.0250025 GB month \n", "10 aws ap-southeast-2 dual-region-backup 0.2179999 GB month \n", "11 aws ap-southeast-3 backup 0.0253456 GB month \n", "12 aws eu-central-1 backup 0.0248419 GB month \n", "13 aws eu-central-1 dual-region-backup 0.2129994 GB month \n", "14 aws sa-east-1 backup 0.0405004 GB month \n", "15 aws sa-east-1 dual-region-backup 0.3519987 GB month \n", "16 aws us-east-1 backup 0.0233162 GB month \n", "17 aws us-east-1 dual-region-backup 0.1999981 GB month \n", "18 aws us-west-2 backup 0.0233162 GB month \n", "19 aws us-west-2 dual-region-backup 0.1999981 GB month \n", "20 gcp asia-east1 backup 0.0202794 GB month \n", "21 gcp asia-east1 dual-region-backup 0.1909972 GB month \n", "22 gcp asia-northeast1 backup 0.0233162 GB month \n", "23 gcp asia-northeast1 dual-region-backup 0.2200001 GB month \n", "24 gcp asia-northeast2 backup 0.0233162 GB month \n", "25 gcp asia-northeast2 dual-region-backup 0.2200001 GB month \n", "26 gcp asia-southeast1 backup 0.0202794 GB month \n", "27 gcp asia-southeast1 dual-region-backup 0.1909972 GB month \n", "28 gcp asia-southeast2 backup 0.0230023 GB month \n", "29 gcp us-central1 backup 0.0202794 GB month \n", "30 gcp us-central1 dual-region-backup 0.1909972 GB month \n", "31 gcp us-east4 backup 0.0233162 GB month \n", "32 gcp us-east4 dual-region-backup 0.2199986 GB month \n", "33 gcp us-west1 backup 0.0202794 GB month \n", "34 gcp us-west1 dual-region-backup 0.1909972 GB month \n", "\n", " region_name is_public \n", "0 Tokyo 1 \n", "1 Tokyo 1 \n", "2 Seoul 1 \n", "3 Seoul 1 \n", "4 Osaka 0 \n", "5 Mumbai 1 \n", "6 Mumbai 1 \n", "7 Singapore 1 \n", "8 Singapore 1 \n", "9 Sydney 0 \n", "10 Sydney 0 \n", "11 Jakarta 0 \n", "12 Frankfurt 1 \n", "13 Frankfurt 1 \n", "14 São Paulo 1 \n", "15 São Paulo 1 \n", "16 N.Virginia 1 \n", "17 N.Virginia 1 \n", "18 Oregon 1 \n", "19 Oregon 1 \n", "20 Taiwan 1 \n", "21 Taiwan 1 \n", "22 Tokyo 1 \n", "23 Tokyo 1 \n", "24 Osaka 0 \n", "25 Osaka 0 \n", "26 Singapore 1 \n", "27 Singapore 1 \n", "28 Jakarta 1 \n", "29 Iowa 1 \n", "30 Iowa 1 \n", "31 N.Virginia 1 \n", "32 N.Virginia 1 \n", "33 Oregon 1 \n", "34 Oregon 1 " ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from google.cloud import bigquery\n", "\n", "client = bigquery.Client()\n", "\n", "backup_query = \"\"\"\n", "SELECT\n", " LOWER(provider) AS provider,\n", " LOWER(region) AS region,\n", " CASE\n", " WHEN SPLIT(unit_key, ':')[OFFSET(0)] = 'backup-dual-region-storage' THEN 'dual-region-backup'\n", " WHEN SPLIT(unit_key, ':')[OFFSET(0)] = 'backup-storage' THEN 'backup'\n", " ELSE SPLIT(unit_key, ':')[OFFSET(0)]\n", " END AS price_type,\n", " price * 60 * 730 as price,\n", " \"GB\" as unit,\n", " \"month\" as period, \n", "FROM\n", " `gcp-digital-bi.metadb_new.units`\n", "where (unit_key like \"backup-storage%\" or unit_key like \"backup-dual-region-storage%\")\n", " and unit_key like \"%backup%\" \n", " and provider in (\"GCP\", \"AWS\")\n", "order by provider, region, price_type\n", "\"\"\"\n", "\n", "backup_query_job = client.query(backup_query)\n", "rdg_backup_df = backup_query_job.to_dataframe()\n", "\n", "# 四舍五入 price 列，保留 7 位小数\n", "rdg_backup_df['price'] = rdg_backup_df['price'].apply(lambda x: x.quantize(decimal_places, rounding=decimal_rounding))\n", "rdg_backup_df = pd.merge(rdg_backup_df, region_list[['provider', 'region', 'region_name', 'is_public']], on=['provider', 'region'], how='left')\n", "rdg_backup_df" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
providerregionprice_typepriceunitperiodregion_nameis_public
0awsap-northeast-1dm0.1669000rcuhourTokyo1
1awsap-northeast-2dm0.1588000rcuhourSeoul1
2awsap-south-1dm0.1393000rcuhourMumbai1
3awsap-southeast-1dm0.1623000rcuhourSingapore1
4awsap-southeast-2dm0.1623000rcuhourSydney0
5awseu-central-1dm0.1564000rcuhourFrankfurt1
6awssa-east-1dm0.2145000rcuhourSão Paulo1
7awsus-east-1dm0.1307000rcuhourN.Virginia1
8awsus-west-2dm0.1307000rcuhourOregon1
9gcpasia-east1dm0.1628000rcuhourTaiwan1
10gcpasia-northeast1dm0.1868000rcuhourTokyo1
11gcpasia-northeast2dm0.1868000rcuhourOsaka0
12gcpasia-southeast1dm0.1746000rcuhourSingapore1
13gcpasia-southeast2dm0.1937000rcuhourJakarta1
14gcpus-central1dm0.1452000rcuhourIowa1
15gcpus-east4dm0.1626000rcuhourN.Virginia1
16gcpus-west1dm0.1452000rcuhourOregon1
\n", "
" ], "text/plain": [ " provider region price_type price unit period region_name \\\n", "0 aws ap-northeast-1 dm 0.1669000 rcu hour Tokyo \n", "1 aws ap-northeast-2 dm 0.1588000 rcu hour Seoul \n", "2 aws ap-south-1 dm 0.1393000 rcu hour Mumbai \n", "3 aws ap-southeast-1 dm 0.1623000 rcu hour Singapore \n", "4 aws ap-southeast-2 dm 0.1623000 rcu hour Sydney \n", "5 aws eu-central-1 dm 0.1564000 rcu hour Frankfurt \n", "6 aws sa-east-1 dm 0.2145000 rcu hour São Paulo \n", "7 aws us-east-1 dm 0.1307000 rcu hour N.Virginia \n", "8 aws us-west-2 dm 0.1307000 rcu hour Oregon \n", "9 gcp asia-east1 dm 0.1628000 rcu hour Taiwan \n", "10 gcp asia-northeast1 dm 0.1868000 rcu hour Tokyo \n", "11 gcp asia-northeast2 dm 0.1868000 rcu hour Osaka \n", "12 gcp asia-southeast1 dm 0.1746000 rcu hour Singapore \n", "13 gcp asia-southeast2 dm 0.1937000 rcu hour Jakarta \n", "14 gcp us-central1 dm 0.1452000 rcu hour Iowa \n", "15 gcp us-east4 dm 0.1626000 rcu hour N.Virginia \n", "16 gcp us-west1 dm 0.1452000 rcu hour Oregon \n", "\n", " is_public \n", "0 1 \n", "1 1 \n", "2 1 \n", "3 1 \n", "4 0 \n", "5 1 \n", "6 1 \n", "7 1 \n", "8 1 \n", "9 1 \n", "10 1 \n", "11 0 \n", "12 1 \n", "13 1 \n", "14 1 \n", "15 1 \n", "16 1 " ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dm_query = \"\"\"\n", "SELECT\n", " LOWER(provider) AS provider,\n", " LOWER(region) AS region,\n", " \"dm\" as price_type,\n", " price * 60 as price,\n", " \"rcu\" as unit,\n", " \"hour\" as period, \n", "FROM\n", " `gcp-digital-bi.metadb_new.units`\n", "where (unit_key like \"dm-rcu%\")\n", " and provider in (\"GCP\", \"AWS\")\n", "order by provider, region, price_type\n", "\"\"\"\n", "\n", "dm_query_job = client.query(dm_query)\n", "rdg_dm_df = dm_query_job.to_dataframe()\n", "# 四舍五入 price 列，保留 7 位小数\n", "rdg_dm_df['price'] = rdg_dm_df['price'].apply(lambda x: x.quantize(decimal_places, rounding=decimal_rounding))\n", "rdg_dm_df = pd.merge(rdg_dm_df, region_list[['provider', 'region', 'region_name', 'is_public']], on=['provider', 'region'], how='left')\n", "rdg_dm_df" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
providerregionprice_typepriceunitperiodregion_nameis_public
0awsap-northeast-1ticdc0.1669000rcuhourTokyo1
1awsap-northeast-2ticdc0.1588000rcuhourSeoul1
2awsap-south-1ticdc0.1393000rcuhourMumbai1
3awsap-southeast-1ticdc0.1623000rcuhourSingapore1
4awsap-southeast-2ticdc0.1623000rcuhourSydney0
5awsap-southeast-3ticdc0.1623000rcuhourJakarta0
6awseu-central-1ticdc0.1564000rcuhourFrankfurt1
7awssa-east-1ticdc0.2145000rcuhourSão Paulo1
8awsus-east-1ticdc0.1307000rcuhourN.Virginia1
9awsus-west-2ticdc0.1307000rcuhourOregon1
10gcpasia-east1ticdc0.1628000rcuhourTaiwan1
11gcpasia-northeast1ticdc0.1868000rcuhourTokyo1
12gcpasia-northeast2ticdc0.1868000rcuhourOsaka0
13gcpasia-southeast1ticdc0.1746000rcuhourSingapore1
14gcpasia-southeast2ticdc0.1937000rcuhourJakarta1
15gcpus-central1ticdc0.1452000rcuhourIowa1
16gcpus-east4ticdc0.1630000rcuhourN.Virginia1
17gcpus-west1ticdc0.1452000rcuhourOregon1
\n", "
" ], "text/plain": [ " provider region price_type price unit period region_name \\\n", "0 aws ap-northeast-1 ticdc 0.1669000 rcu hour Tokyo \n", "1 aws ap-northeast-2 ticdc 0.1588000 rcu hour Seoul \n", "2 aws ap-south-1 ticdc 0.1393000 rcu hour Mumbai \n", "3 aws ap-southeast-1 ticdc 0.1623000 rcu hour Singapore \n", "4 aws ap-southeast-2 ticdc 0.1623000 rcu hour Sydney \n", "5 aws ap-southeast-3 ticdc 0.1623000 rcu hour Jakarta \n", "6 aws eu-central-1 ticdc 0.1564000 rcu hour Frankfurt \n", "7 aws sa-east-1 ticdc 0.2145000 rcu hour São Paulo \n", "8 aws us-east-1 ticdc 0.1307000 rcu hour N.Virginia \n", "9 aws us-west-2 ticdc 0.1307000 rcu hour Oregon \n", "10 gcp asia-east1 ticdc 0.1628000 rcu hour Taiwan \n", "11 gcp asia-northeast1 ticdc 0.1868000 rcu hour Tokyo \n", "12 gcp asia-northeast2 ticdc 0.1868000 rcu hour Osaka \n", "13 gcp asia-southeast1 ticdc 0.1746000 rcu hour Singapore \n", "14 gcp asia-southeast2 ticdc 0.1937000 rcu hour Jakarta \n", "15 gcp us-central1 ticdc 0.1452000 rcu hour Iowa \n", "16 gcp us-east4 ticdc 0.1630000 rcu hour N.Virginia \n", "17 gcp us-west1 ticdc 0.1452000 rcu hour Oregon \n", "\n", " is_public \n", "0 1 \n", "1 1 \n", "2 1 \n", "3 1 \n", "4 0 \n", "5 0 \n", "6 1 \n", "7 1 \n", "8 1 \n", "9 1 \n", "10 1 \n", "11 1 \n", "12 0 \n", "13 1 \n", "14 1 \n", "15 1 \n", "16 1 \n", "17 1 " ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ticdc_query = \"\"\"\n", "SELECT\n", " LOWER(provider) AS provider,\n", " LOWER(region) AS region,\n", " \"ticdc\" as price_type,\n", " price * 60 as price,\n", " \"rcu\" as unit,\n", " \"hour\" as period, \n", "FROM\n", " `gcp-digital-bi.metadb_new.units`\n", "where (unit_key like \"ticdc-tcu%\")\n", " and provider in (\"GCP\", \"AWS\")\n", "order by provider, region, price_type\n", "\"\"\"\n", "\n", "ticdc_query_job = client.query(ticdc_query)\n", "rdg_ticdc_df = ticdc_query_job.to_dataframe()\n", "# 四舍五入 price 列，保留 7 位小数\n", "rdg_ticdc_df['price'] = rdg_ticdc_df['price'].apply(lambda x: x.quantize(decimal_places, rounding=decimal_rounding))\n", "rdg_ticdc_df = pd.merge(rdg_ticdc_df, region_list[['provider', 'region', 'region_name', 'is_public']], on=['provider', 'region'], how='left')\n", "rdg_ticdc_df" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
providerregionprice_typepriceunitperiodregion_nameis_public
0awsap-northeast-1dm0.1669000rcuhourTokyo1
1awsap-northeast-2dm0.1588000rcuhourSeoul1
2awsap-south-1dm0.1393000rcuhourMumbai1
3awsap-southeast-1dm0.1623000rcuhourSingapore1
4awsap-southeast-2dm0.1623000rcuhourSydney0
...........................
13gcpasia-southeast1ticdc0.1746000rcuhourSingapore1
14gcpasia-southeast2ticdc0.1937000rcuhourJakarta1
15gcpus-central1ticdc0.1452000rcuhourIowa1
16gcpus-east4ticdc0.1630000rcuhourN.Virginia1
17gcpus-west1ticdc0.1452000rcuhourOregon1
\n", "

70 rows × 8 columns

\n", "
" ], "text/plain": [ " provider region price_type price unit period region_name \\\n", "0 aws ap-northeast-1 dm 0.1669000 rcu hour Tokyo \n", "1 aws ap-northeast-2 dm 0.1588000 rcu hour Seoul \n", "2 aws ap-south-1 dm 0.1393000 rcu hour Mumbai \n", "3 aws ap-southeast-1 dm 0.1623000 rcu hour Singapore \n", "4 aws ap-southeast-2 dm 0.1623000 rcu hour Sydney \n", ".. ... ... ... ... ... ... ... \n", "13 gcp asia-southeast1 ticdc 0.1746000 rcu hour Singapore \n", "14 gcp asia-southeast2 ticdc 0.1937000 rcu hour Jakarta \n", "15 gcp us-central1 ticdc 0.1452000 rcu hour Iowa \n", "16 gcp us-east4 ticdc 0.1630000 rcu hour N.Virginia \n", "17 gcp us-west1 ticdc 0.1452000 rcu hour Oregon \n", "\n", " is_public \n", "0 1 \n", "1 1 \n", "2 1 \n", "3 1 \n", "4 0 \n", ".. ... \n", "13 1 \n", "14 1 \n", "15 1 \n", "16 1 \n", "17 1 \n", "\n", "[70 rows x 8 columns]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rdg_other_pricing_df = pd.concat([rdg_dm_df, rdg_backup_df, rdg_ticdc_df])\n", "rdg_other_pricing_df" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "只在 rdg_other_pricing_df 中：\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
providerregionregion_nameprice_typeunitperiodis_publicprice
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [provider, region, region_name, price_type, unit, period, is_public, price]\n", "Index: []" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 重命名带有 _x 后缀的列\n", "rename_columns = {\n", " 'price_x': 'price',\n", "}\n", "\n", "# 只在 pc_cluster_pring_df 中存在的组合\n", "only_in_pc = pc_other_pring_df.merge(rdg_other_pricing_df, on=['provider', 'region', 'region_name', 'price_type', 'unit', 'period', 'is_public'], how='left', indicator=True)\n", "only_in_pc = only_in_pc[only_in_pc['_merge'] == 'left_only']\n", "only_in_pc = only_in_pc.rename(columns=rename_columns)\n", "\n", "\n", "# 只在 rdg_cluster_pricing_df 中存在的组合\n", "only_in_rdg = rdg_other_pricing_df.merge(pc_other_pring_df, on=['provider', 'region', 'region_name', 'price_type', 'unit', 'period', 'is_public'], how='left', indicator=True)\n", "only_in_rdg = only_in_rdg[only_in_rdg['_merge'] == 'left_only']\n", "only_in_rdg = only_in_rdg.rename(columns=rename_columns)\n", "\n", "print(\"\\n只在 rdg_other_pricing_df 中：\")\n", "only_in_rdg[['provider', 'region', 'region_name', 'price_type', 'unit', 'period', 'is_public', 'price']]" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "只在 pc_other_pring_df 中：\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
providerregionregion_nameprice_typeunitperiodis_publicprice
8gcpus-east4N.Virginiaticdcrcuhour00.1626
29gcpus-east4N.VirginiabackupGBmonth00.0230
\n", "
" ], "text/plain": [ " provider region region_name price_type unit period is_public price\n", "8 gcp us-east4 N.Virginia ticdc rcu hour 0 0.1626\n", "29 gcp us-east4 N.Virginia backup GB month 0 0.0230" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 打印结果\n", "print(\"只在 pc_other_pring_df 中：\")\n", "only_in_pc[['provider', 'region', 'region_name', 'price_type', 'unit', 'period', 'is_public', 'price']]" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
providerregionregion_nameprice_typeunitperiodprice_rdgis_publicprice_pcprice_error
17awsap-northeast-1TokyobackupGBmonth0.025345610.0250.0003456
19awsap-northeast-2SeoulbackupGBmonth0.025345610.0250.0003456
22awsap-south-1MumbaibackupGBmonth0.025345610.025350.0000044
24awsap-southeast-1SingaporebackupGBmonth0.025345610.0250.0003456
29awseu-central-1FrankfurtbackupGBmonth0.024841910.02450.0003419
33awsus-east-1N.VirginiabackupGBmonth0.023316210.0230.0003162
35awsus-west-2OregonbackupGBmonth0.023316210.0230.0003162
37gcpasia-east1TaiwanbackupGBmonth0.020279410.020.0002794
39gcpasia-northeast1TokyobackupGBmonth0.023316210.0230.0003162
41gcpasia-northeast2OsakabackupGBmonth0.023316200.0230.0003162
43gcpasia-southeast1SingaporebackupGBmonth0.020279410.020.0002794
45gcpasia-southeast2JakartabackupGBmonth0.023002310.0230.0000023
46gcpus-central1IowabackupGBmonth0.020279410.020.0002794
48gcpus-east4N.VirginiabackupGBmonth0.023316210.023320.0000038
50gcpus-west1OregonbackupGBmonth0.020279410.020.0002794
\n", "
" ], "text/plain": [ " provider region region_name price_type unit period price_rdg \\\n", "17 aws ap-northeast-1 Tokyo backup GB month 0.0253456 \n", "19 aws ap-northeast-2 Seoul backup GB month 0.0253456 \n", "22 aws ap-south-1 Mumbai backup GB month 0.0253456 \n", "24 aws ap-southeast-1 Singapore backup GB month 0.0253456 \n", "29 aws eu-central-1 Frankfurt backup GB month 0.0248419 \n", "33 aws us-east-1 N.Virginia backup GB month 0.0233162 \n", "35 aws us-west-2 Oregon backup GB month 0.0233162 \n", "37 gcp asia-east1 Taiwan backup GB month 0.0202794 \n", "39 gcp asia-northeast1 Tokyo backup GB month 0.0233162 \n", "41 gcp asia-northeast2 Osaka backup GB month 0.0233162 \n", "43 gcp asia-southeast1 Singapore backup GB month 0.0202794 \n", "45 gcp asia-southeast2 Jakarta backup GB month 0.0230023 \n", "46 gcp us-central1 Iowa backup GB month 0.0202794 \n", "48 gcp us-east4 N.Virginia backup GB month 0.0233162 \n", "50 gcp us-west1 Oregon backup GB month 0.0202794 \n", "\n", " is_public price_pc price_error \n", "17 1 0.025 0.0003456 \n", "19 1 0.025 0.0003456 \n", "22 1 0.02535 0.0000044 \n", "24 1 0.025 0.0003456 \n", "29 1 0.0245 0.0003419 \n", "33 1 0.023 0.0003162 \n", "35 1 0.023 0.0003162 \n", "37 1 0.02 0.0002794 \n", "39 1 0.023 0.0003162 \n", "41 0 0.023 0.0003162 \n", "43 1 0.02 0.0002794 \n", "45 1 0.023 0.0000023 \n", "46 1 0.02 0.0002794 \n", "48 1 0.02332 0.0000038 \n", "50 1 0.02 0.0002794 " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 合并 DataFrame\n", "other_pricing_error_df = pd.merge(rdg_other_pricing_df, pc_other_pring_df, on=['provider', 'region', 'region_name', 'price_type', 'unit', 'period', 'is_public'], suffixes=('_rdg', '_pc'))\n", "other_pricing_error_df['price_pc'] = other_pricing_error_df['price_pc'].apply(lambda x: Decimal(str(x)) if not pd.isna(x) else x)\n", "\n", "# 计算价格误差\n", "other_pricing_error_df['price_error'] = abs(other_pricing_error_df['price_rdg'] - other_pricing_error_df['price_pc'])\n", "\n", "other_pricing_error_filtered_df = other_pricing_error_df[(other_pricing_error_df['price_error'] != 0) ]\n", "\n", "# 选择相关列\n", "columns_to_display = ['provider', 'region', 'region_name', 'price_type', 'unit', 'period', 'price_rdg', 'is_public', 'price_pc', 'price_error']\n", "other_pricing_error_result_df = other_pricing_error_filtered_df[columns_to_display]\n", "\n", "# 打印结果\n", "other_pricing_error_result_df" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "from decimal import Decimal\n", "# 选定的列\n", "columns = ['provider', 'region', 'region_name', 'price_type', 'unit', 'period', 'price', 'is_public']\n", "columns_back = ['provider', 'region', 'region_name', 'price_type', 'unit', 'period', 'price', 'is_valid']\n", "\n", "# 目标数据库表名\n", "table_name = 'dedicated_other_price_wip'\n", "\n", "def format_value(value):\n", " if isinstance(value, str):\n", " return f\"'{value}'\"\n", " elif isinstance(value, (int, float, Decimal)):\n", " return str(value)\n", " else:\n", " return f\"'{str(value)}'\"\n", "\n", "# 构建 SQL 插入语句\n", "sql_statements = []\n", "for index, row in only_in_rdg[columns].iterrows():\n", " values = [format_value(row[col]) for col in columns]\n", " values_str = ', '.join(values)\n", " sql = f\"INSERT INTO {table_name} ({', '.join(columns_back)}) VALUES ({values_str});\"\n", " sql_statements.append(sql)\n", "\n", "# 打印 SQL 语句\n", "for sql in sql_statements:\n", " print(sql)" ] } ], "metadata": { "kernelspec": { "display_name": "autogen", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.13" } }, "nbformat": 4, "nbformat_minor": 2 }