README.md

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.

importsqlalchemyfromgoogle.cloud.sqlcommenter.sqlalchemy.executorimportBeforeExecuteFactoryengine=sqlalchemy.create_engine(...)
listener=BeforeExecuteFactory(
with_db_driver=True,
with_db_framework=True,
# you may use one of opencensus or opentelemetrywith_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.

fromfastapiimportFastAPIfromgoogle.cloud.sqlcommenter.fastapiimportSQLCommenterMiddlewareapp=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.

importpsycopg2fromgoogle.cloud.sqlcommenter.psycopg2.extensionimportCommenterCursorFactorycursor_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 opentelemetrywith_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.