sqlcommenter

Python modules for popular projects that add meta info to your SQL queries as comments.

• Django
• SQLAlchemy
• Psycopg2

Local Install

pip3 install --user google-cloud-sqlcommenter

If you'd like to record the OpenCensus trace context as well, just install it:

pip3 install google-cloud-sqlcommenter[opencensus]

If you'd like to record the OpenTelemetry trace context as well (Python 3+ only), just install it:

pip3 install google-cloud-sqlcommenter[opentelemetry]

Usage

Django

Add the provided Django middleware to your Django project's settings. All queries executed within the standard request→response cycle will have the SQL comment prepended to them.

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

which when viewed say on Postgresql logs, produces

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'*/

If you want the OpenCensus attributes included, you must set the SQLCOMMENTER_WITH_OPENCENSUS setting to True.

If you want the OpenTelemetry attributes included, you must set the SQLCOMMENTER_WITH_OPENTELEMETRY setting to True.

You cannot use OpenTelemetry and OpenCensus together, as they use the same attributes.

SQLAlchemy

Attach the provided event listener to the before_cursor_execute event of the database engine, with retval=True. All queries executed with that engine will have the SQL comment prepended to them.

import sqlalchemy
from google.cloud.sqlcommenter.sqlalchemy.executor import BeforeExecuteFactory

engine = sqlalchemy.create_engine(...)
listener = BeforeExecuteFactory(
    with_db_driver=True,
    with_db_framework=True,
    # you may use one of opencensus or opentelemetry
    with_opencensus=True,
    with_opentelemetry=True,
)
sqlalchemy.event.listen(engine, 'before_cursor_execute', listener, retval=True)
engine.execute(...) # comment will be added before execution

which will produce a backend log such as when viewed on Postgresql

2019-05-28 11:52:06.527 PDT [64087] LOG:  statement: SELECT * FROM polls_question
/*db_driver='psycopg2',framework='sqlalchemy%3A1.3.4',
traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01',
tracestate='congo%%3Dt61rcWkgMzE%%2Crojo%%3D00f067aa0ba902b7'*/

Psycopg2

Use the provided cursor factory to generate database cursors. All queries executed with such cursors will have the SQL comment prepended to them.

import psycopg2
from google.cloud.sqlcommenter.psycopg2.extension import CommenterCursorFactory

cursor_factory = CommenterCursorFactory(
    with_db_driver=True,
    with_dbapi_level=True,
    with_dbapi_threadsafety=True,
    with_driver_paramstyle=True,
    with_libpq_version=True,
    # you may use one of opencensus or opentelemetry
    with_opencensus=True,
    with_opentelemetry=True,
)
conn = psycopg2.connect(..., cursor_factory=cursor_factory)
cursor = conn.cursor()
cursor.execute(...) # comment will be added before execution

which will produce a backend log such as when viewed on Postgresql

2019-05-28 02:33:25.287 PDT [57302] LOG:  statement: SELECT * FROM
polls_question /*db_driver='psycopg2%%3A2.8.2%%20%%28dt%%20dec%%20pq3%%20ext%%20lo64%%29',
dbapi_level='2.0',dbapi_threadsafety=2,driver_paramstyle='pyformat',
libpq_version=100001,traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01',
tracestate='congo%%3Dt61rcWkgMzE%%2Crojo%%3D00f067aa0ba902b7'*/

Options

With Django, each option translates to a Django setting by uppercasing it and prepending SQLCOMMENTER_. For example, with_framework is controlled by the django setting SQLCOMMENTER_WITH_FRAMEWORK.

[1] opencensus

For opencensus to work correctly, note that OpenCensus for Python must be installed in the python environment.

[2] opentelemetry

For opentelemetry to work correctly, note that OpenTelemetry for Python must be installed in the python environment.

[3] traceparent/tracestate

Because the W3C TraceContext's traceparent and tracestate are quite ephemeral per request, including these attributes can have a negative impact on query caching.