Re: [sqlalchemy] Migration issues - Column value None on select with joins

[Mike Bayer]

in the below mentioned issue I've created a patch at 
https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4615 that removes a 
thread-sensitive approach to generating an internal structure, which is where I 
suspect this might be happening (if anywhere), and then it also adds a guard 
against the None objects in any case.you'd want to take a look at that and 
see if it resolves.

On Fri, May 12, 2023, at 11:30 PM, Mike Bayer wrote:
> I've opened https://github.com/sqlalchemy/sqlalchemy/issues/9777 for this.
> 
> I have another theory where something might be going wrong earler in the 
> chain. This change would be "simpler" in that it removes something that's 
> complicated, prone to race conditions hence seems a good possibility of being 
> involved here.   This would be the patch:
> 
> diff --git a/lib/sqlalchemy/orm/strategies.py 
> b/lib/sqlalchemy/orm/strategies.py
> index 8e06c4f598..bd0193b905 100644
> --- a/lib/sqlalchemy/orm/strategies.py
> +++ b/lib/sqlalchemy/orm/strategies.py
> @@ -2324,8 +2324,7 @@ class JoinedLoader(AbstractRelationshipLoader):
>  else:
>  context.attributes[key] = idx = context.attributes[key] + 1
>  
> -if idx >= len(self._aliased_class_pool):
> -to_adapt = orm_util.AliasedClass(
> +return orm_util.AliasedClass(
>  self.mapper,
>  alias=alt_selectable._anonymous_fromclause(flat=True)
>  if alt_selectable is not None
> @@ -2334,14 +2333,6 @@ class JoinedLoader(AbstractRelationshipLoader):
>  use_mapper_path=True,
>  )
>  
> -# load up the .columns collection on the Alias() before
> -# the object becomes shared among threads.  this prevents
> -# races for column identities.
> -inspect(to_adapt).selectable.c
> -self._aliased_class_pool.append(to_adapt)
> -
> -return self._aliased_class_pool[idx]
> -
>  def _generate_row_adapter(
>  self,
>  compile_state,
> 
> 
> 
> 
> On Fri, May 12, 2023, at 11:05 PM, Shane Holcombe wrote:
>> There seems to be a few moving parts to this so it's been hard to track 
>> down, I can give that fix a try in a few hours when I'm home.
>> I was worried something like that might lose some columns but I'll try and 
>> see what happens.
>> Thanks
>> On Saturday, May 13, 2023 at 12:59:33 PM UTC+10 Mike Bayer wrote:
>>> __
>>> well, not sure yet how a None is coming in there that is sporadic, or even 
>>> at all, but if that's what's happening then we would think this patch would 
>>> fix the problem, the Q is does the query still produce the correct results:
>>> 
>>> diff --git a/lib/sqlalchemy/orm/strategies.py 
>>> b/lib/sqlalchemy/orm/strategies.py
>>> index 8e06c4f598..2bac1aad48 100644
>>> --- a/lib/sqlalchemy/orm/strategies.py
>>> +++ b/lib/sqlalchemy/orm/strategies.py
>>> @@ -218,10 +218,10 @@ class ColumnLoader(LoaderStrategy):
>>>  if adapter:
>>>  if check_for_adapt:
>>>  c = adapter.adapt_check_present(c)
>>> -if c is None:
>>> -return
>>>  else:
>>>  c = adapter.columns[c]
>>> +if c is None:
>>> +return
>>>  
>>>  compile_state._append_dedupe_col_collection(c, 
>>> column_collection)
>>> 
>>> 
>>> 
>>> can you try that patch ?
>>> 
>>> 
>>> On Fri, May 12, 2023, at 10:47 PM, Mike Bayer wrote:
 OK, maybe you are onto something with the theory re: 
 JoinedLoader._generate_row_adapter(). will look at that
 
 On Fri, May 12, 2023, at 6:16 PM, Shane Holcombe wrote:
> Thanks for linking that github issue, completely missed that one.
> 
> Here's the complete stack trace, sorry for not including that at the start
> 
> Traceback (most recent call last):
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", 
> line 333, in run_wsgi
> execute(self.server.app)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", 
> line 320, in execute
> application_iter = app(environ, start_response)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
> line 2551, in __call__
> return self.wsgi_app(environ, start_response)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
> line 2531, in wsgi_app
> response = self.handle_exception(e)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/extension.py",
>  line 165, in wrapped_function
> return cors_after_request(app.make_response(f(*args, **kwargs)))
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", 
> line 673, in error_router
> return original_handler(f)
>   File 
> 

Re: [sqlalchemy] Migration issues - Column value None on select with joins

[Mike Bayer]

I've opened https://github.com/sqlalchemy/sqlalchemy/issues/9777 for this.

I have another theory where something might be going wrong earler in the chain. 
This change would be "simpler" in that it removes something that's 
complicated, prone to race conditions hence seems a good possibility of being 
involved here.   This would be the patch:

diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 8e06c4f598..bd0193b905 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -2324,8 +2324,7 @@ class JoinedLoader(AbstractRelationshipLoader):
 else:
 context.attributes[key] = idx = context.attributes[key] + 1
 
-if idx >= len(self._aliased_class_pool):
-to_adapt = orm_util.AliasedClass(
+return orm_util.AliasedClass(
 self.mapper,
 alias=alt_selectable._anonymous_fromclause(flat=True)
 if alt_selectable is not None
@@ -2334,14 +2333,6 @@ class JoinedLoader(AbstractRelationshipLoader):
 use_mapper_path=True,
 )
 
-# load up the .columns collection on the Alias() before
-# the object becomes shared among threads.  this prevents
-# races for column identities.
-inspect(to_adapt).selectable.c
-self._aliased_class_pool.append(to_adapt)
-
-return self._aliased_class_pool[idx]
-
 def _generate_row_adapter(
 self,
 compile_state,




On Fri, May 12, 2023, at 11:05 PM, Shane Holcombe wrote:
> There seems to be a few moving parts to this so it's been hard to track down, 
> I can give that fix a try in a few hours when I'm home.
> I was worried something like that might lose some columns but I'll try and 
> see what happens.
> Thanks
> On Saturday, May 13, 2023 at 12:59:33 PM UTC+10 Mike Bayer wrote:
>> __
>> well, not sure yet how a None is coming in there that is sporadic, or even 
>> at all, but if that's what's happening then we would think this patch would 
>> fix the problem, the Q is does the query still produce the correct results:
>> 
>> diff --git a/lib/sqlalchemy/orm/strategies.py 
>> b/lib/sqlalchemy/orm/strategies.py
>> index 8e06c4f598..2bac1aad48 100644
>> --- a/lib/sqlalchemy/orm/strategies.py
>> +++ b/lib/sqlalchemy/orm/strategies.py
>> @@ -218,10 +218,10 @@ class ColumnLoader(LoaderStrategy):
>>  if adapter:
>>  if check_for_adapt:
>>  c = adapter.adapt_check_present(c)
>> -if c is None:
>> -return
>>  else:
>>  c = adapter.columns[c]
>> +if c is None:
>> +return
>>  
>>  compile_state._append_dedupe_col_collection(c, 
>> column_collection)
>> 
>> 
>> 
>> can you try that patch ?
>> 
>> 
>> On Fri, May 12, 2023, at 10:47 PM, Mike Bayer wrote:
>>> OK, maybe you are onto something with the theory re: 
>>> JoinedLoader._generate_row_adapter(). will look at that
>>> 
>>> On Fri, May 12, 2023, at 6:16 PM, Shane Holcombe wrote:
 Thanks for linking that github issue, completely missed that one.
 
 Here's the complete stack trace, sorry for not including that at the start
 
 Traceback (most recent call last):
   File 
 "/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", 
 line 333, in run_wsgi
 execute(self.server.app)
   File 
 "/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", 
 line 320, in execute
 application_iter = app(environ, start_response)
   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
 line 2551, in __call__
 return self.wsgi_app(environ, start_response)
   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
 line 2531, in wsgi_app
 response = self.handle_exception(e)
   File 
 "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/extension.py", 
 line 165, in wrapped_function
 return cors_after_request(app.make_response(f(*args, **kwargs)))
   File 
 "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
 673, in error_router
 return original_handler(f)
   File 
 "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
 671, in error_router
 return self.handle_error(e)
   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
 line 2528, in wsgi_app
 response = self.full_dispatch_request()
   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
 line 1825, in full_dispatch_request
 rv = self.handle_user_exception(e)
   File 
 "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/extension.py", 
 line 165, in wrapped_function
 return cors_after_request(app.make_response(f(*args, 

Re: [sqlalchemy] Migration issues - Column value None on select with joins

[Shane Holcombe]

There seems to be a few moving parts to this so it's been hard to track 
down, I can give that fix a try in a few hours when I'm home.
I was worried something like that might lose some columns but I'll try and 
see what happens.
Thanks

On Saturday, May 13, 2023 at 12:59:33 PM UTC+10 Mike Bayer wrote:

> well, not sure yet how a None is coming in there that is sporadic, or even 
> at all, but if that's what's happening then we would think this patch would 
> fix the problem, the Q is does the query still produce the correct results:
>
> diff --git a/lib/sqlalchemy/orm/strategies.py 
> b/lib/sqlalchemy/orm/strategies.py
> index 8e06c4f598..2bac1aad48 100644
> --- a/lib/sqlalchemy/orm/strategies.py
> +++ b/lib/sqlalchemy/orm/strategies.py
> @@ -218,10 +218,10 @@ class ColumnLoader(LoaderStrategy):
>  if adapter:
>  if check_for_adapt:
>  c = adapter.adapt_check_present(c)
> -if c is None:
> -return
>  else:
>  c = adapter.columns[c]
> +if c is None:
> +return
>  
>  compile_state._append_dedupe_col_collection(c, 
> column_collection)
>
>
>
> can you try that patch ?
>
>
> On Fri, May 12, 2023, at 10:47 PM, Mike Bayer wrote:
>
> OK, maybe you are onto something with the theory re: 
> JoinedLoader._generate_row_adapter(). will look at that
>
> On Fri, May 12, 2023, at 6:16 PM, Shane Holcombe wrote:
>
> Thanks for linking that github issue, completely missed that one.
>
> Here's the complete stack trace, sorry for not including that at the start
>
> Traceback (most recent call last):
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", line 
> 333, in run_wsgi
> execute(self.server.app)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", line 
> 320, in execute
> application_iter = app(environ, start_response)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
> line 2551, in __call__
> return self.wsgi_app(environ, start_response)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
> line 2531, in wsgi_app
> response = self.handle_exception(e)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/extension.py", 
> line 165, in wrapped_function
> return cors_after_request(app.make_response(f(*args, **kwargs)))
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
> 673, in error_router
> return original_handler(f)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
> 671, in error_router
> return self.handle_error(e)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
> line 2528, in wsgi_app
> response = self.full_dispatch_request()
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
> line 1825, in full_dispatch_request
> rv = self.handle_user_exception(e)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/extension.py", 
> line 165, in wrapped_function
> return cors_after_request(app.make_response(f(*args, **kwargs)))
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
> 673, in error_router
> return original_handler(f)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
> 671, in error_router
> return self.handle_error(e)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
> line 1823, in full_dispatch_request
> rv = self.dispatch_request()
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", 
> line 1799, in dispatch_request
> return 
> self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
> 404, in wrapper
> resp = resource(*args, **kwargs)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/views.py", 
> line 107, in view
> return current_app.ensure_sync(self.dispatch_request)(**kwargs)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/resource.py", 
> line 46, in dispatch_request
> resp = meth(*args, **kwargs)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/decorator.py", 
> line 128, in wrapped_function
> resp = make_response(f(*args, **kwargs))
>   File "/Users/sfh/PycharmProjects/vita/stockholm/geneva/api/auth.py", 
> line 151, in decorated
> return f(*args, **kwargs)
>   File 
> "/Users/sfh/PycharmProjects/vita/stockholm/geneva/api/sales/lead_api.py", 
> line 116, in get
> lead = lead_factory.extended_lead_for_id(lead_id)
>   File 
> "/Users/sfh/PycharmProjects/vita/stockholm/geneva/sales/lead_factory.py", 
> line 59, in extended_lead_for_id
> return db.session.execute(stmt).scalar_one_or_none()
>   File 
> 

Re: [sqlalchemy] Migration issues - Column value None on select with joins

[Mike Bayer]

well, not sure yet how a None is coming in there that is sporadic, or even at 
all, but if that's what's happening then we would think this patch would fix 
the problem, the Q is does the query still produce the correct results:

diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 8e06c4f598..2bac1aad48 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -218,10 +218,10 @@ class ColumnLoader(LoaderStrategy):
 if adapter:
 if check_for_adapt:
 c = adapter.adapt_check_present(c)
-if c is None:
-return
 else:
 c = adapter.columns[c]
+if c is None:
+return
 
 compile_state._append_dedupe_col_collection(c, column_collection)



can you try that patch ?


On Fri, May 12, 2023, at 10:47 PM, Mike Bayer wrote:
> OK, maybe you are onto something with the theory re: 
> JoinedLoader._generate_row_adapter(). will look at that
> 
> On Fri, May 12, 2023, at 6:16 PM, Shane Holcombe wrote:
>> Thanks for linking that github issue, completely missed that one.
>> 
>> Here's the complete stack trace, sorry for not including that at the start
>> 
>> Traceback (most recent call last):
>>   File 
>> "/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", line 
>> 333, in run_wsgi
>> execute(self.server.app)
>>   File 
>> "/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", line 
>> 320, in execute
>> application_iter = app(environ, start_response)
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
>> 2551, in __call__
>> return self.wsgi_app(environ, start_response)
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
>> 2531, in wsgi_app
>> response = self.handle_exception(e)
>>   File 
>> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/extension.py", 
>> line 165, in wrapped_function
>> return cors_after_request(app.make_response(f(*args, **kwargs)))
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", 
>> line 673, in error_router
>> return original_handler(f)
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", 
>> line 671, in error_router
>> return self.handle_error(e)
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
>> 2528, in wsgi_app
>> response = self.full_dispatch_request()
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
>> 1825, in full_dispatch_request
>> rv = self.handle_user_exception(e)
>>   File 
>> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/extension.py", 
>> line 165, in wrapped_function
>> return cors_after_request(app.make_response(f(*args, **kwargs)))
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", 
>> line 673, in error_router
>> return original_handler(f)
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", 
>> line 671, in error_router
>> return self.handle_error(e)
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
>> 1823, in full_dispatch_request
>> rv = self.dispatch_request()
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
>> 1799, in dispatch_request
>> return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", 
>> line 404, in wrapper
>> resp = resource(*args, **kwargs)
>>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/views.py", 
>> line 107, in view
>> return current_app.ensure_sync(self.dispatch_request)(**kwargs)
>>   File 
>> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/resource.py", 
>> line 46, in dispatch_request
>> resp = meth(*args, **kwargs)
>>   File 
>> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/decorator.py", 
>> line 128, in wrapped_function
>> resp = make_response(f(*args, **kwargs))
>>   File "/Users/sfh/PycharmProjects/vita/stockholm/geneva/api/auth.py", line 
>> 151, in decorated
>> return f(*args, **kwargs)
>>   File 
>> "/Users/sfh/PycharmProjects/vita/stockholm/geneva/api/sales/lead_api.py", 
>> line 116, in get
>> lead = lead_factory.extended_lead_for_id(lead_id)
>>   File 
>> "/Users/sfh/PycharmProjects/vita/stockholm/geneva/sales/lead_factory.py", 
>> line 59, in extended_lead_for_id
>> return db.session.execute(stmt).scalar_one_or_none()
>>   File 
>> "/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/orm/scoping.py", 
>> line 724, in execute
>> return self._proxied.execute(
>>   File 
>> "/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/orm/session.py", 
>> line 2232, in execute
>> return self._execute_internal(
>>   File 
>> 

Re: [sqlalchemy] Migration issues - Column value None on select with joins

[Mike Bayer]

OK, maybe you are onto something with the theory re: 
JoinedLoader._generate_row_adapter(). will look at that

On Fri, May 12, 2023, at 6:16 PM, Shane Holcombe wrote:
> Thanks for linking that github issue, completely missed that one.
> 
> Here's the complete stack trace, sorry for not including that at the start
> 
> Traceback (most recent call last):
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", 
> line 333, in run_wsgi
> execute(self.server.app)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", 
> line 320, in execute
> application_iter = app(environ, start_response)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
> 2551, in __call__
> return self.wsgi_app(environ, start_response)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
> 2531, in wsgi_app
> response = self.handle_exception(e)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/extension.py", 
> line 165, in wrapped_function
> return cors_after_request(app.make_response(f(*args, **kwargs)))
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", 
> line 673, in error_router
> return original_handler(f)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", 
> line 671, in error_router
> return self.handle_error(e)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
> 2528, in wsgi_app
> response = self.full_dispatch_request()
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
> 1825, in full_dispatch_request
> rv = self.handle_user_exception(e)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/extension.py", 
> line 165, in wrapped_function
> return cors_after_request(app.make_response(f(*args, **kwargs)))
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", 
> line 673, in error_router
> return original_handler(f)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", 
> line 671, in error_router
> return self.handle_error(e)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
> 1823, in full_dispatch_request
> rv = self.dispatch_request()
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
> 1799, in dispatch_request
> return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", 
> line 404, in wrapper
> resp = resource(*args, **kwargs)
>   File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/views.py", line 
> 107, in view
> return current_app.ensure_sync(self.dispatch_request)(**kwargs)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/resource.py", 
> line 46, in dispatch_request
> resp = meth(*args, **kwargs)
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/decorator.py", 
> line 128, in wrapped_function
> resp = make_response(f(*args, **kwargs))
>   File "/Users/sfh/PycharmProjects/vita/stockholm/geneva/api/auth.py", line 
> 151, in decorated
> return f(*args, **kwargs)
>   File 
> "/Users/sfh/PycharmProjects/vita/stockholm/geneva/api/sales/lead_api.py", 
> line 116, in get
> lead = lead_factory.extended_lead_for_id(lead_id)
>   File 
> "/Users/sfh/PycharmProjects/vita/stockholm/geneva/sales/lead_factory.py", 
> line 59, in extended_lead_for_id
> return db.session.execute(stmt).scalar_one_or_none()
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/orm/scoping.py", 
> line 724, in execute
> return self._proxied.execute(
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/orm/session.py", 
> line 2232, in execute
> return self._execute_internal(
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/orm/session.py", 
> line 2127, in _execute_internal
> result: Result[Any] = compile_state_cls.orm_execute_statement(
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/orm/context.py", 
> line 292, in orm_execute_statement
> result = conn.execute(
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
> line 1413, in execute
> return meth(
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", 
> line 483, in _execute_on_connection
> return connection._execute_clauseelement(
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
> line 1629, in _execute_clauseelement
> compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
>   File 
> "/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", 
> line 671, in _compile_w_cache
> compiled_sql = self._compiler(
>   File 
> 

Re: [sqlalchemy] Migration issues - Column value None on select with joins

[Shane Holcombe]

Thanks for linking that github issue, completely missed that one.

Here's the complete stack trace, sorry for not including that at the start

Traceback (most recent call last):
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", line 
333, in run_wsgi
execute(self.server.app)
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/werkzeug/serving.py", line 
320, in execute
application_iter = app(environ, start_response)
  File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
2551, in __call__
return self.wsgi_app(environ, start_response)
  File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
2531, in wsgi_app
response = self.handle_exception(e)
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/extension.py", 
line 165, in wrapped_function
return cors_after_request(app.make_response(f(*args, **kwargs)))
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
673, in error_router
return original_handler(f)
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
671, in error_router
return self.handle_error(e)
  File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
2528, in wsgi_app
response = self.full_dispatch_request()
  File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
1825, in full_dispatch_request
rv = self.handle_user_exception(e)
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/extension.py", 
line 165, in wrapped_function
return cors_after_request(app.make_response(f(*args, **kwargs)))
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
673, in error_router
return original_handler(f)
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
671, in error_router
return self.handle_error(e)
  File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
1823, in full_dispatch_request
rv = self.dispatch_request()
  File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/app.py", line 
1799, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/api.py", line 
404, in wrapper
resp = resource(*args, **kwargs)
  File "/Users/sfh/env/vita/lib/python3.9/site-packages/flask/views.py", 
line 107, in view
return current_app.ensure_sync(self.dispatch_request)(**kwargs)
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/flask_restx/resource.py", 
line 46, in dispatch_request
resp = meth(*args, **kwargs)
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/flask_cors/decorator.py", 
line 128, in wrapped_function
resp = make_response(f(*args, **kwargs))
  File "/Users/sfh/PycharmProjects/vita/stockholm/geneva/api/auth.py", line 
151, in decorated
return f(*args, **kwargs)
  File 
"/Users/sfh/PycharmProjects/vita/stockholm/geneva/api/sales/lead_api.py", 
line 116, in get
lead = lead_factory.extended_lead_for_id(lead_id)
  File 
"/Users/sfh/PycharmProjects/vita/stockholm/geneva/sales/lead_factory.py", 
line 59, in extended_lead_for_id
return db.session.execute(stmt).scalar_one_or_none()
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/orm/scoping.py", 
line 724, in execute
return self._proxied.execute(
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/orm/session.py", 
line 2232, in execute
return self._execute_internal(
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/orm/session.py", 
line 2127, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/orm/context.py", 
line 292, in orm_execute_statement
result = conn.execute(
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
line 1413, in execute
return meth(
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", 
line 483, in _execute_on_connection
return connection._execute_clauseelement(
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/engine/base.py", 
line 1629, in _execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", 
line 671, in _compile_w_cache
compiled_sql = self._compiler(
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", 
line 288, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", 
line 1426, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
  File 
"/Users/sfh/env/vita/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", 

Re: [sqlalchemy] Inconsistent SQL Generation in 2.0

[S Mahabl]

https://stackoverflow.com/questions/48811239/how-to-set-alias-in-the-sqlalchemy-for-the-table

Another idea is to create a view in another schema pointing to the same
table ...

On Fri, May 12, 2023 at 3:48 PM Benjamin Taub 
wrote:

> Thanks for the suggestion.
> I thought about using an alias but came up with two problems:
> 1) The FROM clause is auto-generated from SELECT clause, so I'm not sure
> where I would add this alias
> 2) All the aliases in the SELECT clause are also double-quoted. I believe
> a single backquote would be more standard here. Perhaps there is a general
> use on double quotes in SQL generation when backquotes would be more
> appropriate?
>
> In my case, I can solve this by making sure that schema names always start
> with a letter. This will prevent similar situations lurking elsewhere in my
> code. But, I'll have to be careful that I don't mess up any code that deals
> with previously-created schemas. Shouldn't be much of an issue today,
> however.
>
> On Friday, May 12, 2023 at 4:38:23 PM UTC-4 S Mahabl wrote:
>
>> Is there alias you can give for
>>
>> *283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19 XYZ*
>>
>> *?*
>>
>> On Fri, May 12, 2023, 3:30 PM Benjamin Taub 
>> wrote:
>>
>>> I have code that worked under SQLAlchemy 1.4 but recently upgraded to 2.
>>> I am using the add_columns() method to add columns to an existing SQL
>>> statement. The resultant queries sometimes, but not always, crash. I
>>> believe the issue happens when the schema/database name (I'm using MySQL)
>>> starts with a number. When the schema name starts with a letter, the result
>>> runs fine. However, when it starts with a number, the query double-quotes
>>> the schema name, causing the query to crash.
>>>
>>> Here is an example...
>>> My code: *sql = sql.add_columns(self.tbl.c[field])*
>>>
>>> When the schema holding self.tbl.c[field] starts with a letter
>>> (c6961a19b7ed031ce902f056c725b3e3), the following SQL is generated:
>>>
>>>
>>> *SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last
>>> Name", NULL AS "Email",
>>> c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4.master_key
>>> FROM c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4*
>>>
>>> However, when the schema name starts with a number
>>> (283ac7717fe770c5ed6d425c0c739cba), the following SQL results:
>>>
>>> *SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last
>>> Name", NULL AS "Email",
>>> "283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19.master_key
>>> FROM "283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19*
>>>
>>> Note the double quotes around the schema name. This second SQL crashes
>>> as invalid. Back quotes (`) would probably work fine in this situation, and
>>> could be helpful, but double quotes (") are, I think, the cause of my
>>> problem.
>>>
>>> Is there some parameter or assumption that I'm not understanding, or did
>>> I find a bug?
>>>
>>> Thank you!
>>> Ben
>>>
>>>
>>> --
>>> 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/76d38390-e17f-4b90-a438-ee078944b5ffn%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/7022e294-b5bc-493a-b24f-b3f1939741ban%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 

Re: [sqlalchemy] Inconsistent SQL Generation in 2.0

[Mike Bayer]

On Fri, May 12, 2023, at 4:30 PM, Benjamin Taub wrote:
> I have code that worked under SQLAlchemy 1.4 but recently upgraded to 2. I am 
> using the add_columns() method to add columns to an existing SQL statement. 
> The resultant queries sometimes, but not always, crash. I believe the issue 
> happens when the schema/database name (I'm using MySQL) starts with a number. 
> When the schema name starts with a letter, the result runs fine. However, 
> when it starts with a number, the query double-quotes the schema name, 
> causing the query to crash.
> 
> Here is an example...
> My code: *sql = sql.add_columns(self.tbl.c[field])*
> 
> When the schema holding self.tbl.c[field] starts with a letter 
> (c6961a19b7ed031ce902f056c725b3e3), the following SQL is generated:
> *SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last Name", 
> NULL AS "Email", 
> c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4.master_key
>  
> FROM c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4*
> 
> However, when the schema name starts with a number 
> (283ac7717fe770c5ed6d425c0c739cba), the following SQL results:
> *SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last Name", 
> NULL AS "Email", 
> "283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19.master_key
>  
> FROM "283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19*
> 
> Note the double quotes around the schema name. This second SQL crashes as 
> invalid. Back quotes (`) would probably work fine in this situation, and 
> could be helpful, but double quotes (") are, I think, the cause of my problem.
> 
> Is there some parameter or assumption that I'm not understanding, or did I 
> find a bug?

The quoting, if it were the correct quoting format, should be fine.   As to why 
it's the quote char and not the backtick, are you compiling these queries 
manually?You would want to make sure a MySQL dialect is in use, which would 
be using backticks for quoting, unless that dialect were initialized against a 
MySQL database that has ANSI_QUOTES set.

TL;DR quoting is a new thing here but SQLAlchemy should render the correct 
quotes when used with the MySQL dialect.

Here's a demo:

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy.orm import declarative_base
Base = declarative_base()


class A(Base):
__tablename__ = 't_59a33cbea3617986d810e9fbae60ba19'

__table_args__ = {
"schema": "283ac7717fe770c5ed6d425c0c739cba"
}
id = Column(Integer, primary_key=True)
data = Column(String)

e = create_engine("mysql://")

stmt = select(A)

print(stmt.compile(e))

output:

SELECT 
`283ac7717fe770c5ed6d425c0c739cba`.t_59a33cbea3617986d810e9fbae60ba19.id, 
`283ac7717fe770c5ed6d425c0c739cba`.t_59a33cbea3617986d810e9fbae60ba19.data 
FROM `283ac7717fe770c5ed6d425c0c739cba`.t_59a33cbea3617986d810e9fbae60ba19



> 
> Thank you!
> Ben
> 
> 
> 
> 
> -- 
> 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/76d38390-e17f-4b90-a438-ee078944b5ffn%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/a50db81a-5271-4f3e-81c4-d4f02cc38732%40app.fastmail.com.

Re: [sqlalchemy] Inconsistent SQL Generation in 2.0

[Benjamin Taub]

Thanks for the suggestion. 
I thought about using an alias but came up with two problems:
1) The FROM clause is auto-generated from SELECT clause, so I'm not sure 
where I would add this alias
2) All the aliases in the SELECT clause are also double-quoted. I believe a 
single backquote would be more standard here. Perhaps there is a general 
use on double quotes in SQL generation when backquotes would be more 
appropriate?

In my case, I can solve this by making sure that schema names always start 
with a letter. This will prevent similar situations lurking elsewhere in my 
code. But, I'll have to be careful that I don't mess up any code that deals 
with previously-created schemas. Shouldn't be much of an issue today, 
however.

On Friday, May 12, 2023 at 4:38:23 PM UTC-4 S Mahabl wrote:

> Is there alias you can give for
>
> *283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19 XYZ*
>
> *?*
>
> On Fri, May 12, 2023, 3:30 PM Benjamin Taub  
> wrote:
>
>> I have code that worked under SQLAlchemy 1.4 but recently upgraded to 2. 
>> I am using the add_columns() method to add columns to an existing SQL 
>> statement. The resultant queries sometimes, but not always, crash. I 
>> believe the issue happens when the schema/database name (I'm using MySQL) 
>> starts with a number. When the schema name starts with a letter, the result 
>> runs fine. However, when it starts with a number, the query double-quotes 
>> the schema name, causing the query to crash.
>>
>> Here is an example...
>> My code: *sql = sql.add_columns(self.tbl.c[field])*
>>
>> When the schema holding self.tbl.c[field] starts with a letter 
>> (c6961a19b7ed031ce902f056c725b3e3), the following SQL is generated:
>>
>>
>> *SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last 
>> Name", NULL AS "Email", 
>> c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4.master_key
>>  
>> FROM c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4*
>>
>> However, when the schema name starts with a number 
>> (283ac7717fe770c5ed6d425c0c739cba), the following SQL results:
>>
>> *SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last 
>> Name", NULL AS "Email", 
>> "283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19.master_key
>>  
>> FROM "283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19*
>>
>> Note the double quotes around the schema name. This second SQL crashes as 
>> invalid. Back quotes (`) would probably work fine in this situation, and 
>> could be helpful, but double quotes (") are, I think, the cause of my 
>> problem.
>>
>> Is there some parameter or assumption that I'm not understanding, or did 
>> I find a bug?
>>
>> Thank you!
>> Ben
>>
>>
>> -- 
>> 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/76d38390-e17f-4b90-a438-ee078944b5ffn%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/7022e294-b5bc-493a-b24f-b3f1939741ban%40googlegroups.com.

Re: [sqlalchemy] Inconsistent SQL Generation in 2.0

[S Mahabl]

Is there alias you can give for

*283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19 XYZ*

*?*

On Fri, May 12, 2023, 3:30 PM Benjamin Taub 
wrote:

> I have code that worked under SQLAlchemy 1.4 but recently upgraded to 2. I
> am using the add_columns() method to add columns to an existing SQL
> statement. The resultant queries sometimes, but not always, crash. I
> believe the issue happens when the schema/database name (I'm using MySQL)
> starts with a number. When the schema name starts with a letter, the result
> runs fine. However, when it starts with a number, the query double-quotes
> the schema name, causing the query to crash.
>
> Here is an example...
> My code: *sql = sql.add_columns(self.tbl.c[field])*
>
> When the schema holding self.tbl.c[field] starts with a letter
> (c6961a19b7ed031ce902f056c725b3e3), the following SQL is generated:
>
>
> *SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last
> Name", NULL AS "Email",
> c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4.master_key
> FROM c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4*
>
> However, when the schema name starts with a number
> (283ac7717fe770c5ed6d425c0c739cba), the following SQL results:
>
> *SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last
> Name", NULL AS "Email",
> "283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19.master_key
> FROM "283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19*
>
> Note the double quotes around the schema name. This second SQL crashes as
> invalid. Back quotes (`) would probably work fine in this situation, and
> could be helpful, but double quotes (") are, I think, the cause of my
> problem.
>
> Is there some parameter or assumption that I'm not understanding, or did I
> find a bug?
>
> Thank you!
> Ben
>
>
> --
> 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/76d38390-e17f-4b90-a438-ee078944b5ffn%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/CAOV%2B3C3NWdxjjqDX6j8uEgKh_SxSRYDKp%2B9M%2BZydMO4OLjVCxw%40mail.gmail.com.

[sqlalchemy] Inconsistent SQL Generation in 2.0

[Benjamin Taub]

I have code that worked under SQLAlchemy 1.4 but recently upgraded to 2. I 
am using the add_columns() method to add columns to an existing SQL 
statement. The resultant queries sometimes, but not always, crash. I 
believe the issue happens when the schema/database name (I'm using MySQL) 
starts with a number. When the schema name starts with a letter, the result 
runs fine. However, when it starts with a number, the query double-quotes 
the schema name, causing the query to crash.

Here is an example...
My code: *sql = sql.add_columns(self.tbl.c[field])*

When the schema holding self.tbl.c[field] starts with a letter 
(c6961a19b7ed031ce902f056c725b3e3), the following SQL is generated:


*SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last 
Name", NULL AS "Email", 
c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4.master_key 
FROM c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4*

However, when the schema name starts with a number 
(283ac7717fe770c5ed6d425c0c739cba), the following SQL results:

*SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last 
Name", NULL AS "Email", 
"283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19.master_key
 
FROM "283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19*

Note the double quotes around the schema name. This second SQL crashes as 
invalid. Back quotes (`) would probably work fine in this situation, and 
could be helpful, but double quotes (") are, I think, the cause of my 
problem.

Is there some parameter or assumption that I'm not understanding, or did I 
find a bug?

Thank you!
Ben


-- 
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/76d38390-e17f-4b90-a438-ee078944b5ffn%40googlegroups.com.

Re: [sqlalchemy] Migration issues - Column value None on select with joins

[Mike Bayer]

I really need to see:

1. actual models to the degree they illustrate the default loaders set up on 
relationships
2. query in use
3. **complete** stack trace, put it on a github gist if it's too long.  every 
line, the error, top to bottom.  thanks

On Fri, May 12, 2023, at 11:25 AM, Mike Bayer wrote:
> 
> 
> On Fri, May 12, 2023, at 1:25 AM, Shane Holcombe wrote:
>> AttributeError: 'NoneType' object has no attribute '_from_objects'
>> (I've shortened this from the entire traceback)
> 
> OK what kind of traceback, is it a recursion overflow kind of traceback ?  
> (e.g. many hundreds of repeated series of lines)? that would explain the 
> nonsensical error at least (there's no way for None to be in the place that 
> this error reports it)
> 
> 
> 
> -- 
> 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/9762bf53-8443-4780-9091-136863716290%40app.fastmail.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/b4f9ab0e-c402-4828-9db4-1385ad7b10a4%40app.fastmail.com.

Re: [sqlalchemy] Migration issues - Column value None on select with joins

[Mike Bayer]

On Fri, May 12, 2023, at 1:25 AM, Shane Holcombe wrote:
> AttributeError: 'NoneType' object has no attribute '_from_objects'
> (I've shortened this from the entire traceback)

OK what kind of traceback, is it a recursion overflow kind of traceback ?  
(e.g. many hundreds of repeated series of lines)? that would explain the 
nonsensical error at least (there's no way for None to be in the place that 
this error reports it)

-- 
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/9762bf53-8443-4780-9091-136863716290%40app.fastmail.com.

Re: [sqlalchemy] Migration issues - Column value None on select with joins

[Mike Bayer]

this has also been reported at 
https://github.com/sqlalchemy/sqlalchemy/discussions/9666



On Fri, May 12, 2023, at 1:25 AM, Shane Holcombe wrote:
> 
> From the digging around that I've done, it seems to be the ColumnLoader 
> strategy setup_query method sometimes has a value for 'c' of None where an 
> adapter is present but check_for_adapt is False. This is from the c = 
> adapter.columns[c] line.
> This happens only when called from the JoinedLoader class, it seems the 
> _generate_row_adapter method returns a clauses object with a columns dict 
> that has all the keys from the underlying table, but some of the values are 
> None.
> 
> All of this behaviour is fairly hard to get to happen however it seems to 
> occur after the warning "Loader depth for query is excessively deep; caching 
> will be disabled for additional loaders.  Consider using the recursion_depth 
> feature for deeply nested recursive eager loaders." occurs.
> This warning comes from a different part of the system where the base level 
> object, 'User', is very deeply nested, and on some later request to a 
> different part of the system, fetching a different 'User' relationship at a 
> less deep level triggers the above error.
> In our use case we have some fairly deeply nested relationships, 6 or so 
> levels deep, however we never used selectinload or immediateload to my 
> knowledge so there is no way to change the recursion depth.

we would need a reproduction case in order to see what this is about


> 
> Any help on this would be fantastic,
> Thanks,
> Shane
> 
> 
> 
> -- 
> 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/f6ee8641-0dc8-4eec-a79e-941ac3d814b9n%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/a5f85728-b423-4eb3-9470-65bc12ccab6b%40app.fastmail.com.