dive4elements/river: flys-backend/contrib/import-kms.py annotate

annotate flys-backend/contrib/import-kms.py @ 3341:51f037483071

Backend: Strategy to directly transform native SQL results into Maps. flys-backend/trunk@4955 c6561f87-3c4e-4783-a992-168aeb5c3f6f

author	Sascha L. Teichmann <sascha.teichmann@intevation.de>
date	Thu, 12 Jul 2012 14:51:23 +0000
parents	80669241956c
children

rev	line source
162 80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	1 #!/usr/bin/env python
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	2
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	3 import sys
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	4 import logging
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	5 import re
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	6 import os
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	7
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	8 import sqlite3 as db
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	9 import locale
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	10 import codecs
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	11
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	12 from optparse import OptionParser
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	13
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	14 log = logging.getLogger(__name__)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	15 log.setLevel(logging.WARNING)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	16 log.addHandler(logging.StreamHandler(sys.stderr))
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	17
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	18 RANGE = re.compile("([^#])#(.)")
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	19 DEFAULT_DATABASE = "flys.db"
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	20
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	21 SQL_NEXT_ID = "SELECT coalesce(max(id), -1) + 1 FROM %s"
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	22 SQL_SELECT_ID = "SELECT id FROM %s WHERE %s = ?"
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	23 SQL_INSERT_ID = "INSERT INTO %s (id, %s) VALUES (?, ?)"
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	24
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	25 SQL_SELECT_RANGE_ID = """
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	26 SELECT id FROM ranges WHERE river_id = ? AND a = ? AND b = ?
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	27 """
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	28 SQL_INSERT_RANGE_ID = """
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	29 INSERT INTO ranges (id, river_id, a, b) VALUES (?, ?, ?, ?)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	30 """
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	31 SQL_SELECT_ANNOTATION_ID = """
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	32 SELECT id FROM annotations
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	33 WHERE range_id = ? AND attribute_id = ? AND position_id = ?
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	34 """
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	35 SQL_INSERT_ANNOTATION_ID = """
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	36 INSERT INTO annotations (id, range_id, attribute_id, position_id)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	37 VALUES (?, ?, ?, ?)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	38 """
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	39
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	40 def encode(s):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	41 try:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	42 return unicode(s, "latin-1")
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	43 except UnicodeDecodeError:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	44 return unicode.encode(s, locale.getpreferredencoding())
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	45
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	46 class hashabledict(dict):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	47 def __key(self):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	48 return tuple((k, self[k]) for k in sorted(self))
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	49 def __hash__(self):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	50 return hash(self.__key())
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	51 def __eq__(self, other):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	52 return self.__key() == other.__key()
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	53
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	54 def cache(f):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	55 def func(args, *kw):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	56 key = (args, hashabledict(kw))
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	57 try:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	58 return f.__cache__[key]
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	59 except KeyError:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	60 value = f(args, *kw)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	61 f.__cache__[key] = value
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	62 return value
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	63 f.__cache__ = {}
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	64 return func
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	65
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	66 NEXT_IDS = {}
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	67 def next_id(cur, relation):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	68 idx = NEXT_IDS.get(relation)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	69 if idx is None:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	70 cur.execute(SQL_NEXT_ID % relation)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	71 idx = cur.fetchone()[0]
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	72 NEXT_IDS[relation] = idx + 1
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	73 return idx
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	74
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	75 def get_id(cur, relation, attribute, value):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	76 select_stmt = SQL_SELECT_ID % (relation, attribute)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	77 #log.debug(select_stmt)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	78 cur.execute(select_stmt, (value,))
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	79 row = cur.fetchone()
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	80 if row: return row[0]
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	81 idx = next_id(cur, relation)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	82 insert_stmnt = SQL_INSERT_ID % (relation, attribute)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	83 #log.debug(insert_stmnt)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	84 cur.execute(insert_stmnt, (idx, value))
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	85 cur.connection.commit()
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	86 log.debug("insert %s '%s' id: '%d'" % (relation, value, idx))
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	87 return idx
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	88
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	89 #@cache
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	90 def get_river_id(cur, name):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	91 return get_id(cur, "rivers", "name", name)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	92
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	93 #@cache
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	94 def get_attribute_id(cur, value):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	95 return get_id(cur, "attributes", "value", value)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	96
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	97 #@cache
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	98 def get_position_id(cur, value):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	99 return get_id(cur, "positions", "value", value)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	100
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	101 #@cache
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	102 def get_range_id(cur, river_id, a, b):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	103 cur.execute(SQL_SELECT_RANGE_ID, (river_id, a, b))
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	104 row = cur.fetchone()
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	105 if row: return row[0]
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	106 idx = next_id(cur, "ranges")
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	107 cur.execute(SQL_INSERT_RANGE_ID, (idx, river_id, a, b))
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	108 cur.connection.commit()
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	109 return idx
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	110
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	111 #@cache
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	112 def get_annotation_id(cur, range_id, attribute_id, position_id):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	113 cur.execute(SQL_SELECT_ANNOTATION_ID, (
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	114 range_id, attribute_id, position_id))
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	115 row = cur.fetchone()
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	116 if row: return row[0]
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	117 idx = next_id(cur, "annotations")
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	118 cur.execute(SQL_INSERT_ANNOTATION_ID, (
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	119 idx, range_id, attribute_id, position_id))
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	120 cur.connection.commit()
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	121 return idx
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	122
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	123 def files(root, accept=lambda x: True):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	124 if os.path.isfile(root):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	125 if accept(root): yield root
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	126 elif os.path.isdir(root):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	127 stack = [ root ]
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	128 while stack:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	129 cur = stack.pop()
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	130 for f in os.listdir(cur):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	131 p = os.path.join(cur, f)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	132 if os.path.isdir(p):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	133 stack.append(p)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	134 elif os.path.isfile(p) and accept(p):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	135 yield p
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	136
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	137 def feed_km(cur, river_id, km_file):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	138
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	139 log.info("processing: %s" % km_file)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	140
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	141 for line in codecs.open(km_file, "r", "latin-1"):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	142 line = line.strip()
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	143 if not line or line.startswith('*'):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	144 continue
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	145 parts = [x.strip() for x in line.split(';')]
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	146 if len(parts) < 3:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	147 log.error("cannot process: '%s'" % line)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	148 continue
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	149 m = RANGE.match(parts[2])
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	150 try:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	151 if m:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	152 x = [float(x.replace(",", ".")) for x in m.groups()]
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	153 a, b = min(x), max(x)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	154 if a == b: b = None
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	155 else:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	156 a, b = float(parts[2].replace(",", ".")), None
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	157 except ValueError:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	158 log.error("cannot process: '%s'" % line)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	159 continue
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	160
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	161 attribute = parts[0]
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	162 position = parts[1]
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	163 attribute_id = get_attribute_id(cur, attribute) if attribute else None
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	164 position_id = get_position_id(cur, position) if position else None
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	165
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	166 range_id = get_range_id(cur, river_id, a, b)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	167
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	168 get_annotation_id(cur, range_id, attribute_id, position_id)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	169
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	170 def main():
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	171
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	172 usage = "usage: %prog [options] river km-file ..."
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	173 parser = OptionParser(usage=usage)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	174 parser.add_option(
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	175 "-v", "--verbose", action="store_true",
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	176 dest="verbose",
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	177 help="verbose output")
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	178 parser.add_option(
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	179 "-r", "--recursive", action="store_true",
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	180 dest="recursive", default=False,
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	181 help="recursive")
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	182 parser.add_option(
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	183 "-d", "--database", action="store",
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	184 dest="database",
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	185 help="database to connect with",
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	186 default=DEFAULT_DATABASE)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	187
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	188 options, args = parser.parse_args()
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	189
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	190 if options.verbose:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	191 log.setLevel(logging.INFO)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	192
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	193 if len(args) < 1:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	194 log.error("missing river argument")
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	195 sys.exit(1)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	196
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	197 river = unicode(args[0], locale.getpreferredencoding())
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	198
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	199 with db.connect(options.database) as con:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	200 cur = con.cursor()
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	201 river_id = get_river_id(cur, river)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	202
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	203 for arg in args[1:]:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	204 if options.recursive:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	205 for km_file in files(
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	206 arg, lambda x: x.lower().endswith(".km")):
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	207 feed_km(cur, river_id, km_file)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	208 else:
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	209 feed_km(cur, river_id, arg)
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	210
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	211
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	212 if __name__ == '__main__':
80669241956c Initial database import scripts. Not finished, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	213 main()

Mercurial > dive4elements > river

annotate flys-backend/contrib/import-kms.py @ 3341:51f037483071