Re: [sqlalchemy] How to infer the ORM model class from an update()/insert() object?

[mkmo...@gmail.com]

Hi Mike,

Thanks for taking a look into it. Even if only the simple case can be taken 
care of in 2.0, that would be good as it would set up 2.x for completing 
the edge cases over time.

> select(User).from_statement(insert(User).returning(User, 
User.my_column_property)) 

In my particular case, my library function doesn't know anything about the 
model being passed in. I'm just taking in an insert or update, adding a 
return, and executing it.

I think I'll stick with ` ins.returning(*select(model).selected_columns)` 
for the time being - it seems to to the trick. 

Best regards,

Matthew
On Monday, March 28, 2022 at 11:49:54 AM UTC-7 Mike Bayer wrote:

> since it will be very hard to change this after 2.0 is released I will try 
> to further attempt to get ORM objects to be returned, though this will not 
> at first support any special features:
>
> https://github.com/sqlalchemy/sqlalchemy/issues/7865
>
> this will allow retunring(User) to send back an instance but there's a lot 
> of cases to be worked out.
>
>
>
>
> On Mon, Mar 28, 2022, at 2:05 PM, Mike Bayer wrote:
>
>
>
> On Mon, Mar 28, 2022, at 1:31 PM, mkmo...@gmail.com wrote:
>
> Hi Mike,
>
> When using `column_property`, this 
> `select(User).from_statement(ins.returing(User))` construct will not load 
> in the column property. Instead the ORM will issue a second query when the 
> column property is accessed.
>
> I am able to get it working using the following: 
> `select(User).from_statement(ins.returing(*select(User).selected_columns))` 
>
> I get your point that there may not be much of a demand for this, but I 
> would argue that it is a bit unexpected for `returning(User)` to return a 
> Core row, and that the solution is bit unintuitive.
>
>
> as I already agreed, it is unintuitive for now, but it's not clear it can 
> be made fully automatic.  it would be potentially a very large job for 
> something that can already be achieved right now with a little more API use.
>
>
>
> I think it should be as easy as .returning(User) and it should return 
> the full ORM model with column_properties preloaded.
>
>
> this remains a non-trivial improvement that is not on the timeline right 
> now, so you will have to work with what we have.
>
>
>
> This proposed change is backwards incompatible right? E.g. if people 
> are depending on `returning(User)` returning a core Row in 2.0, is it OK to 
> change this to return a Model instance in 2.1?
>
>
> not really sure, this is part of the problem.   we reserve the right to 
> make backwards incompatible changes in a the middle point since we are not 
> on semver.   as returning(User) is not that intuitive when the documented 
> approach isn't used, we will assume people are not using that form very 
> much should we decide to implement this feature.
>
>
> By the way, I think I found a bug with insert().values() when the ORM uses 
> different field names than the Database column names.  update().values() 
> works fine, but not insert().values(). Please check my issue here when you 
> have a moment.
>
>
> that can likely be improved for 2.0.
>
>
> https://github.com/sqlalchemy/sqlalchemy/issues/7864
>
> --
>
> Here is how to reproduce the case where column_property results in an 
> extra query, if you are interested:
>
>
> sure, there's a very complex process that's used to SELECT all columns.  
> your use case should work right now if you do something like this:
>
> select(User).from_statement(insert(User).returning(User, 
> User.my_column_property))
>
>
>
>
>
> class User(Base):
> __tablename__ = 'users'
> id = Column(Integer, primary_key=True)
> first_name = Column(String(30))
> last_name = Column(String(30))
> full_name = column_property(first_name + " " + last_name)
> 
> # returning(User) triggers extra query on column_property access
> res = 
> session.execute(select(User).from_statement(insert(User).values(first_name='foo',
>  
> last_name='bar').returning(User)))
> user = res.scalars().one()
> # This triggers a select
> print(user.full_name)
>
> session.expunge(user)
> 
> # normal query  does not trigger a select as expected
> res = session.execute(select(User).where(User.id == user.id))
> user = res.scalars().one()
> print(user.full_name)
> 
> session.expunge(user)
>
> # use *selected(User).selected_columns to avoid triggering an extra 
> select
> res = 
> session.execute(select(User).from_statement(update(User).values(first_name='foo2').where(User.id
>  
> == user.id).returning(*select(User).selected_columns)))
> user = res.scalars().one()
> # no extra query
> print(user.full_name)
>
> Thanks and best regards,
>
> Matthew
>
>
> On Sunday, March 27, 2022 at 7:28:55 PM UTC-7 Mike Bayer wrote:
>
>
>
> On Sun, Mar 27, 2022, at 2:56 PM, mkmo...@gmail.com wrote:
>
> Hi Mike,
>
> I'm writing a library that uses SQLAlchemy. The user will pass the library 
> an update, 

