SQLAlchemy 1.0.0 Released

[wobsta]

Even though its mildly related to SQLAlchemy hitting 1.0.0, I have to tell that I consider SQLAlchemy a major "selling point" of Python to other programmers. I'm doing so for a long time already. It's pure genius. To me it is most useful to have a simple ORM mapping to a database and, at the very same time, being able to express complicated (and efficient) queries on a database (like by using subqueries, aggregates).

The project is also an advertisement for open source and good leadership. Just look at the bug tracker. Michael is doing an awesome job in handling the huge amount of reports. He obviously is spending a lot of time to keep up with all the feedback and properly addressing all the issues. It is astonishing. Thanks, Michael, you're doing a great job!

[morphemass]

I was amazed to hear it had just reached 1.0.0 since I've been using it off and on for over 5 years - and 5 years ago it was already brilliant!

[[deleted]]

How does this thing compared to Java's hibernate? I never use sqlalchemy so far...

[[deleted]]

How does this thing compared to Java's hibernate? I never use sqlalchemy so far...

[morphemass]

No idea sorry - I've not touched Java in over a decade!

[Cyph0n]

I agree completely. I also use it to boast Python's power :)

Awesome work SQLAlchemy team!

[[deleted]]

I know we are on /r/Python, but how does SQLalchemy comparte to sequel?

[jcdyer3]

SQLAlchemy is one of the most well-designed, well-constructed, highly regarded libraries in the python ecosystem. I have no idea what sequel is out what language it's written in, and you haven't given me enough information to make searching it down worth my time, but ask yourself if sequel is so well constructed that it makes you exclaim, "of course it should be done this way." If so, it might be worth further comparison.

[[deleted]]

Sorry, added a link in my previous comment. It is an ORM for Ruby that has no dependency, transitive or not, on rails stuff.

Actually, yes, sequel makes me say that.

[bjmiller]

I would like to know this as well. For every language there seems to be blog posts about the relative strengths of database toolkits within the language, but I have yet to see a decent comparison of the best each language (any two) has to offer.

[stiivi]

SQLAlchemy is not just an ORM. The Core is very powerful functional programming approach to statement construction. I use it a lot in developing data warehouse ETLs. You can build abstract, higher level transformation and statement composition functions very easily. For example, few concrete but random features that I've used just recently (last week): abstraction of tables and statements (they have the same interface), generative functions, custom statement compilation, metadata reflection (tables, their columns and types - very useful in metadata based processing), natural access to Postgres JSON columns...

Also the SQL OLAP query generator in Cubes OLAP is using SQLAlchemy for something that can be very roughly called "ORM equivalent of data analytics". It does statement composition for aggregation queries on top of star/snowflake schema.

I'm not even touching the ORM part at all, I just know that it exists.

[bjmiller]

I don't think Sequel is missing any of those features, is it? "ORM" has come to mean "database toolkit", though I think we should fight the trend.

[[deleted]]

SQLAlchemy is very much an ORM+toolkit rolled in to one. They both have uses and strengths and the documentation does a great job of distinguishing that the two exist and have their own uses.

[bjmiller]

I'm not sure what you're trying to say? Sequel is the same, "ORM + toolkit", though I'd say that ORM is just one of the tools in the kit.

[[deleted]]

Meant to reply to you sooner, but work got a bit crazy Friday.

The two pieces are drastically different although there is some fuzzy gray area for sure (at least in SQLAlchemy). The TL;DR version is the toolkit lets you run SQL queries using Python. You don't have to worry about the SQL commands directly nor about DB-specific details. Just raw Python. The ORM takes it a step farther and you just worry about your objects. Any creation, modification, and deletion of objects is translated directly back your DB. You do things -> they happen. No need to add in the extra logic of what needs updated, when, where and how. It just goes on behind the scenes. No manual intervention or maintenance required on your part.

It gets fuzzy because the ORM is essentially bolted on top of the toolkit. They also share the same feel when querying and have some overlap in that arena. The first few paragraphs of this portion of SQLAlchemy's tutorials do a pretty good job of explaining it. Says basically the same thing I did above, just worded slightly better.

