Re: [sqlalchemy] Dialect-specific dispatch in user code
Adding that on top of Mike's approach, you may also want to create some
custom functions via the @compiles decorator:
https://docs.sqlalchemy.org/en/14/core/compiler.html
This would allow you to further customize the SQL emitted against Postgres
vs SQLite as needed.For example, I have some examples dealing with the
differences in date operations between those two backends in this file
- https://github.com/aptise/peter_sslers/blob/main/peter_sslers/model/utils.py
On Tuesday, October 19, 2021 at 5:20:13 PM UTC-4 Mike Bayer wrote:
>
>
> On Tue, Oct 19, 2021, at 1:41 PM, Jonathan Brandmeyer wrote:
>
> We're supporting both Postgres and SQLite in our application. For the
> most part, sticking close to ANSI has made this pretty seamless. However,
> there have been occasions where we want to write either DDL or DML in a
> dialect-specific way.
>
> For column data, we want to enable the use of JSON. However, when on
> Postgres we'd like to use JSONB, but when on SQLite we'd use their JSON1
> extension. The generic JSON type provided by sqlalchemy defaults to the
> postgres JSON type when on postgres. How can we get it to default to JSONB
> instead? Using dialect-specific column types in the mapper is a
> non-starter, because a mapped class (or Core table) may have to work with
> both a Postgres connection and an SQLite connection in the same program.
> We almost want to follow[1], except that I'm concerned that some of the
> query syntax renderer might also be affected by switching to JSONB.
>
>
> use with_variant:
>
> Column("data", JSON().with_variant(postgresql.JSONB(), "postgresql"))
>
>
> https://docs.sqlalchemy.org/en/14/core/type_api.html?highlight=with_variant#sqlalchemy.types.TypeEngine.with_variant
>
>
>
> For DML there are a few cases where we'd like to use the
> on_conflict_do_nothing syntax. However, it isn't available as generic
> syntax, only dialect-specific syntax. It's not clear how query-generating
> code can figure out which syntax to use given only a connection.
>
>
> this was just asked yesterday and basically you can dispatch on
> connection.engine.name for different database backends. Simple + more
> generalized decorator approach illustrated at
> https://github.com/sqlalchemy/sqlalchemy/discussions/7199#discussioncomment-1495790
>
>
>
> SQA has some support for adding text() to larger queries, but this is a
> modifier that doesn't clearly fit with the other generative methods that
> accept text arguments. Is there a way to hack on some extra text into the
> _post_values_clause that will be supported into the future?
>
>
> not sure what you're looking to do here.
>
>
>
>
>
> Sincerely,
> --
> Jonathan Brandmeyer
> PlanetiQ
>
>
> --
> 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/CA%2BXzfko00UCaUn%2B8-KAfWWsqjbj6BG_kLCy0vDa-Sn%2BOteZa4g%40mail.gmail.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/c4edb89d-6e33-4bae-98f5-1d0abf5e4217n%40googlegroups.com.
Re: [sqlalchemy] Dialect-specific dispatch in user code
On Tue, Oct 19, 2021, at 1:41 PM, Jonathan Brandmeyer wrote:
> We're supporting both Postgres and SQLite in our application. For the most
> part, sticking close to ANSI has made this pretty seamless. However, there
> have been occasions where we want to write either DDL or DML in a
> dialect-specific way.
>
> For column data, we want to enable the use of JSON. However, when on
> Postgres we'd like to use JSONB, but when on SQLite we'd use their JSON1
> extension. The generic JSON type provided by sqlalchemy defaults to the
> postgres JSON type when on postgres. How can we get it to default to JSONB
> instead? Using dialect-specific column types in the mapper is a non-starter,
> because a mapped class (or Core table) may have to work with both a Postgres
> connection and an SQLite connection in the same program. We almost want to
> follow[1], except that I'm concerned that some of the query syntax renderer
> might also be affected by switching to JSONB.
use with_variant:
Column("data", JSON().with_variant(postgresql.JSONB(), "postgresql"))
https://docs.sqlalchemy.org/en/14/core/type_api.html?highlight=with_variant#sqlalchemy.types.TypeEngine.with_variant
> For DML there are a few cases where we'd like to use the
> on_conflict_do_nothing syntax. However, it isn't available as generic
> syntax, only dialect-specific syntax. It's not clear how query-generating
> code can figure out which syntax to use given only a connection.
this was just asked yesterday and basically you can dispatch on
connection.engine.name for different database backends. Simple + more
generalized decorator approach illustrated at
https://github.com/sqlalchemy/sqlalchemy/discussions/7199#discussioncomment-1495790
>
> SQA has some support for adding text() to larger queries, but this is a
> modifier that doesn't clearly fit with the other generative methods that
> accept text arguments. Is there a way to hack on some extra text into the
> _post_values_clause that will be supported into the future?
not sure what you're looking to do here.
>
>
> Sincerely,
> --
> Jonathan Brandmeyer
> PlanetiQ
>
>
> --
> 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/CA%2BXzfko00UCaUn%2B8-KAfWWsqjbj6BG_kLCy0vDa-Sn%2BOteZa4g%40mail.gmail.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/de4863ac-73d3-4d1b-8f67-e070e10e62fb%40www.fastmail.com.
[sqlalchemy] Dialect-specific dispatch in user code
We're supporting both Postgres and SQLite in our application. For the most part, sticking close to ANSI has made this pretty seamless. However, there have been occasions where we want to write either DDL or DML in a dialect-specific way. For column data, we want to enable the use of JSON. However, when on Postgres we'd like to use JSONB, but when on SQLite we'd use their JSON1 extension. The generic JSON type provided by sqlalchemy defaults to the postgres JSON type when on postgres. How can we get it to default to JSONB instead? Using dialect-specific column types in the mapper is a non-starter, because a mapped class (or Core table) may have to work with both a Postgres connection and an SQLite connection in the same program. We almost want to follow[1], except that I'm concerned that some of the query syntax renderer might also be affected by switching to JSONB. [1]: https://docs.sqlalchemy.org/en/14/core/compiler.html#changing-compilation-of-types For DML there are a few cases where we'd like to use the on_conflict_do_nothing syntax. However, it isn't available as generic syntax, only dialect-specific syntax. It's not clear how query-generating code can figure out which syntax to use given only a connection. SQA has some support for adding text() to larger queries, but this is a modifier that doesn't clearly fit with the other generative methods that accept text arguments. Is there a way to hack on some extra text into the _post_values_clause that will be supported into the future? Sincerely, -- Jonathan Brandmeyer PlanetiQ -- 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/CA%2BXzfko00UCaUn%2B8-KAfWWsqjbj6BG_kLCy0vDa-Sn%2BOteZa4g%40mail.gmail.com.
