파이썬으로 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()
_