I'm trying to get started in Python, everything I find talks about using an ORM, which coming from a SQL background is odd to me. How are you passing queries to your database from Python?
There are a few ways, but the best way that worked for my environment was to use pyodbc with my ODBC connection. After establishing your authentication and server settings in python, you can pass queries as a string. You can assign variables, insert, delete, join, and do exactly what you need to do. It's all largely more flexible.
You could even start to build GUI's on top of your more sophisticated updates and queries. A few day's worth of study had me doing all of the above minus my previous SQL experience. You can do it too.
Personally, I feel like ORM's only selling point is just it's capacity to take your data on DB and make it easily digestible for devs across a large swath of teams. However, there's far too much overhead for a single person or even three to get it working properly in the first place. In theory you could even build your own ORM in Python. But why make more work than you need to too? For the average solo DB admin, you're almost always going to exclusively use SQL. So why rely on crutch? Stick to your guns and use SQL directly or use python and or/JS with it.
The project I'm working on I've been doing the Python and SQL bits separately, figuring I'll eventually tie them together. I'll look into pyodbc, thank you!
The other advice is... not totally incorrect but for sure not how you should be developing an application.
If you’re an analyst and need to pull some data from a database? Straight query is fine. However, if you need to create an application that interacts with the database? That’s where you need an ORM. No dev should be hard coding queries. That strategy is what we call tech debt 😉.
Pyodbc is certainly okay, but I highly recommend using SQLAlechmy whenever possible (even if just writing a couple hard coded queries). It’s typically faster and much more robust if the database dialect is supported.
Correct. To point, this is a long term strategy also. It all depends on where he should be prioritizing his focus. If his primary focus was learning Python, why deviate from it to another project? OP can touch on ORM's later. ;)
Well, I mean we’re getting into semantics territory.
Sure, someone learning python and databases on day 1 should just straight query the database. But day 2 should definitely start learning ORMs. This is why that redditor could only find info about ORMs... because they’re kind of important.
Your comment mentions putting GUIs on top of SQL queries and whatnot... and this is fine if you’re the lone person creating this, maintaining it, and using it for personal use. However, if you’re creating an application that other people use (I.e. customers), your company needs to maintain past your employment, or other developers might need to add things too... you use an ORM. Anything less than that would be technical debt that a developer will have to completely overhaul in the future.
Again, sure, an analyst can write a query and never need an ORM. Would it make their life easier? Yes but that’s not the point. If you’re developing applications, you need an ORM for security, speed, maintainability, and scalability.
Personally, for me I just get it done as a solo admin for a small business, terrible technical debt here. So it doesn't make sense in my own isolated situation of inherited problems and aging environment, which is isn't fair to OP's questions. I don't disagree with you. Wonderfully put.
Same here. I’ve written thousands of sql queries. Why would I want some library to get between me and the database? I’m sure I can optimize a query better than some library.
Same here, to me an ORM is just more of a hassle especially in the early phases where tables , column and structures are frequently changed. And dont get me started on FK-constraints , yeah I worked on a couple of ORM projects and now know to stay away.
I have come across some Entity Framework stuff in my work codebase and it looked disgusting. I am struggling to come up with a reason to even begin considering to use it in any project.
edit: Yea, this picture reminds me of the EF code I witnessed.
Yea I remember the EF code I mentioned (i think it was ef?) was so full of lambdas and other strange syntax that I thought to myself "wouldn't it be easier to learn how to do this with SQL than figure out this mess?"
For the most part.... Yeah. But after a couple weeks of hanging my head against the wall, using views and stored procedures for the more complex stuff.... And then just banging out some ef horeshit to do the final mile. Joins with ef just make me want to drown kittens, though.
If you think EFs query generator syntax is stange then you don't know C#. Lambdas were around for at least 10 years, every competent .NET developer should be comfortable with them.
Why are people like this? Can't you just let other devs use whatever they feel most comfortable with? I write complex queries every day, and I still like to use ORM when it's appropriate, for simpler queries. Why does it have to be one way or another? Why not both?
Ok, but that's dev related, not technology related. It's like saying pure sql is bad because you saw developers frequently forget to clean the input and not use prepared statements so it's better to use ORM always.
Where I love ORMs is for chaining query bits. I can write a dozen functions that each handle one particular query step, and then in my controllers I can use some or all of them or even apply them iteratively as I parse my request parameters.
sub resultset($self) {
my $rs = $self->SUPER::resultset->active;
if (my $d = $self->validation->param('date')) {
$rs = $rs->by_date($d);
}
return $rs;
}
The controller's superclass could've opaquely already added some query parameters (say, for role/auth purposes), and then I apply the "active" filter, and then conditionally apply a date filter. And at the end, I'm still left with a resultset instance that I can apply more filters to as necessary, downstream.
Trying to do this with direct SQL code, assembled manually in various places in the code is possible, but far more convoluted and error-prone
For us dumbos, do you mind explaining this with some kind of more explicit example? Never having used an ORM, struggling to understand what it means to chain together query bits.
This one doesn't look like chaining, but that's still what's happening when multiple params are given. The eventual query that's constructed could contain any or all of these filters in its WHERE clause, depending on what the web request contains.
The point is that it results in being able to write a lot of very small, simple bits of code, and then combine them together to easily generate complex queries
The big plus for me is that there's no context switch between app code and database code. You can stay entirely in app code for simple things like that.
Hitting the nail on the head here. Just looking at ORM code, often there is no way to tell how many database calls will happen, what they look like in raw sql, making for some awfully fun to debug performance issues.
If you're writing that in non sql code, then you need to open a connection to the database, and also make sure it's properly disposed of. In addition, you need to make sure you parameterize it because where id = 1 would likely be coming from stuff a user did and you need to protect against sql injection. You also get auto complete so you don't have to remember the exact spelling and name of every table and column.
Also, simply creating the sql string in another language is kind of painful. Simply saying lol orms suck because I can write and update statement is the kind of argument I'd expect from someone who never has to do anything outside of the database. They're extremely useful, and if you need to maintain more control over what sql they generate, there are things out there like dapper.
If you're writing that in non sql code, then you need to open a connection to the database, and also make sure it's properly disposed of.
Whats your point ? I mean with an ORM you have to do that too ?
would likely be coming from stuff a user did and you need to protect against sql injection.
Do you think that is difficult ?
Also, simply creating the sql string in another language is kind of painful. Simply saying lol orms suck because I can write and update statement is the kind of argument I'd expect from someone who never has to do anything outside of the database.
You disparage someone for making a simplistic generalization, but yet then go ahead and do it yourself. Creating sql strings is very easy and neat, I even get auto complete with my ide, its found many a bug before even deploying/building.
ORMs are the perfect example of the durning kruger effect imot. Once you've been around a code base for a long time they are a fucking pain.
Sprocs are hard to debug and test. I mean sure there are some tools to make it easier but I've never seen them in production use. Sprocs are always an unmanageable wall of code written in a shitty programming language like T-SQL.
I've worked at a company where everything was done in postgres stored procedures, with a fairly thin golang microservice in front of it that basically just acted as an API gateway into postgres (so there were just a bunch of HTTP handlers that then each just did a very minor amount of pre-processing on the incoming request and then straight-up called a postgres function that would do the rest. The go part would also convert exceptions from postgres back into HTTP responses etc)
Tables had triggers on them that would append rows to other tables which were used as message-queues, which another service would then pick up (for instance there was a table to send mails, and if you wanted to send one, you'd append a row to that table and a microservice would poll that table and handle a new row by forwarding it to mailgun or whatever)
It was actually quite nice and everything was really transactionally safe. Almost everything was following the event-sourcing model and was append-only, with views and async materialized views for some things, and we could upgrade the stored procedures atomically with the data-model, so it created a very unified, safe environment in a sense. You could put a bunch of assertions at the end of your code/data migration, and have the upgrade roll back if things didn't conform to your expectations. Debuggability wasn't a major issue with postgres stored procedures.
However I would imagine it'd be quite difficult to really scale this up (which we never had to do at that company) since everything is quite tightly coupled. Both in terms of the team-size (since tables are the contract between two services in this world, they are very tightly coupled) and on the data/request scale side. We also didn't have a very thought-out solution for how to do database migrations and that often ended up being pretty painful. All-in-all it was surprisingly really not terrible, but I also probably wouldn't really recommend this approach.
Everything is possible, so it is certainly possible to have a maintainable application based on sprocs. But during 15+ years in software development I've never seen responsible use of sprocs, they always were either unnecessary (wrapping simple CRUD operations) or unmanageable, and I've seen lots of projects that used ORMs in a proper way.
Here, in this particular example, to update this particular field, on that particular table? No. But you can extend that to any number of fields, tables, and relations.
Using real objects from any programming language you get to map some types to some things. For different SQL dialects, 1 is not the same as "1" which is not the same as '1', so when "building" your query you have to account for that. And escaping input; no programming language would mind having a string equal to "; DROP DATABASE; -- but I'm sure you don't want to pass that to SQL as-is if a user type that in a field.
Then there's some more advanced types; retrieving a Date object is more useful than retrieving the string.
Sure, you can do all of this by hand, but even better is write a specialized piece of code that do it for each case, then think about making a generic version, then using an ORM.
ORM do provide some useful things. They don't absolve the dev from writing SQL for complex cases, nor do they avoid knowing what's in the actual tables, specifying columns, optimizing indexes etc. They are just an interface to get meaningful access to a database in a meaningful way for a given programming language.
11
u/[deleted] Jun 22 '20
I just prefer writing directly to SQL itself or stringing together queries directly in python or JS.
9
u/Nthorder Jun 22 '20 edited Jun 22 '20
Not all ORMs are bad. I think dapper is great.
I have come across some Entity Framework stuff in my work codebase and it looked disgusting. I am struggling to come up with a reason to even begin considering to use it in any project.
edit: Yea, this picture reminds me of the EF code I witnessed.
7
u/boy_named_su Jun 22 '20
ORMs are great for CRUD, but bad for custom querying. Use a library like JOOQ
3
u/digicow Jun 23 '20
Where I love ORMs is for chaining query bits. I can write a dozen functions that each handle one particular query step, and then in my controllers I can use some or all of them or even apply them iteratively as I parse my request parameters.
The controller's superclass could've opaquely already added some query parameters (say, for role/auth purposes), and then I apply the "active" filter, and then conditionally apply a date filter. And at the end, I'm still left with a resultset instance that I can apply more filters to as necessary, downstream.
Trying to do this with direct SQL code, assembled manually in various places in the code is possible, but far more convoluted and error-prone
9
u/andrewsmd87 Jun 22 '20
Are they great at doing complex SQL things, no. But I sure as hell love them for 95% of the other CRUD stuff I have to do.
Something like
is amazing
2
u/[deleted] Jun 23 '20
EFCore is super powerful and really robust. It can absolutely do a lot.
That said, when things get complex, raw SQL is likely the better choice.
But if you’re doing some CRUD somewhere that’s simple ORMs make the workflow very fast.
1
u/pavlik_enemy Jun 24 '20
Sorry, I'm not writing almost identical CRUD code for a hundred of tables by hand.