Re: [sqlalchemy] How to infer the ORM model class from an update()/insert() object?

[Mike Bayer]

since it will be very hard to change this after 2.0 is released I will try to 
further attempt to get ORM objects to be returned, though this will not at 
first support any special features:

https://github.com/sqlalchemy/sqlalchemy/issues/7865

this will allow retunring(User) to send back an instance but there's a lot of 
cases to be worked out.




On Mon, Mar 28, 2022, at 2:05 PM, Mike Bayer wrote:
> 
> 
> On Mon, Mar 28, 2022, at 1:31 PM, mkmo...@gmail.com wrote:
>> Hi Mike,
>> 
>> When using `column_property`, this 
>> `select(User).from_statement(ins.returing(User))` construct will not load in 
>> the column property. Instead the ORM will issue a second query when the 
>> column property is accessed.
>> 
>> I am able to get it working using the following: 
>> `select(User).from_statement(ins.returing(*select(User).selected_columns))` 
>> 
>> I get your point that there may not be much of a demand for this, but I 
>> would argue that it is a bit unexpected for `returning(User)` to return a 
>> Core row, and that the solution is bit unintuitive.
> 
> as I already agreed, it is unintuitive for now, but it's not clear it can be 
> made fully automatic.  it would be potentially a very large job for something 
> that can already be achieved right now with a little more API use.
> 
> 
>> 
>> I think it should be as easy as .returning(User) and it should return 
>> the full ORM model with column_properties preloaded.
> 
> this remains a non-trivial improvement that is not on the timeline right now, 
> so you will have to work with what we have.
> 
> 
>> 
>> This proposed change is backwards incompatible right? E.g. if people are 
>> depending on `returning(User)` returning a core Row in 2.0, is it OK to 
>> change this to return a Model instance in 2.1?
> 
> not really sure, this is part of the problem.   we reserve the right to make 
> backwards incompatible changes in a the middle point since we are not on 
> semver.   as returning(User) is not that intuitive when the documented 
> approach isn't used, we will assume people are not using that form very much 
> should we decide to implement this feature.
> 
>> 
>> By the way, I think I found a bug with insert().values() when the ORM uses 
>> different field names than the Database column names.  update().values() 
>> works fine, but not insert().values(). Please check my issue here when you 
>> have a moment.
> 
> that can likely be improved for 2.0.
> 
>> 
>> https://github.com/sqlalchemy/sqlalchemy/issues/7864
>> 
>> --
>> 
>> Here is how to reproduce the case where column_property results in an extra 
>> query, if you are interested:
> 
> sure, there's a very complex process that's used to SELECT all columns.  your 
> use case should work right now if you do something like this:
> 
> select(User).from_statement(insert(User).returning(User, 
> User.my_column_property))
> 
> 
> 
> 
>> 
>> class User(Base):
>> __tablename__ = 'users'
>> id = Column(Integer, primary_key=True)
>> first_name = Column(String(30))
>> last_name = Column(String(30))
>> full_name = column_property(first_name + " " + last_name)
>> 
>> # returning(User) triggers extra query on column_property access
>> res = 
>> session.execute(select(User).from_statement(insert(User).values(first_name='foo',
>>  last_name='bar').returning(User)))
>> user = res.scalars().one()
>> # This triggers a select
>> print(user.full_name)
>> 
>> session.expunge(user)
>> 
>> # normal query  does not trigger a select as expected
>> res = session.execute(select(User).where(User.id == user.id))
>> user = res.scalars().one()
>> print(user.full_name)
>> 
>> session.expunge(user)
>> 
>> # use *selected(User).selected_columns to avoid triggering an extra 
>> select
>> res = 
>> session.execute(select(User).from_statement(update(User).values(first_name='foo2').where(User.id
>>  == user.id).returning(*select(User).selected_columns)))
>> user = res.scalars().one()
>> # no extra query
>> print(user.full_name)
>> 
>> Thanks and best regards,
>> 
>> Matthew
>> 
>> 
>> On Sunday, March 27, 2022 at 7:28:55 PM UTC-7 Mike Bayer wrote:
>>> 
>>> 
>>> On Sun, Mar 27, 2022, at 2:56 PM, mkmo...@gmail.com wrote:
 Hi Mike,
 
 I'm writing a library that uses SQLAlchemy. The user will pass the library 
 an update, and the library will add a RETURNING clause for postgresql 
 users, and then return the model back to the user. The idea here is to 
 update and select the row in a single database call, instead of the normal 
 approach where two calls are made.
 
 However, `upd.returning(User)` will actually return a Core row, not the 
 ORM model instance:
 
 upd = update(User).values(name='foo').where(User.id == 
 1).returning(User)
 result = session.execute(upd)
 row = result.one()
 assert isinstance(row, Row)
 
 The 

