I guess SQLAlchemy is complicated but we are talking about a library for
interacting with SQL(!) (and sometimes even mysql!). The amount of effort
that has been put into the design, the documentation, the support, the
migration notes, deprecation warnings, BWC, etc. is inspiring. We are
spoiled in Python land and I don't want that to go unsaid. The 2.0 changes
are going to make it even better. The ORM loading features in it blow my
mind. You can ORM and not ORM! Reflection! I love the library. Thank
you zzzeek and friends!
I just about crack a gear every time I see raw SQL used in JS or some other
language ecosystem. We have a space ship right here, right now. Why are
people strapping cardboard to their arms? I've done it before myself many
years ago and all I really learned was that it makes your arms really sore.
Just my 2-cents.
- Ian
On Thu, Aug 18, 2022 at 6:16 AM Mike Orr <sluggos...@gmail.com> wrote:
> 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
> .
>
--
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/CADj1%3D66acqnazaY6M9mn%3DeNjsmoOoJtF7cJpd02yjOuXn2BCCA%40mail.gmail.com.
