# -*- coding: utf-8 -*-
"""
Created on Wed Aug 8 15:29:21 2012
@author: pietro
"""
from __future__ import (nested_scopes, generators, division, absolute_import,
with_statement, print_function, unicode_literals)
import os
import sys
long = int if sys.version_info[0] == 3 else long
import ctypes
import numpy as np
from sqlite3 import OperationalError
try:
from collections import OrderedDict
except:
from grass.pygrass.orderdict import OrderedDict
import grass.lib.vector as libvect
from grass.pygrass.gis import Mapset
from grass.pygrass.errors import DBError
from grass.pygrass.utils import table_exist
from grass.script.db import db_table_in_vector
from grass.script.core import warning
from grass.pygrass.vector import sql
DRIVERS = ('sqlite', 'pg')
[docs]def get_path(path):
"""Return the full path to the database; replacing environment variable
with real values
>>> path = '$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db'
>>> new_path = get_path(path)
>>> from grass.script.core import gisenv
>>> import os
>>> new_path2 = os.path.join(gisenv()['GISDBASE'], gisenv()['LOCATION_NAME'],
... gisenv()['MAPSET'], 'sqlite', 'sqlite.db')
>>> new_path == new_path2
True
"""
if "$" not in path:
return path
else:
mapset = Mapset()
path = path.replace('$GISDBASE', mapset.gisdbase)
path = path.replace('$LOCATION_NAME', mapset.location)
path = path.replace('$MAPSET', mapset.name)
return path
[docs]class Filters(object):
"""Help user to build a simple sql query.
>>> filter = Filters('table')
>>> filter.get_sql()
u'SELECT * FROM table;'
>>> filter.where("area<10000").get_sql()
u'SELECT * FROM table WHERE area<10000;'
>>> filter.select("cat", "area").get_sql()
u'SELECT cat, area FROM table WHERE area<10000;'
>>> filter.order_by("area").limit(10).get_sql()
u'SELECT cat, area FROM table WHERE area<10000 ORDER BY area LIMIT 10;'
"""
def __init__(self, tname):
self.tname = tname
self._select = None
self._where = None
self._orderby = None
self._limit = None
self._groupby = None
def __repr__(self):
return "Filters(%r)" % self.get_sql()
[docs] def select(self, *args):
"""Create the select query"""
cols = ', '.join(args) if args else '*'
select = sql.SELECT[:-1]
self._select = select.format(cols=cols, tname=self.tname)
return self
[docs] def where(self, condition):
"""Create the where condition
:param condition: the condition of where statement, for example
`cat = 1`
:type condition: str
"""
self._where = 'WHERE {condition}'.format(condition=condition)
return self
[docs] def order_by(self, *orderby):
"""Create the order by condition
:param orderby: the name of column/s to order the result
:type orderby: str
"""
self._orderby = 'ORDER BY {orderby}'.format(orderby=', '.join(orderby))
return self
[docs] def limit(self, number):
"""Create the limit condition
:param number: the number to limit the result
:type number: int
"""
if not isinstance(number, int):
raise ValueError("Must be an integer.")
else:
self._limit = 'LIMIT {number}'.format(number=number)
return self
[docs] def group_by(self, *groupby):
"""Create the group by condition
:param groupby: the name of column/s to group the result
:type groupby: str, list
"""
self._groupby = 'GROUP BY {groupby}'.format(groupby=', '.join(groupby))
return self
[docs] def get_sql(self):
"""Return the SQL query"""
sql_list = list()
if self._select is None:
self.select()
sql_list.append(self._select)
if self._where is not None:
sql_list.append(self._where)
if self._groupby is not None:
sql_list.append(self._groupby)
if self._orderby is not None:
sql_list.append(self._orderby)
if self._limit is not None:
sql_list.append(self._limit)
return "%s;" % ' '.join(sql_list)
[docs] def reset(self):
"""Clean internal variables"""
self._select = None
self._where = None
self._orderby = None
self._limit = None
self._groupby = None
[docs]class Columns(object):
"""Object to work with columns table.
It is possible to instantiate a Columns object given the table name and
the database connection.
For a sqlite table:
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> cols_sqlite = Columns('census',
... sqlite3.connect(get_path(path)))
>>> cols_sqlite.tname
u'census'
For a postgreSQL table:
>>> import psycopg2 as pg #doctest: +SKIP
>>> cols_pg = Columns('boundary_municp_pg',
... pg.connect('host=localhost dbname=grassdb')) #doctest: +SKIP
>>> cols_pg.tname #doctest: +SKIP
'boundary_municp_pg' #doctest: +SKIP
"""
def __init__(self, tname, connection, key='cat'):
self.tname = tname
self.conn = connection
self.key = key
self.odict = None
self.update_odict()
def __contains__(self, item):
return item in self.names()
def __repr__(self):
return "Columns(%r)" % self.items()
def __getitem__(self, key):
return self.odict[key]
def __setitem__(self, name, new_type):
self.cast(name, new_type)
self.update_odict(self)
def __iter__(self):
return self.odict.__iter__()
def __len__(self):
return self.odict.__len__()
def __eq__(self, obj):
"""Return True if two table have the same columns.
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> connection = sqlite3.connect(get_path(path))
>>> cols0 = Columns('census', connection)
>>> cols1 = Columns('census', connection)
>>> cols2 = Columns('hospitals', connection)
>>> cols0 == cols1
True
>>> cols1 == cols2
False
"""
return obj.tname == self.tname and obj.odict == self.odict
def __ne__(self, other):
return not self == other
# Restore Python 2 hashing beaviour on Python 3
__hash__ = object.__hash__
[docs] def is_pg(self):
"""Return True if is a psycopg connection.
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> cols_sqlite = Columns('census',
... sqlite3.connect(get_path(path)))
>>> cols_sqlite.is_pg()
False
>>> import psycopg2 as pg #doctest: +SKIP
>>> cols_pg = Columns('boundary_municp_pg',
... pg.connect('host=localhost dbname=grassdb')) #doctest: +SKIP
>>> cols_pg.is_pg() #doctest: +SKIP
True
"""
return hasattr(self.conn, 'xid')
[docs] def update_odict(self):
"""Read columns name and types from table and update the odict
attribute.
"""
if self.is_pg():
# is a postgres connection
cur = self.conn.cursor()
cur.execute("SELECT oid,typname FROM pg_type")
diz = dict(cur.fetchall())
odict = OrderedDict()
import psycopg2 as pg
try:
cur.execute(sql.SELECT.format(cols='*', tname=self.tname))
descr = cur.description
for column in descr:
name, ctype = column[:2]
odict[name] = diz[ctype]
except pg.ProgrammingError:
pass
self.odict = odict
else:
# is a sqlite connection
cur = self.conn.cursor()
cur.execute(sql.PRAGMA.format(tname=self.tname))
descr = cur.fetchall()
odict = OrderedDict()
for column in descr:
name, ctype = column[1:3]
odict[name] = ctype
self.odict = odict
values = ','.join(['?', ] * self.__len__())
kv = ','.join(['%s=?' % k for k in self.odict.keys() if k != self.key])
where = "%s=?" % self.key
self.insert_str = sql.INSERT.format(tname=self.tname, values=values)
self.update_str = sql.UPDATE_WHERE.format(tname=self.tname,
values=kv, condition=where)
[docs] def sql_descr(self, remove=None):
"""Return a string with description of columns.
Remove it is used to remove a columns.
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> cols_sqlite = Columns('census',
... sqlite3.connect(get_path(path)))
>>> cols_sqlite.sql_descr() # doctest: +ELLIPSIS
u'cat integer, OBJECTID integer, AREA double precision, ...'
>>> import psycopg2 as pg # doctest: +SKIP
>>> cols_pg = Columns('boundary_municp_pg',
... pg.connect('host=localhost dbname=grassdb')) # doctest: +SKIP
>>> cols_pg.sql_descr() # doctest: +ELLIPSIS +SKIP
'cat int4, objectid int4, area float8, perimeter float8, ...'
"""
if remove:
return ', '.join(['%s %s' % (key, val) for key, val in self.items()
if key != remove])
else:
return ', '.join(['%s %s' % (key, val)
for key, val in self.items()])
[docs] def types(self):
"""Return a list with the column types.
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> cols_sqlite = Columns('census',
... sqlite3.connect(get_path(path)))
>>> cols_sqlite.types() # doctest: +ELLIPSIS
[u'integer', u'integer', ...]
>>> import psycopg2 as pg # doctest: +SKIP
>>> cols_pg = Columns('boundary_municp_pg',
... pg.connect('host=localhost dbname=grassdb')) # doctest: +SKIP
>>> cols_pg.types() # doctest: +ELLIPSIS +SKIP
['int4', 'int4', 'float8', 'float8', 'float8', ...]
"""
return self.odict.values()
[docs] def names(self, remove=None, unicod=True):
"""Return a list with the column names.
Remove it is used to remove a columns.
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> cols_sqlite = Columns('census',
... sqlite3.connect(get_path(path)))
>>> cols_sqlite.names() # doctest: +ELLIPSIS
[u'cat', u'OBJECTID', u'AREA', u'PERIMETER', ...]
>>> import psycopg2 as pg # doctest: +SKIP
>>> cols_pg = Columns('boundary_municp_pg', # doctest: +SKIP
... pg.connect('host=localhost dbname=grassdb'))
>>> cols_pg.names() # doctest: +ELLIPSIS +SKIP
['cat', 'objectid', 'area', 'perimeter', ...]
"""
if remove:
nams = self.odict.keys()
nams.remove(remove)
else:
nams = self.odict.keys()
if unicod:
return nams
else:
return [str(name) for name in nams]
[docs] def items(self):
"""Return a list of tuple with column name and column type.
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> cols_sqlite = Columns('census',
... sqlite3.connect(get_path(path)))
>>> cols_sqlite.items() # doctest: +ELLIPSIS
[(u'cat', u'integer'), ...]
>>> import psycopg2 as pg # doctest: +SKIP
>>> cols_pg = Columns('boundary_municp_pg',
... pg.connect('host=localhost dbname=grassdb')) # doctest: +SKIP
>>> cols_pg.items() # doctest: +ELLIPSIS +SKIP
[('cat', 'int4'), ('objectid', 'int4'), ('area', 'float8'), ...]
"""
return self.odict.items()
[docs] def add(self, col_name, col_type):
"""Add a new column to the table.
:param col_name: the name of column to add
:type col_name: str
:param col_type: the tipe of column to add
:type col_type: str
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db'
>>> from grass.pygrass.utils import copy, remove
>>> copy('census','mycensus','vect')
>>> cols_sqlite = Columns('mycensus',
... sqlite3.connect(get_path(path)))
>>> cols_sqlite.add(['n_pizza'], ['INT'])
>>> 'n_pizza' in cols_sqlite
True
>>> import psycopg2 as pg # doctest: +SKIP
>>> cols_pg = Columns('boundary_municp_pg',
... pg.connect('host=localhost dbname=grassdb')) #doctest: +SKIP
>>> cols_pg.add('n_pizza', 'INT') # doctest: +SKIP
>>> 'n_pizza' in cols_pg # doctest: +SKIP
True
>>> remove('mycensus', 'vect')
"""
def check(col_type):
"""Check the column type if it is supported by GRASS
:param col_type: the type of column
:type col_type: str
"""
valid_type = ('DOUBLE PRECISION', 'DOUBLE', 'INT', 'INTEGER',
'DATE', 'VARCHAR')
col = col_type.upper()
valid = [col.startswith(tp) for tp in valid_type]
if not any(valid):
str_err = ("Type: %r is not supported."
"\nSupported types are: %s")
raise TypeError(str_err % (col_type, ", ".join(valid_type)))
return col_type
col_type = ([check(col_type), ] if isinstance(col_type, (str, unicode))
else [check(col) for col in col_type])
col_name = ([col_name, ] if isinstance(col_name, (str, unicode))
else col_name)
sqlcode = [sql.ADD_COL.format(tname=self.tname, cname=cn, ctype=ct)
for cn, ct in zip(col_name, col_type)]
cur = self.conn.cursor()
cur.executescript('\n'.join(sqlcode))
self.conn.commit()
cur.close()
self.update_odict()
[docs] def rename(self, old_name, new_name):
"""Rename a column of the table.
:param old_name: the name of existing column
:type old_name: str
:param new_name: the name of new column
:type new_name: str
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db'
>>> from grass.pygrass.utils import copy, remove
>>> copy('census','mycensus','vect')
>>> cols_sqlite = Columns('mycensus',
... sqlite3.connect(get_path(path)))
>>> cols_sqlite.add(['n_pizza'], ['INT'])
>>> 'n_pizza' in cols_sqlite
True
>>> cols_sqlite.rename('n_pizza', 'n_pizzas') # doctest: +ELLIPSIS
>>> 'n_pizza' in cols_sqlite
False
>>> 'n_pizzas' in cols_sqlite
True
>>> import psycopg2 as pg # doctest: +SKIP
>>> cols_pg = Columns('boundary_municp_pg',
... pg.connect('host=localhost dbname=grassdb')) # doctest: +SKIP
>>> cols_pg.rename('n_pizza', 'n_pizzas') # doctest: +SKIP
>>> 'n_pizza' in cols_pg # doctest: +SKIP
False
>>> 'n_pizzas' in cols_pg # doctest: +SKIP
True
>>> remove('mycensus', 'vect')
"""
cur = self.conn.cursor()
if self.is_pg():
cur.execute(sql.RENAME_COL.format(tname=self.tname,
old_name=old_name,
new_name=new_name))
self.conn.commit()
cur.close()
self.update_odict()
else:
cur.execute(sql.ADD_COL.format(tname=self.tname,
cname=new_name,
ctype=str(self.odict[old_name])))
cur.execute(sql.UPDATE.format(tname=self.tname,
new_col=new_name,
old_col=old_name))
self.conn.commit()
cur.close()
self.update_odict()
self.drop(old_name)
[docs] def cast(self, col_name, new_type):
"""Change the column type.
:param col_name: the name of column
:type col_name: str
:param new_type: the new type of column
:type new_type: str
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db'
>>> from grass.pygrass.utils import copy, remove
>>> copy('census','mycensus','vect')
>>> cols_sqlite = Columns('mycensus',
... sqlite3.connect(get_path(path)))
>>> cols_sqlite.add(['n_pizzas'], ['INT'])
>>> cols_sqlite.cast('n_pizzas', 'float8') # doctest: +ELLIPSIS
Traceback (most recent call last):
...
DBError: SQLite does not support to cast columns.
>>> import psycopg2 as pg # doctest: +SKIP
>>> cols_pg = Columns('boundary_municp_pg',
... pg.connect('host=localhost dbname=grassdb')) # doctest: +SKIP
>>> cols_pg.cast('n_pizzas', 'float8') # doctest: +SKIP
>>> cols_pg['n_pizzas'] # doctest: +SKIP
'float8'
>>> remove('mycensus', 'vect')
.. warning ::
It is not possible to cast a column with sqlite
"""
if self.is_pg():
cur = self.conn.cursor()
cur.execute(sql.CAST_COL.format(tname=self.tname, col=col_name,
ctype=new_type))
self.conn.commit()
cur.close()
self.update_odict()
else:
# sqlite does not support rename columns:
raise DBError('SQLite does not support to cast columns.')
[docs] def drop(self, col_name):
"""Drop a column from the table.
:param col_name: the name of column to remove
:type col_name: str
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db'
>>> from grass.pygrass.utils import copy, remove
>>> copy('census','mycensus','vect')
>>> cols_sqlite = Columns('mycensus',
... sqlite3.connect(get_path(path)))
>>> cols_sqlite.drop('CHILD') # doctest: +ELLIPSIS
>>> 'CHILD' in cols_sqlite
False
>>> import psycopg2 as pg # doctest: +SKIP
>>> cols_pg = Columns('boundary_municp_pg',
... pg.connect('host=localhost dbname=grassdb')) # doctest: +SKIP
>>> cols_pg.drop('CHILD') # doctest: +SKIP
>>> 'CHILD' in cols_pg # doctest: +SKIP
False
>>> remove('mycensus','vect')
"""
cur = self.conn.cursor()
if self.is_pg():
cur.execute(sql.DROP_COL.format(tname=self.tname,
cname=col_name))
else:
desc = str(self.sql_descr(remove=col_name))
names = ', '.join(self.names(remove=col_name, unicod=False))
queries = sql.DROP_COL_SQLITE.format(tname=self.tname,
keycol=self.key,
coldef=desc,
colnames=names).split('\n')
for query in queries:
cur.execute(query)
self.conn.commit()
cur.close()
self.update_odict()
[docs]class Link(object):
"""Define a Link between vector map and the attributes table.
It is possible to define a Link object or given all the information
(layer, name, table name, key, database, driver):
>>> link = Link(1, 'link0', 'census', 'cat',
... '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db', 'sqlite')
>>> link.layer
1
>>> link.name
'link0'
>>> link.table_name
'census'
>>> link.key
'cat'
>>> link.database
'$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> link.driver
'sqlite'
>>> link
Link(1, link0, sqlite)
It is possible to change parameters with:
>>> link.driver = 'pg' # doctest: +SKIP
>>> link.driver # doctest: +SKIP
'pg'
>>> link.driver = 'postgres' # doctest: +ELLIPSIS +SKIP
Traceback (most recent call last):
...
TypeError: Driver not supported, use: sqlite, pg.
>>> link.driver # doctest: +SKIP
'pg'
>>> link.number = 0 # doctest: +ELLIPSIS +SKIP
Traceback (most recent call last):
...
TypeError: Number must be positive and greater than 0.
Or given a c_fieldinfo object that is a ctypes pointer to the field_info C
struct. ::
>>> link = Link(c_fieldinfo = ctypes.pointer(libvect.field_info()))
"""
def _get_layer(self):
return self.c_fieldinfo.contents.number
def _set_layer(self, number):
if number <= 0:
raise TypeError("Number must be positive and greater than 0.")
self.c_fieldinfo.contents.number = number
layer = property(fget=_get_layer, fset=_set_layer,
doc="Set and obtain layer number")
def _get_name(self):
return self.c_fieldinfo.contents.name
def _set_name(self, name):
self.c_fieldinfo.contents.name = name
name = property(fget=_get_name, fset=_set_name,
doc="Set and obtain name vale")
def _get_table(self):
return self.c_fieldinfo.contents.table
def _set_table(self, new_name):
self.c_fieldinfo.contents.table = new_name
table_name = property(fget=_get_table, fset=_set_table,
doc="Set and obtain table name value")
def _get_key(self):
return self.c_fieldinfo.contents.key
def _set_key(self, key):
self.c_fieldinfo.contents.key = key
key = property(fget=_get_key, fset=_set_key,
doc="Set and obtain cat value")
def _get_database(self):
return self.c_fieldinfo.contents.database
def _set_database(self, database):
self.c_fieldinfo.contents.database = database
database = property(fget=_get_database, fset=_set_database,
doc="Set and obtain database value")
def _get_driver(self):
return self.c_fieldinfo.contents.driver
def _set_driver(self, driver):
if driver not in ('sqlite', 'pg'):
str_err = "Driver not supported, use: %s." % ", ".join(DRIVERS)
raise TypeError(str_err)
self.c_fieldinfo.contents.driver = driver
driver = property(fget=_get_driver, fset=_set_driver,
doc="Set and obtain driver value. The drivers supported \
by PyGRASS are: SQLite and PostgreSQL")
def __init__(self, layer=1, name=None, table=None, key='cat',
database='$GISDBASE/$LOCATION_NAME/'
'$MAPSET/sqlite/sqlite.db',
driver='sqlite', c_fieldinfo=None):
if c_fieldinfo is not None:
self.c_fieldinfo = c_fieldinfo
else:
self.c_fieldinfo = ctypes.pointer(libvect.field_info())
self.layer = layer
self.name = name
self.table_name = table
self.key = key
self.database = database
self.driver = driver
def __repr__(self):
return "Link(%d, %s, %s)" % (self.layer, self.name, self.driver)
def __eq__(self, link):
"""Return True if two Link instance have the same parameters.
>>> l0 = Link(1, 'link0', 'census', 'cat',
... '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db', 'sqlite')
>>> l1 = Link(1, 'link0', 'census', 'cat',
... '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db', 'sqlite')
>>> l2 = Link(2, 'link0', 'census', 'cat',
... '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db', 'sqlite')
>>> l0 == l1
True
>>> l1 == l2
False
"""
attrs = ['layer', 'name', 'table_name', 'key', 'driver']
for attr in attrs:
if getattr(self, attr) != getattr(link, attr):
return False
return True
def __ne__(self, other):
return not self == other
# Restore Python 2 hashing beaviour on Python 3
__hash__ = object.__hash__
[docs] def connection(self):
"""Return a connection object.
>>> link = Link(1, 'link0', 'census', 'cat',
... '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db',
... 'sqlite')
>>> conn = link.connection()
>>> cur = conn.cursor()
>>> cur.execute("SELECT cat,TOTAL_POP,PERIMETER FROM %s" %
... link.table_name) # doctest: +ELLIPSIS
<sqlite3.Cursor object at ...>
>>> cur.fetchone()
(1, 44, 757.669)
>>> cur.close()
>>> conn.close()
"""
if self.driver == 'sqlite':
import sqlite3
# Numpy is using some custom integer data types to efficiently
# pack data into memory. Since these types aren't familiar to
# sqlite, you'll have to tell it about how to handle them.
for t in (np.int8, np.int16, np.int32, np.int64, np.uint8,
np.uint16, np.uint32, np.uint64):
sqlite3.register_adapter(t, long)
dbpath = get_path(self.database)
dbdirpath = os.path.split(dbpath)[0]
if not os.path.exists(dbdirpath):
os.mkdir(dbdirpath)
return sqlite3.connect(dbpath)
elif self.driver == 'pg':
try:
import psycopg2
psycopg2.paramstyle = 'qmark'
db = ' '.join(self.database.split(','))
return psycopg2.connect(db)
except ImportError:
er = "You need to install psycopg2 to connect with this table."
raise ImportError(er)
else:
str_err = "Driver is not supported yet, pleas use: sqlite or pg"
raise TypeError(str_err)
[docs] def table(self):
"""Return a Table object.
>>> link = Link(1, 'link0', 'census', 'cat',
... '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db',
... 'sqlite')
>>> table = link.table()
>>> table.filters.select('cat', 'TOTAL_POP', 'PERIMETER')
Filters(u'SELECT cat, TOTAL_POP, PERIMETER FROM census;')
>>> cur = table.execute()
>>> cur.fetchone()
(1, 44, 757.669)
>>> cur.close()
"""
return Table(self.table_name, self.connection(), self.key)
[docs] def info(self):
"""Print information of the link.
>>> link = Link(1, 'link0', 'census', 'cat',
... '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db',
... 'sqlite')
>>> link.info()
layer: 1
name: link0
table: census
key: cat
database: $GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db
driver: sqlite
"""
print("layer: ", self.layer)
print("name: ", self.name)
print("table: ", self.table_name)
print("key: ", self.key)
print("database: ", self.database)
print("driver: ", self.driver)
[docs]class DBlinks(object):
"""Interface containing link to the table DB.
>>> from grass.pygrass.vector import VectorTopo
>>> cens = VectorTopo('census')
>>> cens.open(mode='r')
>>> dblinks = DBlinks(cens.c_mapinfo)
>>> dblinks
DBlinks([Link(1, census, sqlite)])
>>> dblinks[0]
Link(1, census, sqlite)
>>> dblinks['census']
Link(1, census, sqlite)
>>> cens.close()
"""
def __init__(self, c_mapinfo):
self.c_mapinfo = c_mapinfo
def __len__(self):
return self.num_dblinks()
def __iter__(self):
return (self.by_index(i)
for i in range(self.num_dblinks()))
def __getitem__(self, item):
"""
"""
if isinstance(item, int):
return self.by_index(item)
else:
return self.by_name(item)
def __repr__(self):
return "DBlinks(%r)" % [link for link in self.__iter__()]
[docs] def by_index(self, indx):
"""Return a Link object by index
:param indx: the index where add new point
:type indx: int
"""
nlinks = self.num_dblinks()
if nlinks == 0:
raise IndexError
if indx < 0:
indx += nlinks
if indx > nlinks:
raise IndexError
c_fieldinfo = libvect.Vect_get_dblink(self.c_mapinfo, indx)
return Link(c_fieldinfo=c_fieldinfo) if c_fieldinfo else None
[docs] def by_layer(self, layer):
"""Return the choosen Link using the layer
:param layer: the number of layer
:type layer: int
"""
c_fieldinfo = libvect.Vect_get_field(self.c_mapinfo, layer)
return Link(c_fieldinfo=c_fieldinfo) if c_fieldinfo else None
[docs] def by_name(self, name):
"""Return the choosen Link using the name
:param name: the name of Link
:type name: str
"""
c_fieldinfo = libvect.Vect_get_field_by_name(self.c_mapinfo, name)
return Link(c_fieldinfo=c_fieldinfo) if c_fieldinfo else None
[docs] def num_dblinks(self):
"""Return the number of DBlinks"""
return libvect.Vect_get_num_dblinks(self.c_mapinfo)
[docs] def add(self, link):
"""Add a new link. Need to open vector map in write mode
:param link: the Link to add to the DBlinks
:type link: a Link object
>>> from grass.pygrass.vector import VectorTopo
>>> municip = VectorTopo('census')
>>> municip.open(mode='r')
>>> dblinks = DBlinks(municip.c_mapinfo)
>>> dblinks
DBlinks([Link(1, census, sqlite)])
>>> link = Link(2, 'pg_link', 'boundary_municp_pg', 'cat',
... 'host=localhost dbname=grassdb', 'pg') # doctest: +SKIP
>>> dblinks.add(link) # doctest: +SKIP
>>> dblinks # doctest: +SKIP
DBlinks([Link(1, boundary_municp, sqlite)])
"""
#TODO: check if open in write mode or not.
libvect.Vect_map_add_dblink(self.c_mapinfo,
link.layer, link.name, link.table_name,
link.key, link.database, link.driver)
[docs] def remove(self, key, force=False):
"""Remove a link. If force set to true remove also the table
:param key: the key of Link
:type key: str
:param force: if True remove also the table from database otherwise
only the link between table and vector
:type force: boole
>>> from grass.pygrass.vector import VectorTopo
>>> municip = VectorTopo('census')
>>> municip.open(mode='r')
>>> dblinks = DBlinks(municip.c_mapinfo)
>>> dblinks
DBlinks([Link(1, census, sqlite)])
>>> dblinks.remove('pg_link') # doctest: +SKIP
>>> dblinks # need to open vector map in write mode
DBlinks([Link(1, census, sqlite)])
"""
if force:
link = self.by_name(key)
table = link.table()
table.drop(force=force)
if isinstance(key, unicode):
key = self.from_name_to_num(key)
libvect.Vect_map_del_dblink(self.c_mapinfo, key)
[docs] def from_name_to_num(self, name):
"""
Vect_get_field_number
"""
return libvect.Vect_get_field_number(self.c_mapinfo, name)
[docs]class Table(object):
"""
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> tab_sqlite = Table(name='census',
... connection=sqlite3.connect(get_path(path)))
>>> tab_sqlite.name
u'census'
>>> import psycopg2 # doctest: +SKIP
>>> tab_pg = Table('boundary_municp_pg',
... psycopg2.connect('host=localhost dbname=grassdb',
... 'pg')) # doctest: +SKIP
>>> tab_pg.columns # doctest: +ELLIPSIS +SKIP
Columns([('cat', 'int4'), ...])
"""
def _get_name(self):
"""Private method to return the name of table"""
return self._name
def _set_name(self, new_name):
"""Private method to set the name of table
:param new_name: the new name of table
:type new_name: str
"""
old_name = self._name
cur = self.conn.cursor()
cur.execute(sql.RENAME_TAB.format(old_name=old_name,
new_name=new_name))
self.conn.commit()
cur.close()
name = property(fget=_get_name, fset=_set_name,
doc="Set and obtain table name")
def __init__(self, name, connection, key='cat'):
self._name = name
self.conn = connection
self.key = key
self.columns = Columns(self.name,
self.conn,
self.key)
self.filters = Filters(self.name)
def __repr__(self):
"""
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> tab_sqlite = Table(name='census',
... connection=sqlite3.connect(get_path(path)))
>>> tab_sqlite
Table(u'census')
"""
return "Table(%r)" % (self.name)
def __iter__(self):
cur = self.execute()
return (cur.fetchone() for _ in range(self.__len__()))
def __len__(self):
"""Return the number of rows"""
return self.n_rows()
[docs] def drop(self, cursor=None, force=False):
"""Method to drop table from database
:param cursor: the cursor to connect, if None it use the cursor
of connection table object
:type cursor: Cursor object
:param force: True to remove the table, by default False to print
advice
:type force: bool
"""
cur = cursor if cursor else self.conn.cursor()
if self.exist(cursor=cur):
used = db_table_in_vector(self.name)
if used is not None and len(used) > 0 and not force:
print(_("Deleting table <%s> which is attached"
" to following map(s):") % self.name)
for vect in used:
warning("%s" % vect)
print(_("You must use the force flag to actually"
" remove it. Exiting."))
else:
cur.execute(sql.DROP_TAB.format(tname=self.name))
[docs] def n_rows(self):
"""Return the number of rows
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> tab_sqlite = Table(name='census',
... connection=sqlite3.connect(get_path(path)))
>>> tab_sqlite.n_rows()
2537
"""
cur = self.conn.cursor()
cur.execute(sql.SELECT.format(cols='Count(*)', tname=self.name))
number = cur.fetchone()[0]
cur.close()
return number
[docs] def execute(self, sql_code=None, cursor=None, many=False, values=None):
"""Execute SQL code from a given string or build with filters and
return a cursor object.
:param sql_code: the SQL code to execute, if not pass it use filters
variable
:type sql_code: str
:param cursor: the cursor to connect, if None it use the cursor
of connection table object
:type cursor: Cursor object
:param many: True to run executemany function
:type many: bool
:param values: The values to substitute into sql_code string
:type values: list of tuple
>>> import sqlite3
>>> path = '$GISDBASE/$LOCATION_NAME/PERMANENT/sqlite/sqlite.db'
>>> tab_sqlite = Table(name='census',
... connection=sqlite3.connect(get_path(path)))
>>> tab_sqlite.filters.select('cat', 'TOTAL_POP').order_by('AREA')
Filters(u'SELECT cat, TOTAL_POP FROM census ORDER BY AREA;')
>>> cur = tab_sqlite.execute()
>>> cur.fetchone()
(1856, 0)
"""
try:
sqlc = sql_code if sql_code else self.filters.get_sql()
cur = cursor if cursor else self.conn.cursor()
if many and values:
return cur.executemany(sqlc, values)
return cur.execute(sqlc)
except:
#import ipdb; ipdb.set_trace()
raise ValueError("The SQL is not correct:\n%r" % sqlc)
[docs] def exist(self, cursor=None):
"""Return True if the table already exist in the DB, False otherwise
:param cursor: the cursor to connect, if None it use the cursor
of connection table object
"""
cur = cursor if cursor else self.conn.cursor()
return table_exist(cur, self.name)
[docs] def insert(self, values, cursor=None, many=False):
"""Insert a new row
:param values: a tuple of values to insert, it is possible to insert
more rows using a list of tuple and parameter `many`
:type values: tuple
:param cursor: the cursor to connect, if None it use the cursor
of connection table object
:type cursor: Cursor object
:param many: True to run executemany function
:type many: bool
"""
cur = cursor if cursor else self.conn.cursor()
if many:
return cur.executemany(self.columns.insert_str, values)
return cur.execute(self.columns.insert_str, values)
[docs] def update(self, key, values, cursor=None):
"""Update a table row
:param key: the rowid
:type key: int
:param values: the values to insert without row id.
For example if we have a table with four columns:
cat, c0, c1, c2 the values list should
containing only c0, c1, c2 values.
:type values: list
:param cursor: the cursor to connect, if None it use the cursor
of connection table object
:type cursor: Cursor object
"""
cur = cursor if cursor else self.conn.cursor()
vals = list(values) + [key, ]
return cur.execute(self.columns.update_str, vals)
[docs] def create(self, cols, name=None, overwrite=False, cursor=None):
"""Create a new table
:param cols:
:type cols:
:param name: the name of table to create, None for the name of Table object
:type name: str
:param overwrite: overwrite existing table
:type overwrite: bool
:param cursor: the cursor to connect, if None it use the cursor
of connection table object
:type cursor: Cursor object
"""
cur = cursor if cursor else self.conn.cursor()
coldef = ',\n'.join(['%s %s' % col for col in cols])
if name:
newname = name
else:
newname = self.name
try:
cur.execute(sql.CREATE_TAB.format(tname=newname, coldef=coldef))
self.conn.commit()
except OperationalError: # OperationalError
if overwrite:
self.drop(force=True)
cur.execute(sql.CREATE_TAB.format(tname=newname,
coldef=coldef))
self.conn.commit()
else:
print("The table: %s already exist." % self.name)
cur.close()
self.columns.update_odict()
Help Index | Topics Index | Keywords Index | Full Index
© 2003-2018 GRASS Development Team, GRASS GIS 7.0.7svn Reference Manual