• Specification
• Go
  • http-tags
  • gorrila/mux
  • database/sql
• Python
  • Django
    • Locally
    • Google Cloud Platform
    • Amazon Web Services
  • psycopg2
  • SQLAlchemy
  • Flask
• Php
  • Laravel
• Ruby
  • Ruby on Rails
• Node.js
  • Knex.js
  • Sequelize.js
  • Express.js
• Java
  • Hibernate
  • Spring
• Databases
  • Postgresql
  • MySQL
  • MariaDB
• FAQ

[]

[aws-logo.png]

[gcp-logo.png]

[locally-logo.png]

• Introduction
• Requirements
• Installation
  • pip install
  • Source
• Fields
  • Sample log entry
  • Expected fields
• References

Introduction

This package is in the form of Django middleware whose purpose is to augment a SQL statement right before execution, with information about the controller and user code to help with later making database optimization decisions, after those statements are examined from the database server’s logs.

The middleware uses Django’s connection.execute_wrapper.

Requirements

The middleware uses Django’s connection.execute_wrapper and therefore requires Django 2.0 or later (which support various versions of Python 3).

To record OpenCensus information opencensus-ext-django, version 0.7 or greater, is required.

Installation

This middleware can be installed by any of the following:

pip3 install google-cloud-sqlcommenter

git clone https://github.com/google/sqlcommenter.git
cd python/sqlcommenter-python && python3 setup.py install

Enabling it

Please edit your settings.py file to include google.cloud.sqlcommenter.django.middleware.SqlCommenter in your MIDDLEWARE section like this:

--- settings.py
+++ settings.py
@@ -1,3 +1,4 @@
 MIDDLEWARE = [
+  'google.cloud.sqlcommenter.django.middleware.SqlCommenter',
   ...
 ]

Fields

In the database server logs, the comment’s fields are:

• comma separated key-value pairs e.g. controller='index'
• values are SQL escaped i.e. key='value'
• URL-quoted except for the equals(=) sign e.g route='%5Epolls/%24'. so should be URL-unquoted when being consumed

Sample log entry

After making a request into the middleware-enabled polls web-app.

2019-05-28 11:54:50.780 PDT [64128] LOG:  statement: INSERT INTO "polls_question"
("question_text", "pub_date") VALUES
('Wassup?', '2019-05-28T18:54:50.767481+00:00'::timestamptz) RETURNING "polls_question"."id"
/*controller='index',framework='django%3A2.2.1',route='%5Epolls/%24'*/

Expected Fields

End to end examples

Examples are based off the polls app from the Django introduction tutorial.

Source code

# settings.py

MIDDLEWARE = [
    'sqlcommenter.django.middleware.SqlCommenter',
    ...
]

# polls/urls.py

from django.urls import path
from . import views

urlpatterns = [
    path('', views.index, name='index'),
]

# polls/views.py

from django.http import HttpResponse
from .models import Question

def index(request):
    count = Question.objects.count()
    return HttpResponse(f"There are {count} questions in the DB.\n")

# settings.py
INSTALLED_APPS = [
    'opencensus.ext.django',
    ...
]


MIDDLEWARE = [
    'opencensus.ext.django.middleware.OpencensusMiddleware',
    'sqlcommenter.django.middleware.SqlCommenter',
    ...
]

OPENCENSUS = {
    'TRACE': {
        'SAMPLER': 'opencensus.trace.samplers.AlwaysOnSampler()',
    }
}

SQLCOMMENTER_WITH_CONTROLLER = False
SQLCOMMENTER_WITH_FRAMEWORK = False
SQLCOMMENTER_WITH_ROUTE = False
SQLCOMMENTER_WITH_OPENCENSUS = True

# polls/urls.py

from django.urls import path
from . import views

urlpatterns = [
    path('', views.index, name='index'),
]

# polls/views.py

from django.http import HttpResponse
from .models import Question

def index(request):
    count = Question.objects.count()
    return HttpResponse(f"There are {count} questions in the DB.\n")

# settings.py

MIDDLEWARE = [
    'sqlcommenter.django.middleware.SqlCommenter',
    ...
]

SQLCOMMENTER_WITH_CONTROLLER = False
SQLCOMMENTER_WITH_FRAMEWORK = False
SQLCOMMENTER_WITH_ROUTE = False
SQLCOMMENTER_WITH_APP_NAME = True

# polls/urls.py

from django.urls import path
from . import apps, views

app_name = apps.PollsConfig.name

urlpatterns = [
    path('', views.index, name='index'),
]

# polls/views.py

from django.http import HttpResponse
from .models import Question

def index(request):
    count = Question.objects.count()
    return HttpResponse(f"There are {count} questions in the DB.\n")

# settings.py

MIDDLEWARE = [
    'sqlcommenter.django.middleware.SqlCommenter',
    ...
]

SQLCOMMENTER_WITH_CONTROLLER = False
SQLCOMMENTER_WITH_FRAMEWORK = False
SQLCOMMENTER_WITH_ROUTE = False
SQLCOMMENTER_WITH_DB_DRIVER = True

# polls/urls.py

from django.urls import path
from . import views

urlpatterns = [
    path('', views.index, name='index'),
]

# polls/views.py

from django.http import HttpResponse
from .models import Question

def index(request):
    count = Question.objects.count()
    return HttpResponse(f"There are {count} questions in the DB.\n")

From the command line, we run the django development server in one terminal:

# python manage.py runserver

And we use curl to make an HTTP request in another:

# curl http://127.0.0.1:8000/polls/

Results

Examining our Postgresql server logs, with the various options

2019-07-19 14:27:51.370 -03 [41382] LOG:  statement: SELECT COUNT(*) AS "__count" FROM "polls_question"
/*controller='index',framework='django%3A2.2.3',route='polls/'*/

2019-07-19 17:39:27.430 -03 [46170] LOG:  statement: SELECT COUNT(*) AS "__count" FROM "polls_question"
/*traceparent='00-fd720cffceba94bbf75940ff3caaf3cc-4fd1a2bdacf56388-01'*/

2019-07-19 15:31:33.681 -03 [42962] LOG:  statement: SELECT COUNT(*) AS "__count" FROM "polls_question"
/*app_name='polls'*/

2019-07-19 14:47:53.066 -03 [41602] LOG:  statement: SELECT COUNT(*) AS "__count" FROM "polls_question"
/*db_driver='django.db.backends.postgresql'*/

References