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
138
139
140
141
142
143
144
145
146
147
148
149
|
#!/usr/bin/env python3
############################################################################
#
# MODULE: db.dropcolumn
# AUTHOR(S): Markus Neteler
# Converted to Python by Glynn Clements
# PURPOSE: Interface to db.execute to drop a column from an
# attribute table
# - with special trick for SQLite
# COPYRIGHT: (C) 2007, 2012 by Markus Neteler and 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 selected attribute table.
# % keyword: database
# % keyword: attribute table
# %End
# %flag
# % key: f
# % description: Force removal (required for actual deletion of files)
# %end
# %option G_OPT_DB_TABLE
# % required : yes
# %end
# %option G_OPT_DB_COLUMN
# % required : yes
# %end
# %option G_OPT_DB_DATABASE
# %end
# %option G_OPT_DB_DRIVER
# % options: dbf,odbc,ogr,sqlite,pg
# %end
import sys
import string
from grass.exceptions import CalledModuleError
import grass.script as gscript
def main():
table = options["table"]
column = options["column"]
database = options["database"]
driver = options["driver"]
force = flags["f"]
# check if DB parameters are set, and if not set them.
gscript.run_command("db.connect", flags="c")
if not database or not driver:
kv = gscript.db_connection()
if not database:
database = kv["database"]
if not driver:
driver = kv["driver"]
# schema needed for PG?
if force:
gscript.message(_("Forcing ..."))
if column == "cat":
gscript.warning(
_(
"Deleting <%s> column which may be needed to keep "
"table connected to a vector map"
)
% column
)
cols = [
f[0]
for f in gscript.db_describe(table, database=database, driver=driver)["cols"]
]
if column not in cols:
gscript.fatal(_("Column <%s> not found in table") % column)
if not force:
gscript.message(_("Column <%s> would be deleted.") % column)
gscript.message("")
gscript.message(
_("You must use the force flag (-f) to actually " "remove it. Exiting.")
)
return 0
if driver == "sqlite":
sqlite3_version = gscript.read_command(
"db.select",
sql="SELECT sqlite_version();",
flags="c",
database=database,
driver=driver,
).split(".")[0:2]
if [int(i) for i in sqlite3_version] >= [int(i) for i in "3.35".split(".")]:
sql = "ALTER TABLE %s DROP COLUMN %s" % (table, column)
if column == "cat":
sql = "DROP INDEX %s_%s; %s" % (table, column, sql)
else:
# for older sqlite3 versions, use old way to remove column
colnames = []
coltypes = []
for f in gscript.db_describe(table)["cols"]:
if f[0] != column:
colnames.append(f[0])
coltypes.append("%s %s" % (f[0], f[1]))
colnames = ", ".join(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",
"DROP TABLE ${table}_backup",
"COMMIT",
]
tmpl = string.Template(";\n".join(cmds))
sql = tmpl.substitute(table=table, coldef=coltypes, colnames=colnames)
else:
sql = "ALTER TABLE %s DROP COLUMN %s" % (table, column)
try:
gscript.write_command(
"db.execute", input="-", database=database, driver=driver, stdin=sql
)
except CalledModuleError:
gscript.fatal(_("Cannot continue (problem deleting column)"))
return 0
if __name__ == "__main__":
options, flags = gscript.parser()
sys.exit(main())
|
