dbms_sanya/python/plugins/ResourceTree/ui/PostgreSQL.py

# -*- coding: utf-8 -*-

__author__ = 'wanger'
__date__ = '2024-08-20'
__copyright__ = '(C) 2024 by siwei'
__revision__ = '1.0'

import time
from typing import Optional
import os
import psycopg2
import uuid


class PostgreSQL:
    # 矢量数据元数据表
    Vector_Storage = "t_vector_storage"
    def __init__(
            self,
            host: Optional[str] = "192.168.60.2",  # default host during installation
            port: Optional[str] = "5432",  # default port during pg installation
            user: Optional[str] = "postgres",  # default user during pg installation
            password: Optional[str] = "postgis",  # default password during pg installation
            dbname: Optional[str] = "real3d",  # default dbname during pg installation
            schema: Optional[str] = None
    ):
        # 配置数据库连接参数并指定schema
        self.connparams = {
            "dbname": dbname,
            "user": user,
            "password": password,
            "host": host,
            "port": port,
            "options": "-c search_path=otherSchema," + schema if schema is not None else None
        }
        self.conn = psycopg2.connect(**self.connparams)
        # 创建一个游标对象
        self.cur = self.conn.cursor()
        # 执行一个查询
        # self.cur.execute("SELECT 省,类型 from \"XZQH3857\";")
        # 获取查询结果
        # rows = self.cur.fetchall()
        # 打印结果
        # for row in rows:
        #     for v in row:
        #         print(v)

    # def execute(self, sql):
    #     # 执行一个查询
    #     self.cur.execute(sql)
    #     # 获取查询结果
    #     return self.cur.fetchall()

    def execute(self, sql, params=None):
        try:
            if params:
                self.cur.execute(sql, params)
            else:
                self.cur.execute(sql)

            # 如果是 SELECT，才 fetch 结果
            if sql.strip().lower().startswith("select"):
                return self.cur.fetchall()
            else:
                self.conn.commit()
                return None
        except Exception as e:
            print(f"SQL执行出错：{e}")
            self.conn.rollback()
            raise

    def close(self):
        # 关闭游标和连接
        self.cur.close()
        self.conn.close()

    # 获取资源目录
    def getZyml(self):
        # self.cur.execute("select t.ywlx as bsm,t.ywlx as name , '' as pbsm,'' as type from (select distinct(ywlx) from t_vector_field  order by ywlx) t union  all select  a.name as bsm, case when a.table_alias is null or a.table_alias = '' then a.name else a.table_alias end as name, a.ywlx as pbsm, a.sjlx as type from t_vector_storage a order by name")
        self.cur.execute(
            "select * from (select t.bsm ,t.name , t.pbsm,'' as type from t_vector_zyml t order by t.sort) t  union  all select  a.name as bsm, case when a.table_alias is null or a.table_alias = '' then a.name else a.table_alias end as name, a.xmlx as pbsm, a.sjlx as type from t_vector_storage a")
        rows = self.cur.fetchall()
        return rows

    def getManagerTables(self, username='admin'):
        sql = f'select t.id "id",name "name", case when t.table_alias != \'\' then  t.table_alias else t.name end as "alias", t.ywlx "ywlx" ' \
              f'from {self.Vector_Storage} t where t.glbm = (select dept_name from sys_dept d where d.dept_id = (select dept_id from sys_user u where u.user_name = \'{username}\' )) and t.sjlx = \'vector\''
        self.cur.execute(sql)
        rows = self.cur.fetchall()
        return rows

    def getVectorZyml(self):
        self.cur.execute(
            "select bsm,name from t_vector_zyml t order by t.sort,t.name")
        rows = self.cur.fetchall()
        return rows

    def dropTable(self, tablename):
        self.cur.execute(
            "delete from t_vector_storage where name = '{}'".format(tablename))
        self.conn.commit()
        self.cur.execute("drop table {}".format(tablename))
        self.conn.commit()

    def addZyml(self, uid, pid, name):
        self.cur.execute(
            "insert into t_vector_zyml (bsm , name , pbsm) values ('{}' , '{}', '{}')".format(uid, name, pid))
        self.conn.commit()

    def renameZyml(self, id, name):
        self.cur.execute("update t_vector_zyml set name = '{}' where bsm = '{}'".format(name, id))
        self.conn.commit()

    def deleteZyml(self, id):
        self.cur.execute(
            "delete from t_vector_zyml where bsm in (WITH RECURSIVE a AS ( SELECT g.bsm, g.pbsm FROM t_vector_zyml g WHERE bsm = '{}' UNION ALL SELECT d.bsm, d.pbsm FROM t_vector_zyml d JOIN a ON a.bsm = d.pbsm ) SELECT bsm FROM a order by a.bsm)".format(
                id))
        self.conn.commit()

    def getResourceAttr(self, tablename):
        self.cur.execute(
            "select t.*, case when t.sjlx = 'vector' then '矢量数据' else '栅格数据' end from t_vector_storage t where name = '{}'".format(
                tablename))
        rows = self.cur.fetchall()
        return rows[0]

    # 获取行政区划
    def getXzqh(self):
        self.cur.execute(
            "select t.id, t.name, case when t.pid = '0' then '' else t.pid end from vector.xzqh t order by t.id")
        rows = self.cur.fetchall()
        return rows

    # 判断数据是否为字符串
    def is_string(self, var):
        return isinstance(var, str)