
Reasons to love SQLAlchemy - edward
http://pajhome.org.uk/blog/10_reasons_to_love_sqlalchemy.html
======
rkrzr
One of the best things about SQLAlchemy is SQLAlchemy Core in my opinion. It
is the layer on which SQLAlchemy ORM is built. There are often times when an
ORM is overkill or when you really just want a lot of control over your SQL or
you just don't want to learn yet another ORM and just want to write SQL with
the added advantage of it being safe, easily composable and which can be
passed around as Python objects.

So if you are thinking about using SQLAlchemy you should make a conscious
decision whether Core or the ORM are better suited for the problem at hand.

~~~
rch
The core is a killer feature for sure, and essential in some cases. With a bit
of pre-planning it doesn't need to be an either-or proposition though.

~~~
rkrzr
Good point. It's true that you can successfully use Core and the ORM in the
same project. Since the ORM is basically just a higher layer of abstraction
(and is cleanly separated), you can always drop down to Core, if that's
required. You only have to be careful in some situations when you do something
"behind the back" of the ORM, so that it doesn't get confused. But that's
usually also pretty straightforward in my experience.

------
ThePhysicist
SQLAlchemy really is great. Alembic (the schema migration toolkit) still lacks
some functionality compared to e.g. the Django "South" package, especially
concerning the handling of cyclic dependencies (where it just "bails out") and
some things that are still not correctly implemented for various database
backends (such as Enum fields), but apart from that it's a really really great
tool. Recently I have used it to implement MongoDB query sntax on SQL which
was not only possible using SQLAlchemy but even enjoyable (for those who are
interested in running Mongo queries on SQL:
[https://github.com/adewes/blitzdb](https://github.com/adewes/blitzdb) -> see
feature/sql_backend branch, which is still not 100 % stable though).

What makes it especially amazing is that Mike Bayer has written 90 % of the
code base by himself
([https://github.com/zzzeek/sqlalchemy/graphs/contributors](https://github.com/zzzeek/sqlalchemy/graphs/contributors)),
which somtimes makes me wonder if he is a real person or just a pseudonym
under which a group of talented programmers publish code ;)

~~~
dangoor
Even more amazing, at the time Mike started SQLAlchemy, his day job was
working in Java... So SQLAlchemy was a total side project. I don't know if
that has changed.

Mike is a master.

~~~
foota
I believe I read that someone hired him to work on SQLAlchemy full time.

~~~
zzzeek
I work for Red Hat in the Openstack group. I can spend part of my time working
on SQLAlchemy upstream as it is a core component of Openstack. But we still
take donations!! [http://www.sqlalchemy.org/](http://www.sqlalchemy.org/) :)
:)

~~~
sametmax
Hi man,

Do you plan on adding await/async support to mako ?

Cheers

------
aidos
I'm pretty comfortable in SQL but I've found myself lazily reaching for SQLA
recently to run queries that I would normally do in SQL. For example

    
    
        session.query(Thing1).join(Thing2).join(Thing3).filter(Thing3.name.like('%easy%'))
    

I'll be in the middle of firing up psql and then I'll think about writing out
my joins and laziness wins out. For more complex stuff I normally go back to
SQL, but mostly because I'm less familiar with constructing some of the more
complex queries in SQLA (though I'm slowly pushing myself to learn more).

~~~
mhd
I use Ruby for the same thing, especially when it's spanning databases.
Sequel+Pry are a pretty decent environment for quickly anlayzing, syncing and
updating stuff. Maybe I'll have to take a second look at SQLAlchemy's query
builder plus ipython/bpython...

In actual code, I'm actually pretty content with raw SQL (wrapped in Perl's
DBIx::Simple these days), as I prefer to move the more complicated things to
functions/views before constructing elaborate abstractions in-place. Complex
ad-hoc queries are another issue, of course...

------
csytan
SQLAlchemy is great, but for those who are looking for something lighter, I
highly recommend Peewee
([https://github.com/coleifer/peewee](https://github.com/coleifer/peewee)).
It's a very well maintained alternative.

~~~
mpdehaan2
I haven't tried it, but the choice to include the entire application in one
file is ... interesting.

[https://github.com/coleifer/peewee/blob/master/peewee.py](https://github.com/coleifer/peewee/blob/master/peewee.py)

~~~
mhd
Between that, bottle and maybe sqlite, you've got your whole setup in a
directory without requiring modules, packagers etc. That can be a big boon for
internal or embedded stuff, when you don't have total control over your
environment.

~~~
collyw
I can't think of an example of when you would want "embedded stuff" running a
webserver. Do you have any examples? (Not saying its not needed, but I am
struggling to see where)

~~~
mhd
Maybe not the apocryphal Forth washing machine controller, but components used
in automation often employ web servers for remote configuration and
statistics. If it isn't a PLC (although there are some devices that basically
mix a small embedded RT PC with a PLC). Connect to the device via ethernet,
and the device then communicates with a machine through some kind of bus.

If you've got wifi/ethernet, configuration through web servers comes
naturally.

------
jhgg
The wonderful ORM support in Python is one of the big reasons why we are
working on a Python GraphQL implementation [1].

[1] [https://github.com/dittos/graphql-py](https://github.com/dittos/graphql-
py)

~~~
aidos
Cool! I'm actually following along with that project to see how it goes. I'll
see if I can find the time to even do something useful on it.

I did a bit of research into the Relay stuff but found it hard to find
examples that explain the api/protocol clearly. So long as you were using the
facebook libs you could follow the examples but I'm more interested in
understanding the mechanics – do you have any pointers of where to look.

~~~
jhgg
Relay is actually a pretty simple specification that sits ontop of graphQL. As
far as the server is concerned, just look here[1]. Don't be alarmed if there's
not a lot to read. There isn't much to the spec[2][3][4].

Also a great place to ask questions is in the GraphQL slack [5]. #python is
where the discussion for the python port is happening.

[1] [https://facebook.github.io/relay/docs/graphql-relay-
specific...](https://facebook.github.io/relay/docs/graphql-relay-
specification.html#content) [2]
[https://facebook.github.io/relay/graphql/objectidentificatio...](https://facebook.github.io/relay/graphql/objectidentification.htm)
[3]
[https://facebook.github.io/relay/graphql/connections.htm](https://facebook.github.io/relay/graphql/connections.htm)
[4]
[https://facebook.github.io/relay/graphql/mutations.htm](https://facebook.github.io/relay/graphql/mutations.htm)
[5] [https://graphql-slack.herokuapp.com](https://graphql-slack.herokuapp.com)

~~~
aidos
Brilliant. Thanks for that - will start having a read through this evening.

------
esaym
I've been enjoying the Perl version as well:
[https://metacpan.org/pod/DBIx::Class](https://metacpan.org/pod/DBIx::Class)

It has built in support for SQL::Translator:
[https://metacpan.org/pod/SQL::Translator](https://metacpan.org/pod/SQL::Translator)
which means you can call ->deploy() on just about any database.

~~~
edibleEnergy
DBIx::Class is amazing. I wouldn't think I'd work with a SQL db without it.

------
sirn
One of many things I love about SQLAlchemy (or could I say the data mapper
pattern) is that it doesn't try to map the column of underlying schema one-to-
one to the object model. This pattern allow for much more flexibility for
building the object model compared to say, the active record pattern.

For example, in SQLAlchemy there is `column_property`[1] which allows me to do
something like:

    
    
        class User(Model):
          id = Column(Integer, primary_key=True)
          first_name = Column(String(50))
          last_name = Column(String(50))
          name = column_property(first_name + " " + last_name)
    

Querying this model will yield the SQL among the line of:

    
    
        SELECT
          user.id,
          user.first_name,
          user.last_name,
          user.first_name || " " || user.last_name AS anon1
        FROM users;
    

This example may not seems very useful (and of course joining a string in the
database is not very exciting), but the same pattern could also be used for
doing subqueries with SQLAlchemy Core[2], e.g.

    
    
        User.post_count = column_property(
          select([func.count()]).where(Post.user_id == User.id)
        )
    

Which yields:

    
    
        SELECT
          user.first_name,
          user.last_name,
          (SELECT COUNT(*) AS count_1 FROM post WHERE post.user_id = user.id) AS anon_1
        FROM users;
    

How SQLAlchemy decouple the object model from the schema and only _map_ them
after they are retrieved makes its ORM very flexible and powerful. Ability to
easily building SQL is one thing, but how SQLAlchemy is designed to allow me
to embrace SQL when necessary rather than trying to hide everything away from
me makes me really love it.

[1]:
[http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_columns.ht...](http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_columns.html#using-
column-property-for-column-level-options)

[2]:
[http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html](http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html)

~~~
itsybitsycoder
In this example, if you select a user object "Bob Smith" and then set
first_name="Jim" but haven't flushed yet, will name be "Bob Smith" or "Jim
Smith"? I haven't worked much with column properties but based on how
unflushed relations+foreign keys work, I'm guessing it'll still be "Bob
Smith"? With a normal @property it would return "Jim Smith".

~~~
sirn
Yes, in that case it would still be Bob Smith.

------
aabajian
I've been using SQLAlchemy for some time now. I found two things that are just
great:

1\. sqlacodegen
([https://github.com/ksindi/sqlacodegen](https://github.com/ksindi/sqlacodegen))
- Creates a single Python file with the SQLAlchemy model from your existing
database.

2\. Bulk inserts:

new_people = []

for person in list_of_new_people:

    
    
        new_people.append({'name' : person.name, 'age' : person.age })
    

People.__table__.insert().values(new_people)

I've prototyped databases in under 30 minutes using the combination of:

1\. MySQL Workbench for design and database creation

2\. sqlacodgen to create SQLAlchemy Python model

3\. SQLAlchemy's bulk insert to populate the database

------
randomsearch
Postgres + SQLAlchemy + Flask + Flask-Restless -> one-page CRUD Restful API

~~~
rch
I've had a better experience with Flask-RESTful, even though it also obviously
needs a lot more work. Did you happen to compare the two?

~~~
aidos
I'd be interested in hearing other peoples' experiences too. I've tried both
flask-restless and flask-restful previously but didn't get along with either
of them.

I recently created a new API and went for a very simple flask.Methodview along
with Marshmallow [0] for (de)serialization. I then wrote a simple replacement
for marshmallow-alchemy since it wouldn't do object loading without a lot of
flapping about.

I found Marshmallow really nice to work with (I'd been using Schematics in the
past).

[0] [https://github.com/marshmallow-
code/marshmallow](https://github.com/marshmallow-code/marshmallow)

~~~
rch
I've taken to using an intermediate 'service' layer (in the parlance, but not
structure, of overholt) to manage metadata on fields and inform serialization.
It looks a bit like Marshmallow, but baked in as a Flask extension.

I've been kicking around different approaches to this for over a decade, going
back to Castor (Java data binding), and with Python, SQLAlchemy, and the
libraries mentioned here I'm finally feeling optimistic.

Request parsing in Flask-RESTful seems bolted on, and the fields in WTForms
seem frustratingly redundant given that almost everything I do is going
through the API anyway. Maybe focusing more on the Fields themselves will help
tie it all together.

------
zeckalpha
I keep telling Rubyists how much greater Alembic is than
ActiveRecord::Migration. If there's a side by side comparison that would be
great to know about!

~~~
stevepike
I'm a happy user of ActiveRecord::Migration, so I'd love to read that
comparison. What do you keep telling the rubyists :)?

~~~
zeckalpha
It forces you to resolve conflicts.

Two developers create a migration on the same day, but they get merged later.
Alembic stops you and ask which goes first. ActiveRecord::Migration runs them
both. This is particularly bad when one migration is a table drop and another
is an add_column on the same table.

And that's just one short coming. Our monkey patched ActiveRecord::Migration
doesn't do this (yet), but we've done quite a few other improvements to it.

Another significant difference is the migration generation. Once you've
generated migrations with alembic, it's hard to tolerate anything else.

------
jdc
I don't know if SQLAlchemy is unique among ORMs for this, but it seems to that
it cleanly, and extensible abstracts SQL. At my previous job, our other
programmer (who had more experience in Java than Python) was able to implement
a soft-delete feature in a couple of days.

------
halayli
constructing queries in python is one of the disadvantages of using ORM. It
takes away all the sql power that a DB can give you. Not to mention the magic
that goes behind constructing the query that could be written in a more
optimized way.

.join.filter.like.order_by etc.. is crazy imo. It's often more complicated
than that and doing it in a non-sql way is suboptimal/unmaintainable.

~~~
zo1
>" _It 's often more complicated than that and doing it in a non-sql way is
suboptimal/unmaintainable._"

It's also more composable, if you ask me. I like to think of it like
"building" SQL statements using smaller components. Just like you do with
code, you essentially compose a complicated business process using smaller
blocks.

It's beautiful in it's simplicity. Even if the syntax requires some extra
work/learning.

~~~
aidos
I've just been using this to my advantage on a system I'm working on.

I've created a bunch of base queries that map from my user account table to
each different table that users have access too. I can then use these base
queries as a starting point to make sure people don't access records that
don't belong to them.

    
    
        bq = db.query(Obj).join(ParentA).join(ParentB).join(User).filter(User.id==bindparam('user_id'))
    
        q = bq.filter(other_rules).params(user_id=1)...
    

It's a really flexible system.

------
hardwaresofton
Just to clarify on why people generally dislike ORMs (I think): at one point
you find yourself struggling with the abstraction (the ORM) to get at some
features that the underlying database engine might allow, or even worse using
the abstraction incorrectly, and creating highly inefficient queries. ORMs are
often mixed with for loops to bad effect.

They obviously have great value for CRUD applications however, and they
prevent you from making mistakes that others have encountered (and fixed).

That said, I really want to try out the Postgres + SQLAlchemy + Flask + Flask-
Restless (as mentioned by others) combo. I usually end up writing my own
queries

~~~
rch
Concerns about the SQL that SQLAlchemy generates are usually referring to the
most basic use of the ORM. The real strength of the framework is that you can
build complex queries directly, yielding optimal SQL, without resorting to
plain text manipulation. This approach is actually most helpful as
applications grow beyond straightforward CRUD operations.

~~~
X-Istence
Not only that, but you can write your optimized queries, and still use the
ORM. So you can get the best of both worlds.

~~~
rch
Absolutely.

------
Phemist
How does SQLAlchemy compare to the Django ORM (if it would be a stand-alone
package, which it sadly isn't)?

~~~
ddorian43
Things that I've used in sqlalchemy that(by a quick search) aren't in django
(as far as I know):

    
    
        composite primary keys
        complex indexes (functional etc)
        arrays + json(these are in django, but I think only recently and before that in contrib)
        server side cursors
        the non-orm part(the lower layer)
        sqlalchemy-alembic (also in django, but only recently I think, still probably less features)
        server_default (define a default value for a column that will be applied to the sql-schema and not just model.field)
        more customization to the lower level db driver(psycopg2, maybe this is also supported in django)
        Use the models + library outside of your web app (ex: in several non-request-serving processes )
    

There are alot more features that I haven't used/don't know/didn't need.

~~~
andybak
A few minor points:

1\. Django's built in migrations are essentially South 2.0 and a poster above
implied that South was more featureful than Alembic. I couldn't say for sure.

2\. "Use the models + library outside of your outside of your web app" \- not
sure why this can't be done with the Django ORM? I use the ORM for many
background and batch tasks

It would be interesting to know What SQLA afficianodos think of the new
goodies in Django 1.7/1.8:

[https://docs.djangoproject.com/en/1.8/ref/models/conditional...](https://docs.djangoproject.com/en/1.8/ref/models/conditional-
expressions/)

[https://docs.djangoproject.com/en/1.8/releases/1.8/#query-
ex...](https://docs.djangoproject.com/en/1.8/releases/1.8/#query-expressions-
conditional-expressions-and-database-functions)

[https://docs.djangoproject.com/en/1.8/releases/1.7/#custom-l...](https://docs.djangoproject.com/en/1.8/releases/1.7/#custom-
lookups)

Obviously the main reason to love the Django ORM is it's tight integration
with Django but I think nowadays it's rather undeserving of it's "SQL
Alchemy's poor cousin" reputation.

------
Syntaf
SQLAlchemy is black magic, and I love it. Over the summer for my internship I
needed to implement a small database in the backend of my application, and
using SQLAlchemy made everything crazy easy.

I'm a huge fan of this library!

------
eevilspock
ASK SQLAlchemy Lovers: Flask or Django for a web app (not API)?

I'm building an app to facilitate more code giving to nonprofits, and help
nonprofits move to the open source world. It will match GitHub coders to
nonprofit projects based on skills, interests and other things. It will
provide an interface for less-technical people at nonprofits, many of which
can't afford tech salaries, to communicate needs and requirements. It will do
as much as possible via GetHub (e.g. Pull Requests) so for coders there will
be no added friction over the already low friction giving GitHub enables.

~~~
dude01
I've been using Pyramid for a few years:
[http://www.pylonsproject.org](http://www.pylonsproject.org)

It's a pretty thin WSGI wrapper around a bunch of best-in-breed Python
libraries including SQLAlchemy.

------
arsalanb
I love SQLAlchemy because I had formally learnt MySQL in high school and I
never seemed to understand why I wouldn't use Excel instead.

SQLAlchemy + Python (preferably using Flask) and the Mega Tutorial by Miguel
Grinberg is the best way to get started in web development. I realize every
body has different methods that work for them, but not only did this work for
me, it also was very well explained by Miguel and also as OP said, the
documentation on SQLAlchemy.

It was sad to bid adieu to SQLAlchemy recently, as I picked up MongoDB (you
should check out MongoEngine if you haven't!)

------
pcote
What I like about SQLAlchemy is that it gives me a choice. I went with Core
because I wanted the sense of security from building SQL queries with a
library instead of out of strings.

Since the ORM wasn't forced on me, I was able to come around to it on my own.
I've started to warm up to it recently even though I can't say I'm totally
sold on it. The nice thing I can say about the ORM as of right now is that I
don't feel like I'd be throwing away my core experiences to embrace it.

------
hokkos
Nice, exactly the same arguments can be said for Entity Framework.

~~~
martinald
Or Fluent NHibernate, which is just lovely.