Re: [sqlalchemy] How to infer the ORM model class from an update()/insert() object?

[Mike Bayer]

On Mon, Mar 28, 2022, at 1:31 PM, mkmo...@gmail.com wrote:
> Hi Mike,
> 
> When using `column_property`, this 
> `select(User).from_statement(ins.returing(User))` construct will not load in 
> the column property. Instead the ORM will issue a second query when the 
> column property is accessed.
> 
> I am able to get it working using the following: 
> `select(User).from_statement(ins.returing(*select(User).selected_columns))` 
> 
> I get your point that there may not be much of a demand for this, but I would 
> argue that it is a bit unexpected for `returning(User)` to return a Core row, 
> and that the solution is bit unintuitive.

as I already agreed, it is unintuitive for now, but it's not clear it can be 
made fully automatic.  it would be potentially a very large job for something 
that can already be achieved right now with a little more API use.


> 
> I think it should be as easy as .returning(User) and it should return the 
> full ORM model with column_properties preloaded.

this remains a non-trivial improvement that is not on the timeline right now, 
so you will have to work with what we have.


> 
> This proposed change is backwards incompatible right? E.g. if people are 
> depending on `returning(User)` returning a core Row in 2.0, is it OK to 
> change this to return a Model instance in 2.1?

not really sure, this is part of the problem.   we reserve the right to make 
backwards incompatible changes in a the middle point since we are not on 
semver.   as returning(User) is not that intuitive when the documented approach 
isn't used, we will assume people are not using that form very much should we 
decide to implement this feature.

> 
> By the way, I think I found a bug with insert().values() when the ORM uses 
> different field names than the Database column names.  update().values() 
> works fine, but not insert().values(). Please check my issue here when you 
> have a moment.

that can likely be improved for 2.0.

> 
> https://github.com/sqlalchemy/sqlalchemy/issues/7864
> 
> --
> 
> Here is how to reproduce the case where column_property results in an extra 
> query, if you are interested:

sure, there's a very complex process that's used to SELECT all columns.  your 
use case should work right now if you do something like this:

select(User).from_statement(insert(User).returning(User, 
User.my_column_property))




