Skip to main content

Augment SQL statements with meta information about frameworks and the running environment.

Project description

sqlcommenter

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

Local Install

pip3 install --user google-cloud-sqlcommenter

If you'd like to record some OpenCensus information as well, just install it:

pip3 install opencensus

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 and include 'opencensus.ext.django.middleware.OpencensusMiddleware' before 'google.cloud.sqlcommenter.django.middleware.SqlCommenter', in your MIDDLEWARE setting.

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, with_opencensus=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, with_opencensus=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.

Options Included by default? Django SQLAlchemy psycopg2 Notes
with_framework :heavy_check_mark: Django version Flask version Flask version
with_controller :heavy_check_mark: Django view Flask endpoint Flask endpoint
with_route :heavy_check_mark: Django route Flask route Flask route
with_app_name Django app name
with_opencensus W3C TraceContext.Traceparent, W3C TraceContext.Tracestate W3C TraceContext.Traceparent, W3C TraceContext.Tracestate W3C TraceContext.Traceparent, W3C TraceContext.Tracestate [1]
with_db_driver Django DB engine SQLAlchemy DB driver psycopg2 version
with_db_framework SQLAlchemy version
with_dbapi_threadsafety psycopg2 thread safety
with_dbapi_level psycopg2 api level
with_libpq_version psycopg2 libpq version
with_driver_paramstyle psycopg2 parameter style

[1] opencensus

For opencensus to work correctly, note that:

  • OpenCensus for Python must be installed in the python environment.
  • Because the W3C TraceContext's traceparent and tracestate are quite ephemeral per request, including these attributes can have a negative impact on query caching.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for google-cloud-sqlcommenter, version 0.1.2
Filename, size File type Python version Upload date Hashes
Filename, size google_cloud_sqlcommenter-0.1.2-py2.py3-none-any.whl (27.0 kB) File type Wheel Python version py2.py3 Upload date Hashes View
Filename, size google-cloud-sqlcommenter-0.1.2.tar.gz (13.2 kB) File type Source Python version None Upload date Hashes View

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page