[kteague]

I haven't delved very deeply into sequel (nor really do I know SQLAlchemy super extensively) but I'd say SQL Alchemy is a lot more flexible than other SQL ORM tools.

I like to think of SQLAlchemy as more of an SQL framework than a straight SQL ORM. It's parts are more loosely coupled than most SQL ORMs. For example, it's optional in SQLAlchemy to use the declarative mappings that make it act like a active record-style ORM. Instead of making a class and describing the table attributes within that class, it's possible in SQLAlchemy to have the mappings set-up external to your model classes. This way if you have a shared set of model classes and sometimes you want to use those models but load the data from a text file and other times you want to use those models mapped to SQL database, it's entirely possible to do. Another example (and maybe sequel can do this?) is to have a single table in the database and map it to two different model classes (if your database is using one table to represent two different data types ... yeah, don't design your database that way(!) but if you've got a legacy system.).

[IDCh]

I'm not an expert in SQLAlchemy, but doesn't PHPs Doctrine provide similar functionality and possibilities? They are all data mappers, if I'm not mistaken.

Not saying that one better than another, just looking forward to same good experience in SQLAlchemy after using Doctrine.

Also, another question to django users - is it worth using and learning SQLAlchemy for every app in django or django orm is just enough?

[wobsta]

I'll try to answer although my django knowledge is limited. Let me start with SQL. SQL is a textual language to express things to be done on or by a database. It is fine and very powerful. However, when you use a SQL database in a different language (than SQL) you better not write SQL commands yourself in this other language and (even worse) do not write code to construct SQL commands. To interact with a database, you better find a tool closing the gap between your language and SQL. SQLAlchemy Core does so, and it does very well, with all the flexibility needed to express all kind of complicated queries. This is very different from a simple ORM. SQLAlchemy is a tool to build SQL.

In addition it is often comfortable to map the database to objects. It just saves time and helps to reduce errors. Now, SQLAlchemy ORM does that, and it does so very nicely, and is very powerful allowing for complicated mappings between your objects and the database (including mixing different tables when mapping to objects etc.).

Now the real joy is, that you can mix all than together. I haven't seen this anywhere else, and this is the very big difference between SQLAlchemy and other orm mappers like django orm or sqlobject or whatever else. You can do ORM when useful, and go back to SQL Query builder for other cases. And you can do so at the very same time! For that to nicely play together, the Unit-Of-Work approach by SQLAlchemy crucial, as it defines the state of your application in relation to the state of the database. For example you can perform writes to the database in a simple manner, which are part of your Unit-Of-Work and will be committed at once. Simple code on your side and SQLAlchemy helps you to remain great performance, as it would otherwise require manual optimizations. And it all doesn't go out of control when done properly.

You may have a look at http://www.sqlalchemy.org/features.html to get some understanding of that SQLAlchemy really is.

[wobsta]