> 
> class User(Base):
> __tablename__ = 'users'
> id = Column(Integer, primary_key=True)
> first_name = Column(String(30))
> last_name = Column(String(30))
> full_name = column_property(first_name + " " + last_name)
> 
> # returning(User) triggers extra query on column_property access
> res = 
> session.execute(select(User).from_statement(insert(User).values(first_name='foo',
>  last_name='bar').returning(User)))
> user = res.scalars().one()
> # This triggers a select
> print(user.full_name)
> 
> session.expunge(user)
> 
> # normal query  does not trigger a select as expected
> res = session.execute(select(User).where(User.id == user.id))
> user = res.scalars().one()
> print(user.full_name)
> 
> session.expunge(user)
> 
> # use *selected(User).selected_columns to avoid triggering an extra select
> res = 
> session.execute(select(User).from_statement(update(User).values(first_name='foo2').where(User.id
>  == user.id).returning(*select(User).selected_columns)))
> user = res.scalars().one()
> # no extra query
> print(user.full_name)
> 
> Thanks and best regards,
> 
> Matthew
> 
> 
> On Sunday, March 27, 2022 at 7:28:55 PM UTC-7 Mike Bayer wrote:
>> 
>> 
>> On Sun, Mar 27, 2022, at 2:56 PM, mkmo...@gmail.com wrote:
>>> Hi Mike,
>>> 
>>> I'm writing a library that uses SQLAlchemy. The user will pass the library 
>>> an update, and the library will add a RETURNING clause for postgresql 
>>> users, and then return the model back to the user. The idea here is to 
>>> update and select the row in a single database call, instead of the normal 
>>> approach where two calls are made.
>>> 
>>> However, `upd.returning(User)` will actually return a Core row, not the ORM 
>>> model instance:
>>> 
>>> upd = update(User).values(name='foo').where(User.id == 
>>> 1).returning(User)
>>> result = session.execute(upd)
>>> row = result.one()
>>> assert isinstance(row, Row)
>>> 
>>> The key question I have is how to convert a Core row into an ORM model 
>>> instance.
>>> 
>> 
>> use the construct select(User).from_statement(update(User)...returning()) .  
>>  See the example at 
>> https://docs.sqlalchemy.org/en/14/orm/persistence_techniques.html#using-insert-update-and-on-conflict-i-e-upsert-to-return-orm-objects
>>  
>> 
>> 
>>> 
>>>  `model(**row._mapping)`  fails in at least these two cases: different 
>>> field name in ORM vs database, and column_property.
>>> 
>>> I also wonder, should SQLAlchemy return the Model instead of the core row 

Re: [sqlalchemy] How to infer the ORM model class from an update()/insert() object?

[mkmo...@gmail.com]

Hi Mike,

When using `column_property`, this 
`select(User).from_statement(ins.returing(User))` construct will not load 
in the column property. Instead the ORM will issue a second query when the 
column property is accessed.

I am able to get it working using the following: 
`select(User).from_statement(ins.returing(*select(User).selected_columns))` 

I get your point that there may not be much of a demand for this, but I 
would argue that it is a bit unexpected for `returning(User)` to return a 
Core row, and that the solution is bit unintuitive.

I think it should be as easy as .returning(User) and it should return 
the full ORM model with column_properties preloaded.

This proposed change is backwards incompatible right? E.g. if people 
are depending on `returning(User)` returning a core Row in 2.0, is it OK to 
change this to return a Model instance in 2.1?

By the way, I think I found a bug with insert().values() when the ORM uses 
different field names than the Database column names.  update().values() 
works fine, but not insert().values(). Please check my issue here when you 
have a moment.

https://github.com/sqlalchemy/sqlalchemy/issues/7864

--

Here is how to reproduce the case where column_property results in an extra 
query, if you are interested:

class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
first_name = Column(String(30))
last_name = Column(String(30))
full_name = column_property(first_name + " " + last_name)

# returning(User) triggers extra query on column_property access
res = 
session.execute(select(User).from_statement(insert(User).values(first_name='foo',
 
last_name='bar').returning(User)))
user = res.scalars().one()
# This triggers a select
print(user.full_name)

session.expunge(user)

# normal query  does not trigger a select as expected 
res = session.execute(select(User).where(User.id == user.id))
user = res.scalars().one()
print(user.full_name)

session.expunge(user)

# use *selected(User).selected_columns to avoid triggering an extra 
select
res = 
session.execute(select(User).from_statement(update(User).values(first_name='foo2').where(User.id
 
== user.id).returning(*select(User).selected_columns)))
user = res.scalars().one()
# no extra query
print(user.full_name)

Thanks and best regards,

Matthew


On Sunday, March 27, 2022 at 7:28:55 PM UTC-7 Mike Bayer wrote:

