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.
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.
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 -> 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), which somtimes makes me wonder if he is a real person or just a pseudonym under which a group of talented programmers publish code ;)
Anytime I write a web frontend in anything other than Python, I miss it very dearly. But really it's well-suited for any kind of templating work, even code generating—I've seen a few projects use it to generate C code. It's very powerful and doesn't try to dumb itself down because of some dogma about not using logic in templates.
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.
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/ :) :)
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).
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...
SQLAlchemy is great, but for those who are looking for something lighter, I highly recommend Peewee (https://github.com/coleifer/peewee). It's a very well maintained alternative.
A lot of folks may not know, but Peewee has a large "contrib" package full of db-specific extension modules and other neat things called `playhouse`. If you use `pip` to install peewee or clone it from GitHub, the `playhouse` modules are included. So while yes, Peewee is a single module, there are lots of extension modules that add a ton of functionality [1].
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.
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)
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.
If you're just looking to distribute, you could use git submodules and point at any package that way.
git checkout --recursive, etc.
Or just a symlink and include it in your setup.py. Or check it out inside your project.
Still allows better organized code. Given, the alternatives are very metaclass heavy black magic, and sometimes spare on comments - actually nearly all ORMs are very heavy on the black magic :)
That made it very convenient for me recently. I had a contract that did a bunch of data analysis. The last step is to upload the results into either MySQL or SQLite database. I used peewee.py, plus some code from playhouse/db_url.py, and got it working.
Even without using the ORM part, the database objects have information to help me roll my own cross-database SQL calls using a bit of string substitution so "?" becomes "%s", etc.
Hey man! Great work and thanks so much for so diligently answering all your user's questions. You're an inspiration for sure, and I've learned a good deal just reading over some of your code :)
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.
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.
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.
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.
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".
TIL... Thanks for this. I've been using SQLA for about 2 years for various side projects and one-offs, and I've never run across this. I've usually implemented things like this in python, which sometimes involves tedious looping and mapping and extraneous queries.
Also worth mentioning, a port of the Django REST Framework browser to Flask - http://www.flaskapi.org/ - which doesn't do as much as DJRF, but it's nice to have the same browser, which is great.
I actually like Django's querysets a little better than SQLAlchemy's, but most other things better in Flask. I may be a heretic.
The declarative API in SQLAlchemy requires much more work to set up relations, and the Django way of being able to chain relations in a query with "__" is pretty friendly in ways that I like.
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).
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.
I tried both flask-restless and restful as well, but like you have moved away from them to a simpler setup. Just using a simple flask app + blueprints with a few extra error handling / cors bits and a custom response class for json. I'm very happy with marshmallow too.
I haven't done too much with either, but for a minimal example, Flask-Restless is more straightforward. Power vs configurability trade-off by the looks of it. I'll certainly keep Flask-RESTful in mind if things get more difficult in the future.
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!
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.
It's always interesting to rummage cross platform to look at libraries so you can steal the good bits. SQLAlchemy has its roots in Hibernate for example.
I'm also a very happy user of Alembic. I change my models as required and then use autogenerate to create the migration scripts. Then I tweak to account for slightly more esoteric things and data migrations. I find that it gets me 80% of the way there without having to think too much about the data model changes while I'm doing the initial development.
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.
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.
>"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.
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.
If you're referring to SQLa Core, it's still SQL. The AST is the same, but by building the SQL AST explicitly you prevent injection and get a degree of composability (with subqueries). IMHO it's the more maintainable way of writing SQL.
Out of interest, are you talking about SQLAlchemy, or ORMs in general?
# a subquery
demo_accounts = db.query(Account.id).join(Client).filter(Client.name=='Demo')
# used inside a query
print(db.query(Account.name).filter(Account.id.in_(demo_accounts)))
SELECT account.name AS account_name
FROM account
WHERE account.id IN (SELECT account.id AS account_id
FROM account JOIN client ON client.id = account.client_id
WHERE client.name = :name_1)
# or as a cte
da_as_cte = demo_accounts.cte()
print(db.query(Account.name).join(da_as_cte, da_as_cte.c.id==Account.id))
WITH anon_1 AS
(SELECT account.id AS id
FROM account JOIN client ON client.id = account.client_id
WHERE client.name = :name_1)
SELECT account.name AS account_name
FROM account JOIN anon_1 ON anon_1.id = account.id
There are obviously much more complex cases, but SA tends to handle things in a pretty sane way. You can compose query segments (like above) and if you really need to go back to the raw sql, you can and still have the results mapped into your python objects.
I don't agree. For other ORMs maybe, but with SA I can get the queries coming out how I want. Also, there's not really that much magic going on – you've defined the relationships so you know how the joins will be constructed.
Also, you're neglecting all the other benefits you get. Things like the loading strategies and the unit of work tracking are extremely powerful abstractions.
I personally like the approach of JOOQ[1] you are basically writing SQL statements using Java and you still can make complex queries and at the time you get benefits of type checking offered by IDE (for example your IDE will actually catch that you're trying to compare column that is an integer with a string).
So, if say, you were inserting a recipe into a db where you had 3 tables (IRL there'd be more, surely), recipe, ingredients, directions. Are you saying you'd use the ORM or the SQL?
I ue ORM's similary. I would basiclly go for the easiest to understand / maintain approach. I don't have much expereince with SQLAlchemy, but I have a pretty good idea of where its worth using SQ rather than the Django ORM. (Usually anything that involves complex joins or subqueries, or similar. "Normal" joins are usually better done in the ORM as it makes a lot more of the Django features available).
I would use the ORM for basic queries, where the conditions were not too complex - its easier than join syntax. If you need anything like subqueries, or extra conditions on the join caluse it gets tricky (in the django ORM at least), and SQL is easier.
Right now I am working with a suboptimal database design, where I need two nested subqueries to get the most recent event in a related row. I changed them to joins then to (indexed) temporary tables, to try and improve MySQl's performance. It worked to a degree. I doubt that would be possible in an ORM as it is working at the databse level rather than a hgher abstraction.
Not the parent, but when I'm using SA (sqlalchemy) I tend to just use the ORM for everything. If I needed something to be super performant I might get closer to the raw SQL – though generally I find development so much faster when SA handles everything for me.
If you're using a different ORM then things are probably going to be different. I've worked with a few in various languages and SA is the only one I've found that allows me to express any complex query directly via the ORM. I've heard there are some edge cases where you need to drop down a layer but I haven't run into them yet.
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
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.
Regarding the for loops I guess you mean the 1+N problem? The great thing about SA is that you can just change your loading strategy at any point to fix it without having to touch any other code.
q = db.query(P)
# add this later to fix your issues
# q = q.options(subqueryload('children'))
p = q.filter(P.id==73)
for c in p.children:
# oops, loads of db queries here
pass
X feature that exists in the period after dbms release and before SA support -- categorical ORM problem.
Also, I'm fully aware of using raw queries in SA/most ORMs -- my point is that if you have to drop down to raw queries, you're paying (mindshare/complexity/concentration/project size/whatever) for an incomplete abstraction you just had to side-step.
Just to make sure there's no hostilities -- I'm not saying "don't use ORMs", because that would be dumb, as they offer tremendous value (and no downsides except for maybe complexity, as you can easily write custom queries) -- I just want to point out that the argument against them still stands, so don't drink too much of the koolaid.
consider that the "rendering of a SELECT string" is only like 10% of what the ORM is doing for you when you create and run `session.query()`. There is: 1. mapping the columns in the SELECT statement to the object attributes 2. dealing with the execution, result set, unicode, type conversion, etc. etc. idiosyncrasies of the underlying database driver as well as the target database backend, 3. routing various parts of each result row not just to different attributes on the mapped class but also into related attributes and collections of other classes all at the same time (e.g. eager loading) 4. maintaining the unit of work / identity map semantics as these rows are loaded 5. presenting the result set as a hierarchical object structure rather than a straight list of rows.
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.
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:
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.
SQLAlchemy gives you a lower level view of your data. That makes a lot of issues with Django ORM just go away (like slow queries for null foreign keys), and makes the ORM much more powerful, making it possible to run queries that would need customizations of the Django ORM. At the price that it is lower level, and your code will be a bit longer.
Both let you escape into bare SQL easily, and Django ORM integrates well with Django, what SQLAlchemy doesn't.
Easy things are easier in Django. Harder things are easier (or even possible) in SQLA.
But that's not quite accurate either, it's more a case of Django being a bit less work to get started with while SQLA needs a little more effort up front. But once set up and mapped, SQLA isn't any harder to use than Django.
I have wrestled the Django ORM into doing some relativley complex queries, but looking back, I am not sure it is worth the effort. The code is more likely to be harder to undersatand by anyone except me, and I started with the equivalent SQL and tweaked the ORM version of the query until it worked. You still need to know the SQL that will be produced when you start using the extra clause.
Maybe SQL alchemy is better. I guess I'll find out soon as my new workplace is using it.
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.
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.
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!)
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.
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.