Posts

Showing posts from April, 2018

SQLAlchemy (Flask) count model instances by unique values

One comes to a task that has to do with counting items in a database. We will describe the right approach here. Despite being so obvious I did not find much of the docs for junior developers to watch and learn. Here is a sample task and solution:
Let's assume we have a model like so:
classCycle(db.Model):id=db.Column(db.Integer,primary_key=True)object_id=db.Column(db.String,nullable=False) Sample date populated into it will be:
{id:1,object_id:'unique1'},{id:2,object_id:'unique1'},{id:3,object_id:'unique2'},{id:4,object_id:'unique2'},{id:5,object_id:'unique2'},{id:6,object_id:'unique3'} We need to count unique model instances with same object_id. To achieve this relatively simple task one would go straightforward. E.g. Fetch all the Cycle instances with a simple query and then iterate through them via for cycle. Looks like so:
objects=Cycle.query.all()cycles_unique={}forobjectinobjects:ifobject.object_idincycles_unique.items():cycles_un…

PostgreSQL DB with pgAdmin4 access through SSH tunnel

Image
Despite using console most of the time I have a preference to edit PostgreSQL databases through UI. Especially when it comes to remote side. Usually one can access this through $ psql command. However this tends to writing raw SQL queries and a lot of typing in overall.
Here is a way to do it with UI. First one needs to make a tunnel.Command is fairly simple:
ssh -fNg -L 5555:localhost:5432{username}@{host.com} One has a tunnel afterwards. This command opens a SSH connection in the background mapping your local port 5555 to your server’s port 5432 (Postgres’ default port). To understand one can observe the meaning of the flags via $ man ssh to see what each of these flags doing.
 It can be accessed via localhost tools like pgAdmin4 at localhost and port 5555
DB config would look like so: