# sqltap - a library for profiling and introspecting SQL queries made through SQLAlchemy
sqltap helps you quickly understand:
* how many times a sql query is executed
* how much time your sql queries take
* where your application is issuing sql queries from
## Full Documentation
[http://sqltap.inconshreveable.com](http://sqltap.inconshreveable.com)
## Motivation
When you work at a high level of abstraction, it’s more common for your code to be inefficient and cause performance problems. SQLAlchemy’s ORM is excellent and gives you the flexibility to fix these inefficiencies if you know where to look! sqltap is a library that hooks into SQLAlchemy to collect metrics on all queries you send to your databases. sqltap can help you find where in your application you are generating slow or redundant queries so that you can fix them with minimal effort.
## Quickstart Example
import sqltap
profiler = sqltap.start()
session.query(Knights).filter_by(who_say = 'Ni').all()
statistics = profiler.collect()
sqltap.report(statistics, "report.html")
## WSGI integration
You can easily integrate SQLTap into any WSGI application. This will create an up-to-date report page at /\_\_sqltap\_\_ where
you can dynamically enable/disable the profiling so you can easily run it selectively in production. Integrating is super-easy:
import sqltap.wsgi
wsgi_app = sqltap.wsgi.SQLTapMiddleware(wsgi_app)
For example, to integrate with a Flask application:
import sqltap.wsgi
app.wsgi_app = sqltap.wsgi.SQLTapMiddleware(app.wsgi_app)
## Text report
Sometimes we want to profile sqlalchemy on remote servers. It's very
inconvenient to view HTML format SQLTap report on these servers. Alternatively,
SQLTap provides text profiling report in a human-readable way.
import sqltap
profiler = sqltap.start()
session.query(Knights).filter_by(who_say = 'Ni').all()
statistics = profiler.collect()
sqltap.report(statistics, "report.txt", report_format="text")
## Profiling Utilities (NEW in v1.1.0)
SQLTap now includes convenient profiling utilities that work anywhere in your application - tests, development, staging, or production. The `sqltap.profiling` module provides a simple context manager with comprehensive statistics and automatic HTML report generation.
### Basic Usage
from sqltap.profiling import sqltap_profiler
def test_my_endpoint():
with sqltap_profiler("my-test") as stats:
response = my_function()
# Assert on query count
assert stats.query_count <= 5
assert stats.total_time <= 1.0
### Detailed Analysis
Check for N+1 queries and analyze query patterns:
def test_check_n_plus_one():
with sqltap_profiler("n-plus-one-test") as stats:
# Your code that might have N+1 queries
posts = session.query(Post).all()
for post in posts:
author = post.author # Potential N+1!
# Check for N+1 queries
selects = stats.get_queries_by_type('SELECT')
for qg in selects:
assert qg.query_count <= 10, f"Potential N+1: {qg.sql_text[:100]}"
# Print detailed summary
print(stats.summary())
### Available Statistics
The `stats` object provides comprehensive query metrics:
# Summary statistics
stats.query_count # Total number of queries
stats.unique_queries # Number of unique SQL queries
stats.total_time # Total DB time in seconds
stats.mean_time # Mean query time
stats.median_time # Median query time
stats.min_time # Fastest query
stats.max_time # Slowest query
# Query analysis
stats.query_groups # All query groups (sorted by total time)
stats.get_queries_by_type('SELECT') # Filter by query type
stats.get_slowest_query() # Get the slowest query group
stats.summary() # Get formatted text summary
### Custom Report Location
Save reports to a custom directory:
with sqltap_profiler("my-test", report_dir="/tmp/reports") as stats:
response = my_function()
### Disable Report Generation
For CI environments or when you only need assertions:
with sqltap_profiler("my-test", save_report=False) as stats:
response = my_function()
assert stats.query_count <= 5
### Integration with pytest fixtures
SQLTap profiler works seamlessly with pytest fixtures, allowing you to profile queries across your entire test without wrapping test code in `with` statements:
import pytest
from sqltap.profiling import sqltap_profiler
@pytest.fixture
def db_profiler(request):
"""Fixture that profiles all DB queries in a test"""
with sqltap_profiler(request.node.name, save_report=False) as stats:
yield stats
def test_with_profiler(db_profiler):
# Your test code here
response = my_function()
# Assert on the profiler stats
assert db_profiler.query_count <= 10
# Log detailed summary for debugging
print(profile_db.summary())
### Using with Report Generation
Enable reports in your local environment:
@pytest.fixture
def db_profiler(request):
"""Fixture with automatic report generation"""
save_report = os.getenv("SAVE_SQLTAP_REPORTS", "false") == "true"
with sqltap_profiler(request.node.name, save_report=save_report) as stats:
yield stats
# Run tests with reports:
# SAVE_SQLTAP_REPORTS=true pytest tests/
def test_list_users(db_profiler):
users = db.query(User).all()
# Check for N+1 queries
selects = db_profiler.get_queries_by_type('SELECT')
for qg in selects:
assert qg.query_count <= 2, f"N+1 detected: {qg.sql_text[:80]}"
## Advanced Example
import sqltap
def context_fn(*args):
""" Associate the request path, unique id with each query statistic """
return (framework.current_request().path,
framework.current_request().id)
# start the profiler immediately
profiler = sqltap.start(user_context_fn=context_fn)
def generate_reports():
""" call this at any time to generate query reports reports """
all_stats = []
per_request_stats = collections.defaultdict(list)
per_page_stats = collections.defaultdict(list)
qstats = profiler.collect()
for qs in qstats:
all_stats.append(qs)
page = qstats.user_context[0]
per_page_stats[page].append(qs)
request_id = qstats.user_context[1]
per_request_stats[request_id].append(qs)
# report with all queries
sqltap.report(all_stats, "report_all.html")
# a report per page
for page, stats in per_page_stats.items():
sqltap.report(stats, "report_page_%s.html" % page)
# a report per request
for request_id, stats in per_request_stats.items():
sqltap.report(stats, "report_request_%s.html" % request_id)
## Testing
Run the sqltap tests:
python setup.py test
## License
Apache
Raw data
{
"_id": null,
"home_page": null,
"name": "sqltap-profiler",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.7",
"maintainer_email": "Bruno Cardoso <bruno@skywatch.com>",
"keywords": "sqlalchemy, sql, profiling, debugging, performance",
"author": null,
"author_email": "Bruno Cardoso <bruno@skywatch.com>",
"download_url": "https://files.pythonhosted.org/packages/8e/1f/019a0293ddd00389b73a7aeae6c07c0c2a2eb39de3d22a9c6ea41a25fdb4/sqltap_profiler-1.2.0.tar.gz",
"platform": null,
"description": "# sqltap - a library for profiling and introspecting SQL queries made through SQLAlchemy\n\nsqltap helps you quickly understand:\n\n * how many times a sql query is executed\n * how much time your sql queries take\n * where your application is issuing sql queries from\n\n\n\n## Full Documentation\n\n[http://sqltap.inconshreveable.com](http://sqltap.inconshreveable.com)\n\n## Motivation\n\nWhen you work at a high level of abstraction, it\u2019s more common for your code to be inefficient and cause performance problems. SQLAlchemy\u2019s ORM is excellent and gives you the flexibility to fix these inefficiencies if you know where to look! sqltap is a library that hooks into SQLAlchemy to collect metrics on all queries you send to your databases. sqltap can help you find where in your application you are generating slow or redundant queries so that you can fix them with minimal effort.\n\n## Quickstart Example\n\n import sqltap\n\n profiler = sqltap.start()\n session.query(Knights).filter_by(who_say = 'Ni').all()\n statistics = profiler.collect()\n sqltap.report(statistics, \"report.html\")\n\n## WSGI integration\n\nYou can easily integrate SQLTap into any WSGI application. This will create an up-to-date report page at /\\_\\_sqltap\\_\\_ where\nyou can dynamically enable/disable the profiling so you can easily run it selectively in production. Integrating is super-easy:\n\n import sqltap.wsgi\n\n wsgi_app = sqltap.wsgi.SQLTapMiddleware(wsgi_app)\n\nFor example, to integrate with a Flask application:\n\n import sqltap.wsgi\n\n app.wsgi_app = sqltap.wsgi.SQLTapMiddleware(app.wsgi_app)\n\n## Text report\n\nSometimes we want to profile sqlalchemy on remote servers. It's very\ninconvenient to view HTML format SQLTap report on these servers. Alternatively,\nSQLTap provides text profiling report in a human-readable way.\n\n import sqltap\n\n profiler = sqltap.start()\n session.query(Knights).filter_by(who_say = 'Ni').all()\n statistics = profiler.collect()\n sqltap.report(statistics, \"report.txt\", report_format=\"text\")\n\n## Profiling Utilities (NEW in v1.1.0)\n\nSQLTap now includes convenient profiling utilities that work anywhere in your application - tests, development, staging, or production. The `sqltap.profiling` module provides a simple context manager with comprehensive statistics and automatic HTML report generation.\n\n### Basic Usage\n\n from sqltap.profiling import sqltap_profiler\n\n def test_my_endpoint():\n with sqltap_profiler(\"my-test\") as stats:\n response = my_function()\n\n # Assert on query count\n assert stats.query_count <= 5\n assert stats.total_time <= 1.0\n\n### Detailed Analysis\n\nCheck for N+1 queries and analyze query patterns:\n\n def test_check_n_plus_one():\n with sqltap_profiler(\"n-plus-one-test\") as stats:\n # Your code that might have N+1 queries\n posts = session.query(Post).all()\n for post in posts:\n author = post.author # Potential N+1!\n\n # Check for N+1 queries\n selects = stats.get_queries_by_type('SELECT')\n for qg in selects:\n assert qg.query_count <= 10, f\"Potential N+1: {qg.sql_text[:100]}\"\n\n # Print detailed summary\n print(stats.summary())\n\n### Available Statistics\n\nThe `stats` object provides comprehensive query metrics:\n\n # Summary statistics\n stats.query_count # Total number of queries\n stats.unique_queries # Number of unique SQL queries\n stats.total_time # Total DB time in seconds\n stats.mean_time # Mean query time\n stats.median_time # Median query time\n stats.min_time # Fastest query\n stats.max_time # Slowest query\n \n # Query analysis\n stats.query_groups # All query groups (sorted by total time)\n stats.get_queries_by_type('SELECT') # Filter by query type\n stats.get_slowest_query() # Get the slowest query group\n stats.summary() # Get formatted text summary\n\n### Custom Report Location\n\nSave reports to a custom directory:\n\n with sqltap_profiler(\"my-test\", report_dir=\"/tmp/reports\") as stats:\n response = my_function()\n\n### Disable Report Generation\n\nFor CI environments or when you only need assertions:\n\n with sqltap_profiler(\"my-test\", save_report=False) as stats:\n response = my_function()\n\n assert stats.query_count <= 5\n\n### Integration with pytest fixtures\n\nSQLTap profiler works seamlessly with pytest fixtures, allowing you to profile queries across your entire test without wrapping test code in `with` statements:\n\n import pytest\n from sqltap.profiling import sqltap_profiler\n\n @pytest.fixture\n def db_profiler(request):\n \"\"\"Fixture that profiles all DB queries in a test\"\"\"\n with sqltap_profiler(request.node.name, save_report=False) as stats:\n yield stats\n\n def test_with_profiler(db_profiler):\n # Your test code here\n response = my_function()\n\n # Assert on the profiler stats\n assert db_profiler.query_count <= 10\n\n # Log detailed summary for debugging\n print(profile_db.summary())\n\n### Using with Report Generation\n\nEnable reports in your local environment:\n\n @pytest.fixture\n def db_profiler(request):\n \"\"\"Fixture with automatic report generation\"\"\"\n save_report = os.getenv(\"SAVE_SQLTAP_REPORTS\", \"false\") == \"true\"\n with sqltap_profiler(request.node.name, save_report=save_report) as stats:\n yield stats\n\n # Run tests with reports:\n # SAVE_SQLTAP_REPORTS=true pytest tests/\n\n def test_list_users(db_profiler):\n users = db.query(User).all()\n \n # Check for N+1 queries\n selects = db_profiler.get_queries_by_type('SELECT')\n for qg in selects:\n assert qg.query_count <= 2, f\"N+1 detected: {qg.sql_text[:80]}\"\n\n## Advanced Example\n\n import sqltap\n\n def context_fn(*args):\n \"\"\" Associate the request path, unique id with each query statistic \"\"\"\n return (framework.current_request().path,\n framework.current_request().id)\n\n # start the profiler immediately\n profiler = sqltap.start(user_context_fn=context_fn)\n\n def generate_reports():\n \"\"\" call this at any time to generate query reports reports \"\"\"\n all_stats = []\n per_request_stats = collections.defaultdict(list)\n per_page_stats = collections.defaultdict(list)\n\n qstats = profiler.collect()\n for qs in qstats:\n all_stats.append(qs)\n\n page = qstats.user_context[0]\n per_page_stats[page].append(qs)\n\n request_id = qstats.user_context[1]\n per_request_stats[request_id].append(qs)\n\n # report with all queries\n sqltap.report(all_stats, \"report_all.html\")\n\n # a report per page\n for page, stats in per_page_stats.items():\n sqltap.report(stats, \"report_page_%s.html\" % page)\n\n # a report per request\n for request_id, stats in per_request_stats.items():\n sqltap.report(stats, \"report_request_%s.html\" % request_id)\n\n## Testing\nRun the sqltap tests:\n\n python setup.py test\n\n## License\nApache\n",
"bugtrack_url": null,
"license": "Apache-2.0",
"summary": "Enhanced SQL profiling and introspection for applications using sqlalchemy",
"version": "1.2.0",
"project_urls": {
"Documentation": "https://github.com/brunobcardoso/sqltap-profiler#readme",
"Homepage": "https://github.com/brunobcardoso/sqltap-profiler",
"Issues": "https://github.com/brunobcardoso/sqltap-profiler/issues",
"Repository": "https://github.com/brunobcardoso/sqltap-profiler"
},
"split_keywords": [
"sqlalchemy",
" sql",
" profiling",
" debugging",
" performance"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "1f2093ffbc78158cf4b4ebb8876f8f93a4e6cff88d0e2a0ca13436b441dfbbe0",
"md5": "0bf98afe7fc5221a7ef5d49c22c4eb85",
"sha256": "b82b8fe15efd5000e57b020e5de3330c04e6769e6b730bab97ecdbafda189d23"
},
"downloads": -1,
"filename": "sqltap_profiler-1.2.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "0bf98afe7fc5221a7ef5d49c22c4eb85",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.7",
"size": 20279,
"upload_time": "2025-10-09T20:46:31",
"upload_time_iso_8601": "2025-10-09T20:46:31.922126Z",
"url": "https://files.pythonhosted.org/packages/1f/20/93ffbc78158cf4b4ebb8876f8f93a4e6cff88d0e2a0ca13436b441dfbbe0/sqltap_profiler-1.2.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "8e1f019a0293ddd00389b73a7aeae6c07c0c2a2eb39de3d22a9c6ea41a25fdb4",
"md5": "d8deb7954511b320e3d88a281b5f6242",
"sha256": "715a6f0da2db165643fc6a595ff3b0ed7b09f5510511126ab798ba7dbdff08d6"
},
"downloads": -1,
"filename": "sqltap_profiler-1.2.0.tar.gz",
"has_sig": false,
"md5_digest": "d8deb7954511b320e3d88a281b5f6242",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.7",
"size": 30972,
"upload_time": "2025-10-09T20:46:33",
"upload_time_iso_8601": "2025-10-09T20:46:33.256971Z",
"url": "https://files.pythonhosted.org/packages/8e/1f/019a0293ddd00389b73a7aeae6c07c0c2a2eb39de3d22a9c6ea41a25fdb4/sqltap_profiler-1.2.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-10-09 20:46:33",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "brunobcardoso",
"github_project": "sqltap-profiler#readme",
"travis_ci": true,
"coveralls": false,
"github_actions": true,
"circle": true,
"tox": true,
"lcname": "sqltap-profiler"
}
JSON
