Re: [sqlalchemy] Re: Presenting a higher-level view of a physical table to the end-user

[Mike Bayer]

This seems straightforward, try reading through the tutorial at 
https://docs.sqlalchemy.org/en/13/core/tutorial.html which covers the basic 
idea. SQLAlchemy is designed first and foremost to present SQL statements as 
composable constructs that work like views, and that is what the select() 
construct will get you.




On Sun, May 31, 2020, at 4:13 PM, Mark Robinson wrote:
> They will be developers, using Python/SqlAlchemy. Thanks.
> 
> On Sunday, 31 May 2020 16:41:33 UTC+1, Jonathan Vanasco wrote:
>> How will the end-users be querying? Are they going to be consumers who are 
>> submitting params to a form, or are they going to be developers using 
>> Python/SqlAlchemy?
> 

> --
>  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/c0c52306-5de9-4df4-89b6-53f0d5c05283%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/88e66e68-4542-48d6-9dff-798ebbf66b46%40www.fastmail.com.

[sqlalchemy] Re: Presenting a higher-level view of a physical table to the end-user

[Mark Robinson]

They will be developers, using Python/SqlAlchemy. Thanks.

On Sunday, 31 May 2020 16:41:33 UTC+1, Jonathan Vanasco wrote:
>
> How will the end-users be querying?  Are they going to be consumers who 
> are submitting params to a form, or are they going to be developers using 
> Python/SqlAlchemy?
>

-- 
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/c0c52306-5de9-4df4-89b6-53f0d5c05283%40googlegroups.com.

[sqlalchemy] Re: AmbiguousForeignKeysError

[Sydo Luciani]

Never mind the  OperationalError, I was missing inserting one of the
required foreign key fields.

The AmbiguousForeignKeysError was caused by missing foreign_keys in both
side of direction
and is resolved.

Thanks

On Sun, 31 May 2020 at 13:49, Sydo Luciani  wrote:

> I got it working with adding additional foreign_keys.
> one inside backref for one direction, and one outside of backref for the
> other direction.
> so we need foreign_keys in both directions if using bidirectional
> relationship.
>
> Now I am able to insert into parent table, but getting sql error inserting
> to child.
>
> sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) foreign key
> mismatch child_one referencing parent.
>
>
>
>
>
> On Sat, 30 May 2020 at 18:13, Sydo Luciani  wrote:
>
>>
>> One parent table, two child tables, two foreign keys pointing to a field
>> in parent with "one to one relationship" works with no problem, but getting
>> "AmbiguousForeignKeysError" as soon as adding the second foreignkey to
>> child table. tried various combinations but none has worked so far.
>> specifically tring to add foreign_keys as suggested in error message.
>>
>> Here is the code that throwing error.
>>
>> class Parent(Base):
>> __tablename__ = 'parent'
>>
>> field_one = Column(String(256),
>> unique=True,
>> nullable=False,
>> primary_key=True)
>>
>> field_two = Column(String(128),
>>nullable=False,
>>primary_key=True)
>>
>> p_child_one_field_one = relationship("ChildOne",
>>  uselist=False,
>>  passive_deletes=True,
>>  backref=backref("ref_to_parent_field_one",
>>  foreign_keys="[ChildOne.field_one,
>> ChildOne.field_two]"),
>>  cascade="all, delete-orphan")
>>
>> p_child_two_field_one = relationship("ChildTwo",
>>   uselist=False,
>>   passive_deletes=True,
>>   backref=backref("ref_to_parent_field_two",
>>   foreign_keys="[ChildTwo.field_one,
>> ChildTwo.field_two]"),
>>   cascade="all, delete-orphan")
>>
>>
>>
>> class ChildOne(Base):
>> __tablename__ = 'child_one'
>>
>> field_one = Column(String(256),
>> ForeignKey('parent.field_one',
>> onupdate="CASCADE",
>> ondelete='CASCADE'),
>> unique=True,
>> nullable=False,
>> primary_key=True)
>>
>> field_two = Column(String(256),
>> ForeignKey('parent.field_two',
>> onupdate="CASCADE",
>> ondelete='CASCADE'),
>> unique=True,
>> nullable=False,
>> primary_key=True)
>>
>>
>> class ChildTwo(Base):
>> __tablename__ = 'child_two'
>>
>> field_one = Column(String(256),
>> ForeignKey('parent.field_one',
>> onupdate="CASCADE",
>> ondelete='CASCADE'),
>> unique=True,
>> nullable=False,
>> primary_key=True)
>>
>> field_two = Column(String(256),
>> ForeignKey('parent.field_two',
>> onupdate="CASCADE",
>> ondelete='CASCADE'),
>> unique=True,
>> nullable=False,
>> primary_key=True)
>>
>>
>> Any suggestion to fix the problem will be appreciated.
>>
>> Thank you
>>
>

