wzy-warehouse
43 lines
1.0 KiB
Python
43 lines
1.0 KiB
Python
"""
|
|
在所有 qgis 动态表中添加 tid 自增主键列
|
|
QGIS 的 datasource key='tid' 需要此列
|
|
"""
|
|
import psycopg2
|
|
|
|
TABLES = [
|
|
'hazard_hydrops',
|
|
'lifeline_outfall',
|
|
'lifeline_pipe',
|
|
'risk_census_population',
|
|
'sx_xa_towns',
|
|
]
|
|
|
|
c = psycopg2.connect(
|
|
host='47.92.216.173', port=7654,
|
|
user='postgres', password='zhangsan',
|
|
database='xian_new'
|
|
)
|
|
c.autocommit = True
|
|
cur = c.cursor()
|
|
|
|
for t in TABLES:
|
|
try:
|
|
# 检查 tid 列是否存在
|
|
cur.execute(f"""
|
|
SELECT EXISTS (
|
|
SELECT FROM information_schema.columns
|
|
WHERE table_schema='qgis' AND table_name='{t}' AND column_name='tid'
|
|
)
|
|
""")
|
|
exists = cur.fetchone()[0]
|
|
|
|
if exists:
|
|
print(f"qgis.{t}: tid 列已存在, 跳过")
|
|
else:
|
|
cur.execute(f'ALTER TABLE qgis."{t}" ADD COLUMN tid SERIAL')
|
|
print(f"qgis.{t}: tid 列添加成功")
|
|
except Exception as e:
|
|
print(f"qgis.{t}: 失败 - {e}")
|
|
|
|
c.close()
|