>
>
> On Sun, Mar 27, 2022, at 2:56 PM, mkmo...@gmail.com wrote:
>
> Hi Mike,
>
> I'm writing a library that uses SQLAlchemy. The user will pass the library 
> an update, and the library will add a RETURNING clause for postgresql 
> users, and then return the model back to the user. The idea here is to 
> update and select the row in a single database call, instead of the normal 
> approach where two calls are made.
>
> However, `upd.returning(User)` will actually return a Core row, not the 
> ORM model instance:
>
> upd = update(User).values(name='foo').where(User.id == 
> 1).returning(User)
> result = session.execute(upd)
> row = result.one()
> assert isinstance(row, Row)
>
> The key question I have is how to convert a Core row into an ORM model 
> instance.
>
>
> use the construct select(User).from_statement(update(User)...returning()) 
> .   See the example at 
> https://docs.sqlalchemy.org/en/14/orm/persistence_techniques.html#using-insert-update-and-on-conflict-i-e-upsert-to-return-orm-objects
>  
>
>
>
>  `model(**row._mapping)`  fails in at least these two cases: different 
> field name in ORM vs database, and column_property.
>
> I also wonder, should SQLAlchemy return the Model instead of the core row 
> in this case?
>
>
> unknown at this time.  The above link illustrates a very new technique by 
> which this can work now.if this were to become more implicit without 
> the extra step, that would at best be a 2.1 thing not expected for at least 
> 18 months, it would be based on general demand for this kind of thing 
> (which does seem to be increasing).
>
>
>
>
>
>
>
> ---
>
> I've gotten this far:
>
> model = model_from_dml(upd)
> upd = upd.returning(*select(model).selected_columns)   # this will 
> apply the column_property to the RETURNING
> row = session.execute(upd).one()
>
> Now I just need to take this row and convert it to an ORM object.
>
> Is there a public API I can use to take a Core `row` and convert it to an 
> ORM model?
>
> Thanks and best regards,
>
> Matthew
> On Sunday, March 27, 2022 at 11:11:30 AM UTC-7 Mike Bayer wrote:
>
>
>
> On Sun, Mar 27, 2022, at 2:08 PM, mkmo...@gmail.com wrote:
>
> Hi Mike,
>
> Thanks. Should I use column_descriptions[0]['type'] ?
>
>
> yup, that should be pretty consistent in this case.
>
> I've implemented most of an actual feature for this but isn't committed 
> yet at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/3742
>
>
>
> In my 

Re: [sqlalchemy] Looking for help with interpreting an error/debugging

[Mike Bayer]

there's probably something up with that server so you will still get errors, 
but at least they will be familiar looking

On Mon, Mar 28, 2022, at 9:01 AM, jens.t...@gmail.com wrote:
> Thank you Mike for your prompt response! I opened an issue CONPY-197 
> , let’s see what comes of that.
> 
> I’ve used mysql+pymysql:// quite happily in the past with a MySQL db, and 
> when we switched to MariaDB I switched the drivers. It didn’t occur to me 
> that I could keep using the MySQL drivers, especially considering that 
> MariaDB claims to be compatible… I’ll try that!
> 
> Jens
> 
> 
> On Monday, March 28, 2022 at 10:35:56 PM UTC+10 Mike Bayer wrote:
>> __
>> hey there -
>> 
>> never seen that before so it is very likely to be an issue specific to 
>> mariadbconnector, which is a much lesser-used driver.  suggestions are to 
>> seek help from mariadb connector devs directly, in the interim try 
>> mysqlclient or pymysql to see if the issue resolves, or shows a better error 
>> message.
> 
> 
> -- 
> 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/c49fa1c5-86f1-4c2e-beb7-b1b93dc46dc0n%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/4acb25d7-3941-48f9-836c-309ce9dd9219%40www.fastmail.com.

Re: [sqlalchemy] Looking for help with interpreting an error/debugging

[jens.t...@gmail.com]

Thank you Mike for your prompt response! I opened an issue CONPY-197 
, let’s see what comes of that.

I’ve used mysql+pymysql:// quite happily in the past with a MySQL db, and 
when we switched to MariaDB I switched the drivers. It didn’t occur to me 
that I could keep using the MySQL drivers, especially considering that 
MariaDB claims to be compatible… I’ll try that!

