Oracle Import by Python XML 예제

파이썬으로 Oracle 데이터베이스 접속해서 XML 파싱 데이터 import 하기

** 참고문서
http://www.oracle.com/technetwork/articles/dsl/python-091105.html
http://www.orafaq.com/wiki/Python
http://stackoverflow.com/questions/1179305/expat-parsing-in-python-3
https://bugs.python.org/msg93782

parser.ParseFile(open(‘sample.xml’, ‘rb’))

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

from xml.parsers import expat
import re
import sys

import json
from collections import namedtuple

import cx_Oracle

class Parser:

    _parser = "";

    def __init__(self, xml_filename, db_cur):
        assert(xml_filename != "")
        self.xml_filename = xml_filename
        self.row_cnt = 0

        self.db_cur = db_cur

        self._parser = expat.ParserCreate()
        self._parser.StartElementHandler = self.start
        self._parser.EndElementHandler = self.end
        self._parser.CharacterDataHandler = self.data

    def parse(self):
        try:
            xml_file = open(self.xml_filename, "r")
        except:
            print "ERROR: Can't open XML file!";
            sys.exit(0)
        else:
            try:
                self._parser.ParseFile( xml_file )
            finally:
                xml_file.close()

    def feed(self, data):
        self._parser.Parse(data, 0)

    def close(self):
        #self._parser.Parse("", True)   # end of data
        del self._parser                # get rid of circular references

    def start(self, tag, attrs):
        if tag == 'item':
            self.row_cnt += 1
        else:
            print "START", repr(tag), attrs,

    def end(self, tag):
        if tag == 'item':
            print "---------------------------------", self.row_cnt

    def data(self, data):
        print "DATA :", data.strip()

if __name__ == '__main__':

    con = cx_Oracle.connect("DB/ID@PW")
    print 'Oracle version: ' + con.version

    cur = con.cursor()

    p = Parser( 'bus_opendata.xml', cur )
    p.parse()
    p.close()

    cur.execute("""
INSERT INTO dev_bis_bus(
dstStationId,orgtStationId,routeColor,routeId,routeLen,routeNm,routeNum,routeSubNm,routeTp,stationCnt,upd,useYn
    )VALUES(
:dstStationId,:orgtStationId,:routeColor,:routeId,:routeLen,:routeNm,:routeNum,:routeSubNm,:routeTp,:stationCnt,:upd,:useYn
    )""",
    {
        'dstStationId': '405000145',
        'orgtStationId': '405000327',
        'routeColor': '2',
        'routeId': '999999999',
        'routeLen': '3957',
        'routeNm': '99-1',
        'routeNum': '99',
        'routeSubNm': '99(시청방면)',
        'routeTp': '13',
        'stationCnt': '9',
        'upd': '1999-01-01 09:55:55',
        'useYn': 'N'
    })

    cur.execute('select * from dev_bis_bus')
    for result in cur:
        print result

    cur.close()
    con.commit()
    con.close()

_