Using JSON dataype for database queries – PostgreSQL

I was torn between using relational database and document database like mongodb, elastic search etc for certain requirement. In fact I started learning elasticsearch but getting some of the tasks done with elasticseach is very painful compared to how easy its done in relational database. This was impacting my timeline and I was spending way too much time doing troubleshooting.

This is when I came across json datatype in PostgreSQL. This is combinations of easiness of relational database and document query capabilities of elastic seach. Of course this would be bit slower than elasticseach (I have not benchmarked the performance, buts its guess) but it is OK as I am in prototype phase, I just need to validate my business case, once it flies, I will make a switch be relevant document database.

Meanwhile, lets us see how to work with JSON datatype of PostgreSQL

select clause

Because -> operator returns a JSON object, you can chain it with the operator ->> to retrieve a it in text format.

SELECT id, productid,  productdetails -> 'productBaseInfoV1' ->> 'productUrl' AS customer
FROM public.ecomm_productdetails;
SELECT id, productid,  productdetails -> 'productBaseInfoV1' -> 'productUrl' AS customer
FROM public.ecomm_productdetails;

where clause

Please note you always need to use final element with ->> since this needs to be matched with text field.

SELECT id, productid,  productdetails
FROM public.ecomm_productdetails
WHERE productdetails -> 'productBaseInfoV1' ->> 'title' = 'Apple iPhone 6 (Grey, 128 GB)';

 

SELECT id, productid,  productdetails
FROM public.ecomm_productdetails
WHERE productdetails -> 'productBaseInfoV1' ->> 'title' like 'OPPO%';

Troubleshooting django web application deployed using NGINX, uWSGI errors

For any type of error, first check if UWSGI is running fine ?

myusername@ubuntu-512:~$ sudo service uwsgi status
● uwsgi.service - uWSGI Emperor
   Loaded: loaded (/etc/systemd/system/uwsgi.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2020-09-09 08:39:27 IST; 9min ago
  Process: 1451 ExecStartPre=/bin/bash -c mkdir -p /run/uwsgi; chown myusername:www-data /run/uwsgi (code=exited, status=0/SUCCESS)
 Main PID: 1530 (uwsgi)
   Status: "The Emperor is governing 3 vassals"
    Tasks: 7
   Memory: 142.7M
      CPU: 1.874s
   CGroup: /system.slice/uwsgi.service
           ├─1530 /usr/local/bin/uwsgi --emperor /home/myusername/uwsgi/sites
           ├─1564 /usr/local/bin/uwsgi --ini website1.ini
           ├─1565 /usr/local/bin/uwsgi --ini website2.ini
           ├─1625 /usr/local/bin/uwsgi --ini website3.ini
           ├─2022 /usr/local/bin/uwsgi --ini website3.ini
           ├─2033 /usr/local/bin/uwsgi --ini website2.ini
           └─2035 /usr/local/bin/uwsgi --ini website1.ini

Sep 09 08:44:29 ubuntu-512 uwsgi[1530]: Input to details english quote We-accept-the-love-we-think-we-deserve.
Sep 09 08:44:29 ubuntu-512 uwsgi[1530]: context: success
Sep 09 08:44:29 ubuntu-512 uwsgi[1530]: context: post_type quote
Sep 09 08:44:29 ubuntu-512 uwsgi[1530]: [pid: 2033|app: 0|req: 7/7] 114.119.146.195 () {40 vars in 768 bytes} [Wed Sep  9 03:14:29 2020] GET
Sep 09 08:44:29 ubuntu-512 uwsgi[1530]: [pid: 2035|app: 0|req: 1/1] 93.158.161.64 () {38 vars in 515 bytes} [Wed Sep  9 03:14:29 2020] GET /
Sep 09 08:44:29 ubuntu-512 uwsgi[1530]: announcing my loyalty to the Emperor...
Sep 09 08:44:29 ubuntu-512 uwsgi[1530]: Wed Sep  9 08:44:29 2020 - [emperor] vassal website1.ini is now loyal
Sep 09 08:46:03 ubuntu-512 uwsgi[1530]: [pid: 2035|app: 0|req: 2/2] 114.119.145.68 () {40 vars in 748 bytes} [Wed Sep  9 03:16:03 2020] GET 
Sep 09 08:49:09 ubuntu-512 uwsgi[1530]: --- no python application found, check your startup logs for errors ---
Sep 09 08:49:09 ubuntu-512 uwsgi[1530]: [pid: 2022|app: -1|req: -1/5] 49.35.75.185 () {46 vars in 692 bytes} [Wed Sep  9 03:19:09 2020] GET

You might see any error here. In above log its showing error as --- no python application found, check your startup logs for errors ---. In this case, we need to check the wsgi.py file settings. Your wsgi file should look something like below. Please not the lines where I have added comment #added this

import os
import sys #added this

from django.core.wsgi import get_wsgi_application

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) #added this
sys.path.append(BASE_DIR) #added this

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'yourapp.settings')

application = get_wsgi_application()

 

Another way to trouble shoot is to check if .sock is created at following location

/run/uwsgi

If nothing gives any clue, try to see if you can run application manually using following command

sudo uwsgi --http 0.0.0.0:8400 --home /var/www/mywebsite1/venvft --chdir /var/www/mywebsite1/mysite07 --wsgi-file /var/www/mywebsite1/mysite07/wsgi.py

You might get different error here. e.g. some library is not installed. I got an error that model requests not found. I installed it in virtualenv as below

