Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Mike Bayer]

This test doesn't run yet because it looks like you need to have initializers 
for things like Content.current_translation, the setup_class fails right now 
because that isn't handled.

In any case, adapting the window recipe from 
https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions
 to the code here can be done directly:

 _alias = (
 select(
 [
 translation_cls,
 func.row_number()
 .over(
 order_by=[
 desc(
 translation_cls.language_id
 == bindparam(
 None,
 callable_=lambda: current_locale(),
 type_=String(),
 )
 ),
 desc(
 translation_cls.language_id
 == bindparam(
 None,
 callable_=lambda: fallback_locale(),
 type_=String(),
 )
 ),
 ],
 partition_by=translation_cls.content_id,
 )
 .label("index"),
 ]
 )
 .where(
 and_(
 translation_cls.language_id.in_(
 (
 bindparam(
 None,
 callable_=lambda: current_locale(),
 type_=String(),
 ),
 bindparam(
 None,
 callable_=lambda: fallback_locale(),
 type_=String(),
 ),
 )
 )
 )
 )
 .alias()
 )

 _alias2 = aliased(translation_cls, _alias)

 content_mapper.add_properties(
 {
 "current_translation": relationship(
 _alias2,
 primaryjoin=and_(
 foreign(_alias2.content_id) == content_cls.content_id,
 _alias.c.index == 1,
 ),
 lazy="joined",
 uselist=False,
 innerjoin=True,
 viewonly=True,
 bake_queries=False,
 ),
 }
 )


On Tue, Jun 18, 2019, at 11:34 AM, Julien Cigar wrote:
> Hello Mike,
> 
> As always thank you for your quick and useful reply. I might not need LATERAL 
> but would be very interrested to see a solution with WINDOW functions ..
> 
> I've added a small test case with some comment on what I'd like to achieve. 
> Basically what I would like is to be able to select the "current" translation 
> in one query, and that it is transparent (it's a Pyramid plugin), that's why 
> I'm also using hybrid properties
> 
> 
> On Tuesday, June 18, 2019 at 3:38:36 PM UTC+2, Mike Bayer wrote:
>> 
>> 
>> On Tue, Jun 18, 2019, at 6:21 AM, Julien Cigar wrote:
>>> Hello,
>>> 
>>> I'm trying to add a 'read-only' relationship involving a subquery and I 
>>> have some problems with remote and foreign sides, SQLAlchemy returns:
>>> 
>>> sqlalchemy.exc.ArgumentError: Relationship Folder.current_translation could 
>>> not determine any unambiguous local/remote column pairs based on join 
>>> condition and remote_side arguments. Consider using the remote() annotation 
>>> to accurately mark those elements of the join condition that are on the 
>>> remote side of the relationship.
>>> 
>>> I've copied my code here 
>>> https://gist.github.com/silenius/e2c7fb393864b3f1fee8131336dd8b61
>>> 
>>> 
>>> 
>>> Any idea what could be wrong ..? 
>>> 
>>> Basically I have a "translation" table and I'd like to JOIN that table for 
>>> the current translation or the "fallback" language, so something like:
>>> select c.id, t.* from content c JOIN LATERAL (select ct.* from 
>>> content_translation ct WHERE ct.content_id=c.id order by 
>>> ct.language_id='fr' desc, ct.language_id='en' desc limit 1) as t ON 
>>> t.content_id = c.id WHERE c.id=4855;
>> 
>> 
>> hiya -
>> 
>> unfortunately JOIN LATERAL is not supported, at least in all cases, as the 
>> target of a relationship(), because loading such as lazy loading does not 
>> use JOIN at all, and it's not clear if the LATERAL construct would work with 
>> other forms of relationship loading as well. That is, it *might* work for 
>> some cases, though it's never been tested, and likely wont work for most/all 
>> cases. Also in your example I don't see any usage of the lateral() modifier 
>> on your subquery.
>> 
>> It would be best to compose the ON clause of the join using more traditional 
>> methods, e.g. that the "right" side of the join is a subquery that does not 
>> use any correlation, and the ON clause relates the left and right sides 
>> together.
>> 
>> Within the example given, the "primaryjoin" argument refers to the ON clause 
>> of a JOIN, so generally a subquery would not be located here. Additionally, 
>> "as_scalar()", which is being renamed to "scalar_subquery()", indicates that 
>> this query is a so-called "scalar" subquery that returns exactly one row in 
>> one column and therefore acts as a column expression to be used in the WHERE 
>> clause, but this would not work as an ON clause in a JOIN by itself unless 
>> it were equated to something (but again, you'd need LATERAL for correlation 
>> to work in the ON clause).
>> 
>> Looking at the actual SQL you're looking for, everything about it seems to 
>> be fully "traditional" in how the join is composed except for the detail 
>> that you're trying to get the first row only that matches in 
>> content_translation. We have a recipe for row-limited relationships using 
>> window functions which will likely fit here directly, at 
>> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions
>>  . you'd want to add "uselist=False" to your relationship() if you are 
>> looking for 

Re: [sqlalchemy] Re: Composite key; one field is also foreign key [RESOLVED]

[Rich Shepard]

On Tue, 18 Jun 2019, Jonathan Vanasco wrote:


adding `primary_key=True` to each of the `Column` constructors should do it.


Thanks, Jonathan.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/alpine.LNX.2.20.1906181504410.21630%40salmo.appl-ecosys.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Composite key; one field is also foreign key

[Jonathan Vanasco]

adding `primary_key=True` to each of the `Column` constructors should do 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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d2d007aa-ec46-4ea9-a49d-51c5660ef064%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Composite key; one field is also foreign key

[Rich Shepard]

I understand that multiple fields in a declarative base class can be
specified as a composite primary key. In my application rather than using a
unique, serialized primary key for each table/field I use a foreign key
reference to a row in the locations table and a date. For example,

site_name = Column(Integer, ForeignKey('locations.site_name', \
onupdate='CASCADE', ondelete='RESTRICT'))
sampdate = Column(Date, nullable=False)

How do I specify both as a composite primary key?

TIA,

Rich


--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/alpine.LNX.2.20.1906181308050.21630%40salmo.appl-ecosys.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Julien Cigar]