-- 
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/CAJspodgK%3DyXOJggd033HiUmoVqRKZL3GqWD%2BrxdGH9LPK45tGQ%40mail.gmail.com.

[sqlalchemy] Re: AmbiguousForeignKeysError

[Sydo Luciani]

I got it working with adding additional foreign_keys.
one inside backref for one direction, and one outside of backref for the
other direction.
so we need foreign_keys in both directions if using bidirectional
relationship.

Now I am able to insert into parent table, but getting sql error inserting
to child.

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) foreign key
mismatch child_one referencing parent.





On Sat, 30 May 2020 at 18:13, Sydo Luciani  wrote:

>
> One parent table, two child tables, two foreign keys pointing to a field
> in parent with "one to one relationship" works with no problem, but getting
> "AmbiguousForeignKeysError" as soon as adding the second foreignkey to
> child table. tried various combinations but none has worked so far.
> specifically tring to add foreign_keys as suggested in error message.
>
> Here is the code that throwing error.
>
> class Parent(Base):
> __tablename__ = 'parent'
>
> field_one = Column(String(256),
> unique=True,
> nullable=False,
> primary_key=True)
>
> field_two = Column(String(128),
>nullable=False,
>primary_key=True)
>
> p_child_one_field_one = relationship("ChildOne",
>  uselist=False,
>  passive_deletes=True,
>  backref=backref("ref_to_parent_field_one",
>  foreign_keys="[ChildOne.field_one,
> ChildOne.field_two]"),
>  cascade="all, delete-orphan")
>
> p_child_two_field_one = relationship("ChildTwo",
>   uselist=False,
>   passive_deletes=True,
>   backref=backref("ref_to_parent_field_two",
>   foreign_keys="[ChildTwo.field_one,
> ChildTwo.field_two]"),
>   cascade="all, delete-orphan")
>
>
>
> class ChildOne(Base):
> __tablename__ = 'child_one'
>
> field_one = Column(String(256),
> ForeignKey('parent.field_one',
> onupdate="CASCADE",
> ondelete='CASCADE'),
> unique=True,
> nullable=False,
> primary_key=True)
>
> field_two = Column(String(256),
> ForeignKey('parent.field_two',
> onupdate="CASCADE",
> ondelete='CASCADE'),
> unique=True,
> nullable=False,
> primary_key=True)
>
>
> class ChildTwo(Base):
> __tablename__ = 'child_two'
>
> field_one = Column(String(256),
> ForeignKey('parent.field_one',
> onupdate="CASCADE",
> ondelete='CASCADE'),
> unique=True,
> nullable=False,
> primary_key=True)
>
> field_two = Column(String(256),
> ForeignKey('parent.field_two',
> onupdate="CASCADE",
> ondelete='CASCADE'),
> unique=True,
> nullable=False,
> primary_key=True)
>
>
> Any suggestion to fix the problem will be appreciated.
>
> Thank you
>

-- 
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/CAJspodjUV%2B73hcrzUDvyOY11dzK6frb2sS%2B9p1Ny8so8NVHGTw%40mail.gmail.com.

[sqlalchemy] Re: Presenting a higher-level view of a physical table to the end-user

[Jonathan Vanasco]

How will the end-users be querying?  Are they going to be consumers who are 
submitting params to a form, or are they going to be developers using 
Python/SqlAlchemy?

-- 
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/357ae2f9-4a82-4214-8986-2c0e9ab64bea%40googlegroups.com.

[sqlalchemy] Presenting a higher-level view of a physical table to the end-user

[Mark Robinson]

Hi there, we are considering using SQLAlchemy, specifically the Core part, 
for our project. I'm guessing we won't need ORM, but we keep an open mind.
One of the things we would like to do is this: after connecting to a 
database (read-only), we would like to present to the end-user a view of a 
given table (or actually a join of tables) as a "view". This won't 
necessarily be an actual database view/table, also given that we don't want 
to modify the database.
For example, say there are two tables (forgive the made-up syntax).

Country(countryCode: string(2), population: Integer, gdp: Float)
City(cityCode: string(3), countryCode: ForeignKey string(2), population: 
Integer, altitude: Integer)

Say I join these and I want to present this schema to the user:
SimplifiedCity(fullCode: string(6), cityPopulation: Integer)
where fullCode is the concatenation of Country.countryCode || '.' || 
City.cityCode, e.g. 'GB.LON'

That way, the users will be able to query SimplifiedCity, with the 
unnecessary details hidden from them, like so:
query = 
select([simplifiedCity.c.cityPopulation]).where(simplifiedCity.c.fullCode 
= 'GB.LON')

Is this possible? I noticed the existence of sqlalchemy-views 
, but I'm guessing that would 
have to be done within a transaction that we then roll back, which doesn't 
sound great. Also, I wonder if there are better, more idiomatic ways.

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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d974d77d-60be-4d52-9e5b-5a6c3208f873%40googlegroups.com.