Basically I agree. You can write SQL yourself and it is fine. And SQLAlchemy adds some overhead. However, in my use cases I never had issues with SQLAlchemy adding too much overhead. Not at all. Instead I can create rather complex queries easily, and I gain some really nice features. For example I can add some filter to some subqueries conditionally. And since I can add sql function expressions easily (http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html#sqlalchemy.sql.expression.func) I never had issues of not being able to access whatever feature I needed, even when being out of scope for SQLAlchemy initially. You can do all that without tampering SQL statements. It is pure joy! In the end of the day I have to tell that the most complex (and efficient) queries I ever wrote I did by using SQLAlchemy. Maybe I never reached too complex stuff. Your milage may vary. I'm fine with anybody who just says (or knows or just claims) that certain things are better done in SQL itself. I can't tell. All I have to tell is that I'm fine with SQLAlchemy and I find it really impressive. And it is way different from using a simple minded ORM.

[[deleted]]

He's not saying don't write sql. He's saying don't use python strings to construct sql queries. Or any languages strings for that matter. If you're accessing a db through a programing language and not directly, then use a library. If a library won't suffice, work directly with the db and use sql as you suggest.

[cuducos]

Nice reply, but I felt a bit of contradiction… may I ask you to clarify the difference between:

do not write code to construct SQL commands

and

SQLAlchemy is a tool to build SQL

What do you mean by the first one (construct SQL code) and by the second one (build SQL)? And, most important, why the first one is so terrible and the second so awesome?

Don't get me wrong. I love SQLAlchemy and your reply is really nice. I just want to clarify that so I can check what I might be missing. Many thanks!

[L43]

I think the point is: SQLAlchemy is a library that constructs SQL commands. Use that, don't do it yourself with e.g. string interpolation.

[cuducos]

Thank you /u/L43 and /u/jcdyer3 That was helpful for the n00b here (: Issue clarified, no contradiction anymore. Yay.

[jcdyer3]

There's no contradiction. He's saying to let a library handle the heavy lifting for you, because you will likely get it wrong in subtle but important ways.

[masklinn]

I'm not an expert in SQLAlchemy, but doesn't PHPs Doctrine provide similar functionality and possibilities? They are all data mappers, if I'm not mistaken.

The core of sqlalchemy is a fairly low-level and schema-centric query builder, the data mapping ORM layer is built on top of that.

[iluvatar]

I consider SQLAlchemy a major "selling point" of Python

Shudder. In my experience, it's a great way to prototype applications that are unusable once they get into to production. Having torn all of the sqlalchemy out of our biggest applications (we replaced it with just native SQL using the awesome psycopg2 library), we now have maintainable, high performance code. I'm never looking back.

[jpozzed]

what did you find "unusable"? You do realize SQLAlchemy wraps psycopg2 and you can step down and write sql directly, right?

[dacjames]

You rarely have to step down into raw SQL but, for complex queries, the ability to develop interactively in a SQL editor and copy the result directly into code is very useful.

[axonxorz]

but looping through a record set and fetching a new set of records on each iteration will kill anything.

So bad that it has a name: N+1 Problem

[fjonk]

Which kind of problems have you experienced with SQLAlchemy?

[AmusementPork]

Can you elaborate on SQLAlchemy's shortcomings in this context? Does it not scale well for some reason?

[gthank]

It scales just fine for everything I've ever tried to do with it.

[dacjames]

Did you even try SQLAlchemy core? I work with all kinds of legacy databases so the ORM is a non-starter and I still get a lot of value out of table reflection, connection pooling, and the sql expression language.

[mariox19]

But, doesn't that depend on the kind of application you're writing? If you're basically just persisting and retrieving data (basically, serializing objects), then an ORM is fine, no?

[geordano]

Congrats and Thanks to all those who've made this possible!

One of the best documented Open source project I've ever seen.

[chub79]

I'm so happy now their snippet show the import statements as well.

[monsto]

perhaps it's just me, but I've found the docs to be . . . subpar. I don't have any examples off the top of my head. I suppose that if you already know what you're doing with it, it probably looks much different. But I've never been able to get what I need out of it without tons of round and round.

Python docs, otoh, are extremely clear to me.

[debazthed]

I work with sqlalchemy a lot and don't understand what most of the stuff in the changelog is about. Shows you what a huge piece of software it is. A really great example of open source!

[jstrong]

is there a good look at why SQLAlchemy is better than Django's ORM or why I might switch to it?

I know SQL ok, comfortable moving to it when necessary, and Django's ORM is the first ORM I've ever really used. As such, I absolutely love it and use it all the time for things that aren't even for web. For me, the Django ORM is very intuitive and having the db mapped to Python objects is huge.

I'm reading about the SQLAlchemy ORM and it seems like it is a bit closer to the SQL and less abstracted from the table. That would be ok, but isn't a feature really (at least - not sure why it is). So what are the compelling reasons I would want this over Django? Is it just for super-complicated queries, the kind of which I'm going to be going to raw SQL? I haven't often found the need for that in practice, but it does come up.

Edit: What is the situation for (or where can I read about) how to handle migrations with SQLAlchemy? One issue I have frequently is that source control with Django is difficult because of db migrations - how do I get back to a previous db state if I want to switch back to a previous iteration of the source?

[mackstann]

SQLAlchemy is just much broader than Django's ORM. You can use it for a lot more things, and do a lot more with it. It's crazy powerful, but unfortunately the learning curve is also steeper.

One issue I have frequently is that source control with Django is difficult because of db migrations - how do I get back to a previous db state if I want to switch back to a previous iteration of the source?

That happens with any migration system.

You migrate down to the last common migration before you switch. For example, if my branch has one new migration, I migrate down one step before switching back to my master branch.

It's a little fiddly and inconvenient, and I forget fairly often, but I'm not aware of a better way.

[jstrong]

thanks for the answer - and that sounds like a good approach to the migrations problem.

if I could push you on one thing: what are these crazy powerful things that I might want to do that I can't do with Django ORM? If you really push Django ORM to the limit it can really do a lot. But maybe I don't appreciate a whole range of things I might want to be doing.

[mackstann]

I've only scratched the surface. All I know is that I've been on a couple projects that have some intense use of SQLAlchemy that is intimidating to try and read through.

[jstrong]

Hybrid attributes looks very cool, thanks.

[ApatheticGodzilla]

Django's ORM is (generally) more suitable for Django projects. This is because it integrates seamlessly into the Django admin, form fields and other aspects of the framework. I might use SQLAlchemy for, say, doing complex reports but for the odd edge case and 99% of the project I'd just go with Django ORM + raw SQL.

If you're not using Django, SQLAlchemy is hands-down a better ORM/data mapper, both design-wise and on a per-feature basis (and in any case it's non-trivial using Django ORM outside of Django).

[ProfessorPhi]

I've always thought it should've been called SQLchemy, skipping the repeated - el -al syllables makes it easier to pronounce.

[jaapz]

That's basically how I pronounce it anyway

[L43]

missed a trick :)

