Camelot a good tool for me

[Chris Angelico]

On Sat, May 23, 2015 at 5:12 AM, Lele Gaifax <lele at metapensiero.it> wrote:
> Chris Angelico <rosuav at gmail.com> writes:
>
>> SQLAlchemy has its uses, and it does solve a number of
>> issues in reasonably clean ways, but I don't like a few of its facets,
>> including its peculiar way of doing foreign key relationships. (You
>> put a foreign key in the child, and you put a relationship in the
>> parent, which feels backwards.)
>
> You are conflating two different layers, core and ORM. ORM relationships can
> be declared either on the parent or on the child, it's up to your taste.

Not sure why that's distinguishable. If I have two tables like this:

Users:
    id sequential primary key
    name text

Tasks
    id sequential primary key
    owner integer
    assignee integer

Both the owner and the assignee refer to the Users table; the owner is
a mandatory connection (every task was created by someone, who
initially owns it), and the assignee is an optional connection (a
newly-created task isn't assigned to anyone). With me so far? Okay.

Now, if I were to represent these tables in SQLAlchemy, obviously I
need to have foreign key relationships encoded in SQLAlchemy. But if
I'm to enforce these relationships on the underlying database, it's
equally obvious that I need foreign key constraints. I would expect
that a relationship encoded in SQLAlchemy should cause the creation of
a constraint in the database. They're fundamentally the same thing.

My point about backwards is that my tables here are declared in a
strict order: parent table, then child table. In the child table, a
constraint is created by saying "references Users", and the Users
table already exists. At no point is there ever a forward reference.
Code would look like this:

create table Users (id serial primary key, name text not null default '');
create table Tasks (id serial primary key, owner integer not null
references Users, assignee integer references Users);

But with SQLAlchemy, you have a tag in the Users table's definition
saying that it has a relationship with Tasks, as well as a foreign key
in Tasks stating the connection to Users. That violates "Define Before
Use", which isn't a strict policy, but it does feel a little bit
"dirty".

>> When magic works, it's great; but when anything goes wrong, it's harder to
>> see what happened.
>
> The same can be said of almost any upper layer in a software stack.

Precisely. All magic has to justify itself. Some can, easily. Some
can't. A lot is in the middle, where it's part of the tradeoffs.

>> Also, when does a transaction begin and end?
>
> When I need transactions (that is, when I'm changing the database) I'm very
> picky and use explicit begins, commits and rollbacks, so I don't recall
> experiencing that doubt.

That's all very well when you write your own code. Now try picking up
someone else's code. Or, for a mid-way concern, try explaining to a
junior developer how to make sure his transactions are right. With
psycopg2, it's easy enough to do this:

with conn, conn.cursor() as cur:
    cur.execute("....")
    cur.execute("....")

When the with block exits, the transaction is either committed (if all
went well) or rolled back (if an exception was raised). It's very
simple, easy to do, and easy to audit ("all SQL queries must be inside
a with block that grants a cursor", and possibly "cursor-granting with
blocks must not be nested").

>> If you session.commit() in the middle of iterating over a query, will it
>> break the query? What if you roll back? Can you see, instantly, in your
>> code?
>
> Why would you do that? Are you closing your files while you iterate them,
> without leaving the loop in some way at the same time?

There's advice out there on the internet that says that committing
periodically in the middle of a big job makes your code run faster.
It's from the PHP + MySQL school of thought, where the assumption is
that finishing is the most important thing, finishing quickly is a
close second, and guaranteeing correctness isn't even on the radar.
Now try coping with code that was written under that model.

Fortunately, I haven't actually seen anything quite like this in
SQLAlchemy. The worst I saw was a case where someone was iterating
over a query and performed another query, which did indeed break the
fetching of results. But the more magic you have, the less obvious
that is.

>> Even if the ORM layer is practically perfect in every way,
>> there's still value in learning SQL; for instance, if you drop to a
>> command-line interpreter like PostgreSQL's psql, or if you switch to
>> another language, or anything like that, it's helpful to know what's
>> going on under the covers. And if you have to know SQL anyway, the
>> advantage of the abstraction layer has to justify the cost of learning
>> an additional, not a replacement, API.
>
> No doubt on that. Working with SQLAlchemy is not an alterative to knowing SQL
> fairly well. SA does hide "details" (name quoting syntax, to mention one
> obvious detail), but does not even try to hide the fact that it's talking SQL
> all the way down.

Yep. SQLAlchemy is miles ahead of some of the things I've worked with.
And I have to say, the declarative style of laying out a table is
excellent. Using psycopg2 or SQLAlchemy is a matter of taste; using
either of the above or DBExpert is a matter of insanity.

> I often have to deal with multiple DB engines at the same time, and being
> able to "write" my queries with an abstract syntax is very valuable for me.

Hmm, I'm not sure there's all that much that this helps with. Thanks
to a consistent PEP 249 API, changing database engines is often just a
matter of changing one import and a connection construction line
(which you'd have to do anyway, given that the credentials will
change). What else is there for SQLAlchemy to paper over? The
differences remain; the common ground is already common.

>> So, while SQLAlchemy is definitely a lot better than most I've seen,
>> it's still not really good enough for me to use everywhere. I'm mostly
>> going to stick to the Python DB API 2.0.
>
> Again, I fully agree: one should work with whatever tool he feels comfortable
> with!

Yep. Both are good enough to use. Personally, I'll use psycopg2, but
if you want to use SQLAlchemy, I won't think you a fool. Same if you
choose Django (I use Flask), etc. They're close enough that
situational differences make all the argument.

Just don't use MySQL if you can help it.

*ducks for cover*

ChrisA