8 ms·

constructing queries in python is one of the disadvantages of using ORM. It takes away all the sql power that a DB can give you. Not to mention the magic that g

• Tobu 11y ago
  If you're referring to SQLa Core, it's still SQL. The AST is the same, but by building the SQL AST explicitly you prevent injection and get a degree of composability (with subqueries). IMHO it's the more maintainable way of writing SQL.
  • halayli 11y ago
    You can prevent injection by passing the parameters to cursor.execute(query, (params,)) as a tuple. You don't need to rely on ORM to do that for you. subqueries are best expressed in sql directly. They can get complicated, especially when using the with clause. I am not saying that all of what ORM offers is bad. But beside the very basics they should step away. The less they offer the better.
    • aidos 11y ago
      Out of interest, are you talking about SQLAlchemy, or ORMs in general? # a subquery demo_accounts = db.query(Account.id).join(Client).filter(Client.name=='Demo') # used inside a query print(db.query(Account.name).filter(Account.id.in_(demo_accounts))) SELECT account.name AS account_name FROM account WHERE account.id IN (SELECT account.id AS account_id FROM account JOIN client ON client.id = account.client_id WHERE client.name = :name_1) # or as a cte da_as_cte = demo_accounts.cte() print(db.query(Account.name).join(da_as_cte, da_as_cte.c.id==Account.id)) WITH anon_1 AS (SELECT account.id AS id FROM account JOIN client ON client.id = account.client_id WHERE client.name = :name_1) SELECT account.name AS account_name FROM account JOIN anon_1 ON anon_1.id = account.id There are obviously much more complex cases, but SA tends to handle things in a pretty sane way. You can compose query segments (like above) and if you really need to go back to the raw sql, you can and still have the results mapped into your python objects.
• treenyc 11y ago
  This is my approach to ORM. Use ORM for: 1. INSERT, UPDATE, DETELE get one record Use SQL/Stored Procedure for: SELECTs that involved more complex joins and queries.
  • JustSomeNobody 11y ago
    So, if say, you were inserting a recipe into a db where you had 3 tables (IRL there'd be more, surely), recipe, ingredients, directions. Are you saying you'd use the ORM or the SQL?
    • collyw 11y ago
      I ue ORM's similary. I would basiclly go for the easiest to understand / maintain approach. I don't have much expereince with SQLAlchemy, but I have a pretty good idea of where its worth using SQ rather than the Django ORM. (Usually anything that involves complex joins or subqueries, or similar. "Normal" joins are usually better done in the ORM as it makes a lot more of the Django features available).
      • JustSomeNobody 11y ago
        So, in my example, you might use the ORM, perform three inserts (one for each table) in a transaction. If any fail, rollback. However, in the select, where'd you'd have to join all three tables, you might use SQL, yes? I'm just curious how people are using ORMs vs SQL, so sorry for the questions.
        • aidos 11y ago
          Not the parent, but when I'm using SA (sqlalchemy) I tend to just use the ORM for everything. If I needed something to be super performant I might get closer to the raw SQL – though generally I find development so much faster when SA handles everything for me. If you're using a different ORM then things are probably going to be different. I've worked with a few in various languages and SA is the only one I've found that allows me to express any complex query directly via the ORM. I've heard there are some edge cases where you need to drop down a layer but I haven't run into them yet.
• zo1 11y ago
  >"It's often more complicated than that and doing it in a non-sql way is suboptimal/unmaintainable." It's also more composable, if you ask me. I like to think of it like "building" SQL statements using smaller components. Just like you do with code, you essentially compose a complicated business process using smaller blocks. It's beautiful in it's simplicity. Even if the syntax requires some extra work/learning.
  • justsomedood 11y ago
    This is such a huge advantage for the approach. It makes it so much easier to build a query with variable filters than using SQL text.
  • aidos 11y ago
    I've just been using this to my advantage on a system I'm working on. I've created a bunch of base queries that map from my user account table to each different table that users have access too. I can then use these base queries as a starting point to make sure people don't access records that don't belong to them. bq = db.query(Obj).join(ParentA).join(ParentB).join(User).filter(User.id==bindparam('user_id')) q = bq.filter(other_rules).params(user_id=1)... It's a really flexible system.
• collyw 11y ago
  I have seen both sides of teh argument. ORM's win hands down over multiple insert and update queries. Complex queries are usually better in SQL.
• aidos 11y ago
  I don't agree. For other ORMs maybe, but with SA I can get the queries coming out how I want. Also, there's not really that much magic going on – you've defined the relationships so you know how the joins will be constructed. Also, you're neglecting all the other benefits you get. Things like the loading strategies and the unit of work tracking are extremely powerful abstractions.
• takeda 11y ago
  I personally like the approach of JOOQ[1] you are basically writing SQL statements using Java and you still can make complex queries and at the time you get benefits of type checking offered by IDE (for example your IDE will actually catch that you're trying to compare column that is an integer with a string). [1] http://www.jooq.org/ http://www.jooq.org/