sqlcommenter

Introduction

sqlcommenter-sqlalchemy provides a factory to create before_cursor_execute, called BeforeExecuteFactory

We provide a BeforeExecuteFactory that takes options such as

CommenterCursorFactory(with_opencensus=<True or False>)

We provide options such as with_opencensus because

Requirements

Python X: any version of Python that is supported by SQLAlchemy
OpenCensus optionally

Installation

Pip
Source

pip3 install google-cloud-sqlcommenter

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

and then we shall perform the following imports in our source code:

BeforeExecuteFactory

BeforeExecuteFactory is a factory that creates a before_cursor_execute hook to your engine to grab information about your application and augment it as a comment to your SQL statement.

from sqlalchemy import create_engine, event from sqlcommenter.sqlalchemy.executor import BeforeExecuteFactory  engine = create_engine(...) # Create the engine with your dialect of SQL event.listen(engine, 'before_cursor_execute', BeforeExecuteFactory(), retval=True) engine.execute(...) # comment will be appended to SQL before execution

NOTE Please ensure that you set retval=True when listening for events

and this will produce such output on for example a Postgresql database logs:

2019-06-30 18:01:16.315 PDT [96973] LOG:  statement: SELECT * FROM polls_question /*traceparent='00-ade4c36dc5e43b503a5bba237ea11746-578a74a562044332-01'*/

with_openCensus=True

To enable the comment cursor to also attach information about the current OpenCensus span (if any exists), pass in option with_opencensus=True when invoking BeforeExecuteFactory, so

engine = create_engine("postgresql://:[email protected]:5432/quickstart_py") event.listen(engine, 'before_cursor_execute', BeforeExecuteFactory(with_opencensus=True), retval=True) engine.execute(...) # comment will be appended to SQL before execution

NOTE Please ensure that you set retval=True when listening for events

Fields

Field	Description	Included by default
`db_driver`	The underlying database driver e.g. `'psycopg2'`	❌
`db_framework`	The version of SQLAlchemy in the form `'sqlalchemy:<sqlalchemy_version>'`	❌
`traceparent`	The W3C TraceContext.Traceparent field of the OpenCensus trace – optionally defined with `with_opencensus=True`	❌
`tracestate`	The W3C TraceContext.Tracestate field of the OpenCensus trace – optionally defined with `with_opencensus=True`	❌

End to end examples

Source code

#!/usr/bin/env python3  from sqlalchemy import create_engine, event from google.cloud.sqlcommenter.sqlalchemy.executor import BeforeExecuteFactory  def main():     tracer = Tracer(exporter=NoopExporter, sampler=AlwaysOnSampler())     engine = create_engine(DB_URL)      listener = BeforeExecuteFactory(with_opencensus=True)     event.listen(engine, 'before_cursor_execute', listener, retval=True)      with tracer.span():         result = engine.execute('SELECT * FROM polls_question')         for row in result:             print(row)  if __name__ == '__main__':     main()

#!/usr/bin/env python3  from sqlalchemy import create_engine, event from google.cloud.sqlcommenter.sqlalchemy.executor import BeforeExecuteFactory  DB_URL = '...'  # DB connection info  def main():     engine = create_engine(DB_URL)      listener = BeforeExecuteFactory(with_db_framework=True)     event.listen(engine, 'before_cursor_execute', listener, retval=True)      result = engine.execute('SELECT * FROM polls_question')     for row in result:         print(row)  if __name__ == '__main__':     main()

#!/usr/bin/env python3  from sqlalchemy import create_engine, event from google.cloud.sqlcommenter.sqlalchemy.executor import BeforeExecuteFactory  DB_URL = '...'  # DB connection info  def main():     engine = create_engine(DB_URL)      listener = BeforeExecuteFactory(with_db_driver=True)     event.listen(engine, 'before_cursor_execute', listener, retval=True)      result = engine.execute('SELECT * FROM polls_question')     for row in result:         print(row)  if __name__ == '__main__':     main()

python3 main.py (1, 'Wassup?', datetime.datetime(2019, 5, 30, 13, 51, 12, 910545, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-420, name=None))) (2, 'Wassup?', datetime.datetime(2019, 5, 30, 13, 57, 45, 905771, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-420, name=None))) (3, 'Wassup?', datetime.datetime(2019, 5, 30, 13, 57, 46, 908185, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-420, name=None))) (4, 'Wassup?', datetime.datetime(2019, 5, 30, 13, 57, 47, 557196, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-420, name=None))) (5, 'Wassup?', datetime.datetime(2019, 5, 30, 13, 57, 47, 853424, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-420, name=None)))

Results

Examining our Postgresql server logs

2019-07-18 14:10:15.228 -03 [30071] LOG:  statement: SELECT * FROM polls_question /*traceparent='00-bf66750ad4c76f614c0a99d843758cbb-e6b27c3caf35de73-01'*/

2019-07-18 14:11:19.576 -03 [30108] LOG:  statement: SELECT * FROM polls_question /*db_framework='sqlalchemy%3A1.3.5'*/

2019-07-18 14:03:33.426 -03 [29858] LOG:  statement: SELECT * FROM polls_question /*db_driver='psycopg2'*/

With flask

When coupled with the web framework flask, we still provide middleware to correlate your web applications with your SQL statements from sqlalchemy. Please see this end-to-end guide below:

References

Resource	URL
sqlcommenter-sqlalchemy on PyPi	https://pypi.org/project/google-cloud-sqlcommenter
sqlcommenter-sqlalchemy on Github	https://github.com/google/sqlcommenter
OpenCensus	https://opencensus.io/
OpenCensus SpanID	https://opencensus.io/tracing/span/spanid
OpenCensus TraceID	https://opencensus.io/tracing/span/traceid

SQLAlchemy