Skip to content

v.db.connect

Prints/sets DB connection for a vector map to attribute table.

v.db.connect [-pgcd] map=name [driver=name] [database=name] [table=name] [key=name] [layer=string] [separator=character] [--overwrite] [--verbose] [--quiet] [--qq] [--ui]

Example:

v.db.connect map=name

grass.script.run_command("v.db.connect", map, driver="sqlite", database="$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db", table=None, key="cat", layer="1", separator="pipe", flags=None, overwrite=False, verbose=False, quiet=False, superquiet=False)

Example:

gs.run_command("v.db.connect", map="name")

Parameters

map=name [required]
    Name of vector map
    Or data source for direct OGR access
driver=name
    Name of database driver
    Allowed values: dbf, ogr, sqlite
    Default: sqlite
database=name
    Name of database
    Default: $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db
table=name
    Name of attribute table
key=name
    Name of key column
    Must refer to an integer column
    Default: cat
layer=string
    Layer number or name
    Format: layer number[/layer name]
    Default: 1
separator=character
    Field separator for shell script style output
    Special characters: pipe, comma, space, tab, newline
    Default: pipe
-p
    Print all map connection parameters and exit
-g
    Print all map connection parameters in shell script style and exit
    Format: layer[/layer name] table key database driver
-c
    Print types/names of table columns for specified layer and exit
-d
    Delete connection for certain layer (not the table)
--overwrite
    Allow output files to overwrite existing files
--help
    Print usage summary
--verbose
    Verbose module output
--quiet
    Quiet module output
--qq
    Very quiet module output
--ui
    Force launching GUI dialog

map : str, required
    Name of vector map
    Or data source for direct OGR access
    Used as: input, vector, name
driver : str, optional
    Name of database driver
    Used as: input, dbdriver, name
    Allowed values: dbf, ogr, sqlite
    Default: sqlite
database : str, optional
    Name of database
    Used as: input, dbname, name
    Default: $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db
table : str, optional
    Name of attribute table
    Used as: input, dbtable, name
key : str, optional
    Name of key column
    Must refer to an integer column
    Used as: name
    Default: cat
layer : str, optional
    Layer number or name
    Format: layer number[/layer name]
    Used as: output, layer
    Default: 1
separator : str, optional
    Field separator for shell script style output
    Special characters: pipe, comma, space, tab, newline
    Used as: input, separator, character
    Default: pipe
flags : str, optional
    Allowed values: p, g, c, d
    p
        Print all map connection parameters and exit
    g
        Print all map connection parameters in shell script style and exit
        Format: layer[/layer name] table key database driver
    c
        Print types/names of table columns for specified layer and exit
    d
        Delete connection for certain layer (not the table)
overwrite: bool, optional
    Allow output files to overwrite existing files
    Default: False
verbose: bool, optional
    Verbose module output
    Default: False
quiet: bool, optional
    Quiet module output
    Default: False
superquiet: bool, optional
    Very quiet module output
    Default: False

DESCRIPTION

v.db.connect prints or sets database connection for a vector map. The user can add or remove link to attribute table on the certain layer.

NOTE

Connection information (driver, database, table, key) is stored for each map, in the file

<database>/<project>/<mapset>/vector/<map>/dbln

If parameters for database connection are already set with db.connect, they are taken as default values and do not need to be specified each time.

When printing database connection (p or g flag) the parameter layer is ignored, i.e. all connections are printed to the output.

Attention: Removing a vector map will also delete all tables linked to it! If you use v.db.connect to link further tables to your map, it is advisable to make a copy from those tables first and connect the copied tables to the vector map (see also v.overlay).

EXAMPLE

Note: The default database backend setting is SQLite.

Print all database connection parameters for vector map.

v.db.connect -p map=roads

Print column types and names of table linked to vector map.

v.db.connect -c map=roads

Connect vector map to database (DBF driver)

Connect vector map to DBF table without or with variables.

Using default DB connection:

v.db.connect map=vectormap table=table

