Re: [sqlalchemy] Not able to importing fixtures from sqlalchemy from 1.3.16 version

not expected to work right now unless you run pytest and you have the appropriate project layout installed as documented at https://github.com/sqlalchemy/sqlalchemy/blob/main/README.dialects.rst On Sat, Dec 11, 2021, at 3:49 AM, sairohith yerramilli wrote: > Hi > > from sqlalchemy.testing

Re: [sqlalchemy] Not able to importing fixtures from sqlalchemy from 1.3.16 version

Hi from sqlalchemy.testing import fixtures, AssertsCompiledSQL from sqlalchemy.sql.functions import ReturnTypeFromArgs from sqlalchemy import select from sqlalchemy.sql import column class isnull(ReturnTypeFromArgs): name = 'isnull' class

Re: [sqlalchemy] Not able to importing fixtures from sqlalchemy from 1.3.16 version

these fixtures are only for use within a pytest test suite that is using SQLAlchemy's plugins. is this for a third party dialect project? On Thu, Dec 9, 2021, at 9:42 AM, sairohith yerramilli wrote: > Hi > I am facing an issue when importing fixtures from sqlalchemy in version > 1.3.16 and

Re: [sqlalchemy] session merge sets missing children foreign keys to null

Thank you for the quick and clear answer that solves the test case. I've applied the same approach to the issue in our current codebase and it completely fixed the issue. You're the best! On Wednesday, December 8, 2021 at 1:48:45 PM UTC-6 Mike Bayer wrote: > > > On Wed, Dec 8, 2021, at 10:38

[sqlalchemy] Not able to importing fixtures from sqlalchemy from 1.3.16 version

Hi I am facing an issue when importing fixtures from sqlalchemy in version 1.3.16 and above. When i am trying to import fixtures from sqlalchemy i.e "from sqlachemy.testing import fixtures" it throwing me an attribute error says 'NoneType' object has no attribute 'fixture' This change in

Re: [sqlalchemy] session merge sets missing children foreign keys to null

On Wed, Dec 8, 2021, at 10:38 AM, Gabriel Smith wrote: > Hi, I'm a recent adopter of sqlalchemy, starting fresh with all the 2.0 > stuff. Thanks so much for the entire teams hard work! great! > > I have a small issue with the merge functionality and it could honestly just > be a

[sqlalchemy] session merge sets missing children foreign keys to null

Hi, I'm a recent adopter of sqlalchemy, starting fresh with all the 2.0 stuff. Thanks so much for the entire teams hard work! I have a small issue with the merge functionality and it could honestly just be a misunderstanding of the function from my own point of view. I have a fairly

Re: [sqlalchemy] Re: How to add the index_elements to the on_conflict_do_update() method

Does the table definition in postgres match your SQLAlchemy definition? Adding "unique=True" to the SQLAlchemy table definition will not automatically add an index to an existing table in the database. If you connect to the database using "psql" and run "\d message_symbol", does it show the

[sqlalchemy] Re: How to add the index_elements to the on_conflict_do_update() method

I also tried to add unique=True to the column message_id but still fail with the same error On Tuesday, 7 December 2021 at 22:21:49 UTC Chaozy Z wrote: > Hi there, I just started to learn SQLAlchemy. 0 > > > I have a `on_conflict_do_update`

[sqlalchemy] How to add the index_elements to the on_conflict_do_update() method

