3 ms·
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 composab
by 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 agoYou 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 agoOut 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.