Hello Mike,

As always thank you for your quick and useful reply. I might not need 
LATERAL but would be very interrested to see a solution with WINDOW 
functions ..

I've added a small test case with some comment on what I'd like to achieve. 
Basically what I would like is to be able to select the "current" 
translation in one query, and that it is transparent (it's a Pyramid 
plugin), that's why I'm also using hybrid properties

On Tuesday, June 18, 2019 at 3:38:36 PM UTC+2, Mike Bayer wrote:
>
>
>
> On Tue, Jun 18, 2019, at 6:21 AM, Julien Cigar wrote:
>
> Hello,
>
> I'm trying to add a 'read-only' relationship involving a subquery and I 
> have some problems with remote and foreign sides, SQLAlchemy returns:
>
> sqlalchemy.exc.ArgumentError: Relationship Folder.current_translation 
> could not determine any unambiguous local/remote column pairs based on join 
> condition and remote_side arguments.  Consider using the remote() 
> annotation to accurately mark those elements of the join condition that are 
> on the remote side of the relationship.
>
> I've copied my code here 
> https://gist.github.com/silenius/e2c7fb393864b3f1fee8131336dd8b61
>
>
>
> Any idea what could be wrong ..? 
>
> Basically I have a "translation" table and I'd like to JOIN that table for 
> the current translation or the "fallback" language, so something like:
> select c.id, t.* from content c JOIN LATERAL (select ct.* from 
> content_translation ct WHERE ct.content_id=c.id order by 
> ct.language_id='fr' desc, ct.language_id='en' desc limit 1) as t ON 
> t.content_id = c.id WHERE c.id=4855;
>
>
>
> hiya -
>
> unfortunately JOIN LATERAL is not supported, at least in all cases, as the 
> target of a relationship(), because loading such as lazy loading does not 
> use JOIN at all, and it's not clear if the LATERAL construct would work 
> with other forms of relationship loading as well.That is, it *might* 
> work for some cases, though it's never been tested, and likely wont work 
> for most/all cases.  Also in your example I don't see any usage of the 
> lateral() modifier on your subquery.
>
> It would be best to compose the ON clause of the join using more 
> traditional methods, e.g. that the "right" side of the join is a subquery 
> that does not use any correlation, and the ON clause relates the left and 
> right sides together.
>
> Within the example given, the "primaryjoin" argument refers to the ON 
> clause of a JOIN, so generally a subquery would not be located here.  
> Additionally, "as_scalar()", which is being renamed to "scalar_subquery()", 
> indicates that this query is a so-called "scalar" subquery that returns 
> exactly one row in one column and therefore acts as a column expression to 
> be used in the WHERE clause, but this would not work as an ON clause in a 
> JOIN by itself unless it were equated to something (but again, you'd need 
> LATERAL for correlation to work in the ON clause).
>
> Looking at the actual SQL you're looking for, everything about it seems to 
> be fully "traditional" in how the join is composed except for the detail 
> that you're trying to get the first row only that matches in 
> content_translation.We have a recipe for row-limited relationships 
> using window functions which will likely fit here directly, at 
> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions
>  
> .   you'd want to add "uselist=False" to your relationship() if you are 
> looking for a many-to-one style relationship.  The window function works by 
> including a "row number", eg. index of a row, partitioned against the 
> groupings within which you'd want to be limiting, in this case it can 
> perhaps be partition_by=[ct.content_id], and it then orders within those 
> partitions where you could apply your interesting "lang='fr' desc, 
> lang='en' desc" trick above.Then the limit is applied in the ON clause 
> by asking for "partition.c.index == 1".
>
> if you can share rudimentary mappings I can show you the composition, 
> although the example as given in the docs should translate fairly directly 
> here.
>
>
>
>
>
>
>
> Thank you!
>
> Julien
>
>
> --
> 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 sqlal...@googlegroups.com .
> To post to this group, send email to sqlal...@googlegroups.com 
> .
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/16dddc84-d927-44e0-95dc-da9f579da733%40googlegroups.com
>  
> 

