Download this Jupyter notebook from github

Establish a database connection and retrieve the inventory and User settings

Internally, geoslurp uses the python sqlalchemy tools to establish a connection with the database. The information provided is therefore similar (hostname+port, username, password). This information can be provided directly to the constructor of GeoslurpConnector. Alternatively, the convenience function geoslurpConnect uses local settings from the file .geoslurp_lastused.yaml in the users’s home. This has the advantage that possible sensitive information (logins, passwords) does not need to be stored in notebooks such as these.

Read-only user versus priviliged user

For the majority of the query operations, it is not needed to have write privileges to the database tables. For this purpose, it is recommended to access the database with a readonly user to avoid making unwanted changes to the tables. How this is done is shown below.

Using geoslurpConnect

There are several ways to specify passwords for use with the convenience function geoslurpConnect, some which are more secure than others.

[1]:

#Connect to a database using the settings in ${HOME}/.geoslurp_lastused.yaml
from geoslurp.db import geoslurpConnect

#connect to the database with the normal user as specified in ${HOME}/.geoslurp_lastused.yaml
dbcon=geoslurpConnect(readonlyuser=False)

#Connect to a database with the readonly user as specified in ${HOME}/.geoslurp_lastused.yaml
dbconReadOnly=geoslurpConnect()

Alternatively one can use the class GeoslurpConnector and provide all connections details manually

This will not consult the file ${HOME}/.geoslurp_lastused.yaml and can look like:

from geoslurp.db import GeoslurpConnector dbcon=GeoslurpConnector(host="geoslurphost",user="yourusername")

The above will prompt for a password but if this is not desired one can non-interactively initiate a connection as:

dbcon=GeoslurpConnector(host="geoslurphost",user="slurpy",passwd="supersecretpassword")

Note that this not recommended for code which is likely to be shared later, such as for example jupyter notebooks such as this.

Loading the Dataset Inventory

The Inventory class is coupled to the admin.inventory table in the database, and contains information on registered datasets. One can load the data the table by initializing a Inventory class instance with a database connection such as obtained from above. Entries (i.e.) can be requested or iterated over as shown below.

[2]:

from geoslurp.db import Inventory


inventory=Inventory(dbcon)

entry=inventory["globalgis.gshhs_c"]
print(entry.scheme, entry.dataset, entry.pgfunc,entry.owner,entry.lastupdate,entry.version,entry.cache,entry.datadir,entry.data)

dsets=[ds.dataset for ds in inventory]
print("found %d registered datasets"%len(dsets))

globalgis gshhs_c None roelof 2019-02-27 09:47:02.441119 (0, 0, 0) None None {'GSHHGversion': [2, 3, 7]}
found 150 registered datasets

Setting and getting usersettings from the database

In contrast to settings which are specific for a host and user, the database also contains a table, admin.settings which contains settings which are specific to the server and user. These can be loaded by initializing a Settings class. Note that the read-only user does not have any settings stored in the database and has no read permission to the table.

Default settings

The admin.settings table has a default entry which contains settings which are valid for all users, unless they are overruled by the user themselves.

[3]:

from geoslurp.db import Settings
import random

conf=Settings(dbcon)

#to show the user settings:
#conf.show()

# try setting an entry
testvalue=random.random()
conf["test"]=testvalue
#synchronize the settings with the database
conf.update()

#load the settings from the database
conf2=Settings(dbcon)
if testvalue == conf2["test"]:
    print("succesfully set and synchronize an user setting")

#clean up after ourselves
del conf["test"]

succesfully set and synchronize an user setting

Storing and retrieving Authentication details

To enable non-interactive downloading, users may need to access webservices which require authentication details. These authentication details are also stored in the settings table of the database, but as this is sensitive information, the authentication data is encrypted in the database. The encryption is based on the user’s password, which is unknown to the database (only the hash is known to the database). Consequently, the authentication details can only be decrypted on clients. The implication of this is that it is not possible to retrieve the authentication data, when a user forgets their password.

The Credentials is a named tuple which is used to store the authentication details.

[6]:

#store new authentication details
from geoslurp.db import Credentials
import string


passw=random.choice(string.ascii_letters)


testauth=Credentials(alias="servicex",user="donny",passw=passw)

conf.updateAuth(testauth)

conf2=Settings(dbcon)
cred2=conf2.authCred("servicex")
if cred2.passw == passw:
    print("succesfully stored and retrieved authentication credentials")

#clean up
conf.delAuth("servicex")

succesfully stored and retrieved authentication credentials