1#!/usr/local/bin/python3.8 2 3############################################################################ 4# 5# MODULE: v.db.join 6# AUTHOR(S): Markus Neteler 7# Converted to Python by Glynn Clements 8# PURPOSE: Join a table to a map table 9# COPYRIGHT: (C) 2007-2009 by Markus Neteler and the GRASS Development Team 10# 11# This program is free software under the GNU General Public 12# License (>=v2). Read the file COPYING that comes with GRASS 13# for details. 14# 15############################################################################# 16 17#%module 18#% description: Joins a database table to a vector map table. 19#% keyword: vector 20#% keyword: attribute table 21#% keyword: database 22#%end 23 24#%option G_OPT_V_MAP 25#% description: Vector map to which to join other table 26#% guidependency: layer,column 27#%end 28 29#%option G_OPT_V_FIELD 30#% description: Layer where to join 31#% guidependency: column 32#%end 33 34#%option G_OPT_DB_COLUMN 35#% description: Identifier column (e.g.: cat) in the vector table to be used for join 36#% required : yes 37#%end 38 39#%option G_OPT_DB_TABLE 40#% key: other_table 41#% description: Other table name 42#% required: yes 43#% guidependency: ocolumn,scolumns 44#%end 45 46#%option G_OPT_DB_COLUMN 47#% key: other_column 48#% description: Identifier column (e.g.: id) in the other table used for join 49#% required: yes 50#%end 51 52#%option G_OPT_DB_COLUMN 53#% key: subset_columns 54#% multiple: yes 55#% required: no 56#% description: Subset of columns from the other table 57#%end 58 59import sys 60import string 61import grass.script as grass 62from grass.exceptions import CalledModuleError 63 64 65def main(): 66 map = options['map'] 67 layer = options['layer'] 68 column = options['column'] 69 otable = options['other_table'] 70 ocolumn = options['other_column'] 71 if options['subset_columns']: 72 scolumns = options['subset_columns'].split(',') 73 else: 74 scolumns = None 75 76 try: 77 f = grass.vector_layer_db(map, layer) 78 except CalledModuleError: 79 sys.exit(1) 80 81 maptable = f['table'] 82 database = f['database'] 83 driver = f['driver'] 84 85 if driver == 'dbf': 86 grass.fatal(_("JOIN is not supported for tables stored in DBF format")) 87 88 if not maptable: 89 grass.fatal(_("There is no table connected to this map. Unable to join any column.")) 90 91 # check if column is in map table 92 if column not in grass.vector_columns(map, layer): 93 grass.fatal(_("Column <%s> not found in table <%s>") % (column, 94 maptable)) 95 96 # describe other table 97 all_cols_ot = grass.db_describe(otable, driver=driver, database=database)['cols'] 98 99 # check if ocolumn is on other table 100 if ocolumn not in [ocol[0] for ocol in all_cols_ot]: 101 grass.fatal(_("Column <%s> not found in table <%s>") % (ocolumn, otable)) 102 103 # determine columns subset from other table 104 if not scolumns: 105 # select all columns from other table 106 cols_to_add = all_cols_ot 107 else: 108 cols_to_add = [] 109 # check if scolumns exists in the other table 110 for scol in scolumns: 111 found = False 112 for col_ot in all_cols_ot: 113 if scol == col_ot[0]: 114 found = True 115 cols_to_add.append(col_ot) 116 break 117 if not found: 118 grass.warning(_("Column <%s> not found in table <%s>") % (scol, otable)) 119 120 all_cols_tt = grass.vector_columns(map, int(layer)).keys() 121 122 select = "SELECT $colname FROM $otable WHERE $otable.$ocolumn=$table.$column" 123 template = string.Template("UPDATE $table SET $colname=(%s);" % select) 124 125 for col in cols_to_add: 126 # skip the vector column which is used for join 127 colname = col[0] 128 if colname == column: 129 continue 130 131 use_len = False 132 if len(col) > 2: 133 use_len = True 134 # Sqlite 3 does not support the precision number any more 135 if driver == "sqlite": 136 use_len = False 137 # MySQL - expect format DOUBLE PRECISION(M,D), see #2792 138 elif driver == "mysql" and col[1] == 'DOUBLE PRECISION': 139 use_len = False 140 141 if use_len: 142 coltype = "%s(%s)" % (col[1], col[2]) 143 else: 144 coltype = "%s" % col[1] 145 146 colspec = "%s %s" % (colname, coltype) 147 148 # add only the new column to the table 149 if colname not in all_cols_tt: 150 try: 151 grass.run_command('v.db.addcolumn', map=map, 152 columns=colspec, layer=layer) 153 except CalledModuleError: 154 grass.fatal(_("Error creating column <%s>") % colname) 155 156 stmt = template.substitute(table=maptable, column=column, 157 otable=otable, ocolumn=ocolumn, 158 colname=colname) 159 grass.debug(stmt, 1) 160 grass.verbose( 161 _("Updating column <%s> of vector map <%s>...") % 162 (colname, map)) 163 try: 164 grass.write_command('db.execute', stdin=stmt, input='-', 165 database=database, driver=driver) 166 except CalledModuleError: 167 grass.fatal(_("Error filling column <%s>") % colname) 168 169 # write cmd history 170 grass.vector_history(map) 171 172 return 0 173if __name__ == "__main__": 174 options, flags = grass.parser() 175 sys.exit(main()) 176