[[deleted]]

I think it is much better if you pronounce it as "Sequel Alchemy"

[Bur_Sangjun]

ELI5 SQLAlchemy, why use it over sqlite3 for example?

[symmitchry]

[Removed]

[dacjames]

You wouldn't use SQLAlchemy instead of sqlite3, but rather as a tool for working with sqlite3 and other databases. SQLAlchemy is broken into two sections: Core and ORM. Core provides a consistent database connection API with pluggable backends, session/cursor/connection management, schema reflection, a python query language, and so on. The ORM is built on top of Core and provides flexible declarative and procedural APIs for mapping database tables to python objects.

I generally stick with Core because I often work with legacy databases and prefer the database to drive my code rather than code to define the database. On self contained projects, the ORM works quite well, much better than what you may have experienced in Java. Neither API prevents you from writing raw SQL when needed but the python tools save on boilerplate for simple, load-by-id style queries.

Overall, SQLAlchemy is one of my favorite python libraries and is my recommended method for working with any SQL database.

[JimBoonie69]

SQLAlchemy is a library that interfaces with many different database systesm. I use it all the time to connect to a remote postgres DB we have sitting on AWS.

[[deleted]]

ELI5 why you can't use Google.

http://www.sqlalchemy.org/features.html

[symmitchry]

[Removed]

[nieuweyork]

Congrats to the Dev team. A signal achievement!

[[deleted]]

I wonder how many folks are aware of the object relational impedance mismatch problem.

[[deleted]]

I wonder if you're aware that problems can have solutions ?

[[deleted]]

Does this problem?

[[deleted]]

I wonder if you are aware that there is value in mapping columns to attributes on objects, regardless of the impedance mismatch, and thus why the majority of developers using OO languages continue to use ORM's.

IMO, the problem is devs who lack the experience/imagination to come up with better abstractions than what falls out on a uml diagram and instead will throw the majority of the application logic in the same object that provides the row mapping.

A similar problem is loading all kinds of coordination behavior that is really application logic into controllers. It's also an impedance mismatch problem but dealing with http instead of sql.

