Oracle Spatial + Python

(1)、下载cx_Oracle 编译包

如果要使用编译好的包, 选择什么版本就能讲究了. 如果你的机器已经安装好Oracle客户端了, 先要看一下看操作系统的是x64还是i386架构, 再看机器上安装的Oracle客户端的版本(10g还是11g, 64bit还是32bit), 最后再看你的python版本(2.7还是2.6, 64bit还是32bit). cx_Oracle版本一定要选对. 否则即使安装完也无法使用。

(2)、下载cx_Oracle并按照下载cx_Oracle

http://sourceforge.net/projects/cx-oracle/files/  选择一个版本，比如5.1.2，(这里选择源码安装)，下载cx_Oracle-5.1.2.tar.gz

安装，请参考如何安装第三方库：http://blog.csdn.net/cdl2008sky/article/details/10382663

(4)、建立连接，查询及查询空间数据转换，变量绑定，批量删除。

#!/usr/bin/python
# -*- coding: utf-8 -*-

'''
Created on 2013-9-5

@author: chenll
'''
import os,sys
import cx_Oracle
reload(sys)
sys.setdefaultencoding('utf-8')

user = "map13spring"
password = "123456"
url = "192.168.9.104:1521/orcl"
con = None 

#获取数据库连接
def getConn():
    global con;
    con = cx_Oracle.Connection(user, password, url);
    return con

#关闭数据库连接
def closeConn():
    if con != None:
         con.close();
         
#查询数据，处理Oracle Spatial 的GEOLOC字段需要做转换         
def query():
    global con;
    cursor = con.cursor()
    #注意：Python 不能直接解析GEOLOC对象， 只能把cx_Oracle.OBJECT对象转换为 XML或者GML
    cursor.execute('SELECT ID,KIND,WIDTH,PATHNAME,TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(geoloc)) GEOLOC FROM rbeijing where PATHNAME is not null and rownum<10')
    fields = [];
    for i in cursor.description: #数据字段及类型
        fields.append(i[0]);
    #所有结果值
    list = [];
    for row in cursor:
        i = 0;
         #<key,value> <字段，值>
        fieldMap = {};
        for field in fields:
            fieldMap[field] = row[i]
            list.append(fieldMap);
            i = i+1;
            
    #根据字段名取值
    for item in list:
        print item['GEOLOC']
        print item['ID']
        pathName = item['PATHNAME']
        if pathName != None:
            print pathName.decode('gbk')
        
    cursor.close();
   
#绑定变量的查询
def queryPara():
    global con;
    cursor = con.cursor()
    nameParames = {'id':195699}
    cursor.execute('SELECT ID,KIND,WIDTH,PATHNAME,TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(geoloc)) GEOLOC FROM rbeijing where ID=:id',nameParames);
    #检查目前已指定的绑定变量
    print cursor.bindnames();
    for row in cursor:
        print row
    #准备语句,准备好的语句可执行任意次
    cursor.prepare('SELECT ID,KIND,WIDTH,PATHNAME,TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(geoloc)) GEOLOC FROM rbeijing where ID=:id')
    row = cursor.execute(None, {'id':195699})
    row = cursor.execute(None, {'id':195663})
    row = cursor.execute(None, {'id':195657})
    cursor.close();
    
#插入多行 executemany
def inserManyRow():
    global con;
    cursor = con.cursor()
    #存放需要插入到表中的值
    values = []
    values.append((195657,'中鼎路'));
    values.append((195663,'S322/黄马路/南六环辅路'));
    values.append((195699,'二干路'));
    cursor.prepare("INSERT INTO rbeijing(id,PATHNAME) VALUES (:1,:2)");
    cursor.executemany(None, values);
    cursor.close();
    con.commit();
    
def main():
    con = getConn();
    query();
    queryPara();
    closeConn();
    
if __name__ ==  '__main__':
    main();

ps:字段类型对应关系

转载自：https://blog.csdn.net/cdl2008sky/article/details/11140957