1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
#!/usr/bin/env python3
############################################################################
#
# MODULE: v.db.dropcolumn
# AUTHOR(S): Markus Neteler
# Converted to Python by Glynn Clements
# PURPOSE: interface to db.execute to drop a column from the
# attribute table connected to a given vector map
# - Based on v.db.addcolumn
# - with special trick for SQLite
# COPYRIGHT: (C) 2007 by the GRASS Development Team
#
# This program is free software under the GNU General Public
# License (>=v2). Read the file COPYING that comes with GRASS
# for details.
#
#############################################################################
# %module
# % description: Drops a column from the attribute table connected to a given vector map.
# % keyword: vector
# % keyword: attribute table
# % keyword: database
# %end
# %option G_OPT_V_MAP
# % key: map
# %end
# %option G_OPT_V_FIELD
# %end
# %option G_OPT_DB_COLUMNS
# % description: Name of attribute column(s) to drop
# % required: yes
# %end
import string
import grass.script as grass
from grass.exceptions import CalledModuleError
def main():
map = options["map"]
layer = options["layer"]
columns = options["columns"].split(",")
mapset = grass.gisenv()["MAPSET"]
# does map exist in CURRENT mapset?
if not grass.find_file(map, element="vector", mapset=mapset)["file"]:
grass.fatal(_("Vector map <%s> not found in current mapset") % map)
f = grass.vector_layer_db(map, layer)
table = f["table"]
keycol = f["key"]
database = f["database"]
driver = f["driver"]
if not table:
grass.fatal(
_(
"There is no table connected to the input vector map. "
"Unable to delete any column. Exiting."
)
)
if keycol in columns:
grass.fatal(
_(
"Unable to delete <%s> column as it is needed to keep table <%s> "
"connected to the input vector map <%s>"
)
% (keycol, table, map)
)
for column in columns:
if column not in grass.vector_columns(map, layer):
grass.warning(
_("Column <%s> not found in table <%s>. Skipped") % (column, table)
)
continue
if driver == "sqlite":
# echo "Using special trick for SQLite"
# https://www.sqlite.org/faq.html#q11
colnames = []
coltypes = []
for f in grass.db_describe(table, database=database, driver=driver)["cols"]:
if f[0] == column:
continue
colnames.append(f[0])
# see db_sqltype_name() for type names
if f[1] == "CHARACTER":
# preserve field length for sql type "CHARACTER"
coltypes.append(f'"{f[0]}" {f[1]}({f[2]})')
else:
coltypes.append(f'"{f[0]}" {f[1]}')
colnames = ", ".join([f'"{col}"' for col in colnames])
coltypes = ", ".join(coltypes)
cmds = [
"BEGIN TRANSACTION",
"CREATE TEMPORARY TABLE ${table}_backup (${coldef})",
"INSERT INTO ${table}_backup SELECT ${colnames} FROM ${table}",
"DROP TABLE ${table}",
"CREATE TABLE ${table}(${coldef})",
"INSERT INTO ${table} SELECT ${colnames} FROM ${table}_backup",
"CREATE UNIQUE INDEX ${table}_cat ON ${table} (${keycol} )",
"DROP TABLE ${table}_backup",
"COMMIT",
]
tmpl = string.Template(";\n".join(cmds))
sql = tmpl.substitute(
table=table, coldef=coltypes, colnames=colnames, keycol=keycol
)
else:
sql = f'ALTER TABLE {table} DROP COLUMN "{column}"'
try:
grass.write_command(
"db.execute", input="-", database=database, driver=driver, stdin=sql
)
except CalledModuleError:
grass.fatal(_("Deleting column failed"))
# write cmd history:
grass.vector_history(map)
if __name__ == "__main__":
options, flags = grass.parser()
main()
|
