Skip to content

Latest commit

 

History

History
166 lines (126 loc) · 13.2 KB

README.md

File metadata and controls

166 lines (126 loc) · 13.2 KB
Raw

PyPI - Python Version PyPI PyPI - Django Version

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

FastAPI

If you are using SQLAlchemy with FastAPI, add the middleware to get: framework, app_name, controller and route.

from fastapi import FastAPI
from google.cloud.sqlcommenter.fastapi import SQLCommenterMiddleware

app = FastAPI()

app.add_middleware(SQLCommenterMiddleware)

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.

Options Included by default? Django SQLAlchemy psycopg2 Notes
with_framework ✔️ Django version Flask version Flask version
with_controller ✔️ Django view Flask endpoint Flask endpoint
with_route ✔️ 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][3]
with_opentelemetry W3C TraceContext.Traceparent, W3C TraceContext.Tracestate W3C TraceContext.Traceparent, W3C TraceContext.Tracestate W3C TraceContext.Traceparent, W3C TraceContext.Tracestate [2][3]
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.

[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.