【基于arcpy读取矢量数据写入PostgreSQL】

class DataSdeToPostgerSql():
    # 读取矢量数据,并写入pg库
    def __read_layer(self,in_layer,fields,lyr_name):
        conn = psycopg2.connect(host=self.db['host'], user=self.db['user'], password=self.db['password'],
                                database=self.db['database'], port=self.db['port'])
        cur = conn.cursor()
        datalist = []
        totalCount = int(str(arcpy.GetCount_management(in_layer)))
        spatial_ref = arcpy.Describe(in_layer).spatialReference
        fields_copy = fields
        fid = 0
        with arcpy.da.SearchCursor(in_layer, fields) as cursor:

            for row in cursor:
                sou = []
                for r in row:
                    if r is None:
                        r = 'null'
                    sou.append(r)
                sou[-1] = "GeomFromEWKT('SRID={};{}')".format(spatial_ref.PCSCode, sou[-1])
                sou = tuple(sou)
                datalist.append(sou)
                sou_value = self.__combination_sql(sou)

                fields_copy[-1] = 'SHAPE'
                columns_str = ', '.join(fields_copy)
                ins_sql = "INSERT INTO {} ({}) VALUES({})".format(lyr_name, columns_str, sou_value)
                # print(ins_sql)
                cur.execute(ins_sql)
                fid += 1
                if fid % 500 == 0:
                    conn.commit()
                    msg = '【{}[{}/{}]】'.format(lyr_name, fid, totalCount)
                    logHelp.writelog(self.rid, msg)
                    # break

        # fields_copy[-1] = 'SHAPE'
        # multiples = [i for i in fields_copy if i == 'SHAPE@WKT' is 'SHAPE']
        # placeholders = ', '.join(['%s'] * len(fields_copy))
        # columns_str = ', '.join(fields_copy)
        # # fields_copy = tuple(fields_copy)
        # # cur.copy_from(f, 'tb_user', columns=fields_copy)
        # ins_sql = "INSERT INTO {} ({}) VALUES ({})".format(lyr_name, columns_str,placeholders)
        # cur.executemany(ins_sql, datalist)
        # pyex.execute_values(cur, ins_sql, datalist, page_size=100000)
        conn.commit()
        msg = '【{}[{}/{}]】'.format(lyr_name, fid, totalCount)
        logHelp.writelog(self.rid, msg)
        cur.close()
        conn.close()

    # 组合拼接sql语句
    def __combination_sql(self,sou):
        sou_value = ','

        for variate in sou:
            if 'null' in str(variate):
                sou_value += ',{}'.format(variate)
            elif isinstance(variate, int):
                sou_value += ',{}'.format(variate)
            elif isinstance(variate, unicode):
                sou_value += ",'{}'".format(variate)
            elif 'GeomFromEWKT' in str(variate):
                sou_value += ',{}'.format(variate)
            elif isinstance(variate, str):
                sou_value += ",'{}'".format(variate)
            elif isinstance(variate, float):
                sou_value += ',{}'.format(variate)
            elif isinstance(variate, datetime.datetime):  # type(variate) in ['datetime.datetime']:
                sou_value += ",'{}'".format(variate)
            else:
                sou_value += ',{}'.format(variate)
        sou_value = sou_value.replace(',,', '')
        return sou_value

    def typeof(self,variate):
        type = None
        if isinstance(variate, int):
            type = "int"
        elif isinstance(variate, str):
            type = "str"
        elif isinstance(variate, float):
            type = "float"
        elif isinstance(variate, list):
            type = "list"
        elif isinstance(variate, tuple):
            type = "tuple"
        elif isinstance(variate, dict):
            type = "dict"
        elif isinstance(variate, set):
            type = "set"
        return type
    # 判断pg库有无该图层，有则删除创建，无则创建
    def __creat_layer(self,layer_name):
        if self.__del_layer(layer_name):
            try:
                msg = '获取图层数据结构'
                logHelp.writelog(self.rid, msg)
                additional_sql = '('
                for oField in self.all_fields:
                    fid = oField['Type'].lower()
                    if oField['Type'].lower() in ['string']:
                        if oField['length'] == 0 or oField['length'] >240:
                            additional_sql += '{} text,'.format(oField['Field'])
                        else:
                            additional_sql += '{} varchar({}),'.format(oField['Field'], oField['length'])
                    elif oField['Type'].lower() in ['smallinteger']:
                        additional_sql += '{} integer,'.format(oField['Field'])
                    elif oField['Type'].lower() in ['double']:
                        additional_sql += '{} numeric,'.format(oField['Field'])
                    elif oField['Type'].lower() in ['datetime','date']:
                        additional_sql += '{} date,'.format(oField['Field'])
                    elif oField['Type'].lower() in ['geometry']:
                        additional_sql += 'SHAPE geometry,'
                    else:
                        additional_sql += '{} {},'.format(oField['Field'], oField['Type'])
                additional_sql += ',);'

                msg = '数据结构获取成功'
                print(msg)
                logHelp.writelog(self.rid, msg)

                additional_sql = additional_sql.replace(',,','')
                msg = 'PostgreSql数据库创建表【{}】开始'.format(layer_name)
                print(msg)
                logHelp.writelog(self.rid, msg)
                ct_sql = 'CREATE TABLE {} {}'.format(layer_name, additional_sql)
                logHelp.writelog(self.rid, ct_sql)
                print(ct_sql)
                conn = psycopg2.connect(host=self.db['host'], user=self.db['user'], password=self.db['password'],
                                        database=self.db['database'], port=self.db['port'])
                cur = conn.cursor()
                cur.execute(ct_sql)
                conn.commit()
                cur.close()
                conn.close()
                msg = 'PostgreSql数据库创建表【{}】成功'.format(layer_name)
                print(msg)
                logHelp.writelog(self.rid, msg)
                m_falg = True

            except Exception as err:
                m_falg = False
                msg = 'PostgreSql数据库创建表【{}】失败'.format(layer_name)
                print(msg)
                logHelp.writelog(self.rid, msg)
                logHelp.writelog(self.rid, str(err))
                print(str(err))

        else:
            m_falg = False
        return m_falg

    # 浮点型,Float
    # 长整型,Long
    # 日期型,Date
    # 文本型,Text
    # 短整型,Short
    # 双精度,Double
    def esriFieldType(self, field_type):
        type_value = ''
        '''
        esriFieldTypeSmallInteger = 0,
        esriFieldTypeInteger = 1,
        esriFieldTypeSingle = 2,
        esriFieldTypeDouble = 3,
        esriFieldTypeString = 4,
        esriFieldTypeDate = 5,
        esriFieldTypeOID = 6,
        esriFieldTypeGeometry = 7,
        esriFieldTypeBlob = 8,
        esriFieldTypeRaster = 9,
        esriFieldTypeGUID = 10,
        esriFieldTypeGlobalID = 11,
        esriFieldTypeXML = 12
        '''
        if field_type == 0:
            type_value = 'Short'
        elif field_type == 1:
            type_value = 'Long'
        elif field_type == 2:
            type_value = 'Float'
        elif field_type == 3:
            type_value = 'Double'
        elif field_type == 4:
            type_value = 'Text'
        elif field_type == 5:
            type_value = 'Date'
        elif field_type == 6:
            type_value = 'OID'
        elif field_type == 7:
            type_value = 'Geometry'
        elif field_type == 8:
            type_value = 'Blob'
        elif field_type == 9:
            type_value = 'Raster'
        elif field_type == 10:
            type_value = 'GUID'
        elif field_type == 11:
            type_value = 'GlobalID'
        elif field_type == 12:
            type_value = 'XML'

        return type_value

    # 删除图层，
    def __del_layer(self,layer_name):
        try:
            select_sql = "select count(*) from pg_class where relname = '{}';".format(layer_name.lower())

            conn = psycopg2.connect(host=self.db['host'],
                                    user=self.db['user'],
                                    password=self.db['password'],
                                    database=self.db['database'],
                                    port=self.db['port'])
            cur = conn.cursor()
            cur.execute(select_sql)
            rows = cur.fetchall()
            m_count = rows[0][0]
            if int(m_count) == 1:
                drop_sql = 'DROP TABLE {};'.format(layer_name)
                cur.execute(drop_sql)
                conn.commit()
            cur.close()
            conn.close()
            m_falg = True
        except Exception as err:
            m_falg = False
            print(str(err))

        return m_falg

    # 获取字段，输入图层
    def __GetFields(self, in_features):
        flds_out = []
        out_fields = []
        flds = arcpy.ListFields(in_features)
        for fld in flds:
            if not fld.type in ['OID'] and not fld.name.upper() in ['SHAPE.AREA', 'SHAPE.LEN', 'SHAPE_AREA',
                                                            'SHAPE_LENGTH','SHAPE']:
                m_field = {
                    'Field': fld.name.upper(),
                    'Alias': fld.aliasName,
                    'Type': fld.type,
                    'Editable': fld.editable,
                    'Required': fld.required,
                    'length': fld.length,
                    'Scale': fld.scale,
                    'Precision': fld.precision
                }
                flds_out.append(m_field)
        # for field in flds_out:
        #     if field['Field'] == 'SHAPE':
        #         field['Field'] = field['Field']+ "@WKT"
        #     out_fields.append(field)

        m_field = {
            'Field': 'SHAPE@WKT'.upper(),
            'Alias': '',
            'Type': 'Geometry',
            'Editable': '',
            'Required': '',
            'length': 0,
            'Scale': 0,
            'Precision': 0
        }
        flds_out.append(m_field)
        return flds_out

    # 对外开放调用,传入图层，pg连接参数 'postgis',#
    def main(self, in_table, db, rid,out_table):
        self.db = {
            'host': db['ip'],
            'user': db['user'],
            'password': db['pwd'],
            'database': db['sid'],
            'port': int(db['port'])
        }
        # in_table = 'D:/Data/Workspace/DLTB_441324.gdb/G2019XZDCQHAORP9GQK'
        self.rid = rid
        lyr_name = out_table.split('/')[-1]
        self.all_fields = self.__GetFields(in_table)
        if self.__creat_layer(lyr_name):
            Search_fields = []
            for i in self.all_fields:
                Search_fields.append(i['Field'])
            msg = '解析矢量数据，写入pg数据库。'
            logHelp.writelog(self.rid, msg)
            tim_start = datetime.datetime.now()
            self.__read_layer(in_table,Search_fields,lyr_name)
            tim_end = datetime.datetime.now()
            tim_dif = tim_end - tim_start
            msg = '写入pg数据库完成，耗时：{}'.format(tim_dif)
            logHelp.writelog(self.rid, msg)

        print (1)


if __name__ == '__main__':
    db = {
        'ip': '192.xxx.xxx.xx',
        'user': 'xxx',
        'pwd': 'xxx',
        'sid': 'xxx',
        'port': '5432'
    }
    DataSdeToPostgerSql().main('d:/data/127.0.0.1.sde/input_layer',db, '123', 'new_table')