Mercurial > dive4elements > river
comparison flys-backend/contrib/shpimporter/dgm.py @ 5301:7d29c277000e
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(";") |