12 ms·
Reasons to love SQLAlchemy
- zeckalpha 11y agoI 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!
- vinceguidry 11y agoBut then you'd have to use Python?
- aidos 11y agoGet to use Python :) (joking, obvs) 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.
- stevepike 11y agoI'm a happy user of ActiveRecord::Migration, so I'd love to read that comparison. What do you keep telling the rubyists :)?
- zeckalpha 11y agoIt 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.
- randomsearch 11y agoPostgres + SQLAlchemy + Flask + Flask-Restless -> one-page CRUD Restful API
- rch 11y agoI'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 11y agoI'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 11y agoI'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.
- hurtmyknee 11y agoFlask-RESTful worked out for me, I like how easy it is to define routes. I'm eager to try Falcon next: http://falconframework.org/ http://falconframework.org/
- csytan 11y agoSQLAlchemy 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 11y agoI 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 11y agoBetween 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.
- mpdehaan2 11y agoIf 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 :)
- collyw 11y agoI 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 11y agoMaybe 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.
- aidos 11y agoI'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 11y agoI 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...
- Phemist 11y agoHow does SQLAlchemy compare to the Django ORM (if it would be a stand-alone package, which it sadly isn't)?
- ddorian43 11y agoThings 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 11y agoA 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-expressions/ https://docs.djangoproject.com/en/1.8/ref/models/conditional... 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.8/#query-ex... https://docs.djangoproject.com/en/1.8/releases/1.7/#custom-lookups https://docs.djangoproject.com/en/1.8/releases/1.7/#custom-l... 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.
- marcosdumay 11y agoSQLAlchemy 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.
- ThePhysicist 11y agoSQLAlchemy 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 ;)
- arsalanb 11y agoI 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!)
- jhgg 11y agoThe 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 11y agoCool! 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 11y agoRelay 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-specification.html#content https://facebook.github.io/relay/docs/graphql-relay-specific... [2] https://facebook.github.io/relay/graphql/objectidentification.htm https://facebook.github.io/relay/graphql/objectidentificatio... [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 11y agoBrilliant. Thanks for that - will start having a read through this evening.
- rkrzr 11y agoOne 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 11y agoThe 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 11y agoGood 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.
- hardwaresofton 11y agoJust 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 11y agoConcerns 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.
- plumeria 11y agoYou can use raw queries for that. What kind of features are you referring to?
- hardwaresofton 11y agoX 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.
- esaym 11y agoI'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 11y agoDBIx::Class is amazing. I wouldn't think I'd work with a SQL db without it.
- hokkos 11y agoNice, exactly the same arguments can be said for Entity Framework.
- Syntaf 11y agoSQLAlchemy 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!
- halayli 11y agoconstructing 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.
- Tobu 11y agoIf 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.
- halayli 11y agoYou can prevent injection by passing the parameters to cursor.execute(query, (params,)) as a tuple. You don't need to rely on ORM to do that for you. subqueries are best expressed in sql directly. They can get complicated, especially when using the with clause. I am not saying that all of what ORM offers is bad. But beside the very basics they should step away. The less they offer the better.
- aidos 11y agoOut 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.
- eevilspock 11y agoASK 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.
- thearn4 11y agoI'm a huge fan of flask, but my projects tend to be too small for Django to be a reasonable choice.
- dude01 11y agoI'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.
- puredemo 11y agoAnother vote for Pyramid.
- sirn 11y agoOne 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.html#using-column-property-for-column-level-options http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_columns.ht... [2]: http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html
- ajford 11y agoTIL... 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. Thanks!
- itsybitsycoder 11y agoIn 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".
- aabajian 11y agoI'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
- pcote 11y agoWhat 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.
- jdc 11y agoI 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.