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!

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

[Mark Robinson]

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

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

--

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