Using hardcoded path to DBF directory (not recommended):

v.db.connect map=vectormap table=table \
             database=/home/user/grassdata/spearfish60/PERMANENT/dbf

Using variable as DBF directory definition, single quotes must be used:

v.db.connect map=vectormap table=table \
             database='$GISDBASE/$LOCATION_NAME/$MAPSET/dbf/'

Connect vector map layer 2 and key ID to database with variables (note: if needed, single quotes must be used for the database parameter):

v.db.connect map=vectormap table=table layer=2 key=ID

Connect vector map to database (SQLite driver)

Very similar to DBF driver example above.

db.connect driver=sqlite database='$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db'
db.tables -p
v.db.connect map=vectormap table=table driver=sqlite \
             database='$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db'
v.db.connect -p map=vectormap

Connect vector map to database (MySQL driver)

# note: connection which requires password
db.connect driver=mysql database="host=dbserver.foo.org,dbname=my_database"
db.login user=joshua [password=xxx]
# ... or enter password interactively.

db.tables -p

# connect external table to layer 2:
v.db.connect map=my_map table=my_mysql_table key=baz layer=2
v.db.connect -p my_map

Connect vector map to database (PostgreSQL driver)

# note: connection without password being asked
v.db.connect map=vectormap table=table layer=1 key=oid driver=pg \
             database="host=myserver.itc.it,dbname=mydb,user=name" \
             table=mytable key=id

Store geometry in GRASS but attributes in PostgreSQL

This example illustrates a mixed data storage with possibility to update attributes in an external PostgreSQL database:

# Check current settings for attribute storage:
db.connect -p

# Import table from PostgreSQL to new map
# (NOTE: output map name needs to be different from table name in
#        case that GRASS is connected to PostgreSQL):
v.in.db driver=pg database="host=localhost,dbname=meteo" \
        table=mytable x=lon y=lat key=cat out=mytable

v.db.connect map=mytable -p

# Cancel table connection between map and attribute table:
v.db.connect map=mytable -d
v.db.connect map=mytable -p

# Drop table which was replicated due to import:
db.tables -p
echo "DROP TABLE mytable" | db.execute
db.tables -p

# reconnect map to table in PostgreSQL:
v.db.connect map=mytable driver=pg database="host=localhost,dbname=meteo" \
        table=mytable key=cat

# Now the geometry is stored in GRASS while the attributes are stored
# in PostgreSQL.

An alternative is to create a "view" of only ID, x, y [,z] columns and to use v.in.db on this view, then connect the original table to the geometry. This will be faster if the original table is very large.

Store geometry in GRASS but attributes in PostGIS

This example illustrated a mixed data storage with possibility top update attributes in external PostGIS database:

# Check current settings for attribute storage:
db.connect -p

# Import table from PostGIS to new map
# (NOTE: output map name needs to be different from table name in
#        case that GRASS is connected to PostGIS):
v.in.db driver=pg database="host=localhost,dbname=meteo" \
        table=mytable x="x(geom)" y="y(geom)" key=cat out=mytable

v.db.connect map=mytable -p

# Cancel table connection between map and attribute table:
v.db.connect map=mytable -d
v.db.connect map=mytable -p

# Drop table which was replicated due to import:
db.tables -p
echo "DROP TABLE mytable" | db.execute
db.tables -p

# reconnect map to table in PostGIS:
v.db.connect map=mytable driver=pg database="host=localhost,dbname=meteo" \
        table=mytable key=cat

# Now the geometry is stored in GRASS while the attributes are stored
# in PostGIS.

SEE ALSO

db.connect, db.copy, db.tables, v.db.addtable, v.db.droptable, v.db.addcolumn, v.db.dropcolumn, v.external, v.in.db, v.overlay

GRASS SQL interface

AUTHOR

Radim Blazek, ITC-Irst, Trento, Italy

SOURCE CODE

Available at: v.db.connect source code (history)
Latest change: Friday Feb 07 19:16:09 2025 in commit a82a39f