Re: [sqlalchemy] Re: primaryjoin and remote/foreign sides

[Mike Bayer]

On Tue, Jun 18, 2019, at 9:00 AM, Julien Cigar wrote:
> 
> 
> On Tuesday, June 18, 2019 at 12:20:55 PM UTC+2, Julien Cigar wrote:
>> Hello,
>> 
>> I'm trying to add a 'read-only' relationship involving a subquery and I have 
>> some problems with remote and foreign sides, SQLAlchemy returns:
>> 
>> sqlalchemy.exc.ArgumentError: Relationship Folder.current_translation could 
>> not determine any unambiguous local/remote column pairs based on join 
>> condition and remote_side arguments. Consider using the remote() annotation 
>> to accurately mark those elements of the join condition that are on the 
>> remote side of the relationship.
>> 
>> I've copied my code here 
>> https://gist.github.com/silenius/e2c7fb393864b3f1fee8131336dd8b61
>> 
>> 
>> 
>> Any idea what could be wrong ..? 
>> 
>> Basically I have a "translation" table and I'd like to JOIN that table for 
>> the current translation or the "fallback" language, so something like:
>> select c.id, t.* from content c JOIN LATERAL (select ct.* from 
>> content_translation ct WHERE ct.content_id=c.id order by ct.language_id='fr' 
>> desc, ct.language_id='en' desc limit 1) as t ON t.content_id = c.id WHERE 
>> c.id=4855;
>> 
>> 
>> 
>> Thank you!
>> 
>> Julien
> 
> 
> I've taken another approach, as it looks like that I need something like 
> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions
> 
> It works almost with: 
> https://gist.github.com/silenius/b866232305e22b05e3f1f8705f4401a2 which 
> produce something like 
> https://gist.github.com/silenius/161c9a1263e7251e0213843598786640


