Basic instruction

There are some notes here.

Repository

The code is here.

Please check the README that I attached the docker-compose file. Then you can easily deploy a PostgreSQL server.

Basic commands

1. How to login to the PosgreSQL database?

   psql -U admin -w admin

2. Check all tables

   \dt

   For example:

   admin=# \dt
    List of relations
    Schema |   Name   | Type  | Owner 
   --------+----------+-------+-------
    public | docs     | table | admin
    public | models   | table | admin
    public | password | table | admin
   (3 rows)

3. Check the content from a table

   SELECT * FROM {table name};

   For example:

   admin=# SELECT * FROM docs;
    id |                                    path                 
   ----+-----------------------------------------------------------
     1 | bread_1.png
     2 | bread_2.png

4. Delete the table

   DROP TABLE {table name} ;

   For example:

   admin-# \dt
          List of relations
    Schema |     Name     | Type  | Owner 
   --------+--------------+-------+-------
    public | numpy_arrays | table | admin
    public | password     | table | admin
   (2 rows)
   
   admin=# DROP TABLE numpy_arrays ;
   DROP TABLE
   
   admin=# \dt
        List of relations
    Schema |   Name   | Type  | Owner 
   --------+----------+-------+-------
    public | password | table | admin
   (1 rows)

5. Create a table

   For storing numpy array:

   CREATE TABLE {table name} (
           id serial PRIMARY KEY,
           name varchar(100) NOT NULL
               );

   For normal table:

   CREATE TABLE {table name} (
                   id serial PRIMARY KEY,
                   path varchar(255) NOT NULL
               );

6. Delete the value from a table

   DELETE FROM {table_name} WHERE id={key};

   For example:

   admin=# SELECT * FROM password;
    id  |  name  
   -----+--------
      1 | 123123
    123 | 123456
   (2 rows)
   
   admin=# DELETE FROM password WHERE id=123;
   DELETE 1
   
   admin=# SELECT * FROM password;
    id |  name  
   ----+--------
     1 | 123123
   (1 row)

7. Insert a row into a table

   INSERT INTO {table_name} {key} VALUES {value};

   For example:

   INSERT INTO password (id, name) VALUES (1, '123456');
   or 
   INSERT INTO password (id, name) VALUES (1, 123456);

   However, if you insert letters, then you need to use '' for value.

   INSERT INTO password (id, name) VALUES (1, 'test');

Python API

To store the numpy array into DB.

Check from this post

import psycopg2 as psy
import numpy as np
import pickle
import time

if __name__ == "__main__":

    dbname = "admin"
    user = "admin"
    password = "admin"
    host = "0.0.0.0"
    port = "5432"

    #### -----------
    db_connect_kwargs = {
        'dbname': dbname,
        'user': user,
        'password': password,
        'host': host,
        'port': port
    }

    connection = psy.connect(**db_connect_kwargs)
    connection.set_session(autocommit=True)
    cursor = connection.cursor()

    cursor.execute(
        """
        DROP TABLE IF EXISTS numpy_arrays;
        CREATE TABLE numpy_arrays (
            uuid VARCHAR PRIMARY KEY,
            np_array_bytes BYTEA
        )
        """
    )
    #### -----------

    some_array = np.random.rand(1500,550)
    some_array_uuid = 'some_array'

    res = cursor.execute(
        """
        INSERT INTO numpy_arrays(uuid, np_array_bytes)
        VALUES (%s, %s)
        """,
        (some_array_uuid, pickle.dumps(some_array))
    )

    print(res)

    uuid = 'some_array'
    for i in range(10):
        lt = time.time()
        cursor.execute(
            """
            SELECT np_array_bytes
            FROM numpy_arrays
            WHERE uuid=%s
            """,
            (uuid,)
        )
        some_array = pickle.loads(cursor.fetchone()[0])
        print("Take : {}".format(time.time()-lt))
    print("---")
    print(type(some_array))

Output:

None
Take : 0.05200076103210449
Take : 0.04594135284423828
Take : 0.04079699516296387
Take : 0.041504859924316406
Take : 0.041455984115600586
Take : 0.044516563415527344
Take : 0.03917431831359863
Take : 0.04490780830383301
Take : 0.03983473777770996
Take : 0.041825056076049805

Troubleshooting

Docker Container

1. Error response from daemon: driver failed programming external connectivity on endpoint database: Error starting userland proxy: listen tcp 0.0.0.0:5432: bind: address already in use

   Solution:

   systemctl stop postgresql