Database

We use 2 database, PostgreSQL and Redis to construct our service

PostgreSQL: save trajectory and ROI datasets

Redis: save TRD dataset for faster accessing

Link to PostgreSQL

• Use cmd to communicate with db

  psql -h localhost -U {id}
  ####語法###
  \dt trajectory.*    #list all tables in schema trajecroty
  \dt roi.*           #list all tables in schema roi
  \dn                 #list schemas
  \q                  #exit pgsql
  

• Or, use python to communicate with db

  #!myenv/bin/python
  import psycopg2cffi
  conn_string = "host='127.0.0.1' dbname='DBNAME' user='USER' password='PASWD'"
  conn = psycopg2cffi.connect(conn_string)
  query = "SELECT a,b From c;"
  cur.execute(query)
  rows = [r for r in cur]
  for row in rows:
    a = row[0]
    b = row[1]
    print ' a = %s , b = %s' % ( a, b )
  

• example

  save as mysever.py

  #!myenv/bin/python
  
  from flask import Flask, jsonify
  from flask import abort
  from flask import make_response
  from flask import request
  import psycopg2cffi
  
  app = Flask(__name__)
  
  @app.route('/datasets/trajectory', methods=['GET'])
  def list_datasets():
    conn_string = "host='localhost' dbname='dbname' user='user' password='passwd'"
    conn = pg.connect(conn_string)
    query = "select relname as table from pg_stat_user_tables where schemaname = 'trajectory';"
    cur.execute(query)
    rows = [r for r in cur]
    datasets = []
    for row in rows:
        datasets.append(row[0])
    return jsonify({'datasets':datasets})
  
  if __name__ == '__main__':
    app.run(port=5566,debug=True)
  

  Execute myserver.py

  $ chmod a+x mysever.py
  $ ./mysever.py
  $ curl 127.0.0.1:5566/datasets/trajectory
  

Addititional

• Import data

  import csv file to database

  \copy '{table name}' from '{file name}' DELIMITER ',' CSV header