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.
