.TH db.execute 1grass "" "GRASS 7.8.5" "GRASS GIS User's Manual"
.SH NAME
\fI\fBdb.execute\fR\fR  \- Executes any SQL statement.
.br
For SELECT statements use \(cqdb.select\(cq.
.SH KEYWORDS
database, attribute table, SQL
.SH SYNOPSIS
\fBdb.execute\fR
.br
\fBdb.execute \-\-help\fR
.br
\fBdb.execute\fR [\-\fBi\fR]  [\fBsql\fR=\fIsql_query\fR]   [\fBinput\fR=\fIname\fR]   [\fBdriver\fR=\fIname\fR]   [\fBdatabase\fR=\fIname\fR]   [\fBschema\fR=\fIname\fR]   [\-\-\fBhelp\fR]  [\-\-\fBverbose\fR]  [\-\-\fBquiet\fR]  [\-\-\fBui\fR]
.SS Flags:
.IP "\fB\-i\fR" 4m
.br
Ignore SQL errors and continue
.IP "\fB\-\-help\fR" 4m
.br
Print usage summary
.IP "\fB\-\-verbose\fR" 4m
.br
Verbose module output
.IP "\fB\-\-quiet\fR" 4m
.br
Quiet module output
.IP "\fB\-\-ui\fR" 4m
.br
Force launching GUI dialog
.SS Parameters:
.IP "\fBsql\fR=\fIsql_query\fR" 4m
.br
SQL statement
.br
Example: update rybniky set kapri = \(cqhodne\(cq where kapri = \(cqmalo\(cq
.IP "\fBinput\fR=\fIname\fR" 4m
.br
Name of file containing SQL statement(s)
.br
\(cq\-\(cq for standard input
.IP "\fBdriver\fR=\fIname\fR" 4m
.br
Name of database driver
.br
Options: \fIdbf, mysql, odbc, ogr, pg, sqlite\fR
.br
Default: \fIsqlite\fR
.IP "\fBdatabase\fR=\fIname\fR" 4m
.br
Name of database
.br
Default: \fI$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db\fR
.IP "\fBschema\fR=\fIname\fR" 4m
.br
Database schema
.br
Do not use this option if schemas are not supported by driver/database server
.SH DESCRIPTION
\fIdb.execute\fR allows the user to execute SQL statements.
.SH NOTES
\fIdb.execute\fR only executes SQL statements and does not return
any data. If you need data returned from the database, use
\fIdb.select\fR.
.PP
If parameters for database connection are already set with
\fIdb.connect\fR, they are taken as default
values and do not need to be specified each time.
.PP
If you have a large number of SQL commands to process, it is much much
faster to place all the SQL statements into a text file and
use \fBinput\fR file parameter than it is to process each statement
individually in a loop. If multiple instruction lines are given, each
SQL line must end with a semicolon.
.PP
Please see the individual \fIGRASS SQL interface\fR
for how to create a new database.
.SH EXAMPLES
Create a new table with columns \(cqcat\(cq and \(cqsoiltype\(cq:
.br
.nf
\fC
db.execute sql=\(dqCREATE TABLE soils (cat integer, soiltype varchar(10))\(dq
\fR
.fi
Create a new table using a file with SQL statements
.br
.nf
\fC
db.execute driver=odbc database=grassdb input=file.sql
\fR
.fi
Insert new row into attribute table:
.br
.nf
\fC
db.execute sql=\(dqINSERT INTO mysites (id,name,east,north) values (30,\(cqAla\(cq,1657340,5072301)\(dq
\fR
.fi
Update attribute entries to new value based on SQL rule:
.br
.nf
\fC
db.execute sql=\(dqUPDATE roads SET travelcost=5 WHERE cat=1\(dq
\fR
.fi
Update attribute entries to new value based on SQL rule:
.br
.nf
\fC
db.execute sql=\(dqUPDATE dourokukan SET testc=50 WHERE testc is NULL\(dq
\fR
.fi
Delete selected rows from attribute table:
.br
.nf
\fC
db.execute sql=\(dqDELETE FROM gsod_stationlist WHERE latitude < \-91\(dq
\fR
.fi
Add new column to attribute table:
.br
.nf
\fC
db.execute sql=\(dqALTER TABLE roads ADD COLUMN length double\(dq
\fR
.fi
Column type conversion \- update new column from existing column (all
drivers except for DBF):
.br
.nf
\fC
# \(cqz_value\(cq is varchar and \(cqz\(cq is double precision:
echo \(dqUPDATE geodetic_pts SET z = CAST(z_value AS numeric)\(dq | db.execute input=\-
\fR
.fi
Drop column from attribute table:
.br
.nf
\fC
db.execute sql=\(dqALTER TABLE roads DROP COLUMN length\(dq
\fR
.fi
Drop table (not supported by all drivers):
.br
.nf
\fC
db.execute sql=\(dqDROP TABLE fmacopy\(dq
\fR
.fi
Update attribute with multiple SQL instructions in file
(e.g., file.sql, instruction line must end with a semicolon):
.br
.nf
\fC
UPDATE roads SET travelcost=5 WHERE cat=1;
UPDATE roads SET travelcost=2 WHERE cat=2;
db.execute input=file.sql
\fR
.fi
Join table \(cqmyroads\(cq to table \(cqextratab\(cq based on common \(cqcat\(cq column
values (not supported by DBF driver):
.br
.nf
\fC
db.execute sql=\(dqUPDATE extratab SET names=(SELECT label FROM myroads WHERE extratab.cat=myroads.cat)\(dq
\fR
.fi
.SH SEE ALSO
\fI
db.columns,
db.describe,
db.drivers,
db.droptable,
db.login,
db.select,
db.tables,
\fR
.PP
\fI
GRASS SQL interface
\fI
\fR\fR
.SH AUTHOR
CERL
.SH SOURCE CODE
.PP
Available at: db.execute source code (history)
.PP
Main index |
Database index |
Topics index |
Keywords index |
Graphical index |
Full index
.PP
© 2003\-2020
GRASS Development Team,
GRASS GIS 7.8.5 Reference Manual