#!/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();