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 [email protected].
> 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
>
> <https://groups.google.com/d/msgid/sqlalchemy/CA%2BXzfko00UCaUn%2B8-KAfWWsqjbj6BG_kLCy0vDa-Sn%2BOteZa4g%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/de4863ac-73d3-4d1b-8f67-e070e10e62fb%40www.fastmail.com.
