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