[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.
Re: [sqlalchemy] 2.0 Migration: Transaction Scope Performance Pitfalls
On Fri, Mar 5, 2021 at 8:34 AM Mike Bayer wrote: > yeah in fact that engine.begin() here is using a brand new SQLite > connection each time as the default pool for SQlite files is NullPool, as > it is a very fast connecting database and it is the safest option for > concurrency. So in your first example, you have a brand new file > connection and all of your WAL setup code is running each tme. In the > second example, it runs only once. > > background on pooling: > > > https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#threading-pooling-behavior > > if you use the SingletonThreadPool instead of the default NullPool, you > will again get the same performance with engine.begin(). however, there is > no need to change existing code that uses engine.connect() / > connection.begin(). > Got it, thanks. I had read https://docs.sqlalchemy.org/en/13/core/pooling.html#connection-pool-configuration but managed to miss the fine print that SingletonThreadPool was used for in-memory connections and NullPool was used for file connection. -- Jonathan Brandmeyer -- 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%2BXzfkoPwxu0EdHTHWK1Ya8xEMJ8NUdDnf%3DBNVxeBG8vLOsOJA%40mail.gmail.com.
Re: [sqlalchemy] 2.0 Migration: Transaction Scope Performance Pitfalls
On Fri, Mar 5, 2021 at 8:29 AM Mike Bayer wrote: > Can I ask what documentation led you to believe that engine.connect() was > somehow being removed ? > The guidance at [1] makes it clear that the connection is available as a transaction context manager. However, the guidance at [2] suggests that the preferred method is to use the engine. [1]: https://docs.sqlalchemy.org/en/14/changelog/migration_20.html#migration-core-connection-transaction [2]: https://docs.sqlalchemy.org/en/14/changelog/migration_20.html#many-choices-becomes-one-choice Thanks, -- Jonathan Brandmeyer -- 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%2BXzfkpqV2PDBm9EQYBZT6jiO_ZKHR1uKn%2BwB90t2D23An10ug%40mail.gmail.com.
Re: [sqlalchemy] 2.0 Migration: Transaction Scope Performance Pitfalls
On Thu, Mar 4, 2021 at 5:00 PM Mike Bayer wrote:
> hey there-
>
> engine.begin() does not do anything to the SQLite connection at all as
> there is no begin() method in the Python DBAPI. this is why in your
> logging you will see a line that says "BEGIN (implicit)". nothing
> happened. the pysqlite driver controls the scope of the actual BEGIN on
> the sqlite3 library implcitly and there should be no difference at all in
> 2.0 vs. 1.x in how this occurs. There is also no difference between
> calling engine.begin() or connection.begin(), assuming "connection" here is
> the SQLAlchemy connection- engine.begin() simply calls connection.begin()
> after procuring the connection.
>
I agree that the `echo`ed trace is identical in both cases. But the
throughput and filesystem effects certainly look as if something is
triggering a checkpoint every time the connection is returned to the pool.
>
> note that pysqlite's implicit control of transactions is often
> insufficient for some scenarios where fine-grained control of transactions
> scope is desired, which is where the recipe at
> https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#serializable-isolation-savepoints-transactional-ddl
> comes from in order to circumvent pysqlite's assumptions.
>
Thanks for the heads-up.
>
> if you can please provide specifics that illustrate the difference in
> behavior as well as how you detect the WAL condition (as I am unfamiliar
> with this aspect of sqlite) that would be appreciated.
>
Here is a demo that illustrates what I'm seeing with the WAL. See the
commentary inline.
Thanks,
--
Jonathan Brandmeyer
--
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%2BXzfkqB_qr3Lgthda3ySkog9RgdbE8eB5z2Lhx3BcLxmLjiXw%40mail.gmail.com.
#!/usr/bin/env python3
# Copyright 2021 Space Sciences and Engineering, LLC.
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are met:
# 1. Redistributions of source code must retain the above copyright notice, this
# list of conditions and the following disclaimer.
# 2. Redistributions in binary form must reproduce the above copyright notice,
# this list of conditions and the following disclaimer in the documentation
# and/or other materials provided with the distribution.
# 3. Neither the name of the copyright holder nor the names of its contributors
# may be used to endorse or promote products derived from this software without
# specific prior written permission.
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
# DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
# FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
# SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
# CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
# OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
# OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
import argparse
import os
import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy.event import listen
def sqlite_standard_options(dbapi_connection, connection_record):
cursor = dbapi_connection.cursor()
# Enable the WAL. When the database is open read-write, then sometime after
# the first transaction opens up a pair of additional files will be created
# in the filesystem named after the main database file, with suffies -wal
# and -shm.
# https://sqlite.org/wal.html
# https://sqlite.org/pragma.html#pragma_journal_mode
cursor.execute("PRAGMA journal_mode=WAL")
# The default (for backwards-compat reasons) is FULL. When using the WAL,
# sync much less often. The cost is a little more lost work in the face of
# a crash, but the database retains consistency. Its also substantially
# faster.
# https://sqlite.org/pragma.html#pragma_synchronous
cursor.execute("PRAGMA synchronous=NORMAL")
# Enforce the typical default for demonstration purpos
[sqlalchemy] 2.0 Migration: Transaction Scope Performance Pitfalls
We are currently on v1.3, but we've noticed the big announcements about the 2.0 API and are reviewing our practices with future migration in mind. One of our applications is INSERT-heavy and also uses SQLite. Standard performance guidance in this situation SQLite is to use the write-ahead log. We found that the baseline guidance in 2.0 to use engine.begin() as the transaction's context manager doesn't cooperate well with the WAL. The WAL is getting checkpointed and deleted at every transaction. The behavior is consistent with failing to cache the connection. Using connection.begin() as the transaction's context manager restores full use of the WAL. Is this a known pitfall when using 2.0-style transactions in the 1.3 release? Or are we misunderstanding the connection pool? Thanks, Jonathan Brandmeyer -- 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/bd49f8f6-d930-4726-8c59-6eea6ed06825n%40googlegroups.com.
