oracle 数据连接

# -*- coding: UTF-8 -*-
import codecs,pdb
import time
import cx_Oracle
import csv
import sys
import urllib
import os
from datetime import datetime
from datetime import timedelta
reload(sys)  
sys.setdefaultencoding('utf-8')  
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'   
now = datetime.now()

name= now.strftime('%Y_%m_%d')

# pdb.set_trace()
def connectDB(dbname='dave'):
    if dbname == 'dave':
        connstr = 'thunder/thunder@:1521/thunder'

    db = cx_Oracle.connect(connstr)

    return db


def sqlSelect(sql, db):
    # include:select

    cr = db.cursor()

    cr.execute(sql)

    rs = cr.fetchall()

    cr.close()

    return rs


def sqlDML(sql, db):
    # include: insert,update,delete

    cr = db.cursor()

    cr.execute(sql)

    cr.close()

    db.commit()


def sqlDML2(sql, params, db):
    # execute dml with parameters

    cr = db.cursor()

    cr.execute(sql, params)

    cr.close()

    db.commit()


def sqlDDL(sql, db):
    # include: create

    cr = db.cursor()

    cr.execute(sql)

    cr.close()


if __name__ == '__main__':
    db = connectDB()
    sql = 'select DATETIME,LATITUDE,LONGITUDE,INTENS,SLOPE,ERROR,LOCATION,PROVINCE,DISTRICT,COUNTRY,MINISECOND from (select * from THUNDER2015 order by DATETIME desc)  aaa where rownum<=1000'




    rs = sqlSelect(sql, db)
    csvfile = open(r"E:\smart_vips\shandian"+os.sep+name+'.txt', 'wb')
    csvfile.write(codecs.BOM_UTF8)
    writer = csv.writer(csvfile)
    # writer.writerows(rs)
    i = 0
    for xx in rs:
        x = list(xx)
        i = i + 1
        x.insert(0, i)
        # 编号
        # x[0] = ''
        timeTemp = x[1]
        # 时间日期
        x[1]=x[1].strftime("%Y-%m-%d")
        # 时间分钟数
        x.insert(2, i)
        x[2]=timeTemp.strftime("%H:%M:%S") + '.' + str(x[12])
        # 纬度=
        x[3] = '纬度=' + str(x[3])
        # 经度=
        x[4] = '经度=' + str(x[4])
        # 强度=
        x[5] = '强度=' + str(x[5])
        # 陡度=
        x[6] = '陡度=' + str(x[6])
        # 误差=
        x[7] = '误差=' + str(x[7])
        # 定位方式=
        x[8] = '定位方式=' + str(x[8])
        # 省=
        x[9] = '省=' + str(x[9])
        # 市=
        x[10] = '市=' + str(x[10])
        # 县=
        x[11] = '县=' + str(x[11])
        # print x
        x.pop()
        writer.writerow(x)
    csvfile.close()
    # print "This is a test python program,write by tianlesoftware!\n"
    #
    # os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
    #
    # # connect to database:
    #
    # db = connectDB()
    #
    # # create a table:
    #
    # sql = 'create table dave(id number,name varchar2(20),phone number)'
    #
    # sqlDDL(sql, db)
    #
    # # insert data to table dave:
    #
    # sql = 'insert into dave values(1,\'tianlesoftware\',13888888888)'
    #
    # sqlDML(sql, db)
    #
    # dt = [{'id': 2, 'name': 'dave', 'phone': 138888888888},
    #
    #       {'id': 3, 'name': 'Oracle', 'phone': 13888888888},
    #
    #       {'id': 4, 'name': 'anqing', 'phone': 13888888888}]
    #
    # sql = 'insert into dave values(:id,:name,:phone)'
    #
    # for x in dt:
    #     sqlDML2(sql, x, db)
    #
    # # select the result:
    #
    # print "this is the first time select the data from dave"
    #
    # sql = 'select * from dave'
    #
    # rs = sqlSelect(sql, db)
    #
    # for x in rs:
    #     print x
    #
    # # update data where id=1,change the name to anhui
    #
    # sql = 'update dave set name=\'anhui\' where id=1'
    #
    # sqlDML(sql, db)
    #
    # # select again:
    #
    # print "\n change the nanme to anhui where id equal 1,and select the result"
    #
    # sql = 'select * from dave'
    #
    # rs = sqlSelect(sql, db)
    #
    # for x in rs:
    #     print x
    #
    # # delete data where id=3
    #
    # sql = 'delete from dave where id=3'
    #
    # sqlDML(sql, db)
    #
    # # select again:
    #
    # print "\n delete the data where id equal 3 and select the result"
    #
    # sql = 'select * from dave'
    #
    # rs = sqlSelect(sql, db)
    #
    # for x in rs:
    #     print x
    #
    # db.close()