$virtualenv venvft
created virtual environment CPython3.6.4.final.0-64 in 453ms
creator CPython3Posix(dest=/var/www/yourapp/venvft, clear=False, global=False)
seeder FromAppData(download=False, pip=latest, setuptools=latest, wheel=latest, via=copy, app_data_dir=/home/user/.local/share/virtualenv/seed-app-data/v1.0.1)
activators BashActivator,CShellActivator,FishActivator,PowerShellActivator,PythonActivator,XonshActivator

$source venvft/bin/activate
$pip install django 
$pip install psycopg2
$pip install requests
deactivate

Another error I got is as below

*** WARNING: you are running uWSGI as root !!! (use the --uid flag) *** 
your server socket listen backlog is limited to 100 connections
your mercy for graceful operations on workers is 60 seconds
mapped 72920 bytes (71 KB) for 1 cores
*** Operational MODE: single process ***
Traceback (most recent call last):
  File "/var/www/website3/mysite07/wsgi.py", line 19, in 
    application = get_wsgi_application()
  File "/var/www/website3/venvft/lib/python3.6/site-packages/django/core/wsgi.py", line 12, in get_wsgi_application
    django.setup(set_prefix=False)
  File "/var/www/website3/venvft/lib/python3.6/site-packages/django/__init__.py", line 24, in setup
    apps.populate(settings.INSTALLED_APPS)
  File "/var/www/website3/venvft/lib/python3.6/site-packages/django/apps/registry.py", line 91, in populate
    app_config = AppConfig.create(entry)
  File "/var/www/website3/venvft/lib/python3.6/site-packages/django/apps/config.py", line 116, in create
    mod = import_module(mod_path)
  File "/usr/local/lib/python3.6/importlib/__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "", line 994, in _gcd_import
  File "", line 971, in _find_and_load
  File "", line 955, in _find_and_load_unlocked
  File "", line 665, in _load_unlocked
  File "", line 678, in exec_module
  File "", line 219, in _call_with_frames_removed
  File "/var/www/website3/venvft/lib/python3.6/site-packages/django/contrib/auth/apps.py", line 8, in 
    from .checks import check_models_permissions, check_user_model
  File "/var/www/website3/venvft/lib/python3.6/site-packages/django/contrib/auth/checks.py", line 8, in 
    from .management import _get_builtin_permissions
  File "/var/www/website3/venvft/lib/python3.6/site-packages/django/contrib/auth/management/__init__.py", line 9, in 
    from django.contrib.contenttypes.management import create_contenttypes
  File "/var/www/website3/venvft/lib/python3.6/site-packages/django/contrib/contenttypes/management/__init__.py", line 2, in 
    from django.db import (
  File "/var/www/website3/venvft/lib/python3.6/site-packages/django/db/migrations/__init__.py", line 1, in 
    from .migration import Migration, swappable_dependency  # NOQA
ModuleNotFoundError: No module named 'django.db.migrations.migration'
unable to load app 0 (mountpoint='') (callable not found or import error)
*** no app loaded. going in full dynamic mode ***
uWSGI running as root, you can use --uid/--gid/--chroot options
*** WARNING: you are running uWSGI as root !!! (use the --uid flag) *** 
*** uWSGI is running in multiple interpreter mode ***
spawned uWSGI worker 1 (and the only) (pid: 2699, cores: 1)

Here if you see in details, error is with the migrations. In above screen error is ModuleNotFoundError: No module named 'django.db.migrations.migration' common solution is to reinstall django. Here fortunately we are using virtualenv so I can remove and reinstall it without impacting other applications

$rm -rf venvft
$virtualenv venvft
created virtual environment CPython3.6.4.final.0-64 in 453ms
creator CPython3Posix(dest=/var/www/yourapp/venvft, clear=False, global=False)
seeder FromAppData(download=False, pip=latest, setuptools=latest, wheel=latest, via=copy, app_data_dir=/home/user/.local/share/virtualenv/seed-app-data/v1.0.1)
activators BashActivator,CShellActivator,FishActivator,PowerShellActivator,PythonActivator,XonshActivator

$source venvft/bin/activate
$pip install django 
$pip install psycopg2
pip install requests
deactivate

once its done I rerun the following

sudo uwsgi --http 0.0.0.0:8400 --home /var/www/mywebsite1/venvft --chdir /var/www/mywebsite1/mysite07 --wsgi-file /var/www/mywebsite1/mysite07/wsgi.py

Here again I got below error

sFatal Python error: Py_Initialize: Unable to get the locale encoding
ModuleNotFoundError: No module named 'encodings'

Current thread 0x00007f7b5b79d700 (most recent call first):
Aborted (core dumped)

however when I tried to access my website, it was working properly. so for now, I decided to ignore the error

django QuerySet examples

If you want to fetch whole table or if you want to fetch n number of rows (lets try 30 in current case)

context = MyModel.objects.all()
context = MyModel.objects.all()[:30]
## You can iterate over the output as well 
for e in MyModel.objects.all():
      print(e.headline)

if you want to fetch data in specific order. – sign indicates descending

context = MyModel.objects.all().order_by('pub_date')
context = MyModel.objects.all().order_by('-pub_date')

Filtering

(name__startswith='Beatles')
#like clause
filter(name__contains='Smith')
Product.objects.filter(name__search='Shiny')

exluding

 context = MyModel.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3)).exclude(headline='Hello')

if you want to fetch distinct values. Please note you can combineorder_by and distinct

context = MyModel.objects.all().distinct('blog')
context = MyModel.objects.all().order_by('pub_date').distinct('blog')

 

.values()

Returns a QuerySet that returns dictionaries, rather than model instances, when used as an iterable.

context = MyModel.objects.all().distinct('blog').values()