where's the ROW_NUMBER function above? I still see it using LIMIT. the core of 
the approach needs to include code like:

partition = select([
ContentTranslation,
func.row_number().over(
order_by=[
 (ContentTranslation.lang == 'fr').desc(),  
 (ContentTranslation.lang == 'en').desc(), 
],
partition_by=ContentTranslation.content_id
).label('index')
]).alias()


Then you specify partition.c.index == 1 as *part* of your primaryjoin. LIMIT is 
not used.




> 
> The only remaining problem is that translation_cls (line 14 of first paste) 
> is involved in a joined load inheritance scenario and that I'm getting a:
> 
> sqlalchemy.exc.ProgrammingError: (psycopg2.errors.AmbiguousColumn) column 
> reference "content_id" is ambiguous
> 
> which is because SQLAlchemy doesn't alias properly in the subselect .. any 
> idea how to fix this ?
> 
> Thanks :)
> 

> --
>  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 post to this group, send email to sqlalchemy@googlegroups.com.
>  Visit this group at https://groups.google.com/group/sqlalchemy.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/fb55e73a-10d1-4a4f-b410-f614e2d8a736%40googlegroups.com
>  
> .
>  For more options, visit https://groups.google.com/d/optout.

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/28f6b59e-392a-45bf-870d-3e1e4f30a2a4%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to Assign Blob Field?

[Mike Bayer]

On Tue, Jun 18, 2019, at 9:14 AM, Guy Zehavi wrote:
> When I use sqlalchemy using mysql-connector-python to read unicode from a 
> blob I am getting:


Hi there -

While this issue may be impacted if you are using an old version of 
mysql-connector-python or of SQLAlchemy, assuming you are on the latest version 
of everything, then we have to conclude this driver has issues in this area.

Please try a driver such as mysqlclient or pymysql. mysql-connector-python is 
known to have lots of ongoing issues particularly in the area of unicode 
translation; the previous two drivers will accept BLOB values as bytes without 
issue.



> `Error closing cursor
> UnicodeDecodeError: 'utf-8' codec can't decode byte 0x9a in position 3: 
> invalid start byte`
> Now, I can solve this by doing `, connect_args={'use_pure': True}` to the 
> `create_engine`. But that smells bad and I wanted to know the 'proper' way to 
> do this; notably how do I put a Blob using SqlAlchemy? I have:

> `my_blob = Column(BLOB, nullable=True)`

> Does anyone know how to assign bytes the the above column? I assume 
> `my_object.my_blob = the_bytes` however that does not work. Specifically, my 
> Blob is of type `bytes` (I get it from a protobuff being serialized). Is 
> there anything else I need to do?

> 

> --
>  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 post to this group, send email to sqlalchemy@googlegroups.com.
>  Visit this group at https://groups.google.com/group/sqlalchemy.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/b5982571-7098-4bdd-8670-5102dd955ee1%40googlegroups.com
>  
> .
>  For more options, visit https://groups.google.com/d/optout.

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/11526203-eccb-40d0-9f75-423c282e4cc5%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] primaryjoin and remote/foreign sides

[Mike Bayer]

