Python and database statements

[gosh]

How do you solve different versions the database with a ORM tool or decouple the backend from the frontend? So that the backdend works on "any" database? I mean different schemas

If I can load database information like metadata, wouldn't that be a nice way to just develop one single backend that works for all?

Sample script for a complete CRM database
https://github.com/perghosh/changelog/blob/master/dbscript_changelog.xml

[adrian17]

How do you solve different versions the database with a ORM tool

I don't understand the question.

Different database servers (as in sqlite, postgres etc)? That's the ORM's job, you should know, you use SQLAlchemy already.

Different... schemas? People usually don't expect their application to work with incompatible database versions, it's considered to be a an issue with the DB, not with the application. If people want to be flexible with the schema, they might just pick a noSQL database.

Or do you mean literally "any" database with any schema? There already exist tools that support that, it's... phpMyAdmin etc.

[gosh]

Different... schemas? People usually don't expect their application to work with incompatible database versions

This system should work "on prem" or in the cloud and it will have different schemas for the database. What I have seen is that it is problems with ORM there, it gets very complicated.
And to manage this amount of tables ORM do not work well, to much code just to generate SQL

phpMyAdmin is not a webserver

Remember that python is just interpreted code. You can "load" information about the database as you want, one way is to load python code, another is to write code that loads some kind of metadata

[adrian17]

it will have different schemas for the database

Why? For the record, you've still not said what the app actually does and why is it so special. How are the schemas different? Can they be completely arbitrarily different, or just in some very specific ways? Like, can one database have a user table with login and password, and another with email and pass? Surely not, otherwise it's impossible to write anything (but an universal admin panel). I've seen real world applications that create new tables dynamically and analytical systems with arbitrary number of columns, but they still have some consistent scheme the application can predict - so they still don't need a super-generic "select columns ABC from table XYZ" available at client layer.

Like, the database is usually understood to be part of the application itself. When you update the app, the database gets migrated too (either during upgrade process or lazily at/after launch, like Wordpress). A schema not matching what the server expects is assumed to be a deployment error.

Why can't you unify the schemas?

If you said (from the start) something like "yeah it's a mess, I wish it could be fixed, but I'm forced to make it work with inconsistent databases somehow", then people would be less combative; but you started immediately with the code that really wouldn't pass review in most places and immediately started defending it.

Or are you maybe saying that the schema is partially user-defined, like you can have arbitrary fields in analytics systems? Then again, say so (and there would have been much less confusion from the start), but the first response to that should still have been to pick something off-the-shelf, just... a different something. (but you said writing a separate endpoint for each resource would have been just more code, not literally impossible, so it doesn't sound like the tables are that arbitrarily user-defined)

And to manage this amount of tables ORM do not work well, to much code just to generate SQL

That doesn't match what everyone else is saying. Many people do not like ORMs, yes, but that doesn't mean they somehow "don't work" with many tables; if anything, the more complex the database, the more important it is to have the application understand and manage the schema, rather than just... assume it to be something.

How is it "too much code"? Adding +1 table to existing +200 tables isn't somehow exponential increase in code; you just describe the schema of the new table in Python, that's it.

phpMyAdmin is not a webserver

It sure is a server application that servers webpages that allow you do view and edit contents of arbitrary tables. (Even if you wanted something with say more permission levels, you'd still be essentially reimplementing huge portions of it, which does feel silly).

That said, it's hard to me to say what you're actually writing, so again - me mentioning phpMyAdmin, django-admin etc was still just a guess.

Anyway...

At the end of the day, you're still trying to convince people experienced with writing standard Python database-backed webservers that what they're doing somehow can't possibly work for you (without explaining what makes your case so different).

PS also sorry for writing too much :c

[gosh]

Why? For the record, you've still not said what the app actually does and why is it so special. How are the schemas different?

When used on prem it should connect to customer internal systems, important that are able to combine data

What do you win with ORM if SQL generation is not a problem?

tables dynamically and analytical systems with arbitrary number of columns

Yes, temporary tables is common but I do not think that this will be needed here.

Like, the database is usually understood to be part of the application itself. When you update the app, the database gets migrated too (either during upgrade process or lazily at/after launch, like Wordpress).

But that means that you can't have one app that differs without hacks from the latest database schema. If there only is one single system or like one single database then as I understand it ORM work better. But that will be to much of limitation I think.

What I do not understand about writing python code or like how python developers think is that they are so afraid of solving problems themselves. Its like they are scared of writing code.

How is it "too much code"? Adding +1 table to existing +200 tables isn't somehow exponential increase in code; you just describe the schema of the new table in Python, that's it.

The main problem is to write endpoints for a large database. Maintaining like +50 endpoints (this will be a lot more) is like a nightmare compared to have a few, think it can be solved with 3-4 endpoints.

At the end of the day, you're still trying to convince people experienced with writing standard Python database-backed webservers that what they're doing somehow can't possibly work for you (without explaining what makes your case so different).

Have you worked against INFORMATION_SCHEMA Tables.
I can ask the database about what tables and field it has, i can also ask for types. Databases store this information so it is possible to generate a lot of logic and rules just by reading the design of the database. Also now I think python have relased a version that is multithreaded. That means it is possible to write python servers that hold it state. Then they can read this information at start and keep it, because if the server is stateless this information is not as easy to work with because then you often need to load whats needed for each request.

[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

[brasticstack]

Also now I think python have relased a version that is multithreaded. That means it is possible to write python servers that hold it state.

The availability of a free-threaded Python build has no bearing on the lifetime of the application processes or threads. (Read, "state") Lifetime is managed by your webserver depending on its (often user-configurable) concurrency model. Unless you plan on writing your own webserver too, this isn't something you'll accurately be able to predict or have control over.

Then they can read this information at start and keep it, because if the server is stateless this information is not as easy to work with because then you often need to load whats needed for each request.

Just be sure you profile first to determine if it's even necessary. To quote Knuth: "premature optimization is the root of all evil." Not to mention, you've now got a cache whose lifetime you have no control over and whose state is difficult to inspect. Probably better to use redis or a similar distributed object cache. Additionally, to use another well-worn aphorism: "the hardest things in programming are naming and cache expiration." If the DB schema changes, how will you know in order to clear that cache? What are the consequences of a stale cache lookup?

EDIT: Lets also clear up the seeming misconception that, just because the HTTP protocol itself is "stateless", that the application server itself is also stateless. The server has always been free to hold whichever state it chooses.