Extraction of data from database#

Option 1#

To extract data from the database, either dev, acc or prod, you have to connect to the right container using the container id. The first step is to extract the data from the database, to the container storage. The second step is to extract the csv file from the container to your local storage. The identifying of container id, and extracting the data can be done with the syntax below:

                                docker ps -a 
# go into the docker containre
docker exec -it {container_id} /bin/bash 
#log in to the sql pdms interface database
psql -U pdms_interface_writer --dbname pdms_interface
#copy the needed table with 
\copy {table} to '/absolute/path/file.csv' csv header;
#or using a sql statement 
\copy (select * from {table} where ..) to '/absolute/path/file.csv' csv header;
#leave database and docker container and extract to padtwin1-l server using
docker cp {container_id}:/aboslute/path/file.csv /path/on/padtwin/file.csv

                            

Option 2#

For client analysts wishing to do adhoc queries and analysis, limited access can be granted to the database which stores the output of the PDMS model. It is important the querying of this database is kept to a minimum to avoid conflict with the nervecentre process.

You can do this by:

installing the psycopg2 python package to connect to postgres databases.
get the password from Maureen - note you will need to add ‘md5’ to the start of the password for it to connect’.
connect using the code below

                                # Import required packaged
import psycopg2
import pandas as pd

# Connect to the PostgreSQL database (replace port with 15432 for the acceptance environment, 5432 is production)
conn = psycopg2.connect("host=padtwin1-l.hissdom dbname=pdms_interface port=<> user=**[replace]** password=**[replace]** ")
# Create a new cursor
cur=conn.cursor()

# A function that takes in a PostgreSQL query and outputs a pandas database
df = pd.read_sql_query("Select * FROM pdms_details", conn)
# Close the cursor and connection to so the server can allocate
# bandwidth to other requests
cur.close()
conn.close()


                            