On Tue, Jun 18, 2019, at 6:21 AM, Julien Cigar wrote:
> Hello,
> 
> I'm trying to add a 'read-only' relationship involving a subquery and I have 
> some problems with remote and foreign sides, SQLAlchemy returns:
> 
> sqlalchemy.exc.ArgumentError: Relationship Folder.current_translation could 
> not determine any unambiguous local/remote column pairs based on join 
> condition and remote_side arguments. Consider using the remote() annotation 
> to accurately mark those elements of the join condition that are on the 
> remote side of the relationship.
> 
> I've copied my code here 
> https://gist.github.com/silenius/e2c7fb393864b3f1fee8131336dd8b61
> 
> 
> 
> Any idea what could be wrong ..? 
> 
> Basically I have a "translation" table and I'd like to JOIN that table for 
> the current translation or the "fallback" language, so something like:
> select c.id, t.* from content c JOIN LATERAL (select ct.* from 
> content_translation ct WHERE ct.content_id=c.id order by ct.language_id='fr' 
> desc, ct.language_id='en' desc limit 1) as t ON t.content_id = c.id WHERE 
> c.id=4855;


hiya -

unfortunately JOIN LATERAL is not supported, at least in all cases, as the 
target of a relationship(), because loading such as lazy loading does not use 
JOIN at all, and it's not clear if the LATERAL construct would work with other 
forms of relationship loading as well. That is, it *might* work for some cases, 
though it's never been tested, and likely wont work for most/all cases. Also in 
your example I don't see any usage of the lateral() modifier on your subquery.

It would be best to compose the ON clause of the join using more traditional 
methods, e.g. that the "right" side of the join is a subquery that does not use 
any correlation, and the ON clause relates the left and right sides together.

Within the example given, the "primaryjoin" argument refers to the ON clause of 
a JOIN, so generally a subquery would not be located here. Additionally, 
"as_scalar()", which is being renamed to "scalar_subquery()", indicates that 
this query is a so-called "scalar" subquery that returns exactly one row in one 
column and therefore acts as a column expression to be used in the WHERE 
clause, but this would not work as an ON clause in a JOIN by itself unless it 
were equated to something (but again, you'd need LATERAL for correlation to 
work in the ON clause).

Looking at the actual SQL you're looking for, everything about it seems to be 
fully "traditional" in how the join is composed except for the detail that 
you're trying to get the first row only that matches in content_translation. We 
have a recipe for row-limited relationships using window functions which will 
likely fit here directly, at 
https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions
 . you'd want to add "uselist=False" to your relationship() if you are looking 
for a many-to-one style relationship. The window function works by including a 
"row number", eg. index of a row, partitioned against the groupings within 
which you'd want to be limiting, in this case it can perhaps be 
partition_by=[ct.content_id], and it then orders within those partitions where 
you could apply your interesting "lang='fr' desc, lang='en' desc" trick above. 
Then the limit is applied in the ON clause by asking for "partition.c.index == 
1".

if you can share rudimentary mappings I can show you the composition, although 
the example as given in the docs should translate fairly directly here.





> 
> 
> Thank you!
> 
> Julien
> 

> --
>  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 post to this group, send email to sqlalchemy@googlegroups.com.
>  Visit this group at https://groups.google.com/group/sqlalchemy.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/16dddc84-d927-44e0-95dc-da9f579da733%40googlegroups.com
>  
> .
>  For more options, visit https://groups.google.com/d/optout.

-- 
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, 

[sqlalchemy] Re: How to Assign Blob Field?

[Guy Zehavi]

Just as a note, the full stack trace is:

Error closing cursorUnicodeDecodeError: 'utf-8' codec can't decode byte 0x9a in 
position 3: invalid start byte

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 
1193, in _execute_context
context)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", 
line 507, in do_execute
cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.5/dist-packages/mysql/connector/cursor_cext.py", 
line 272, in execute
self._handle_result(result)
  File "/usr/local/lib/python3.5/dist-packages/mysql/connector/cursor_cext.py", 
line 163, in _handle_result
self._handle_resultset()
  File "/usr/local/lib/python3.5/dist-packages/mysql/connector/cursor_cext.py", 
line 651, in _handle_resultset
self._rows = self._cnx.get_rows()[0]
  File 
"/usr/local/lib/python3.5/dist-packages/mysql/connector/connection_cext.py", 
line 276, in get_rows
row = self._cmysql.fetch_row()
SystemError:  returned a result with an error set


