Query AnnData Objects with SQL
The Python based AnnSQL package enables SQL-based queries on AnnData objects, returning results as either a Pandas DataFrame, an AnnData object, or a Parquet file that can easily be imported into a variety of data analysis tools. Behind the scenes, AnnSQL converts the layers of an AnnData object into a relational DuckDB database. Each layer is stored as an individual table, allowing for simple or complex SQL queries, including table joins.
Features
- Query AnnData with SQL.
- Fast for complex queries and aggregative functions.
- Return query results as Pandas Dataframes, Parquet files, or AnnData objects.
- Create in-memory or on-disk databases directly from AnnData objects.
- Open AnnSQL databases in R. No conversions necessary. Learn more
Full Documentation
docs.annsql.com
Quick Setup
pip install annsql
Basic Usage (In-Memory)
Ideal for smaller datasets.
from AnnSQL import AnnSQL
import scanpy as sc
#read sample data
adata = sc.datasets.pbmc68k_reduced()
#instantiate the AnnData object (you may also pass a h5ad file to the adata parameter)
asql = AnnSQL(adata=adata)
#query the expression table. Returns Pandas Dataframe by Default
asql.query("SELECT * FROM adata LIMIT 10")
Basic Usage (On-Disk)
For larger datasets, AnnSQL can create a local database (asql) from the AnnData object. This database is stored on-disk, can be queried, and is persistent.
import scanpy as sc
from AnnSQL import AnnSQL
from AnnSQL.MakeDb import MakeDb
#read sample data
adata = sc.datasets.pbmc68k_reduced()
#build the AnnSQL database
MakeDb(adata=adata, db_name="pbmc3k_reduced", db_path="db/")
#open the AnnSQL database
asql = AnnSQL(db="db/pbmc3k_reduced.asql")
#query the expression table
asql.query("SELECT * FROM adata LIMIT 5")
Advanced Queries and Usage
from AnnSQL import AnnSQL
import scanpy as sc
#read sample data
adata = sc.datasets.pbmc68k_reduced()
#pass the AnnData object to the AnnSQL class
asql = AnnSQL(adata=adata)
#group and count all labels
asql.query("SELECT obs.bulk_labels, COUNT(*) FROM obs GROUP BY obs.bulk_labels")
#take the log10 of a value
asql.query("SELECT LOG10(HES4) FROM X WHERE HES4 > 0")
#sum all gene counts | Memory intensive | See method calculate_gene_counts for chunked approach.
asql.query("SELECT SUM(COLUMNS(*)) FROM (SELECT * EXCLUDE (cell_id) FROM X)")
#taking the correlation of genes ITGB2 and SSU72 in dendritic cells that express either gene > 0
asql.query("SELECT corr(ITGB2,SSU72) as correlation FROM adata WHERE bulk_labels = 'Dendritic' AND (ITGB2 > 0 OR SSU72 >0)")
############################################################################
# Extended AnnSQL methods (See: https://docs.annsql.com/preprocessing)
# These methods are either SQL based or Python/SQL hybrid implementations.
############################################################################
#basic QC on the dataset
asql.calculate_total_counts()
asql.filter_by_cell_counts(min_cell_count=1000, max_cell_count=50000)
asql.filter_by_gene_counts(min_gene_counts=100, max_gene_counts=10000)
#normalize & log umi counts
asql.expression_normalize(total_counts_per_cell=10000)
asql.expression_log(log_type="LN")
#select highly variable genes
asql.calculate_variable_genes(save_var_names=True, top_variable_genes=1000)
#run pca
asql.calculate_pca(n_pcs=50, top_variable_genes=1000, zero_center=False)
#umap, cluster, then and plot.
asql.calculate_umap()
asql.calculate_leiden_clusters(resolution=0.25, n_neighbors=5)
asql.plot_umap(color_by="leiden_clusters", annotate=True)
Reference
Kenny Pavan, Arpiar Saunders, AnnSQL: A Python SQL-based package for fast large-scale single-cell genomics analysis using minimal computational resources
Bioinformatics Advances, 2025; vbaf105, https://doi.org/10.1093/bioadv/vbaf105
Metadata
Release files for AnnSQL 1.0.6
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| annsql-1.0.6.tar.gz | 31.9 kB | Without provenance | Details for annsql-1.0.6.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| annsql-1.0.6-py3-none-any.whl | Python 3 | none | any | Details for annsql-1.0.6-py3-none-any.whl |
Total release size: 61.1 kB
Release files / annsql-1.0.6.tar.gz
| Download URL | annsql-1.0.6.tar.gz |
|---|---|
| Size | 31.9 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
04aa719917080ced4307f15ccacbefce4c4dcb5dc419446a529d8e3d4d99732c
|
|
BLAKE2b-256 checksum How to use checksums |
3bfe8bc396e962af271a57a4396b61dcad19573b115923167431e7c08a5fbc74
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
twine/6.1.0 CPython/3.12.3
|
Release files / annsql-1.0.6-py3-none-any.whl
| Download URL | annsql-1.0.6-py3-none-any.whl |
|---|---|
| Size | 29.3 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
00619058b21795add04f3f54d084d4292c8fb662d5762e8ad8a0c3bcb5a84dc1
|
|
BLAKE2b-256 checksum How to use checksums |
e8c3a70cea59e5ce7054e721ad5fb9e2d0d1451df1cacd510254ad27c6dcbd79
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
twine/6.1.0 CPython/3.12.3
|
Security for AnnSQL 1.0.6
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
No provenance found
None of the files in this release were uploaded with provenance metadata.
A note on package security
PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.
To learn more about protecting yourself from malicious actors, visit our package security documentation.