Hi there, I just started to learn SQLAlchemy. 0 I have a `on_conflict_do_update` command as follows: ``` insert_stmt = insert(MessageSymbol).values(message_id=12345, symbol_id=1) do_update_stmt = insert_stmt.on_conflict_do_update(

[sqlalchemy] How to add constraint or index_elements to the `on_conflict_do_update()`

Hi there, I just started to learn SQLAlchemy. 0 I have a `on_conflict_do_update` command as follows: ``` insert_stmt = insert(MessageSymbol).values(message_id=12345, symbol_id=1) do_update_stmt = insert_stmt.on_conflict_do_update(

Re: [sqlalchemy] @declared_attr.cascading doesn't work when inheritance of multiple levels

yeah I am not a huge fan of declared_attr.cascading except for maybe a table name convention On Sun, Nov 28, 2021, at 11:38 PM, niuji...@gmail.com wrote: > Thanks for pointing this out. It did address this problem already. > I just solved this by manually adding all the primary/foreign keys to

Re: [sqlalchemy] @declared_attr.cascading doesn't work when inheritance of multiple levels

Thanks for pointing this out. It did address this problem already. I just solved this by manually adding all the primary/foreign keys to each classes. Not a big deal, actually it helps clear code! On Sunday, November 28, 2021 at 6:37:30 PM UTC-8 Mike Bayer wrote: > this is addressed in the docs

Re: [sqlalchemy] @declared_attr.cascading doesn't work when inheritance of multiple levels

this is addressed in the docs which discuss "cascading" here: https://docs.sqlalchemy.org/en/14/orm/declarative_mixins.html#mixing-in-columns-in-inheritance-scenarios "The `declared_attr.cascading` feature currently does *not* allow for a subclass to override the attribute with a different

Re: [sqlalchemy] @declared_attr.cascading doesn't work when inheritance of multiple levels

I've just manually put this line to the `Programmer` class definition, but it still gives me the same error, strangely: class Programmer(Engineer): __tablename__ = 'programmer' record_id = Column(ForeignKey('engineer.record_id'), primary_key=True) On

Re: [sqlalchemy] @declared_attr.cascading doesn't work when inheritance of multiple levels

On Sun, Nov 28, 2021, at 4:24 AM, niuji...@gmail.com wrote: > I'm using the "joined table inheritance" model. I have three levels of > inheritance. > > class has_polymorphic_id(object): > @declared_attr.cascading > def record_id(cls): > if has_inherited_table(cls): >

[sqlalchemy] @declared_attr.cascading doesn't work when inheritance of multiple levels

I'm using the "joined table inheritance" model. I have three levels of inheritance. class has_polymorphic_id(object): @declared_attr.cascading def record_id(cls): if has_inherited_table(cls): return Column(ForeignKey('employee.record_id'),

Re: [sqlalchemy] When building a query, option to only join once

well as the email noted, it referred to issue https://github.com/sqlalchemy/sqlalchemy/issues/3225 , which was an ambitious proposal for a richly featured inspection API on the Query object. which would mean you'd still need to write your own logic that pokes around in the query, decides

[sqlalchemy] When building a query, option to only join once

I keep running into this issue where I have a complex set of filter parameters, some of which need a join, and it would be nice to have an ergonomic way to say: join this table, but not if there already is a join. I found this old post on the subject:

Re: [sqlalchemy] Turn off connections invalidation functionality

I've spent some time thinking about what might be being asked here.the only thing I can think of is that when a particular database connection is found to be in what we call a "disconnect" state, the connection is invalidated, so that the connection will reconnect and make a new connection.

Re: [sqlalchemy] Turn off connections invalidation functionality

Im not sure if I understand the question? if you don't call .invalidate(), then the connection is not invalidated. what does "turn off" mean ? On Fri, Nov 26, 2021, at 11:17 AM, Anupama Goparaju wrote: > Hi, > > Is there a way to safely turn off connection invalidation functionality >

[sqlalchemy] Turn off connections invalidation functionality

Hi, Is there a way to safely turn off connection invalidation functionality (based on invalidation time set, all the connections created prior to the timestamp are invalidated) in sqlalchemy? https://github.com/Noethys/Connecthys/blob/master/connecthys/lib/sqlalchemy/pool.py#L574 Thanks,

Re: [sqlalchemy] Using replacement_traverse() to augment a join

hey there - replacement_traverse is a mostly internal API that is very fickle and is not guaranteed to work in generic situations; it's tuned very specifically to cases that the ORM needs internally and one of the architectural trends in SQLAlchemy is to try to reduce dependence on it. Can we

[sqlalchemy] Using replacement_traverse() to augment a join

*Hello! I'm trying to replace a joined table in a join statement with a subquery with WHERE clause added using **replacement_traverse() utility**. And I've managed to achieve a replacement

Re: [sqlalchemy] sorting hybrid_properties getting NotImplementedError: Operator 'getitem' is not supported on this expression

Hi Mike, Thank you very much. A thousand years and I still wouldn't be able to figure that one out. And thank you also for clarifying use of Comparators - makes coding simpler with expression instead. Thanks, George On Saturday, 20 November 2021 at 00:28:00 UTC+11 Mike Bayer wrote: >

Re: [sqlalchemy] raise error on insert/update PK?

You ought to be able to use the "sqlalchemy.func" system: https://docs.sqlalchemy.org/en/14/core/tutorial.html#functions server_default=sa.func.gen_random_uuid() Hope that helps, Simon On Fri, Nov 19, 2021 at 6:21 AM jens.t...@gmail.com wrote: > > Tim, > > I wanted to offload the UUID

Re: [sqlalchemy] sorting hybrid_properties getting NotImplementedError: Operator 'getitem' is not supported on this expression

there's no need to use custom Comparator objects, these are difficult to get right and there's not really any need to use them except in the extremely unusual case that you need specific SQL operators to do something different, which itself is not really a thing in modern SQLAlchemy. Just use

Re: [sqlalchemy] sorting hybrid_properties getting NotImplementedError: Operator 'getitem' is not supported on this expression

heya - haven't run it yet but if you are on SQLAlchemy 1.3, the signature for case() is different (sorry, note the list ): https://docs.sqlalchemy.org/en/13/core/sqlelement.html?highlight=case#sqlalchemy.sql.expression.case expr = case( [(cls.Type == "SELL", cast(cls.Units *

[sqlalchemy] sorting hybrid_properties getting NotImplementedError: Operator 'getitem' is not supported on this expression

Hi All, Sorry About my previous post - did not read the posting Guidelines. Using Sqlalchemy 1.3.23 In this example using SQLlite memory, but is also happening in Postgresql 12.9 class TotalCostComparator(Comparator): def __init__(self, cls): expr = case( (cls.Type ==

Re: [sqlalchemy] raise error on insert/update PK?

Tim, I wanted to offload the UUID generation for the PK to the server ( server_default instead of just default argument). But I wasn’t able to find gen_random_uuid() documented in the PostgreSQL dialect

[sqlalchemy] sorting hybrid_properties getting NotImplementedError: Operator 'getitem' is not supported on this expression

Hi All, Using Sqlalchemy 1.3.23 I am getting a NotImplementedError: Operator 'getitem' is not supported on this expression when sorting on some hybrid_properties. I have attached a sample code to replicate it. falls over with the following traceback: Traceback (most recent call last): File

Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?

if you use 1.4 /2.0 querying style you can call upon the scalars() method of the result result = session.execute(select(MyClass.attr)) elements = result.scalars().all() or result = session.scalars(select(...)) elements = result.all() otherwise you can just iterate like this: elements = [e

Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?

This is very helpful. How to return a list of scalar values in this case? Now the query returns a list of tuples, and each tuple only has one value, which is what actually needed. Is there a parameter to return a series of scalar value like this? On Wednesday, November 10, 2021 at 12:05:27 PM

Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?

it has to do with how SQLAlchemy determines the FROM list in a select() statement. if you say select(table.c.id), it knows that "table" is the thing to select "from". however, if you say select(select(...).correlate(...).scalar_subquery()), that's assuming it's a SELECT from a scalar

Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?

This works! Could you explain a little about this differences by using select_from here? I think this is very important and useful, really want to learn it right. On Wednesday, November 10, 2021 at 5:55:44 AM UTC-8 Mike Bayer wrote: > try calling: > > query(C.symbol_from_a).select_from(C) > >

Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?

try calling: query(C.symbol_from_a).select_from(C) On Wed, Nov 10, 2021, at 4:50 AM, niuji...@gmail.com wrote: > class A(Base): > primary_id = Column(Integer, prirmary_key=True) > some_A_marker = Column(String) > > class B(Base): > primary_id = Column(Integer, primary_key=True) >

[sqlalchemy] correlated subquery as column_property can't be queried independently?

class A(Base): primary_id = Column(Integer, prirmary_key=True) some_A_marker = Column(String) class B(Base): primary_id = Column(Integer, primary_key=True) referencing_A_id = Column(Integer, ForeignKey(A.primary_id)) class C(Base): primary_id = Column(Integer,

Re: [sqlalchemy] Child class attributes not loading up in joined inheritance model

Thanks. I'll follow up at discussions. On Tuesday, November 9, 2021 at 5:56:52 PM UTC+4 Mike Bayer wrote: > note we are following up w/ this question at discussions: > https://github.com/sqlalchemy/sqlalchemy/discussions/7303 > > On Mon, Nov 8, 2021, at 6:07 AM, Shefeek Najeeb wrote: > > Hi

Re: [sqlalchemy] Child class attributes not loading up in joined inheritance model

note we are following up w/ this question at discussions: https://github.com/sqlalchemy/sqlalchemy/discussions/7303 On Mon, Nov 8, 2021, at 6:07 AM, Shefeek Najeeb wrote: > Hi all, > > It has been only a while since I've started tinkering with SQLAlchemy. I aws > working on a particular

[sqlalchemy] Child class attributes not loading up in joined inheritance model

Hi all, It has been only a while since I've started tinkering with SQLAlchemy. I aws working on a particular requirement of mine, where I had 4 types of users with login functionality. But each type has different attributes. The user types are : Client, Brand, Merchant, Customer. All these

[sqlalchemy] 100% Remote Role - Business Analyst

Hi Hope you are doing well! My name is Mohit Sandhu and I am working with Alpha Silicon. We Are Headquartered in Santa Clara, CA serving customers nationwide in the United States. We have the following Job Openings... *Position:* Business Analyst *Location:* 100% Remote *Duration:*

Re: [sqlalchemy] Many-to-many cascade delete

Thanks a lot, this is what I was hoping for! Anders On 11/2/21 9:33 PM, Mike Bayer wrote: The answers regarding "cascade" are correct, however this question has been asked a few times before and we have a recipe on the

Re: [sqlalchemy] Many-to-many cascade delete

The answers regarding "cascade" are correct, however this question has been asked a few times before and we have a recipe on the Wiki that discusses this problem and presents an ORM event oriented solution, which is at https://github.com/sqlalchemy/sqlalchemy/wiki/ManyToManyOrphan , which I put

Re: [sqlalchemy] Many-to-many cascade delete

Fundamentally the cascade applies to doing something to the 'many' side when something happens on the 'one' side. A many-to-many association can't do this sort of things, and in fact, you can't just 'create' a many-to-many relationship in a relational database. To implement a many-to-many

[sqlalchemy] Re: Many-to-many cascade delete

Interesting problem... it's not exactly a cascade delete, which goes from parent (1 side) to children (many side). I think you have to provide code to do this - brute force check the parent to see if it's an "empty nester". This can be expensive if you are cascading a person delete to many

[sqlalchemy] Many-to-many cascade delete

Hello All, Suppose I have a table of people, a table of secrets, and a many-to-many association table between them. A person can exist without knowing any secrets, but a secret without any associated people might as well be deleted. My reading of the documentation is that it is not possible to

Re: [sqlalchemy] Returning Postgres JSON view as JSON string without serialization

Thanks Mike, wrapping the view declaration with ()::TEXT worked perfectly, but good to know I can do it from SQLA at load time if need be. On Saturday 30 October 2021 at 02:17:49 UTC+1 Mike Bayer wrote: > oh also, if this is a view, much easier, just put the CAST to TEXT in your > CREATE VIEW

Re: [sqlalchemy] Returning Postgres JSON view as JSON string without serialization

oh also, if this is a view, much easier, just put the CAST to TEXT in your CREATE VIEW statement. that way you will definitely get strings back and nothing json related will kick in client side. On Fri, Oct 29, 2021, at 9:16 PM, Mike Bayer wrote: > psycopg2 driver (if that's what you're

Re: [sqlalchemy] Returning Postgres JSON view as JSON string without serialization

psycopg2 driver (if that's what you're using) jumps in to do the JSON so if you dont want json you need to cast as a string, like cast(table.c.json_col, String). if you are using a Table with autoload you'd want to override this type using the technique detailed at

[sqlalchemy] Returning Postgres JSON view as JSON string without serialization

I’m querying a Postgres view which returns JSON (SELECT json_build_object(…)) which I’ve declared as a view in my db: allinfra = Table("allinfra", db.metadata, autoload_with=db.engine) res = db.session.query(allinfra).scalar() But this gives me a Python dict, when what I want is the JSON string

[sqlalchemy] Posgtesql - delete with using

Hi, Is there a better way how to do it? Or i just miss something in the docs? var = a delete_stmt = delete(A).where(A.b_id==B.id, B.name==var) stmt = text(str(delete_stmt.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}))) db.execute(stmt) Thanks -- SQLAlchemy -

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

Re: [sqlalchemy] Is "uselist=False" imcompatible with lazy='joined' in relationship?

On Fri, Oct 22, 2021, at 6:45 AM, niuji...@gmail.com wrote: > Although the official documentation is very robust and detailed, one thing I > noticed is not very clear. > > When specifying arguments for a relationship like this: > > class Bonus(Base): > > basis =

[sqlalchemy] Is "uselist=False" imcompatible with lazy='joined' in relationship?

Although the official documentation is very robust and detailed, one thing I noticed is not very clear. When specifying arguments for a relationship like this: class Bonus(Base): basis = relationship("Accomplishment", uselist=False, lazy='joined') whenever there is

Re: [sqlalchemy] Alembic: Logging issue after configuration change (EBv2)

On Thu, Oct 21, 2021, at 12:08 PM, cmana...@gmail.com wrote: > Hi, > > Like the title says, I'm seeing some weird logging issues in production. > Using alembic and flask-migrate latest. Works great locally (no issues) and > worked great on the server prior to a platform change. > > I think

[sqlalchemy] Alembic: Logging issue after configuration change (EBv2)

Hi, Like the title says, I'm seeing some weird logging issues in production. Using alembic and flask-migrate latest. Works great locally (no issues) and worked great on the server prior to a platform change. I think I'm dealing with either a log level issue or I'm just logging into a black

Re: [sqlalchemy] Core insert and named columns

Ok, thank you. środa, 20 października 2021 o 19:05:46 UTC+2 Mike Bayer napisał(a): > https://github.com/sqlalchemy/sqlalchemy/issues/7217 > > On Wed, Oct 20, 2021, at 1:01 PM, Mike Bayer wrote: > > for the moment yes, because there is no ORM overlay for the insert() > construct.the case

Re: [sqlalchemy] Core insert and named columns

https://github.com/sqlalchemy/sqlalchemy/issues/7217 On Wed, Oct 20, 2021, at 1:01 PM, Mike Bayer wrote: > for the moment yes, because there is no ORM overlay for the insert() > construct.the case below should be improved however. > > reliable way is to use the attributes: > > stmt =

Re: [sqlalchemy] Core insert and named columns

for the moment yes, because there is no ORM overlay for the insert() construct. the case below should be improved however. reliable way is to use the attributes: stmt = insert(A).values({A.id: 1, A.field: 3}) On Wed, Oct 20, 2021, at 6:31 AM, Tomas Pavlovsky wrote: > Hi, > > class A():

[sqlalchemy] Core insert and named columns

Hi, class A(): id = Column('object_id", Integer, primary_key=True) field = Column(Integer, primary_key=True) insert(A).values(**{id:1, field:3}) sqlalchemy.exc.CompileError: Unconsumed column names: id It is normal behavior or not? Thanks -- SQLAlchemy - The Python SQL Toolkit and Object

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 >

[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.

Re: [sqlalchemy] Re: SqlAlchemy with Postgres: How can I make a query that checks if a string is in a list inside a json column

For what it's worth, I think the "?" operator would work for this with JSONB, but not with JSON: postgres=# select '["user1", "user2"]'::jsonb ? 'user1'; ?column? -- t (1 row) postgres=# select '["user1", "user2"]'::jsonb ? 'user2'; ?column? -- t (1 row) postgres=# select

[sqlalchemy] Re: SqlAlchemy with Postgres: How can I make a query that checks if a string is in a list inside a json column

I'm not sure, but AFAIK, this type of search isn't *easily* doable in PostgreSQL. The json and jsonb operators and functions are really targeting "object literals" style data, not lists. https://www.postgresql.org/docs/current/functions-json.html In the past, I think one could search against

[sqlalchemy] Re: SqlAlchemy with Postgres: How can I make a query that checks if a string is in a list inside a json column

Use .any(): session.query(Gizmo).filter(Gizmo.users.any('user1')) On Wednesday, October 13, 2021 at 11:50:16 PM UTC+10 chat...@gmail.com wrote: > Imagine a Postgres JSON column with values like below: > "["user1", "user2"]" > > Is there any way to query a postgres JSON (not JSONB) column

[sqlalchemy] SqlAlchemy with Postgres: How can I make a query that checks if a string is in a list inside a json column

Imagine a Postgres JSON column with values like below: "["user1", "user2"]" Is there any way to query a postgres JSON (not JSONB) column with SqlAlchemy,like above that checks if the value "user1" is contained in this column? -- SQLAlchemy - The Python SQL Toolkit and Object Relational

Re: [sqlalchemy] Is there an event hook for putting a class into class_registry?

your stack trace shows a different event hook that's causing the problem. codebase/database/__init__.py, so you want to move that event to before_mapper_configured: > File "/codebase_mountpoint/codebase/database/__init__.py", line 177, in > set_serialize_map > for c in

Re: [sqlalchemy] Is there an event hook for putting a class into class_registry?

Thank you Mike! My event-hooking code is like this: @sqlalchemy.event.listens_for(_MyBase, "class_instrument") def set_serialize_map(cls): module_logger.info( f" for {cls.__name__} is just invoked!") ... Here is the stack trace: 2021-10-10 03:47:34 AM database MainThread

Re: [sqlalchemy] Is there an event hook for putting a class into class_registry?

On Fri, Oct 8, 2021, at 4:13 PM, niuji...@gmail.com wrote: > Thanks for this very important information! > > If I do want to utilize the mapper functions at this stage, what can I do? > currently I have this error showing up: > > AttributeError: 'Mapper' object has no attribute

Re: [sqlalchemy] Is there an event hook for putting a class into class_registry?

Thanks for this very important information! If I do want to utilize the mapper functions at this stage, what can I do? currently I have this error showing up: AttributeError: 'Mapper' object has no attribute '_columntoproperty' On Friday, October 8, 2021 at 6:58:02 AM UTC-7 Mike Bayer wrote:

Re: [sqlalchemy] Is there an event hook for putting a class into class_registry?

On Fri, Oct 8, 2021, at 7:09 AM, niuji...@gmail.com wrote: > In order to make sure that the mapped class is fully ready, I chose the > latest point, namely `class_instrument`. > However, it seems that at that moment the `__mapper__` attribute is not > available. > When I tried > return

Re: [sqlalchemy] Is there an event hook for putting a class into class_registry?

In order to make sure that the mapped class is fully ready, I chose the latest point, namely `class_instrument`. However, it seems that at that moment the `__mapper__` attribute is not available. When I tried return cls.__mapper__.get_property_by_column(column_obj).key I got:

Re: [sqlalchemy] Is there an event hook for putting a class into class_registry?

events that occur around this time include this one claims it's before: https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.MapperEvents.instrument_class this one says after: https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.InstrumentationEvents.class_instrument

[sqlalchemy] Is there an event hook for putting a class into class_registry?

I want to do some after processing on each class that is just put into the class_registry. Is there a event hook for this? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and

Re: [sqlalchemy] 'validates' include_removes=True doesn't seem to prevent deletion?

On Mon, Oct 4, 2021, at 9:49 PM, niuji...@gmail.com wrote: > I have the following validator: > > @sa_orm.validates('variant_names', include_removes=True) > def validate_unique_identifying(self, key, name_dict, > is_remove): > if is_remove: >

[sqlalchemy] 'validates' include_removes=True doesn't seem to prevent deletion?

I have the following validator: @sa_orm.validates('variant_names', include_removes=True) def validate_unique_identifying(self, key, name_dict, is_remove): if is_remove: raise ValueError("DOn't") else: if

Re: [sqlalchemy] Can `sqlalchemy.orm.validates` be set on multiple attributes?

sure it's just a setter, it seems like you are passing "start_time_local" to your constructor, the ORM does not automatically add hybrid properties to the constructor so you would need to imlpement the __init__ method manually. On Fri, Oct 1, 2021, at 5:04 PM, niuji...@gmail.com wrote: > > Hi

Re: [sqlalchemy] Can `sqlalchemy.orm.validates` be set on multiple attributes?

Hi Mike, Instead of this: class Entity: def set_timestamp(self, timestamp, timezone): # ... which you have to manually call it to set, I'd like to have an automated setting directly on initiation, so that I can create new records in a more uniformed ways:

[sqlalchemy] many-to-many-to-one?

I'm trying to do something a little unusual, I'm open to alternative ideas for how to accomplish this as well but I think I need a 3 column mixer table with 3 foreign keys. Right now I have many to many relationships between 3 tables, e.g. a m2m b b mbm c however, I really something like

[sqlalchemy] Announcement: API Logic Server Docker Support

You may be interested in ApiLogicServer , open source on Git (79k downloads to date). With 1 command, it creates a customizable project providing a JSON:API, and a Basic Web App. These are based on SQLAlchemy, so it uses sqlacodegen to create

Re: [sqlalchemy] recursive cte use current row as anchor

changing it more the way you had lineage_nodes = recursive_cte.union_all( select([s.c.id, s.c._enabling_factor, s.c.selfreferencing_staff_id]) .join( recursive_cte, recursive_cte.c.selfreferencing_staff_id == s.c.id ) .filter(recursive_cte.c._enabling_factor == None) )

Re: [sqlalchemy] recursive cte use current row as anchor

i applied an alias to the CTE before unioning it, please take that out and try again. CTE inside the subquery can't happen, but shoudnt be necessary. On Sun, Sep 26, 2021, at 3:53 PM, niuji...@gmail.com wrote: > Hi Mike, > This code doesn't seem to yield desired result, now all the >

Re: [sqlalchemy] recursive cte use current row as anchor

Hi Mike, This code doesn't seem to yield desired result, now all the `effective_enabling_factor` is `None`, except for those rows that carries a `_enabling_factor` on itself. In other words, it doesn't seem to recusively search the next immediately parent's marker at all. And the SQL it

Re: [sqlalchemy] recursive cte use current row as anchor

OK there's various small issues here but overall SQLAlchemy's new "nesting" feature does not seem to work correctly yet for a RECURSIVE CTE with UNION, meaning it's not very useful for RECURSIVE right now. But here's the thing, I'm not as SQL expert as some people think but I don't think it

Re: [sqlalchemy] recursive cte use current row as anchor

Hi Mike, Yes the SQL code runs as desired, but the Python code doesn't, unfortunately. After reading the references you pointed out, my Python code looks like this: class Staff(Base): id = Column(Integer, primary_key=True) selfreferencing_staff_id = Column( Integer,

Re: [sqlalchemy] recursive cte use current row as anchor

well __class__.__table__ isn't going to be there inside the class body, just to make things simple you will want to add this column_property() after the Staff class is fully defined; then you make use of Staff. to get at columns.

Re: [sqlalchemy] recursive cte use current row as anchor

Hi Mike, thanks for pointing out the direction. I've worked out the SQL, but failed when converting to SQLAlchemy construct. My SQL query looks like this: SELECT id, ( WITH lineage_nodes (id, _enabling_factor, selfreferencing_staff_id) AS ( SELECT anchor_s.id,

Re: [sqlalchemy] recursive cte use current row as anchor

this is a hefty query to dig in to but column_property() subqueries have to be formed in terms of a correlated subquery. So instead of injecting a particular primary key into it, you set it to point to the Staff.id column. correlated subqueries are not terrific performers and the construct can

[sqlalchemy] recursive cte use current row as anchor

class Staff(Base): id = Column(Integer, primary_key=True) selfreferencing_staff_id = Column( Integer, ForeignKey('staff_table.id', onupdate="CASCADE", ondelete='SET NULL')) _enabling_factor = Column(Integer)

Re: [sqlalchemy] UPSERT method for MS SQL Server

it is something that would ideally be available for the MS SQL and Oracle dialects. We've gotten by implementing specific "upsert" constructs for SQLite, Postgresql and MySQL that are not "MERGE". The thing is MERGE is SQL standard, although I'm not sure it's worth it to make "MERGE" a

[sqlalchemy] UPSERT method for MS SQL Server

Hi, I have recently had to make a custom UPSERT method with Microsoft SQL, using their suggested MERGE method here: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15 Is it within the scope of the SQLAlchemy project to create an extension for

Re: [sqlalchemy] Decoupling business and ORM model using dataclass(es)

Many thanks - it is much clearer now! Best regards On Tuesday, September 21, 2021 at 5:25:41 PM UTC+2 Mike Bayer wrote: > > > On Tue, Sep 21, 2021, at 10:41 AM, Nikola Radovanovic wrote: > > Thank you. > > Maybe I did not explain well (English is not my first language): when I > declare a

Re: [sqlalchemy] Decoupling business and ORM model using dataclass(es)

On Tue, Sep 21, 2021, at 10:41 AM, Nikola Radovanovic wrote: > Thank you. > > Maybe I did not explain well (English is not my first language): when I > declare a dataclass to serve as business model (so something passed around > the code, between libraries, layers, frameworks, etc.) - I

Re: [sqlalchemy] Decoupling business and ORM model using dataclass(es)

Thank you. Maybe I did not explain well (English is not my first language): when I declare a dataclass to serve as business model (so something passed around the code, between libraries, layers, frameworks, etc.) - I don't want any ORM stuff in it. No matter if it is hidden in

Re: [sqlalchemy] Decoupling business and ORM model using dataclass(es)

On Mon, Sep 20, 2021, at 11:40 PM, Nikola Radovanovic wrote: > Thank you, > I am reading Architecture Patterns with Python > and trying to > find a way to refactor our code to become maintainable. However I noticed > that even

Re: [sqlalchemy] Decoupling business and ORM model using dataclass(es)

Thank you, I am reading Architecture Patterns with Python and trying to find a way to refactor our code to become maintainable. However I noticed that even with mapped dataclass there are problems like the one mentioned here

Re: [sqlalchemy] Decoupling business and ORM model using dataclass(es)

On Mon, Sep 20, 2021, at 5:04 AM, Nikola Radovanovic wrote: > > Hi, > When decoupling business from ORM model, dataclass passed to SA's imperative > mapper *map_imperatively* will receive SA's internals like for example > *_sa_instance_state* and *_sa_class_manager*. > > I am wondering, what

[sqlalchemy] Decoupling business and ORM model using dataclass(es)

Hi, When decoupling business from ORM model, dataclass passed to SA's imperative mapper *map_imperatively* will receive SA's internals like for example *_sa_instance_state* and *_sa_class_manager*. I am wondering, what would be the best way to have "pure" dataclass, without SA internals?

Re: [sqlalchemy] Re: AsyncEngine always returns UTC time for datetime column

using the "sync" engine I would assume you are using psycopg2, which is going to use native Postgresql client libraries. asyncpg I think is written in cython and doesn't actually use libpq if im reading it correctly. therefore the difference may be a libpq related setting of the local timezone

[sqlalchemy] Re: AsyncEngine always returns UTC time for datetime column

Hi Jonathan, I use postgres docker image from docker hub version 12 with this settings in docker-compose file: db: image: postgres:12 environment: POSTGRES_USER: test_db POSTGRES_DB: test_db POSTGRES_PASS: test_db POSTGRES_HOST_AUTH_METHOD: trust

<    4   5   6   7   8   9   10   11   12   13   >