annotate flys-backend/contrib/import-kms.py @ 1243:3ebc0a7d6793

Removed all tables for different datasets at HYdrologie/Hydr.Grenzen/Linien/* flys-backend/trunk@2716 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Hans Plum <hans.plum@intevation.de>
date Tue, 13 Sep 2011 10:34:44 +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()

http://dive4elements.wald.intevation.org