80 lines
2.1 KiB
Python
80 lines
2.1 KiB
Python
import os
|
|
import psycopg2
|
|
from dotenv import load_dotenv
|
|
|
|
# Load environment variables
|
|
root_env = os.path.join(os.path.dirname(os.path.dirname(__file__)), '.env')
|
|
load_dotenv(root_env)
|
|
|
|
# Connect to database
|
|
conn = psycopg2.connect(
|
|
host=os.getenv('DB_HOST', '192.168.0.61'),
|
|
port=os.getenv('DB_PORT', '55432'),
|
|
dbname=os.getenv('DB_NAME', 'njts_acct'),
|
|
user=os.getenv('DB_USER', 'njts_app'),
|
|
password=os.getenv('DB_PASSWORD')
|
|
)
|
|
|
|
cur = conn.cursor()
|
|
|
|
print("保険率カラムを DECIMAL(5,3) に変更中...")
|
|
|
|
# Alter insurance_rates table
|
|
cur.execute("""
|
|
ALTER TABLE insurance_rates
|
|
ALTER COLUMN employee_rate TYPE DECIMAL(5, 3),
|
|
ALTER COLUMN employer_rate TYPE DECIMAL(5, 3)
|
|
""")
|
|
|
|
# Alter child_support_contribution_rates table
|
|
cur.execute("""
|
|
ALTER TABLE child_support_contribution_rates
|
|
ALTER COLUMN contribution_rate TYPE DECIMAL(5, 3)
|
|
""")
|
|
|
|
conn.commit()
|
|
|
|
print("OK カラム変更完了")
|
|
|
|
# Verify changes
|
|
cur.execute("""
|
|
SELECT column_name, data_type, numeric_precision, numeric_scale
|
|
FROM information_schema.columns
|
|
WHERE table_name='insurance_rates'
|
|
AND column_name IN ('employee_rate', 'employer_rate')
|
|
ORDER BY column_name
|
|
""")
|
|
|
|
print("\n変更後の insurance_rates カラム:")
|
|
for row in cur.fetchall():
|
|
print(f" {row[0]}: {row[1]}({row[2]},{row[3]})")
|
|
|
|
cur.execute("""
|
|
SELECT column_name, data_type, numeric_precision, numeric_scale
|
|
FROM information_schema.columns
|
|
WHERE table_name='child_support_contribution_rates'
|
|
AND column_name = 'contribution_rate'
|
|
""")
|
|
|
|
print("\n変更後の child_support_contribution_rates カラム:")
|
|
for row in cur.fetchall():
|
|
print(f" {row[0]}: {row[1]}({row[2]},{row[3]})")
|
|
|
|
# Check data
|
|
cur.execute("""
|
|
SELECT rate_year, rate_type, calculation_target, prefecture,
|
|
employee_rate, employer_rate
|
|
FROM insurance_rates
|
|
WHERE rate_year = 2025 AND rate_type = '健康保険'
|
|
LIMIT 3
|
|
""")
|
|
|
|
print("\n実際のデータ (2025年健康保険):")
|
|
for row in cur.fetchall():
|
|
print(f" {row[0]} {row[1]} {row[2]} {row[3]}: 従業員={float(row[4]):.3f} 会社={float(row[5]):.3f}")
|
|
|
|
cur.close()
|
|
conn.close()
|
|
|
|
print("\n完了！")
|