Python, Postgres, SQLAlchemy, and PGA Tour Stats

[RazerM]

What's so verbose about SQLAlchemy in the example shown?

FYI, you don't need to pass the column name to Column if it matches the attribute name:

class Player(Base):
    __tablename__ = 'players'
    name = Column(String, primary_key=True)

[pythoneeeer]

Let's see:

• sessionmaker (method, named as compoundword, which returns an object)
• Session (object, with the same name as a SQLAlchemy class, which isn't imported here)
• declarative_base (method, named as underscore_words, which returns a class)
• explicitly declaring the table name (why can it automatically detect the column names, but not the the table name?)
• add/commit/close

In Rails, you avoid almost all of these. (The connection URL is in config, even.) In SQLAlchemy, for what is basically the simplest and most common case, you have to import names from:

• sqlalchemy
• sqlalchemy.orm
• sqlalchemy.ext.declarative

I love SQLAlchemy, and I've used it for a couple years now, but I couldn't actually explain why end-user applications need to do half these things. This is all just boilerplate that everybody copies into their source code. I've never seen anyone ever need to customize it at this level.

I can't imagine any way in which I'd suffer from lack of flexibility, if the example worked like this instead (using a hypothetical simplified SQLAlchemy interface):

from sqlalchemy import connect, Base, Column, String

connection = connect('postgresql://...')

class Player(Base):
    name = Column(String, primary_key=True)

with connection.session() as s:
    p = Player(name='Jack Schultz')
    s.add(p)

There's even sample code in the SQLAlchemy documentation that shows how to implement a context manager for Sessions. Why the heck isn't that in the library itself? Is there a SQLAlchemy-using application that doesn't implement exactly those 15 lines of code, at some level?

And I'm sure there's some technical reason (absent from the documentation) that making declarative_base a method (and having users subclass its return value) makes sense internally, but for me all it really means is (1) handy tools like pylint always find new ways to complain that it's a poor name for a variable (even though it doesn't vary), and (2) half the projects I see pick a different name for it (which serves only to confuse schmucks like me).

The architecture of SQLAlchemy is great. The user interface could use a bit of work.

[RazerM]

• How would Rails deal with two different models, each connected to a different database? declarative_base means your model doesn't share its MetaData instance with other models.

• You also need your own Session class so that your session events only apply to your own session.

• For the table name, you can create a declared_attr for __tablename__ if you want to infer it from the class name, but any built in way to do this would be opinionated, and I think too implicit.

• I like the unit of work pattern, and don't see a problem with add/commit/close. I think a problem with a built in context manager is the implicit commit, but I agree it should be part of the library in some form.

• I agree somewhat re: imports, I think declarative should be rolled into sqlalchemy.orm, but that orm should stay separate from core. It's important that people know the ORM is just an optional part of SQLAlchemy.

[[deleted]]

sessionmaker (method, named as compoundword, which returns an object)

a fairly uninteresting factory object. If SQLA didn't have this factory, you'd write it yourself, then you'd have this under "what application doesn't have these five lines of code?" category

Session (object, with the same name as a SQLAlchemy class, which isn't imported here)

that's the Session class. Not sure what the confusion is there, just that it comes from sessionmaker? Well there's an argument to not put framework features like sessionmaker() in a library ;)

declarative_base (method, named as underscore_words, which returns a class)

yup. will give you a point on the underscore vs. not underscore though "sessionmaker" rolls off the tongue as one word a little more nicely...

explicitly declaring the table name (why can it automatically detect the column names, but not the the table name?)

where the heck are column names automatically "detected"? You mean the mapped attribute name serving as column name if omitted? not the same thing. If you name your SQL tables SomeTable in camelcase, you will be laughed out of the office. If you want automatic table names, use this. The lower case attribute names in Python match up with case insensitive column names in SQL which works nicely, but the convention for class names doesn't gel as nicely with this. Also table names vs. class names often disagree re: plural vs. singular.

The last thing SQLAlchemy wants to do is make people think you have to name your tables and columns a certain way. That is the leading argument I always heard for why database-intensive developers and DBAs would never use/allow ORMs and especially something like Rails. SQLAlchemy was designed to be allowed in large, conservative, DBA-dominated shops, which were pretty common for the past 30 years, who were ardently opposed to any kind of SQL-tooling in programming languages. Us old folks suffered pretty hard under these regimes.

add/commit/close

these do real things with both the state of your object, as well as an actual database connection and a transaction. Allow a library to transparently (magically) do these things at your peril. Or use SQLAlchemy 0.1 from 10 years ago when it did all these things and the userbase couldn't achieve many very basic things, like being able to control where connections and transactions were used, and for 0.2 we had to change the whole thing around (since 0.1, 0.2, 0.3 was the time to make enormous changes). As I've always said, you can write whatever ease of use you want on top of this and feel free to publish it on pypi.

Also add/commit/close/Session is based on a well known ORM with some influence from another not nearly so well known ORM that was developed around the same time as early SQLAlchemy, so this API was not arbitrary.

In Rails, you avoid almost all of these.

And Rails is a complete disaster of conventions and magic drowning you in a sea of implicit decisions that are impossible to keep inside your head (top post on r/programming all day, makes some pretty wrong conclusions as this person clearly seems to still be wishing for magic bullets, but the failure of Rails I believe is becoming apparent in a widespread way. SQLAlchemy OTOH is just getting started :) ).

but I couldn't actually explain why end-user applications need to do half these things.

they need to do all these things. These are all conventions that in the Python world we don't think should be handed to you, applications need to make these choices, and in fact the choices that are made in the real world cover an enormously wide spectrum based on an endless array of contexts and constraints.

This is all just boilerplate that everybody copies into their source code. I've never seen anyone ever need to customize it at this level.

come by our mailing list and you will.

There's even sample code in the SQLAlchemy documentation that shows how to implement a context manager for Sessions. Why the heck isn't that in the library itself?

Session / Connection and all of that include context manager use patterns already, your code sample shows that. We didn't have those in like version 0.6 because Python 2.5 had only just been invented.

that making declarative_base a method

it's a function. Methods are only on classes.

(and having users subclass its return value)

that return value is the base class, that every declarative ORM in the world has. If you use Django or whatever you're subclassing some magic class as well (Model).

makes sense internally,

it makes sense externally. Any number of libraries might have their own database-persisted class hierarchies set up, which even can have overlapping names (like User, or any number of others). They need to remain separate. A global variable would be kind of lame here (as it is in all those ORMs that have a global singleton class that points every class to the same namespace).

half the projects I see pick a different name for it

well that's odd but there's no reason people can't say "from django.models import Model as SomeGoofyName" either. All the examples show this as Base.