On Thu, Aug 18, 2022 at 5:03 AM Mikko Ohtamaa <mi...@redinnovation.com> wrote: > Nowadays I program SQLAlchemy by using StackOverflow. This is after 10 years > of using SQLAlchemy. Makes the process more tolerable. However, the extra > power you get from it in a big project is worth it. The project needs to be a > big though, and fit nicely into the web app/CRUD style scope. > > Which brings me to the offtopic questions: what are the best alternatives of > SQLAlchemy in Python today - something that could be a bit easier to approach > and might work better for smaller projects?
Nothing does SQLAlchemy as well as SQLAlchemy. It's much better and more flexible than previous Python ORMs like SQLObject. However, it is complex, and the learning curve can be high. If you introspect a table or result object, or trace the code to see how a column type is implemented, etc, even a simple thing has several layers of calls and abstraction and you wonder whether it's overkill. So let me describe how to do low-level Postges things both with SQLAlchemy and without. I've used SQLAlchemy since ca. 2007 so I've gone through several versions and applied it to several websites. However, my experience is mostly in the 1.0 - 1.3 days, so I haven't migrated to the 2.0/1.4-future API yet. And I'm just writing from memory so the syntax may not be exact. First of all, you need an Engine and a Connection. You may want a Transaction and an (ORM) Session. Turn on logging so you can see the SQL statements executed: that's the key to seeing what it's doing with Postgres. The SQL may be more elaborate than you would write; e.g., "SELECT `tablename`.fieldname" instead of "SELECT fieldname". Say you want to do everything with SQL strings: import logging import sqlalchemy logging.basicConfig(level=logging.INFO) logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO) # Enable SQL logging, or use engine 'echo' argument. t = sqlalchemy.text # Wrapper for SQL strings. dburl = "postgresql://USERNAME:PASSWORD@HOST/DBNAME" engine = sqlalchemy.create_engine(dburl) with engine.connect() as conn: # Different in 2.0: transactions, stricter text(), way of executing query and the result object. with conn.begin(): # Transaction. Or use 'try' block for full commit/rollback control. sql = t "SQLECT id, priority, my_postgres_func() AS funk FROM tablename WHERE priority >= :pri") params = {"pri": 5} rslt = conn.execute(sql, params) rows = list(result) print(rows) print(tuple(rows[0]) print(rows[0].funk) # Can also insert, delete, update, create tables, define Postgres functions, etc. Without SQLAlchemy, you can use 'psycopg2' directly. You'd connect, define a cursor, and do queries with the cursor, in the same spirit as the SQL string above. I normally define SQLAlchemy tables in any case, so that they're there for anything I might do in the future. You can define the columns explicitly, or use reflection to have SQLAlchemy figure it out from the existing database. I've also gone to using a Session in most cases, even if I'm only executing Core queries, again for flexibility. in 2.0 there's only one way to execute both Core and ORM queries anyway. -- You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/CAH9f%3Dur%2Bxgfz5qg4oWPPfh6AE7YL5YnfuF75%2BVpy-X4kmKzohQ%40mail.gmail.com.