Jens


On Monday, March 28, 2022 at 10:35:56 PM UTC+10 Mike Bayer wrote:

> hey there -
>
> never seen that before so it is very likely to be an issue specific to 
> mariadbconnector, which is a much lesser-used driver.  suggestions are to 
> seek help from mariadb connector devs directly, in the interim try 
> mysqlclient or pymysql to see if the issue resolves, or shows a better 
> error message.
>

-- 
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/c49fa1c5-86f1-4c2e-beb7-b1b93dc46dc0n%40googlegroups.com.

Re: [sqlalchemy] Looking for help with interpreting an error/debugging

[Mike Bayer]

hey there -

never seen that before so it is very likely to be an issue specific to 
mariadbconnector, which is a much lesser-used driver.  suggestions are to seek 
help from mariadb connector devs directly, in the interim try mysqlclient or 
pymysql to see if the issue resolves, or shows a better error message.



On Mon, Mar 28, 2022, at 6:58 AM, jens.t...@gmail.com wrote:
> Hello,
> 
> I’m utterly stomped by this problem and I hope that the wisdom of the crowd 
> (and the much more experienced users here) can point me into the right 
> direction.
> 
> I’ve got a single MariaDB  (Ver 15.1 Distrib 
> 10.6.5-MariaDB, for Linux (x86_64)) running natively on a Linux host, and 
> multiple containerized web servers (Pyramid ) access 
> that db through a mounted volume /var/run/mysqld/mysqld.sock. Pyramid uses 
> the pyramid_tm  package to manage its 
> per-request transactions, and it uses SQLAlchemy v1.4.31 with the connect URL
> 
> sqlalchemy.url = 
> mariadb+mariadbconnector://user:pwd@localhost/db?charset=utf8_prefix=true_socket=/var/run/mysqld/mysqld.sock
> 
> For two days now I’ve seen repeatedly the following errors for only one of 
> the servers (oddly, one user of that server) while the other servers seem to 
> operate just fine and continue talking to the db without problems.
> 
> Traceback (most recent call last):
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
> line 1802, in _execute_context
> self.dialect.do_execute(
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", 
> line 732, in do_execute
> cursor.execute(statement, parameters)
> mariadb.InterfaceError
> 
> The above exception was the direct cause of the following exception:
> 
> Traceback (most recent call last):
>   File "/.../venv/lib/python3.9/site-packages/waitress/channel.py", line 426, 
> in service
> task.service()
>   File "/.../venv/lib/python3.9/site-packages/waitress/task.py", line 168, in 
> service
> self.execute()
>   ...
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", 
> line 481, in __get__
> return self.impl.get(state, dict_)
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", 
> line 941, in get
> value = self._fire_loader_callables(state, key, passive)
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", 
> line 977, in _fire_loader_callables
> return self.callable_(state, passive)
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py", 
> line 911, in _load_for_state
> return self._emit_lazyload(
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py", 
> line 1047, in _emit_lazyload
> result = session.execute(
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", 
> line 1692, in execute
> result = conn._execute_20(statement, params or {}, execution_options)
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
> line 1614, in _execute_20
> return meth(self, args_10style, kwargs_10style, execution_options)
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", 
> line 325, in _execute_on_connection
> return connection._execute_clauseelement(
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
> line 1481, in _execute_clauseelement
> ret = self._execute_context(
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
> line 1845, in _execute_context
> self._handle_dbapi_exception(
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
> line 2026, in _handle_dbapi_exception
> util.raise_(
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", 
> line 207, in raise_
> raise exception
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
> line 1802, in _execute_context
> self.dialect.do_execute(
>   File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", 
> line 732, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.InterfaceError: (mariadb.InterfaceError) 
> [SQL: SELECT credits.id AS credits_id, credits.created AS credits_created, 
> credits.expires AS credits_expires, credits.deleted AS credits_deleted, 
> credits.user_id AS credits_user_id, credits.type AS credits_type, 
> credits.unit_price AS credits_unit_price, credits.quantity AS 
> credits_quantity, credits.count AS credits_count, credits.max_count AS 
> credits_max_count, credits.stripe_invoice_id AS credits_stripe_invoice_id, 
> credits.stripe_charge_id AS credits_stripe_charge_id, 
> credits.stripe_refunded_charge_id AS credits_stripe_refunded_charge_id, 
> credits.import_formats AS credits_import_formats, credits.export_formats AS 
> credits_export_formats, credits.coupon_id AS credits_coupon_id 
> FROM 

[sqlalchemy] Looking for help with interpreting an error/debugging

[jens.t...@gmail.com]

Hello,

I’m utterly stomped by this problem and I hope that the wisdom of the crowd 
(and the much more experienced users here) can point me into the right 
direction.

I’ve got a single MariaDB  (Ver 15.1 Distrib 
10.6.5-MariaDB, for Linux (x86_64)) running natively on a Linux host, and 
multiple containerized web servers (Pyramid ) 
access that db through a mounted volume /var/run/mysqld/mysqld.sock. 
Pyramid uses the pyramid_tm  package 
to manage its per-request transactions, and it uses SQLAlchemy v1.4.31 with 
the connect URL

sqlalchemy.url = 
mariadb+mariadbconnector://user:pwd@localhost/db?charset=utf8_prefix=true_socket=/var/run/mysqld/mysqld.sock

For two days now I’ve seen repeatedly the following errors for only one of 
the servers (oddly, one user of that server) while the other servers seem 
to operate just fine and continue talking to the db without problems.

Traceback (most recent call last):
  File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
line 1802, in _execute_context
self.dialect.do_execute(
  File 
"/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 
732, in do_execute
cursor.execute(statement, parameters)
mariadb.InterfaceError

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/.../venv/lib/python3.9/site-packages/waitress/channel.py", line 
426, in service
task.service()
  File "/.../venv/lib/python3.9/site-packages/waitress/task.py", line 168, 
in service
self.execute()
  ...
  File 
"/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 
481, in __get__
return self.impl.get(state, dict_)
  File 
"/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 
941, in get
value = self._fire_loader_callables(state, key, passive)
  File 
"/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 
977, in _fire_loader_callables
return self.callable_(state, passive)
  File 
"/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py", line 
911, in _load_for_state
return self._emit_lazyload(
  File 
"/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py", line 
1047, in _emit_lazyload
result = session.execute(
  File "/.../venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", 
line 1692, in execute
result = conn._execute_20(statement, params or {}, execution_options)
  File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
line 1614, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
  File "/.../venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", 
line 325, in _execute_on_connection
return connection._execute_clauseelement(
  File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
line 1481, in _execute_clauseelement
ret = self._execute_context(
  File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
line 1845, in _execute_context
self._handle_dbapi_exception(
  File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
line 2026, in _handle_dbapi_exception
util.raise_(
  File "/.../venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", 
line 207, in raise_
raise exception
  File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
line 1802, in _execute_context
self.dialect.do_execute(
  File 
"/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 
732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.InterfaceError: (mariadb.InterfaceError) 
[SQL: SELECT credits.id AS credits_id, credits.created AS credits_created, 
credits.expires AS credits_expires, credits.deleted AS credits_deleted, 
credits.user_id AS credits_user_id, credits.type AS credits_type, 
credits.unit_price AS credits_unit_price, credits.quantity AS 
credits_quantity, credits.count AS credits_count, credits.max_count AS 
credits_max_count, credits.stripe_invoice_id AS credits_stripe_invoice_id, 
credits.stripe_charge_id AS credits_stripe_charge_id, 
credits.stripe_refunded_charge_id AS credits_stripe_refunded_charge_id, 
credits.import_formats AS credits_import_formats, credits.export_formats AS 
credits_export_formats, credits.coupon_id AS credits_coupon_id 
FROM credits 
WHERE ? = credits.user_id ORDER BY -credits.created]
[parameters: ('bd87a54b6ca8404baeca03fc216f5e4e',)]
(Background on this error at: https://sqlalche.me/e/14/rvf5)

The other error I’m seeing scattered throughout is:

Traceback (most recent call last):
  File "/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
line 1802, in _execute_context
self.dialect.do_execute(
  File 
"/.../venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 
732, in do_execute
cursor.execute(statement, parameters)