[](https://github.com/pylint-dev/pylint)
[](https://github.com/pre-commit/pre-commit)
# Apache Solr dialect for SQLAlchemy
A [SQLAlchemy](https://www.sqlalchemy.org/) dialect for Apache Solr.
## Requirements
The dialect is compatible with Solr version 6.0 and higher.
## Installation
The package can either be installed through PyPi or from the source code.
#### Through Python Package Index
`pip install sqlalchemy-solr`
#### Latest from Source Code
`pip install git+https://github.com/aadel/sqlalchemy-solr`
## Usage
To connect to Solr with SQLAlchemy, the following URL pattern can be used:
```
solr://<username>:<password>@<host>:<port>/solr/<collection>[?parameter=value]
```
_Note_: port 8983 is used when `port` in the URL is omitted
### Authentication
#### Basic Authentication
Basic authentication credentials can be supplied in connection URL as shown in the URL pattern above
#### JWT Authentication
JWT authentication token can be supplied as a `token` URL parameter, for example:
```
solr://<host>:<port>/solr/<collection>?token=<token_value>
```
### Additional Parameters:
If HTTPS is enabled, the following parameters can be supplied:
1. `use_ssl`: a boolean parameter when set to `true` an HTTPS connection is used. Default value is `false`.
2. `verify_ssl`: a boolean parameter that controls whether to verify SSL certificate. Default value is `true`.
### Aliases
Aliases are supported as tables where columns are the union of all the underlying collections fields. For example, if an alias `collection_alias` has linked collection members, the following query is valid:
```
SELECT f1, f2, f3 FROM collection_alias
```
where `f1`, `f2`, and `f3` are defined in the linked collections.
### Time Filters
Time filtration predicates are transformed to Solr syntax when ORM mode is used and Solr datasource release is lower than 9. Time filters transformations are handled internally by Solr 9 and above without the driver intervention. Open- and close-ended date ranges are supported.
### Multi-valued Fields
Multi-value fields are mapped to SQL arrays of a specific scalar type. For example:
```
phones = Column('PHONE_ss', ARRAY(String))
```
### SQL Compilation Caching
The dialect supports caching by leveraging SQLAlchemy SQL compilation caching capabilities, which include query caching.
### Schema
If the ORM query supplied explicitly refers to a schema, the schema would be filtered out before query execution.
## Basic Example
The following example illustrates the basic usage in a Python project:
```
engine = create_engine('solr://solr:8983/solr/examples_books')
with engine.connect() as connection:
result = connection.execute(text("SELECT sequence_i, genre_s FROM examples_books"))
for row in result:
print("Sequence: {}, Genre: {}".format(row['sequence_i'], row['genre_s']))
```
## ORM Example
```
Base = declarative_base()
class Book(Base):
__tablename__ = "books"
id = Column('index_i', Integer, primary_key=True)
name = Column('book_name_t', String)
publishing_year = Column('publishing_year_i', Integer)
def __repr__(self):
return f"Book(id={self.id!r}, name={self.name!r}, publishing_year={self.publishing_year!r})"
engine = create_engine('solr://solr:8983/solr/books')
with Session(engine) as session:
stmt = select(Book).where(Book.publishing_year.in_([2014, 2015])).limit(10)
for book in session.scalars(stmt):
print(book)
```
where `index_i`, `book_name_t`, and `publishing_year_i` are fields of `books` collection.
## Time Filters Example
```
Base = declarative_base()
class SalesHistory(Base):
__tablename__ = "sales"
order_number = Column('order_number_i', Integer, primary_key=True)
price_each = Column('price_each_f', Float)
status = Column('status_s', String)
order_date = Column('order_date_dt', Integer)
def __repr__(self):
return f"SalesHistory(order number={self.order_number!r}, status={self.status!r}, price={self.price_each}, order date={self.order_date!r})"
engine = create_engine('solr://solr:8983/solr/sales')
with Session(engine) as session:
stmt = select(SalesHistory) \
.where(and_(SalesHistory.order_date >= "2024-01-01 00:00:00", SalesHistory.order_date < "2024-02-01 00:00:00")) \
.order_by(SalesHistory.price_each.asc()) \
.limit(10)
for row in session.scalars(stmt):
print(row)
```
where `order_number_i`, `price_each_f`, `status_s`, and `order_date_dt` fields of `sales` collection.
In the above example, date predicates are transformed to `[2024-01-01T00:00:00Z TO 2024-02-01T00:00:00Z}`.
Open-ended date ranges are supported. For example:
```
.where(SalesHistory.order_date >= "2024-01-01 00:00:00")
```
translates to `[2024-01-01T00:00:00Z TO *]`
## Operator Translation
`ILIKE` and `NOT ILIKE` operators are translated to `LIKE` amd `NOT LIKE` operators which are case in-sensetive in Solr.
## Miscellaneous
SQL statement semi-colon is removed if present.
## Compatibility
| Feature | 6.0 | 6.5 | 6.6 | 7.x | 8.x | 9.x |
|----------------------------------|-----|-----|-----|-----|-----|-----|
| Aliases | ✗ | ✗ | ✓ | ✓ | ✓ | ✓ |
| Built-in date range compilation | ✗ | ✗ | ✗ | ✗ | ✗ | ✓ |
| `SELECT` _expression_ statements | ✗ | ✓ | ✓ | ✓ | ✓ | ✓ |
| SQL compilation caching | ✗ | ✗ | ✗ | ✗ | ✗ | ✓ |
## Use Cases
### Apache Superset
To connect Apache Superset with Solr datasource, add the package to the requirements then create a database connection using the URL pattern shown above.
## Testing with Apache Superset
#### Requirements
* A Solr instance with a Parallel SQL supported up and running
* A Superset instance up and running with this package installed
* `pytest` >= 7.4.4 installed on the testing machine
#### Procedure
1. Change `conftest.py` as appropriate
2. Run `pytest`
## Resources
1. [SQLAlchemy documentation](https://docs.sqlalchemy.org/en/13/index.html)
2. [SQLAlchemy dialects](https://docs.sqlalchemy.org/en/13/dialects/index.html)
3. [PEP 249 – Python Database API Specification v2.0](https://peps.python.org/pep-0249/)
Raw data
{
"_id": null,
"home_page": null,
"name": "sqlalchemy-solr",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": null,
"keywords": "Apache Solr, Solr, SQLAlchemy, dialect, Apache Superset",
"author": "Ahmed Adel",
"author_email": "Ahmed Adel <hello@aadel.io>",
"download_url": "https://files.pythonhosted.org/packages/39/00/0bc20f647ae1b608d4ad309e707b8b18bc179fc43cbf6a031645effd0602/sqlalchemy_solr-0.2.4.3.tar.gz",
"platform": null,
"description": "[](https://github.com/pylint-dev/pylint)\n[](https://github.com/pre-commit/pre-commit)\n\n# Apache Solr dialect for SQLAlchemy\n\nA [SQLAlchemy](https://www.sqlalchemy.org/) dialect for Apache Solr.\n\n## Requirements\n\nThe dialect is compatible with Solr version 6.0 and higher.\n\n## Installation\n\nThe package can either be installed through PyPi or from the source code.\n\n#### Through Python Package Index\n\n`pip install sqlalchemy-solr`\n\n#### Latest from Source Code\n\n`pip install git+https://github.com/aadel/sqlalchemy-solr`\n\n## Usage\n\nTo connect to Solr with SQLAlchemy, the following URL pattern can be used:\n\n```\nsolr://<username>:<password>@<host>:<port>/solr/<collection>[?parameter=value]\n```\n\n_Note_: port 8983 is used when `port` in the URL is omitted\n\n### Authentication\n\n#### Basic Authentication\n\nBasic authentication credentials can be supplied in connection URL as shown in the URL pattern above\n\n#### JWT Authentication\n\nJWT authentication token can be supplied as a `token` URL parameter, for example:\n\n```\nsolr://<host>:<port>/solr/<collection>?token=<token_value>\n```\n\n### Additional Parameters:\n\nIf HTTPS is enabled, the following parameters can be supplied:\n\n1. `use_ssl`: a boolean parameter when set to `true` an HTTPS connection is used. Default value is `false`.\n2. `verify_ssl`: a boolean parameter that controls whether to verify SSL certificate. Default value is `true`.\n\n### Aliases\n\nAliases are supported as tables where columns are the union of all the underlying collections fields. For example, if an alias `collection_alias` has linked collection members, the following query is valid:\n\n```\nSELECT f1, f2, f3 FROM collection_alias\n```\n\nwhere `f1`, `f2`, and `f3` are defined in the linked collections.\n\n### Time Filters\n\nTime filtration predicates are transformed to Solr syntax when ORM mode is used and Solr datasource release is lower than 9. Time filters transformations are handled internally by Solr 9 and above without the driver intervention. Open- and close-ended date ranges are supported.\n\n### Multi-valued Fields\n\nMulti-value fields are mapped to SQL arrays of a specific scalar type. For example:\n```\nphones = Column('PHONE_ss', ARRAY(String))\n```\n\n### SQL Compilation Caching\n\nThe dialect supports caching by leveraging SQLAlchemy SQL compilation caching capabilities, which include query caching.\n\n### Schema\n\nIf the ORM query supplied explicitly refers to a schema, the schema would be filtered out before query execution.\n\n## Basic Example\n\nThe following example illustrates the basic usage in a Python project:\n\n```\nengine = create_engine('solr://solr:8983/solr/examples_books')\n\nwith engine.connect() as connection:\n result = connection.execute(text(\"SELECT sequence_i, genre_s FROM examples_books\"))\n for row in result:\n print(\"Sequence: {}, Genre: {}\".format(row['sequence_i'], row['genre_s']))\n```\n\n## ORM Example\n\n```\nBase = declarative_base()\n\nclass Book(Base):\n __tablename__ = \"books\"\n\n id = Column('index_i', Integer, primary_key=True)\n name = Column('book_name_t', String)\n publishing_year = Column('publishing_year_i', Integer)\n\n def __repr__(self):\n return f\"Book(id={self.id!r}, name={self.name!r}, publishing_year={self.publishing_year!r})\"\n\nengine = create_engine('solr://solr:8983/solr/books')\n\nwith Session(engine) as session:\n stmt = select(Book).where(Book.publishing_year.in_([2014, 2015])).limit(10)\n\n for book in session.scalars(stmt):\n print(book)\n```\nwhere `index_i`, `book_name_t`, and `publishing_year_i` are fields of `books` collection.\n\n## Time Filters Example\n\n```\nBase = declarative_base()\n\nclass SalesHistory(Base):\n __tablename__ = \"sales\"\n\n order_number = Column('order_number_i', Integer, primary_key=True)\n price_each = Column('price_each_f', Float)\n status = Column('status_s', String)\n order_date = Column('order_date_dt', Integer)\n\n def __repr__(self):\n return f\"SalesHistory(order number={self.order_number!r}, status={self.status!r}, price={self.price_each}, order date={self.order_date!r})\"\n\nengine = create_engine('solr://solr:8983/solr/sales')\n\nwith Session(engine) as session:\n stmt = select(SalesHistory) \\\n .where(and_(SalesHistory.order_date >= \"2024-01-01 00:00:00\", SalesHistory.order_date < \"2024-02-01 00:00:00\")) \\\n .order_by(SalesHistory.price_each.asc()) \\\n .limit(10)\n\n for row in session.scalars(stmt):\n print(row)\n```\nwhere `order_number_i`, `price_each_f`, `status_s`, and `order_date_dt` fields of `sales` collection.\n\nIn the above example, date predicates are transformed to `[2024-01-01T00:00:00Z TO 2024-02-01T00:00:00Z}`.\n\nOpen-ended date ranges are supported. For example:\n\n```\n.where(SalesHistory.order_date >= \"2024-01-01 00:00:00\")\n```\n\ntranslates to `[2024-01-01T00:00:00Z TO *]`\n\n## Operator Translation\n`ILIKE` and `NOT ILIKE` operators are translated to `LIKE` amd `NOT LIKE` operators which are case in-sensetive in Solr.\n\n## Miscellaneous\nSQL statement semi-colon is removed if present.\n\n## Compatibility\n\n| Feature | 6.0 | 6.5 | 6.6 | 7.x | 8.x | 9.x |\n|----------------------------------|-----|-----|-----|-----|-----|-----|\n| Aliases | \u2717 | \u2717 | \u2713 | \u2713 | \u2713 | \u2713 |\n| Built-in date range compilation | \u2717 | \u2717 | \u2717 | \u2717 | \u2717 | \u2713 |\n| `SELECT` _expression_ statements | \u2717 | \u2713 | \u2713 | \u2713 | \u2713 | \u2713 |\n| SQL compilation caching | \u2717 | \u2717 | \u2717 | \u2717 | \u2717 | \u2713 |\n\n## Use Cases\n\n### Apache Superset\n\nTo connect Apache Superset with Solr datasource, add the package to the requirements then create a database connection using the URL pattern shown above.\n\n## Testing with Apache Superset\n\n#### Requirements\n\n* A Solr instance with a Parallel SQL supported up and running\n* A Superset instance up and running with this package installed\n* `pytest` >= 7.4.4 installed on the testing machine\n\n#### Procedure\n\n1. Change `conftest.py` as appropriate\n2. Run `pytest`\n\n## Resources\n1. [SQLAlchemy documentation](https://docs.sqlalchemy.org/en/13/index.html)\n2. [SQLAlchemy dialects](https://docs.sqlalchemy.org/en/13/dialects/index.html)\n3. [PEP 249 \u2013 Python Database API Specification v2.0](https://peps.python.org/pep-0249/)\n",
"bugtrack_url": null,
"license": "MIT License",
"summary": "Apache Solr Dialect for SQLAlchemy",
"version": "0.2.4.3",
"project_urls": {
"Homepage": "https://github.com/aadel/sqlalchemy-solr",
"Issues": "https://github.com/aadel/sqlalchemy-solr/issues"
},
"split_keywords": [
"apache solr",
" solr",
" sqlalchemy",
" dialect",
" apache superset"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "fe4242e61dbe76fe7462fcd1e8aab1d68f66352369c46faf268fcb9bea50ecb6",
"md5": "1514a71be1c183071a8701255f8b936f",
"sha256": "355a7884a8cb32a77e6e4f2b03584f1e5db1fcb37c88af93989d9987cf3fc46f"
},
"downloads": -1,
"filename": "sqlalchemy_solr-0.2.4.3-py3-none-any.whl",
"has_sig": false,
"md5_digest": "1514a71be1c183071a8701255f8b936f",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 22272,
"upload_time": "2024-08-08T15:30:25",
"upload_time_iso_8601": "2024-08-08T15:30:25.075565Z",
"url": "https://files.pythonhosted.org/packages/fe/42/42e61dbe76fe7462fcd1e8aab1d68f66352369c46faf268fcb9bea50ecb6/sqlalchemy_solr-0.2.4.3-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "39000bc20f647ae1b608d4ad309e707b8b18bc179fc43cbf6a031645effd0602",
"md5": "cc82a4146948a7790087ec1632d6d6f0",
"sha256": "409409f4a4c91cd3a7b5d0cf407d29cb75a0994894ab2467031bdc34fb5cddc8"
},
"downloads": -1,
"filename": "sqlalchemy_solr-0.2.4.3.tar.gz",
"has_sig": false,
"md5_digest": "cc82a4146948a7790087ec1632d6d6f0",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 23272,
"upload_time": "2024-08-08T15:30:26",
"upload_time_iso_8601": "2024-08-08T15:30:26.869269Z",
"url": "https://files.pythonhosted.org/packages/39/00/0bc20f647ae1b608d4ad309e707b8b18bc179fc43cbf6a031645effd0602/sqlalchemy_solr-0.2.4.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-08-08 15:30:26",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "aadel",
"github_project": "sqlalchemy-solr",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqlalchemy-solr"
}
JSON
