Mercurial > dive4elements > river

comparison flys-backend/contrib/shpimporter/dgm.py @ 5301:7d29c277000e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Do not explicitly set id for ranges and time_intervals
author Andre Heinecke <aheinecke@intevation.de>
date Thu, 14 Mar 2013 15:01:36 +0100
parents 9245f4b434b3
children 292f00337015
comparison
equal deleted inserted replaced
5300:a82b621d87b0 5301:7d29c277000e
32 ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)" 32 ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)"
33 SQL_SELECT_TIME_ID = """ 33 SQL_SELECT_TIME_ID = """
34 SELECT id FROM time_intervals WHERE start_time = %s AND stop_time = %s 34 SELECT id FROM time_intervals WHERE start_time = %s AND stop_time = %s
35 """ 35 """
36 SQL_INSERT_TIME_ID = """ 36 SQL_INSERT_TIME_ID = """
37 INSERT INTO time_intervals (id, start_time, stop_time) VALUES (%s, %s, %s) 37 INSERT INTO time_intervals (start_time, stop_time) VALUES (%s, %s)
38 """ 38 """
39 SQL_SELECT_TIME_ID_ORA = """ 39 SQL_SELECT_TIME_ID_ORA = """
40 SELECT id FROM time_intervals WHERE start_time = :s AND stop_time = :s 40 SELECT id FROM time_intervals WHERE start_time = :s AND stop_time = :s
41 """ 41 """
42 SQL_INSERT_TIME_ID_ORA = """ 42 SQL_INSERT_TIME_ID_ORA = """
43 INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:s, :s, :s) 43 INSERT INTO time_intervals (start_time, stop_time) VALUES (:s, :s)
44 """ 44 """
45 SQL_SELECT_RANGE_ID = """ 45 SQL_SELECT_RANGE_ID = """
46 SELECT id FROM ranges WHERE river_id = %s AND a = %s AND b = %s 46 SELECT id FROM ranges WHERE river_id = %s AND a = %s AND b = %s
47 """ 47 """
48 SQL_INSERT_RANGE_ID = """ 48 SQL_INSERT_RANGE_ID = """
49 INSERT INTO ranges (id, river_id, a, b) VALUES (%s, %s, %s, %s) 49 INSERT INTO ranges (river_id, a, b) VALUES (%s, %s, %s)
50 """ 50 """
51 SQL_SELECT_RANGE_ID_ORA = """ 51 SQL_SELECT_RANGE_ID_ORA = """
52 SELECT id FROM ranges WHERE river_id = :s AND a = :s AND b = :s 52 SELECT id FROM ranges WHERE river_id = :s AND a = :s AND b = :s
53 """ 53 """
54 SQL_INSERT_RANGE_ID_ORA = """ 54 SQL_INSERT_RANGE_ID_ORA = """
55 INSERT INTO ranges (id, river_id, a, b) VALUES (:s, :s, :s, :s) 55 INSERT INTO ranges (river_id, a, b) VALUES (:s, :s, :s)
56 """ 56 """
57 SQL_NEXT_ID = "SELECT coalesce(max(id), -1) + 1 FROM %s" 57 SQL_NEW_ID = "SELECT max(id) FROM %s"
58 58
59 59
60 NEXT_IDS = {} 60 NEXT_IDS = {}
61 def next_id(cur, relation): 61 def next_id(cur, relation):
62 idx = NEXT_IDS.get(relation) 62 idx = NEXT_IDS.get(relation)
63 if idx is None: 63 if idx is None:
64 cur.execute(SQL_NEXT_ID % relation) 64 cur.execute(SQL_NEW_ID % relation)
65 idx = cur.fetchone()[0] 65 idx = cur.fetchone()[0]
66 NEXT_IDS[relation] = idx + 1 66 NEXT_IDS[relation] = idx + 1
67 return idx 67 return idx
68 68
69 def get_range_id(cur, river_id, a, b, oracle): 69 def get_range_id(cur, river_id, a, b, oracle):
71 cur.execute(SQL_SELECT_RANGE_ID_ORA, (river_id, a, b)) 71 cur.execute(SQL_SELECT_RANGE_ID_ORA, (river_id, a, b))
72 else: 72 else:
73 cur.execute(SQL_SELECT_RANGE_ID, (river_id, a, b)) 73 cur.execute(SQL_SELECT_RANGE_ID, (river_id, a, b))
74 row = cur.fetchone() 74 row = cur.fetchone()
75 if row: return row[0] 75 if row: return row[0]
76 idx = next_id(cur, "ranges")
77 if oracle: 76 if oracle:
78 cur.execute(SQL_INSERT_RANGE_ID_ORA, (idx, river_id, a, b)) 77 cur.execute(SQL_INSERT_RANGE_ID_ORA, (river_id, a, b))
79 else: 78 else:
80 cur.execute(SQL_INSERT_RANGE_ID, (idx, river_id, a, b)) 79 cur.execute(SQL_INSERT_RANGE_ID, (river_id, a, b))
81 cur.connection.commit() 80 cur.connection.commit()
82 return idx 81 return next_id(cur, "ranges")
83 82
84 def get_time_interval_id(cur, a, b, oracle): 83 def get_time_interval_id(cur, a, b, oracle):
85 if not a or not b: 84 if not a or not b:
86 return None 85 return None
87 if oracle: 86 if oracle:
88 cur.execute(SQL_SELECT_TIME_ID_ORA, (a, b)) 87 cur.execute(SQL_SELECT_TIME_ID_ORA, (a, b))
89 else: 88 else:
90 cur.execute(SQL_SELECT_TIME_ID, (a, b)) 89 cur.execute(SQL_SELECT_TIME_ID, (a, b))
91 row = cur.fetchone() 90 row = cur.fetchone()
92 if row: return row[0] 91 if row: return row[0]
93 idx = next_id(cur, "time_intervals")
94 if oracle: 92 if oracle:
95 cur.execute(SQL_INSERT_TIME_ID_ORA, (idx, a, b)) 93 cur.execute(SQL_INSERT_TIME_ID_ORA, (a, b))
96 else: 94 else:
97 cur.execute(SQL_INSERT_TIME_ID, (idx, a, b)) 95 cur.execute(SQL_INSERT_TIME_ID, (a, b))
98 cur.connection.commit() 96 cur.connection.commit()
99 return idx 97 return next_id(cur, "time_intervals")
100 98
101 def insertRiverDgm(dbconn, dgmfile, river_name, dry_run, oracle): 99 def insertRiverDgm(dbconn, dgmfile, river_name, dry_run, oracle):
102 with codecs.open(dgmfile, "r", "latin1") as csvfile: 100 with codecs.open(dgmfile, "r", "latin1") as csvfile:
103 firstline = csvfile.readline() 101 firstline = csvfile.readline()
104 names = firstline.split(";") 102 names = firstline.split(";")
http://dive4elements.wald.intevation.org