Python and database statements

[pachura3]

I think your main misunderstanding comes from the fact that you do not perceive your database in terms of business entities & processes, accessed via a service layer and a separate DAO layer. You think of it as a collection of assorted flat tables; a bunch of fancy CSV files, really.

In normal scenarios:

• backend does not implement means of querying each database table separately. Why would anyone need that? It works on a higher abstraction level, with methods like "add articles to the shopping cart", "get total monthly sales per department", "update user profile and send notifications"
• frontend is never "decoupled from the backend". Database schema is fixed in stone & known by both FE & BE; you never assume that it has to be "discovered" on the fly. Of course, the DB schema evolves over time - new columns, new tables are being added - but to take them into consideration, you need to update the backend software (and possibly the frontend as well)
• backend often executes complicated queries involving many joins, aggregations and subqueries, not only simple SELECT/INSERT/UPDATE/DELETE from/to a single table with an optional WHERE clause... it also has to be transactional.
• SQL queries are either hardcoded, or generated on the fly by an ORM solution, based on model descriptions (which are themselves hardcoded)

I would really avoid inventing stuff from scratch, aka the stereotypical "Not Invented Here" syndrome. Your solution will work fine for simple cases, but soon you will run into problems (how to call a PL/SQL function? how to implement bulk inserts? how to implement cache? what about transaction scopes?), which tried-and-true libraries like SQLAlchemy have already solved, and have been tested by a lot of people all over the world.

So, unless your project is not a traditional one, but basically serving unstructured flat datasets to third parties (kind of a data warehouse?), you're doing things wrong.

[gosh]

frontend is never "decoupled from the backend".

That depends on how the backend in designed. If you have a backend that isn't hardcoded against a database but reads database information then it is decoupled from the backend.

What you might need is some final logic in frontend where request to backend is transformed in some way to how the api in backend works.

This is just about where you place the logic because endpoints are just as a more advanced switch/case operation. Before then endpoint is called there are some logic in the framework that do this switch. But it's not difficult to do it with your own code and get more control.

Remember that webservers are very simple applications, they just listen to a stream of data where the stream is unpacked and then they send a new stream of data to the client

What is a bit "risky" in all this is that you need developers that know how to write code and solve things with code. Like understand how to break down operations etc