-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/6badfcd2-1099-4b8b-b07f-7c88bb5fd9c5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] How to Assign Blob Field?

[Guy Zehavi]

When I use sqlalchemy using mysql-connector-python to read unicode from a 
blob I am getting:

Error closing cursorUnicodeDecodeError: 'utf-8' codec can't decode byte 0x9a in 
position 3: invalid start byte

Now, I can solve this by doing , connect_args={'use_pure': True} to the 
create_engine. But that smells bad and I wanted to know the 'proper' way to 
do this; notably how do I put a Blob using SqlAlchemy? I have:

my_blob = Column(BLOB, nullable=True)

Does anyone know how to assign bytes the the above column? I assume 
my_object.my_blob 
= the_bytes however that does not work. Specifically, my Blob is of type 
`bytes` (I get it from a protobuff being serialized). Is there anything 
else I need to do?

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b5982571-7098-4bdd-8670-5102dd955ee1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: primaryjoin and remote/foreign sides

[Julien Cigar]

On Tuesday, June 18, 2019 at 12:20:55 PM UTC+2, Julien Cigar wrote:
>
> Hello,
>
> I'm trying to add a 'read-only' relationship involving a subquery and I 
> have some problems with remote and foreign sides, SQLAlchemy returns:
>
> sqlalchemy.exc.ArgumentError: Relationship Folder.current_translation 
> could not determine any unambiguous local/remote column pairs based on join 
> condition and remote_side arguments.  Consider using the remote() 
> annotation to accurately mark those elements of the join condition that are 
> on the remote side of the relationship.
>
> I've copied my code here 
> https://gist.github.com/silenius/e2c7fb393864b3f1fee8131336dd8b61
>
> Any idea what could be wrong ..?  
>
> Basically I have a "translation" table and I'd like to JOIN that table for 
> the current translation or the "fallback" language, so something like:
> select c.id, t.* from content c JOIN LATERAL (select ct.* from 
> content_translation ct WHERE ct.content_id=c.id order by 
> ct.language_id='fr' desc, ct.language_id='en' desc limit 1) as t ON 
> t.content_id = c.id WHERE c.id=4855;
>
> Thank you!
>
> Julien
>


I've taken another approach, as it looks like that I need something like 
https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions

It works almost with: 
https://gist.github.com/silenius/b866232305e22b05e3f1f8705f4401a2 which 
produce something like 
https://gist.github.com/silenius/161c9a1263e7251e0213843598786640

The only remaining problem is that translation_cls (line 14 of first paste) 
is involved in a joined load inheritance scenario and that I'm getting a:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.AmbiguousColumn) column 
reference "content_id" is ambiguous

which is because SQLAlchemy doesn't alias properly in the subselect .. any 
idea how to fix this ?

Thanks :)

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/fb55e73a-10d1-4a4f-b410-f614e2d8a736%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] primaryjoin and remote/foreign sides

[Julien Cigar]

Hello,

I'm trying to add a 'read-only' relationship involving a subquery and I 
have some problems with remote and foreign sides, SQLAlchemy returns:

sqlalchemy.exc.ArgumentError: Relationship Folder.current_translation could 
not determine any unambiguous local/remote column pairs based on join 
condition and remote_side arguments.  Consider using the remote() 
annotation to accurately mark those elements of the join condition that are 
on the remote side of the relationship.

I've copied my code here 
https://gist.github.com/silenius/e2c7fb393864b3f1fee8131336dd8b61

Any idea what could be wrong ..?  

Basically I have a "translation" table and I'd like to JOIN that table for 
the current translation or the "fallback" language, so something like:
select c.id, t.* from content c JOIN LATERAL (select ct.* from 
content_translation ct WHERE ct.content_id=c.id order by 
ct.language_id='fr' desc, ct.language_id='en' desc limit 1) as t ON 
t.content_id = c.id WHERE c.id=4855;

Thank you!

Julien

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/16dddc84-d927-44e0-95dc-da9f579da733%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.