Re: [sqlalchemy] Supporting Function Indexes on a Minimum Sqlite Version
Thank you so much, Mike!
I roughly had that same @compiles in my tests, but I didn't trust myself...
and the .dbapi looked like what I wanted, but I really wasn't sure!
On Monday, March 8, 2021 at 4:36:03 PM UTC-5 Mike Bayer wrote:
>
>
> On Mon, Mar 8, 2021, at 12:06 PM, 'Jonathan Vanasco' via sqlalchemy wrote:
>
> I have a project that, in a few rare situations, may run on a version of
> sqlite that does not support function indexes, and "need" to run a unique
> index on `lower(name)`. For simplicity, I'll just use a normal index on
> correct systems,
>
> I'm trying to figure out the best way to implement this.
>
> 1. in terms of sqlite3, what is the best way to access the version
> Sqlalchemy is using? the import is in a classmethod, and could either be
> pysqlite2 or sqlite3? i seriously doubt anyone would deploy with
> pysqlite2, but I feel like I should do things the right way.
>
>
> you'd get this from the dbapi:
>
> >>> from sqlalchemy import create_engine
> >>> e = create_engine("sqlite://")
> >>> e.dialect.dbapi.sqlite_version
> '3.34.1'
>
>
>
>
>
> 2. What is the best way to implement this contextual switch? I thought
> about a `expression.FunctionElement` with custom `@compiles`.
>
>
> yeah that is probably the best approach
>
> from sqlalchemy.sql import expression
> from sqlalchemy.ext.compiler import compiles
>
> class maybe_lower(expression.FunctionElement):
> type = String()
>
> @compiles(maybe_lower, 'sqlite')
> def sl_maybe_lower(element, compiler, **kw):
> args = list(element.clauses)
> if compiler.dialect.dbapi_version < ...:
> return "LOWER(%s)" % (compiler.process(args[0], **kw))
> else:
> return compiler.process(args[0], **kw)
>
> @compiles(maybe_lower)
> def default_maybe_lower(element, compiler, **kw):
> args = list(element.clauses)
> return compiler.process(args[0], **kw)
>
>
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/99598f81-3851-4f2c-988c-1560d2f5e906n%40googlegroups.com
>
>
> .
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/106d60b5-8610-42a4-9738-bd27788b253bn%40googlegroups.com.
Re: [sqlalchemy] Supporting Function Indexes on a Minimum Sqlite Version
On Mon, Mar 8, 2021, at 12:06 PM, 'Jonathan Vanasco' via sqlalchemy wrote:
> I have a project that, in a few rare situations, may run on a version of
> sqlite that does not support function indexes, and "need" to run a unique
> index on `lower(name)`. For simplicity, I'll just use a normal index on
> correct systems,
>
> I'm trying to figure out the best way to implement this.
>
> 1. in terms of sqlite3, what is the best way to access the version Sqlalchemy
> is using? the import is in a classmethod, and could either be pysqlite2 or
> sqlite3? i seriously doubt anyone would deploy with pysqlite2, but I feel
> like I should do things the right way.
you'd get this from the dbapi:
>>> from sqlalchemy import create_engine
>>> e = create_engine("sqlite://")
>>> e.dialect.dbapi.sqlite_version
'3.34.1'
>
> 2. What is the best way to implement this contextual switch? I thought about
> a `expression.FunctionElement` with custom `@compiles`.
yeah that is probably the best approach
from sqlalchemy.sql import expression
from sqlalchemy.ext.compiler import compiles
class maybe_lower(expression.FunctionElement):
type = String()
@compiles(maybe_lower, 'sqlite')
def sl_maybe_lower(element, compiler, **kw):
args = list(element.clauses)
if compiler.dialect.dbapi_version < ...:
return "LOWER(%s)" % (compiler.process(args[0], **kw))
else:
return compiler.process(args[0], **kw)
@compiles(maybe_lower)
def default_maybe_lower(element, compiler, **kw):
args = list(element.clauses)
return compiler.process(args[0], **kw)
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/99598f81-3851-4f2c-988c-1560d2f5e906n%40googlegroups.com
>
> .
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a4a601f5-b3db-400e-97e7-9efbe8556568%40www.fastmail.com.
[sqlalchemy] Supporting Function Indexes on a Minimum Sqlite Version
I have a project that, in a few rare situations, may run on a version of sqlite that does not support function indexes, and "need" to run a unique index on `lower(name)`. For simplicity, I'll just use a normal index on correct systems, I'm trying to figure out the best way to implement this. 1. in terms of sqlite3, what is the best way to access the version Sqlalchemy is using? the import is in a classmethod, and could either be pysqlite2 or sqlite3? i seriously doubt anyone would deploy with pysqlite2, but I feel like I should do things the right way. 2. What is the best way to implement this contextual switch? I thought about a `expression.FunctionElement` with custom `@compiles`. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/99598f81-3851-4f2c-988c-1560d2f5e906n%40googlegroups.com.