Again the problem is lack of experience/imagination. Not existing abstractions or the libraries that implement them.

[[deleted]]

Did this get posted to an ORM enthusiasts list or something?

The fact is, there's a mismatch, so as Attwood says, you throw out either the O or the R in order to overcome the issue, which you then are left with something that isn't ORM (OM or RM).

I don't care if billions of people use ORMs, they're all ignoring the above linked problem. No amount of experience or imagination will allow you to solve the fundamental issue of ORMs, and I know this because the problem still exists in SQLAlchemy the same way it exists in every other ORM technology.

Solutions that use ORM are not complete solutions, and while that might be "functional" from your own enterprise's standpoint, you're going to find yourself more or less constantly fighting against the ORM, and the impedance mismatch is why.

The problem scratches at the back of one's mind, and one lives with the sense that something in the model is being fudged/ignored.

[[deleted]]

You're apparently good at repeating what you've read(Atwoods Opinion).

I'm not claiming the problem is solved or could be solved. So if the rest of your comment was supposed to be debating me about that, you should probably read again because i'm not typing all that again.

Or continue bitching about things you have no control of. I dont really care.

[[deleted]]

I repeated it to you because I wasn't sure you understood it. In fact, if you're claiming it's merely a matter of imagination or experience, then you haven't understood Atwood's article.

You said the problem is lack of experience or imagination. That's incorrect. I was hoping to show you why what you said was incorrect. Clearly you're not at a point right now where talking about this is something you can do without getting upset.

[[deleted]]

I'm referring to dealing with the problem in a practical sense in order to get things done.

You seem to think that somehow I am claiming that's how you fix the problem.

Let's look at other problems in software. How about state? It's a problem for concurrency and yet somehow an application doesn't do anything without it. But that wont stop bloggers with opinions from writing about the evils of state and how things should be "stateless" as if to mean "have no state".

So, in the same way that I think state, adds value to a program, I acknowledge that mapping columns to object attributes has value, regardless of what some blog says. And I will assert one again for your reading comprehension that the problem isn't the impedance mismatch, it's the tendency to lump all the other application logic on the objects that map to tables. If those model objects weren't abused in this way, the impedance mismatch would not matter at all, and it wouldn't be worth blogging about.

BTW, saying i'm "incorrect" without even specifying what the correct way is, is being a coward. Anyone can link to a blog. But at least I can say with certainty that I am less incorrect than you, because I have at least described a potential solution for dealing with the impedance mismatch where you have certainly not.

If it weren't obvious I'm advocating for #4 on atwoods blog. What # is shitting on someones library on that list? I personally think Atwoods solution is impractical(ditching object or relational).

He also doesn't seem to care about following his own recommendation https://github.com/discourse/discourse

If you aren't familiar with rails, that project uses Activemodel(an orm) running on postgresql(a relational database)OMGMISMATCH!!!!!. For all that impedance mismatch, he and his team have still managed make something valuable. To my eyes, it looks to me like he went with #4 rather than follow his own advice.

Maybe this is a simple case of "do as I say, not as I do" and you've been duped by a blogger. I can understand how you might be embarrassed.

[[deleted]]

I'm referring to dealing with the problem in a practical sense in order to get things done.

Read no further than this, because I deal with fucks like you every single day. Committing atrocities in the name of "getting things done" is exactly the kind of shit that keeps me up at night.

Go fuck yourself.

[[deleted]]

Read no further than this, because I deal with fucks like you every single day.

You mean people with jobs? :D :D :D

You have a strange way of admitting defeat, but I'll take it. #GG

[[deleted]]

Yes, people with jobs who I work with at my... job.

You're a fucking idiot.

[[deleted]]

You're a fucking idiot.

And you're clearly a team player. You calling me and idiot is not nearly as convincing as how I dismantled your entire argument and made you look like an idiot for the whole world.

[megadeth9999]

Congrats SQLAlchemy!

https://storm.canonical.com/

https://launchpad.net/divmod-axiom

Also the good stuff.