sde for Oracle 与 shp 、oralce spatial 的数据读写（二）
通过sql查询，从oracle spatial查询出空间字段，可以逐条写入到sde空间表中。
在上一博客里，已经完成通过 SQL 插入语句的形式完成，这里改为通过 arcpy函数完成，这么做的优点在于安全。直接通过 SQL 对 SDE 库进行创建、增删操作存在一些风险，虽然该风险是可控的。
直接上代码，第二个测试函数 insert_test_sde_table_pt() 是通过X、Y坐标的形式，插入点；第三个是通过 WKT 的形式，直接插入点；第四个函数，是简化形式。
直接从 oracle spatial 或者 SDE 中查询出以 WKT 字符串表达的几何图形，不需要任何额外操作，即可通过 ‘SHAPE@WKT’ shape令牌插入到目标空间字段。该方法适用于 ArcGIS Desktop 10.1 SP1 以上的版本。
参考：http://pro.arcgis.com/zh-cn/pro-app/arcpy/data-access/insertcursor-class.htm
完整的python脚本：
'''
Created on 2016年7月7日
@author: Tommy
'''
import cx_Oracle
import arcpy
class Oracle(object):
""" oracle db operator """
def __init__(self,userName,password,host,instance):
self._conn = cx_Oracle.connect("%s/%s@%s/%s" % (userName,password,host,instance))
self.cursor = self._conn.cursor()
def queryTitle(self,sql,nameParams={}):
if len(nameParams) > 0 :
self.cursor.execute(sql,nameParams)
else:
self.cursor.execute(sql)
colNames = []
for i in range(0,len(self.cursor.description)):
colNames.append(self.cursor.description[i][0])
return colNames
# query methods
def queryAll(self,sql):
self.cursor.execute(sql)
return self.cursor.fetchall()
def queryOne(self,sql):
self.cursor.execute(sql)
return self.cursor.fetchone()
def queryBy(self,sql,nameParams={}):
if len(nameParams) > 0 :
self.cursor.execute(sql,nameParams)
else:
self.cursor.execute(sql)
return self.cursor.fetchall()
def queryClobBy(self,sql,nameParams={}):
if len(nameParams) > 0 :
self.cursor.execute(sql,nameParams)
else:
self.cursor.execute(sql)
str_list=[]
for i in self.cursor:
text = i[0].read()
str_list.append(text)
return ''.join(str_list)
def insertBatch(self,sql,nameParams=[]):
"""batch insert much rows one time,use location parameter"""
self.cursor.prepare(sql)
self.cursor.executemany(None, nameParams)
self.commit()
def commit(self):
self._conn.commit()
def __del__(self):
if hasattr(self,'cursor'):
self.cursor.close()
if hasattr(self,'_conn'):
self._conn.close()
def test_create_sdo_table_pt():
oraDb = Oracle('sde','sde','localhost','orcl')
cursor = oraDb.cursor
create_table = """
CREATE TABLE test_sp_restaurants_new_point
(
id number,
poi_name NVARCHAR2(65),
location SDO_GEOMETRY -- New column to store locations
)
"""
cursor.execute(create_table)
#sql = "INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)"
sql = """
INSERT INTO test_sp_restaurants_new_point
VALUES
(:1,
:2,
SDO_GEOMETRY(2001, -- SDO_GTYPE attribute: "2" in 2001 specifies dimensionality is 2.
NULL, -- other fields are set to NULL.
SDO_POINT_TYPE -- Specifies the coordinates of the point
(:3, -- first ordinate, i.e., value in longitude dimension
:4, -- second ordinate, i.e., value in latitude dimension
NULL -- third ordinate, if any
),
NULL,
NULL))
"""
M = []
M.append((1, 'PIZZA HUT', -87, -78))
M.append((2, 'PIZZA HUT2', -88, -79))
M.append((3, 'PIZZA HUT3', -89, -80))
M.append((4, 'PIZZA HUT3', -85, -76))
oraDb.insertBatch(sql,M)
cursor.execute("SELECT COUNT(*) FROM test_sp_restaurants_new_point")
print(cursor.fetchone())
print('insert batch ok.')
# cursor.execute("DROP TABLE test_sp_restaurants_new_point PURGE")
def insert_test_sde_table_pt():
oraDb = Oracle('sde','sde','localhost','orcl')
cursor = oraDb.cursor
sql = "select sdo_geometry.get_wkt(location) shape from test_sp_restaurants_new_point t where id = 1"
shpstr = oraDb.queryClobBy(sql)
print shpstr
print shpstr[7:-1]
spls = shpstr[7:-1].split(' ')
print spls
print float(spls[0])
print float(spls[1])
fc = ur'数据库连接\连接到 localhost.sde\SDE.test_sde_table_pt'
SR= arcpy.Describe(fc).spatialReference
print SR
row_values = [(11, 111, '1111', (float(spls[0])+4, float(spls[1])+4))]
# Open an InsertCursor
cursor = arcpy.da.InsertCursor(fc, ['ATR1', 'ATR2', 'ATR', 'SHAPE@XY'])
# Insert new rows that include the county name and a x,y coordinate
# pair that represents the county center
for row in row_values:
cursor.insertRow(row)
# Delete cursor object
del cursor
def insert_test_sde_table_pt_wkt():
oraDb = Oracle('sde','sde','localhost','orcl')
cursor = oraDb.cursor
sql = "select sdo_geometry.get_wkt(location) shape from test_sp_restaurants_new_point t where id = 1"
shpstr = oraDb.queryClobBy(sql)
print shpstr
print shpstr[7:-1]
spls = shpstr[7:-1].split(' ')
print spls
print float(spls[0])
print float(spls[1])
fc = ur'数据库连接\连接到 localhost.sde\SDE.test_sde_table_pt'
SR= arcpy.Describe(fc).spatialReference
print SR
row_values = [(222, 2222, '22222', shpstr)]
# Open an InsertCursor
cursor = arcpy.da.InsertCursor(fc, ['ATR1', 'ATR2', 'ATR', 'SHAPE@WKT'])
# Insert new rows that include the county name and a x,y coordinate
# pair that represents the county center
for row in row_values:
cursor.insertRow(row)
# Delete cursor object
del cursor
def insert_test_sde_table_pt_wkt_test():
fc = ur'数据库连接\连接到 localhost.sde\SDE.test_sde_table_pt'
shpstr = 'POINT (-87.0 -78.0)'
row_values = [(222, 2222, '22222', shpstr)]
# Open an InsertCursor
cursor = arcpy.da.InsertCursor(fc, ['ATR1', 'ATR2', 'ATR', 'SHAPE@WKT'])
# Insert new rows that include the county name and a x,y coordinate
# pair that represents the county center
for row in row_values:
cursor.insertRow(row)
# Delete cursor object
del cursor
# test_create_sdo_table_pt()
# insert_test_sde_table_pt()
# insert_test_sde_table_pt_wkt()
insert_test_sde_table_pt_wkt_test()
转载自：https://blog.csdn.net/firefly2123/article/details/86265763
