Re: Proposal: use SQLAlchemy Core for query generation

[Asif Saifuddin]

Hi Luke,

I have been thinking and researching a lot recently about SQLalchemy and 
django integration.

Now SQLalchemy provides a devlarative orm extension too quite identical to 
django orm[not same], django has also refactored a lot in db internals with 
model meta refactore, expressions, new migrations and many stuff.

There would be 2 new approach for your proposal--

1) Probably the people used to/willing to use sqlalchemy's declarative orm 
extension will use that and there will be needed to create new model meta 
API driven abstraction layer which will help to show sqlalchemy generated 
models in django admin and django form/model form.
  * migrations would be handled by alaembic/sqlalchemy-migrate/whatever 
best available

2) Django Model ORM will be  rewritten in a 3rd party project as a parallel 
counterpart/equivalent to SQLalchemy's declarative ORM extension which will 
work like django model//migrations/command based normal djangoish workflow 
but under the hood will be used by sqlalchemy core/3rd party tools.

I'm willing to address the both issue in a solid 3rd party package. In both 
case propably django internal too needed to be ironed for edge cases.

I'm trying to tracking this thoughts in a git repo and also looked for some 
older attempts to understand the implementation way.

Looking forward to hear your thoughts.


Regards,

Asif



On Saturday, June 30, 2012 at 8:22:21 PM UTC+6, Luke Plant wrote:
>
> Hi all, 
>
> A good while back I put forward the idea of using SQLAlchemy Core in 
> Django [1]. Having had more experience working with SQLAlchemy, I'm 
> putting that idea forward as a formal proposal, as I mentioned in a more 
> recent thread here. 
>
> Apologies in advance for the length! I've included a few 'TL;DR' 
> summaries and headings in the different sections which you might want to 
> scan first. 
>
> === Proposal === 
>
> We should re-write our query generation code to use SQLAlchemy Core. 
> This includes DDL queries as well as DML (e.g. CREATE TABLE as well as 
> SELECT, INSERT etc). 
>
> This would also involve replacing our database connection objects with 
> SQLAlchemy's. In this proposal, our high-level ORM, with model 
> definition and query API, would remain the same - we wouldn't be using 
> the SQLAlchemy ORM. 
>
> This is a "Django 2.0" proposal i.e. not immediate future, and not fully 
> backwards compatible. 
>
> === Background - Django 2.0 philosophy === 
>
> TL;DR: "evolution not revolution, some backwards incompatibilities" 
>
> Although we haven't really discussed the timing of Django 2.0, or its 
> philosophy, I think we should be doing. My own assumption is that it 
> should be evolution, not revolution, similar to Python 3, where we break 
> stuff that has dogged us in the past, and will hamper us going forward, 
> but don't make radical changes where not necessary. This proposal fits 
> that basic assumption. 
>
> Also, in Django to date we've eschewed external dependencies. That has 
> been partly due to the poor and confusing state of Python packaging, 
> which is hopefully improving. I think it will make us a very poor Python 
> citizen if we don't reverse this policy at some point, and Django 2.0 is 
> an obvious point to do it. 
>
> This proposal does not represent a move away from being a 'full stack' 
> framework. "Full stack" does not mean "no dependencies". 
>
> Our current recommended installation method is via pip [2], and we would 
> be doing our users a disservice to recommend otherwise. Installation via 
> pip actually makes the instructions shorter - manual methods require 
> things like removing old versions manually - and for anything beyond 
> trivial use of Django you have to know pip anyway. 
>
> So, with our recommended installation method, adding a dependency 
> doesn't make things any more difficult at all. 
>
> === Background - ORM philosophy === 
>
> TL;DR: "Let's make Django's DB layer the best it can be for relational 
> databases." 
>
> Whether we call it "the ORM" or "the model layer" as some people prefer, 
> I think it's fairly certain that the overwhelming majority of our users 
> are using relational databases. 
>
> Many of the things that make Django a compelling choice, 
> including the admin and re-usable apps, either don't work or are of 
> little use if you are not using a relational database. 
>
> So my philosophy is that we should aim to provide a really excellent 
> ORM that will take users as far as possible. 
>
> This doesn't preclude having non-relational support in Django. But 
> it seems very strange to make that the focus, when we've had 
> little-to-no support for it so far, or to allow that support to limit 
> how well we can cater for the 99%. 
>
> === Motivation === 
>
> == Motivation 1: Django's ORM leaves you high and dry when you reach its 
> limits. 
>
> While the ORM can do a surprising number of queries, there are plenty it 
> can't, and in all the medium-to-large projects I've worked on I've 

Re: Proposal: use SQLAlchemy Core for query generation

[Donald Stufft]

On Wednesday, December 26, 2012 at 10:56 PM, Russell Keith-Magee wrote:  
> That depends entirely on what you consider the goal of the ORM to be.
>  
> You have assumed that the goal would be "allow an arbitrary query to run on 
> any underlying data store, and run with equivalent efficiency". In this 
> model, you could take your fully operational Django PostgreSQL project, and 
> roll it out under MongoDB (or any other supported store), and it would 
> Magically Work™.   
>  
> I completely agree that this is a completely unrealistic goal, and would, as 
> you rightly point out, constitute a high-calibre footgun.  
That's what people will expect from it in large part. It may not be what's 
intended but it's what people will expect.  
>  
> However, there's another way of looking at it. You're focussing on the ORM as 
> a query generation engine. Of more interest is the ORM as a metadata layer 
> for models in a data store, with some basic reliable querying features.  
>  
> Think of it this way -- the goal isn't to allow an arbitrary query to run on 
> any data store. The goal is to allow Django's admin to operate on a model in 
> any data store, or to allow a Django ModelForm to retrieve and/or store an 
> object in any datastore.  
This can easily be done with 2 (or more) separate systems. There is 0 reason 
why the Admin and the ModelForms need to only realistically work within a 
single system. If your goal is to allow someone to use the admin, modelforms, 
or some other tool to interact with all of them that does _not_ require 
anything more than basic access then you can define an API that the admin 
expects and as long as your "Model" fits that API it can be used there. Then 
you can have one ORM for Relational databases that is able to use the full 
power of the ORM, one for each of the various "classification" of ORM (which 
don't even need to live in Django Core) and as long as they implement the 
interface that the Admin (for example) expect they will work out of the box.

A system like this removes the expectation from users that you can take a model 
written for a relational database and cram it into a NonRel (and the inverse), 
allows you to have "shared" consumers of the various implementations, but still 
have access to the full power of the underlying implementations.
> The queries required to support Django's admin and/or ModelForms are all 
> inherently simple CRUD operations -- operations that have simple (and for the 
> most part, efficient) analogs in every data store.  
>  

FWIW the Admin cannot even currently handle all database tables because of 
assumptions it makes about performance (so far I've seen it expects `SELECT 
COUNT(*) FROM ;` to return in a reasonable amount of time). Applying 
even greater diverging performance patterns to it is a harder than you're 
assuming especially if you take all of the features of the admin into account.

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Russell Keith-Magee]

On Thu, Dec 27, 2012 at 11:29 AM, Donald Stufft wrote:

> On Wednesday, December 26, 2012 at 10:00 PM, Russell Keith-Magee wrote:
>
> Why? Because we've gone to extraordinary lengths to make sure this sort of
> thing is at least theoretically possible.
>
> Although we use the term "ORM", and there's currently only relational
> implementations of Django's ORM, there's nothing relational about the
> Django ORM API. We've very deliberately posed the API in terms of functions
> you want to perform on objects:
>
>  * Get me the author named "Douglas Adams"
>  * Get a list of books that are more than 3 years old
>  * Update the login counter on this user by one.
>
>  Because except for very simple models you will not be able to sanely take
> a model written for a Relational database and switch it to a NonRelational
> database. If you cannot provide the same sort of mostly transparent
> switching like the ORM provides for MySQL -> PostgreSQL -> Oracle then
> there is little benefit in keeping it within the same system.
>
> All of your examples work on simple models sure. What about:
>
> * Anything requiring a join, explicitly with select_related() or
> implicitly with __ magic.
> * select_for_update
> * No standard way of handling "Related" fields (Do you Inline them?
> Mimic a ForeignKey?)
> * The entire transaction system on systems without transactions
>
> There is also the problem of vastly different access patterns,
> assumptions, and performance characteristics.
>
> * Getting a list of Books that are more than 3 year old is a very
> simple operation in SQL with very predictable performance, getting a list
> of books older than 3 years old if they are stored in Redis, less so.
> * Systems that depended on a unique=True enforcing a constraint of
> uniqueness no longer happening.
> * index=True becoming NO-OP.
> * A simple Join potentially goes from an inexpensive operation to one
> that requires traversing several million rows with horrible performance.
>
> The access patterns, assumptions of functionality, and assumption of
> performances are so different between even the different NoSQL solutions,
> much less the various NoSQL solutions and Relational databases that either
> you're going to have second class citizens (Sure you can use X system with
> Django models, but only as a competely segregated unit and you can't touch
> [a list of features]), or you're going to need to limit the features down
> to a subset that all databases can support (We already have this problem
> with PostgreSQL vs MySQL vs SQLite, it will be tenfold if we include NoSQL
> databases). In order to actually use the power of your datastore you need
> to use a class of "ORM" that is designed to work within it's access
> patterns.
>
> Django as a whole should be avoiding giving people footguns, and
> attempting to shove NonRelational databases into the ORM will be providing
> a massive footgun. As soon as it happens you'll have a whole host of people
> attempting to run apps and sites that depended on things that relational
> databases assured suddenly having it yanked out from underneath them and it
> will be Django's fault for providing that footgun.
>

That depends entirely on what you consider the goal of the ORM to be.

You have assumed that the goal would be "allow an arbitrary query to run on
any underlying data store, and run with equivalent efficiency". In this
model, you could take your fully operational Django PostgreSQL project, and
roll it out under MongoDB (or any other supported store), and it would
Magically Work™.

I completely agree that this is a completely unrealistic goal, and would,
as you rightly point out, constitute a high-calibre footgun.

However, there's another way of looking at it. You're focussing on the ORM
as a query generation engine. Of more interest is the ORM as a metadata
layer for models in a data store, with some basic reliable querying
features.

Think of it this way -- the goal isn't to allow an arbitrary query to run
on any data store. The goal is to allow Django's admin to operate on a
model in any data store, or to allow a Django ModelForm to retrieve and/or
store an object in any datastore.

The queries required to support Django's admin and/or ModelForms are all
inherently simple CRUD operations -- operations that have simple (and for
the most part, efficient) analogs in every data store.

Any non-trivial query will *always* require an understanding of the
underlying data storage. The ORM is an abstraction, and while it can make
certain queries easier to write, you can't use it in a vacuum -- you have
to be aware of the SQL that is being generated. And sometimes, you need to
fall back to raw SQL to get the job done.

I don't see a non-relational backend to Django's ORM being any different.
We can make simple retrieval operations easy. But there's no way we can
automatically optimise queries for every possible data store -- at some

Re: Proposal: use SQLAlchemy Core for query generation

[Donald Stufft]

On Wednesday, December 26, 2012 at 10:00 PM, Russell Keith-Magee wrote:
> Why? Because we've gone to extraordinary lengths to make sure this sort of 
> thing is at least theoretically possible.
> 
> Although we use the term "ORM", and there's currently only relational 
> implementations of Django's ORM, there's nothing relational about the Django 
> ORM API. We've very deliberately posed the API in terms of functions you want 
> to perform on objects:
> 
>  * Get me the author named "Douglas Adams"
>  * Get a list of books that are more than 3 years old
>  * Update the login counter on this user by one.

Because except for very simple models you will not be able to sanely take a 
model written for a Relational database and switch it to a NonRelational 
database. If you cannot provide the same sort of mostly transparent switching 
like the ORM provides for MySQL -> PostgreSQL -> Oracle then there is little 
benefit in keeping it within the same system. 

All of your examples work on simple models sure. What about:

* Anything requiring a join, explicitly with select_related() or implicitly 
with __ magic.
* select_for_update
* No standard way of handling "Related" fields (Do you Inline them? Mimic a 
ForeignKey?)
* The entire transaction system on systems without transactions

There is also the problem of vastly different access patterns, assumptions, and 
performance characteristics.

* Getting a list of Books that are more than 3 year old is a very simple 
operation in SQL with very predictable performance, getting a list of books 
older than 3 years old if they are stored in Redis, less so.
* Systems that depended on a unique=True enforcing a constraint of 
uniqueness no longer happening.
* index=True becoming NO-OP.
* A simple Join potentially goes from an inexpensive operation to one that 
requires traversing several million rows with horrible performance.

The access patterns, assumptions of functionality, and assumption of 
performances are so different between even the different NoSQL solutions, much 
less the various NoSQL solutions and Relational databases that either you're 
going to have second class citizens (Sure you can use X system with Django 
models, but only as a competely segregated unit and you can't touch [a list of 
features]), or you're going to need to limit the features down to a subset that 
all databases can support (We already have this problem with PostgreSQL vs 
MySQL vs SQLite, it will be tenfold if we include NoSQL databases). In order to 
actually use the power of your datastore you need to use a class of "ORM" that 
is designed to work within it's access patterns.

Django as a whole should be avoiding giving people footguns, and attempting to 
shove NonRelational databases into the ORM will be providing a massive footgun. 
As soon as it happens you'll have a whole host of people attempting to run apps 
and sites that depended on things that relational databases assured suddenly 
having it yanked out from underneath them and it will be Django's fault for 
providing that footgun.


-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Russell Keith-Magee]

On Mon, Dec 24, 2012 at 7:10 AM, Donald Stufft wrote:

> On Sunday, December 23, 2012 at 4:08 PM, Florent Gallaire wrote:
>
> Django ORM should work for SQL and NoSQL DBMS.
> NoSQL integration in Django is a more interesting and needed subject,
> but who cares about that in the core team ?
>
> Why should the Django Object Relational Mapper be modified to
> work with non relational databases? Trying to shoe horn random NoSQL
> into the Django ORM feels like a bad idea.
>
> Why? Because we've gone to extraordinary lengths to make sure this sort of
thing is at least theoretically possible.

Although we use the term "ORM", and there's currently only relational
implementations of Django's ORM, there's nothing relational about the
Django ORM API. We've very deliberately posed the API in terms of functions
you want to perform on objects:

 * Get me the author named "Douglas Adams"
 * Get a list of books that are more than 3 years old
 * Update the login counter on this user by one.

and so on. Although a relational store can satisfy these queries, there's
nothing inherently relational about the queries that are issued; in theory,
a non-relational store would be just as capable of answering them, and
there's no high-level conceptual reason that Django's admin, or ModelForms,
or any of the other Django tools that leverage the model metadata couldn't
be backed by a non-relational data store.

The only exceptions to this are raw queries, which are, as the name
suggest, raw, and extra() clauses, which the docs make clear are pretty
close to raw queries anyway. If a non-relational store were to be added to
Django, it wouldn't be unreasonable to suggest that analogs of 'raw' and
'extra' would exist.

The bigger question is who cares about this in the core team.

A few years back, when the excitement about non-relational stores was
growing, there was a lot more interest in the core team. There was a Summer
of Code project aimed at modifying the ORM to allow the development of
third-party non-relational backends. A proof of concept MongoDB backend was
developed as part of this effort.

The code from this SoC is still available -- although it hasn't been kept
up to date with trunk. The changes were relatively minor (and only to
internal APIs) -- however, there were some major design problems. In
particular, Django's internals make some assumptions about the fact that
AutoFields (the default type for primary keys) are always integers, which
doesn't hold for many non-relational stores. If you search the django-dev
archives, you'll find further discussions on the points that needed design
decisions.

These days, I'm seeing less interest in non-relational stores from the core
team. I'm not saying that non-relational stores are unimportant -- just
that nobody in the core team has an itch that currently requires an
ORM-level wrapper around a relational store.

That said: if a clean, easy to understand (or well explained) patch were to
magically appear that resolved all the design issues in a palatable way,
I'd guess that someone in the core team could probably be convinced to take
the time to review and commit it. Alternatively, if someone were to get the
SoC patch up to date, and provide a good analysis of the problems and
potential solutions, they would be able to get the attention of the core
team to provide a design decisions.

Yours,
Russ Magee %-)

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Donald Stufft]

On Sunday, December 23, 2012 at 4:08 PM, Florent Gallaire wrote: 
> Django ORM should work for SQL and NoSQL DBMS.
> NoSQL integration in Django is a more interesting and needed subject,
> but who cares about that in the core team ?
> 
> 

Why should the Django Object Relational Mapper be modified to
work with non relational databases? Trying to shoe horn random NoSQL
into the Django ORM feels like a bad idea.  

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Hannu Krosing]

On 12/23/2012 10:08 PM, Florent Gallaire wrote:

SQLA is, as is its name indicates, a SQL ORM.
Django ORM should work for SQL and NoSQL DBMS.

Why? Because of name ?

NoSQL integration in Django is a more interesting and needed subject,
but who cares about that in the core team ?

There is no single NoSQL to integrate.

NoSQL is either simple key/value store or has a different
(arguably also Structured) Query Language with similar but
often limited functionality.

Most major NoSQL databases for which it makes sense have at
least some form of Django integration.

Hannu



-1

Cheers, Florent.

On Tue, Dec 4, 2012 at 4:19 AM, Ivan  wrote:

вторник, 4 декабря 2012 г., 0:48:04 UTC+2 пользователь Ivan написал:

Allows to use both, Django-ORM + SQLAlchemy. Single connection.
SQLAlchemy's models are generated automatically form Django's models.
https://github.com/Deepwalker/aldjemy


Sorry for bad link. This is the correct link:
https://github.com/Deepwalker/aldjemy

--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/django-developers/-/L0B7gjhBTc8J.

To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en.





--
You received this message because you are subscribed to the Google Groups "Django 
developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Florent Gallaire]

SQLA is, as is its name indicates, a SQL ORM.
Django ORM should work for SQL and NoSQL DBMS.
NoSQL integration in Django is a more interesting and needed subject,
but who cares about that in the core team ?

-1

Cheers, Florent.

On Tue, Dec 4, 2012 at 4:19 AM, Ivan  wrote:
> вторник, 4 декабря 2012 г., 0:48:04 UTC+2 пользователь Ivan написал:
>>
>> Allows to use both, Django-ORM + SQLAlchemy. Single connection.
>> SQLAlchemy's models are generated automatically form Django's models.
>> https://github.com/Deepwalker/aldjemy
>
>
> Sorry for bad link. This is the correct link:
> https://github.com/Deepwalker/aldjemy
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/django-developers/-/L0B7gjhBTc8J.
>
> To post to this group, send email to django-developers@googlegroups.com.
> To unsubscribe from this group, send email to
> django-developers+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-developers?hl=en.



-- 
FLOSS Engineer & Lawyer

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Ivan]

вторник, 4 декабря 2012 г., 0:48:04 UTC+2 пользователь Ivan написал:
>
>
>1. Allows to use both, Django-ORM + SQLAlchemy. Single connection. 
>SQLAlchemy's models are generated automatically form Django's models. 
>https://github.com/Deepwalker/aldjemy
>
>
Sorry for bad link. This is the correct link: 
https://github.com/Deepwalker/aldjemy 

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-developers/-/L0B7gjhBTc8J.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Ivan]

Hi all,
I've viewed a few interesting solutions.


   1. Allows to use both, Django-ORM + SQLAlchemy. Single connection. 
   SQLAlchemy's models are generated automatically form Django's models. 
   https://github.com/Deepwalker/aldjemy
   2. Uses only Django's ORM, but allows to use 4 SQLBuilders together 
   https://bitbucket.org/evotech/sqlbuilder :
  1. Dajngo QuerySet
  2. Lightweight "smartsql" SQLBuilder from this library.
  3. SQLBuilder from SQLObject.
  4. SQLBuilder from SGQLAlchemy.
   
Items 3 and 4 are not as well supported as item 2.

So, It is not a problem to use Django with other SQL builders. Django 
QuerySet syntax is really very simple, and convenient for > 85% SQL 
queries. For others, can be used external SQLBuilders.

Sorry for my English.

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-developers/-/WYkvuZCL2jMJ.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Ivan]

See also syntax evolution of peewee ORM, which initially was Django's 
lightweight clone. 
http://peewee.readthedocs.org/en/latest/peewee/upgrading.html#goals-for-the-new-api

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-developers/-/_uk05lcy7OoJ.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[milosh]

On Saturday, June 30, 2012 4:22:21 PM UTC+2, Luke Plant wrote:
>
>
> D) Write a patch to extend the ORM. 
>
> D) This is basically too hard for most of us, including core developers. 
> One reason is that the the code involved is not in the best shape. (More 
> below. This might change if Anssi's work succeeds, but I still have big 
> doubts about how far we can push our ORM). 
>
>
>From my point of view it is much easier to hack / patch / optimize / test / 
debug the Django ORM with no dependencies on SQLA.

I'm also using django.contrib.gis extensively in my projects, I doubt that 
the GIS Django ORM is something that could be just "somehow" replaced.

-1 for the whole idea

--
Milos


>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-developers/-/JYVvhsa9lNIJ.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[megaman821]

On Tuesday, July 3, 2012 7:59:26 AM UTC-5, Chris Wilson wrote:
>
> Hi Reinout, 
>
> On Tue, 3 Jul 2012, Reinout van Rees wrote: 
>
> > On 30-06-12 16:22, Luke Plant wrote: 
> >> Also, in Django to date we've eschewed external dependencies. That has 
> >> been partly due to the poor and confusing state of Python packaging, 
> >> which is hopefully improving. 
> > [snip] 
> >> Our current recommended installation method is via pip [2], and we 
> would 
> >> be doing our users a disservice to recommend otherwise. 
> > 
> > Slight warning: everyone recommends pip, but watch out with windows. Pip 
> > doesn't support binary eggs (which easy_install does), so all those 
> python 
> > database drivers will need to be compiled. 
>
> Good point, thanks. 
>
> > And virtualenv/pip defaults to a nicely isolated virtualenv, so the 
> > clicky-clicky-install python database drivers (and other binary eggs) 
> > won't get used. 
> > 
> > So: pip sounds great right until you need binaries on windows. 
>
> The virtualenv inherits system-wide packages. So you can install whatever 
> binary packages you need system-wide, either using easy_install or an MSI, 
> and pip won't try (and fail) to install them again unless they don't meet 
> the version requirements of the pip manifest. 
>
> Cheers, Chris. 
> -- 
> Aptivate | http://www.aptivate.org | Phone: +44 1223 967 838 
> Future Business, Cam City FC, Milton Rd, Cambridge, CB4 1UY, UK 
>
> Aptivate is a not-for-profit company registered in England and Wales 
> with company number 04980791. 
>
>
On Windows, if you have an exe built with distutils (most are) it is very 
simple to install in a virtual environment. From inside a virtual 
environment you can do *easy_install C:\path\to\binary_package.exe*.

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-developers/-/d9zjbJHBHfIJ.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Michael Manfre]

On Tuesday, July 3, 2012 9:42:28 AM UTC-4, Luke Granger-Brown wrote:
>
> In new versions of virtualenv the default has changed to 
> --no-site-packages, at least on Linux. Is this not also the case for 
> Windows? 
>
> Luke
>
> Unfortunately, it is the same for windows. Every python project that I run 
on windows has PIL or pywin32 as a dependency, which requires me to 
remember to add "--system-site-packages", else it breaks (and not always in 
a way that is detected immediately).

--
Michael Manfre

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-developers/-/kgVhijIl39IJ.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Luke Granger-Brown]

On Jul 3, 2012 1:59 PM, "Chris Wilson"  wrote:
>
> Hi Reinout,
>
>
> On Tue, 3 Jul 2012, Reinout van Rees wrote:
>
>> On 30-06-12 16:22, Luke Plant wrote:
>>>
>>> Also, in Django to date we've eschewed external dependencies. That has
>>> been partly due to the poor and confusing state of Python packaging,
>>> which is hopefully improving.
>>
>> [snip]
>>>
>>> Our current recommended installation method is via pip [2], and we would
>>> be doing our users a disservice to recommend otherwise.
>>
>>
>> Slight warning: everyone recommends pip, but watch out with windows. Pip
doesn't support binary eggs (which easy_install does), so all those python
database drivers will need to be compiled.
>
>
> Good point, thanks.
>
>
>> And virtualenv/pip defaults to a nicely isolated virtualenv, so the
clicky-clicky-install python database drivers (and other binary eggs) won't
get used.
>>
>> So: pip sounds great right until you need binaries on windows.
>
>
> The virtualenv inherits system-wide packages. So you can install whatever
binary packages you need system-wide, either using easy_install or an MSI,
and pip won't try (and fail) to install them again unless they don't meet
the version requirements of the pip manifest.

In new versions of virtualenv the default has changed to
--no-site-packages, at least on Linux. Is this not also the case for
Windows?

Luke

>
> Cheers, Chris.
> --
> Aptivate | http://www.aptivate.org | Phone: +44 1223 967 838
> Future Business, Cam City FC, Milton Rd, Cambridge, CB4 1UY, UK
>
> Aptivate is a not-for-profit company registered in England and Wales
> with company number 04980791.
>
>
> --
> You received this message because you are subscribed to the Google Groups
"Django developers" group.
> To post to this group, send email to django-developers@googlegroups.com.
> To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en.
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Chris Wilson]

Hi Reinout,

On Tue, 3 Jul 2012, Reinout van Rees wrote:


On 30-06-12 16:22, Luke Plant wrote:

Also, in Django to date we've eschewed external dependencies. That has
been partly due to the poor and confusing state of Python packaging,
which is hopefully improving.

[snip]

Our current recommended installation method is via pip [2], and we would
be doing our users a disservice to recommend otherwise.


Slight warning: everyone recommends pip, but watch out with windows. Pip 
doesn't support binary eggs (which easy_install does), so all those python 
database drivers will need to be compiled.


Good point, thanks.

And virtualenv/pip defaults to a nicely isolated virtualenv, so the 
clicky-clicky-install python database drivers (and other binary eggs) 
won't get used.


So: pip sounds great right until you need binaries on windows.


The virtualenv inherits system-wide packages. So you can install whatever 
binary packages you need system-wide, either using easy_install or an MSI, 
and pip won't try (and fail) to install them again unless they don't meet 
the version requirements of the pip manifest.


Cheers, Chris.
--
Aptivate | http://www.aptivate.org | Phone: +44 1223 967 838
Future Business, Cam City FC, Milton Rd, Cambridge, CB4 1UY, UK

Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.

--
You received this message because you are subscribed to the Google Groups "Django 
developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Reinout van Rees]

On 30-06-12 16:22, Luke Plant wrote:

Also, in Django to date we've eschewed external dependencies. That has
been partly due to the poor and confusing state of Python packaging,
which is hopefully improving.

[snip]

Our current recommended installation method is via pip [2], and we would
be doing our users a disservice to recommend otherwise.


Slight warning: everyone recommends pip, but watch out with windows. Pip 
doesn't support binary eggs (which easy_install does), so all those 
python database drivers will need to be compiled.


And virtualenv/pip defaults to a nicely isolated virtualenv, so the 
clicky-clicky-install python database drivers (and other binary eggs) 
won't get used.



So: pip sounds great right until you need binaries on windows.


Reinout

--
Reinout van Reeshttp://reinout.vanrees.org/
rein...@vanrees.org http://www.nelen-schuurmans.nl/
"If you're not sure what to do, make something. -- Paul Graham"



--
You received this message because you are subscribed to the Google Groups "Django 
developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[zzzeek]

On Jul 2, 5:57 am, Anssi Kääriäinen  wrote:
> In step 1 Django constantly keeps the correct join information and
> filtering conditions available. These are ready to be converted into
> SQL on the spot. In addition, some information is kept available, but
> it isn't processed yet. For example: select_related, order_by etc.
> These are just lists of what is wanted, but these are not turned into
> joins yet. It is notable that the join information can be changed on
> the fly: some operation might turn some existing inner joins to outer
> joins for example. Here the main part of complexity is in deciding
> what kind of joins to use, or if a subquery is needed.

The logic that Django has for querying in this regard would remain
intact.

>
> In step 2 things like needed GROUP BY clauses is calculated. In
> particular, the order_by, select_related and only()/defer() structures
> are calculated. Here the most complex part is calculating which joins
> to use for select_related descent. In addition there are some details
> and dependencies which make this somewhat complex (select cols must be
> added to group by - but some backends do not allow using select
> aliases in group by, and some backends prefer doing group by only by
> table pk).

While there is a lot of logic in Django's query here that probably
needs to remain in some form, the SQLAlchemy ORM does do a lot of this
part, if I understand select_related() correctly, via its eager
loading via joins and subqueries capabilities.  Joined eager loading
does do the "convert from JOIN to OUTERJOIN if an ancestor is an
OUTERJOIN" thing, as well as "wrap the whole query in a subquery and
apply the joins to that if the query has limit/offset applied".
Eager loading is where SQLAlchemy does do a lot of these "automatic"
things (though probably not nearly as many as Django).  A port of
the Django ORM to that of SQLAlchemy would certainly want to get at
SQLAlchemy's collection loading facilities as SQLA's collections are
associated in memory with parent objects and have very different
behavior from collections that load each time they are accessed (we
call this latter type of collection a "dynamic" collection).

However, that all assumes the proposal to use the SQLAlchemy ORM as
the core for Django's which even I think is a little ambitious.  I do
have a notion of how an *alternate* Django ORM compatibility layer
could be provided but I think it would require expectation of
significant behavioral differences - it would mostly be a way for an
existing application build on Django models and QuerySet usage to port
to a SQLAlchemy ORM paradigm without too many changes.   But I don't
know that you could just hoist those paradigm shifts onto everyone at
once without a lot of problems, and its certainly not what a lot of
people would want.

As far as Luke's proposal to use just SQLAlchemy Core, all of #1 and
#2 is still handled 100% by django's query system.

> Finally, in step 3 the SQL is generated. This is dirty as different
> backends have endless amount of little differences in their take of
> what is legal SQL.

this is of course the area where both Django and SQLAlchemy core have
tremendous overlap and I'm confident SQLA's expression language can do
whatever is needed here in a straightforward way.

>
> I don't see SQLA helping much in step 1 - it just doesn't do what
> Django does. It might help somewhat in step 2. It would definitely
> help in step 3.
>
> My understanding is that SQLA and Django's ORM work on different
> levels. I do support trying to use SQLA. This is just a warning that
> one should not expect to replace everything or even most of the code
> under django/db/models/sql and django/db/backends by SQLA.

totally agree, the usage paradigm of Django's ORM is very different
from that of SQLAlchemy's.

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[zzzeek]

On Jul 1, 12:53 pm, Andrew Godwin  wrote:
>
> Ah, interesting. Alembic doesn't appear to support fully mutating SQLite
> databases, which is the really gnarly part of the South code I'd love to
> outsource. Looks like there's still plenty of work to be done no matter
> what happens.

While I'm quite annoyed at the SQLite project itself for not
supporting ALTER in a comprehensive way, especially given in SQLite4
they will have constraints enabled by default (note this makes
SQLite's notion of rename->drop->recreate-copy impossible in many
cases) I have accepted the feature request to add the "rename->drop-
>recreate->copy" system into Alembic's SQLite module.  It's already
been done in SQLAlchemy-migrate so this is just one of those areas
where we need a willing volunteer to help. Alembic contains primitives
for all the DDL stuff otherwise, building on SQLAlchemy's extensible
DDL/SQL construct system.

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Anssi Kääriäinen]

On 1 heinä, 00:11, Luke Plant  wrote:
> On 30/06/12 20:23, Anssi Kääriäinen wrote:
>
> > TL;DR ;) But I intend to do so shortly.
>
> > For now I have to comment on the "clear up sql/query.py" part. I am
> > doubtful if moving to SQLAlchemy will solve the sql/query.py and sql/
> > compiler.py complexities.
>
> > I have little knowledge of SQLAlchemy, but my understanding is that it
> > does not do things like "when filtering on negated multijoin
> > condition, push the condition to subquery", automatic grouping by the
> > right set of columns or handle what kind of joins are needed by select
> > related. It probably doesn't do join promotion ("should this join be
> > INNER or LEFT OUTER join, if dealing with an ORed where condition,
> > then LEFT OUTER, and so on). The sql/query.py deals mostly with these
> > kinds of problems. The sql/compiler.py also deals in part with
> > problems like this.
>
> Mike Bayer pointed me to this code, which does something like Django's
> join syntax on top of SQLAlchemy:
>
> https://github.com/mitsuhiko/sqlalchemy-django-query/blob/master/sqla...
>
> I really don't know enough to know how well it is approximating what
> Django does. It would be surprising if it was really doing the same thing!
>
> I think it's one of those things where we really won't know the impact
> until we've got most of the way there, and even then differences of
> approach could make all the difference.

The SQLA ORM doesn't automatically generate subqueries or outer joins.
The sqlalchemy-django-query uses only .join() so on that basis I am
pretty sure it doesn't do what Django does.

The complexity of Django's ORM is in large part in determining the
type of joins, handling nullable fields correctly, doing automatically
subqueries where needed and deciding what parts of your filter clause
need to go into HAVING, what parts into WHERE clause. This is also the
biggest strength of Django's ORM: the ORM generates the SQL needed,
you don't have to care if subqueries or left joins are needed or not.
You don't even have to know what a HAVING clause is.

I have now read the full proposal, and I have a feeling there is an
expectation that SQLA can do more for us that it in reality can. I
don't see anything in SQLA that is capable of replacing sql/query.py.
There isn't anything that is capable of replacing all of sql/
compiler.py.

The current query generation can be separated to roughly three parts:
 1. Doing operations on the query (sql/query.py).
 2. Doing pre-execution stuff. (sql/compiler.py)
 3. Generating the actual SQL. (sql/compiler.py, backends).

In step 1 Django constantly keeps the correct join information and
filtering conditions available. These are ready to be converted into
SQL on the spot. In addition, some information is kept available, but
it isn't processed yet. For example: select_related, order_by etc.
These are just lists of what is wanted, but these are not turned into
joins yet. It is notable that the join information can be changed on
the fly: some operation might turn some existing inner joins to outer
joins for example. Here the main part of complexity is in deciding
what kind of joins to use, or if a subquery is needed.

In step 2 things like needed GROUP BY clauses is calculated. In
particular, the order_by, select_related and only()/defer() structures
are calculated. Here the most complex part is calculating which joins
to use for select_related descent. In addition there are some details
and dependencies which make this somewhat complex (select cols must be
added to group by - but some backends do not allow using select
aliases in group by, and some backends prefer doing group by only by
table pk).

Finally, in step 3 the SQL is generated. This is dirty as different
backends have endless amount of little differences in their take of
what is legal SQL.

In practice, steps 2 and 3 are mostly interleaved currently. It might
be a good idea to separate them as much as possible.

I don't see SQLA helping much in step 1 - it just doesn't do what
Django does. It might help somewhat in step 2. It would definitely
help in step 3.

My understanding is that SQLA and Django's ORM work on different
levels. I do support trying to use SQLA. This is just a warning that
one should not expect to replace everything or even most of the code
under django/db/models/sql and django/db/backends by SQLA.

 - Anssi

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Harro]

Ehhm,

version numbers aren't decimal numers. 2.0 doesn't have to wait for 1.9.

Maybe even drop python 2 for django 2.0?

Harro

On Saturday, 30 June 2012 21:25:07 UTC+2, Jacob Kaplan-Moss wrote:
>
> Wow. There's really a lot to think about here, and I'm only just 
> starting. Thanks for putting this together, Luke: I know it's been 
> something that's been discussed a ton, but until now nobody's really 
> done the due diligence to figure out exactly what the process and 
> ramifications would be. 
>
> Before we do get too deep into this, however, I want to talk about 
> this "Django 2.0" thing: 
>
> Clearly there will be something called "Django 2.0" at some point -- 
> after 1.9, if we get there, comes 2.0. However, I think it would be a 
> mistake to make "Django 2.0" backwards-incompatible. We've seen 
> countless examples -- Perl 6, Python 3, Rails 3, ... -- that these 
> sorts of "breaks from the past" really alienate and frustrate the 
> community. Over the years we've actually gotten really good at 
> balancing forward motion with stability. Our reputation as a stable, 
> reliable platform is something I'm intensely proud of. 
>
> It's going to take a lot of work to convince me of the value of a 
> "break from the past" sort of approach. If this can't be done in a way 
> that promises a smooth upgrade path... I'm not sure it's worth doing. 
>
> Now, that's not a vote against (at least not yet); I think we can find 
> balance here. I'm certainly not arguing that any backwards 
> incompatibilities sink the proposal. There's a certain level of 
> incompatibility that'll be OK, especially when the upside's so great. 
> External dependencies? If the ecosystem's ready (and it's getting 
> there), then we can adopt them without affecting most users. Changed 
> internals? We've already been pretty clear that the internals of the 
> model system is off-limits, and I think we can tolerate some changes 
> there. 
>
> So: if we're going to go down this path -- and your reasons for why we 
> should are spot-on -- I say we have to figure out if we can minimize 
> the upgrade path. 
>
> Jacob 
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-developers/-/qlGNeeZ9x94J.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Andrew Godwin]

> I am pretty sure SQLAlchemy-Migrate has fallen out of favor. You should
> check Alembic, http://alembic.readthedocs.org/en/latest/. 

Ah, interesting. Alembic doesn't appear to support fully mutating SQLite
databases, which is the really gnarly part of the South code I'd love to
outsource. Looks like there's still plenty of work to be done no matter
what happens.

Andrew

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[megaman821]

On Sunday, July 1, 2012 6:11:34 AM UTC-5, Andrew Godwin wrote:
>
> Nice to see this after you mentioned it a couple of weeks ago, Luke! I'm 
> very slightly in favour of the whole idea, but I'm skeptical about the 
> amount of work this will require in the DB backend, and what that means 
> for the ORM's large number of little tricks that people are used to 
> using. It's going to be a big change for them. 
>
> One area I'm particularly interested in (naturally) is the DDL 
> generation stuff - I'm in the middle of writing new full DDL (i.e. 
> ALTER, DROP) backends for Django, and obviously that comes with its own 
> set of DB-specific changes and workarounds. 
>
> It looks like SQLAlchemy Core would help that aspect, but only slightly 
> - most of the code is in mapping operations on models to operations on 
> tables, and I don't think SQLAlchemy Core doesn't have any support for 
> mutating SQLite (for that we'd need to turn to SQLAlchemy-Migrate). 
>
> Basically, what I'm saying is that while I'd jump behind it if it would 
> save me this work in the short term, I don't think it will - otherwise, 
> I'd propose we start the gradual introduction of it in the new DDL 
> modules, and get the ball rolling. However, it looks like it would end 
> up resulting in far more work than sticking with what's there (mostly 
> due to having to fiddle with things like how fields specify types and 
> relationships and tables work). 
>
> Still, I'm +0 on the whole idea. 
>
> Andrew 
>
>
I am pretty sure SQLAlchemy-Migrate has fallen out of favor. You should 
check Alembic, http://alembic.readthedocs.org/en/latest/. 

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-developers/-/JW0hkvvcflgJ.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Andrew Godwin]

On 30/06/12 15:22, Luke Plant wrote:
> Hi all,
> 
> A good while back I put forward the idea of using SQLAlchemy Core in
> Django [1]. Having had more experience working with SQLAlchemy, I'm
> putting that idea forward as a formal proposal, as I mentioned in a more
> recent thread here.
> 
> Apologies in advance for the length! I've included a few 'TL;DR'
> summaries and headings in the different sections which you might want to
> scan first.
> 
> === Proposal ===
> (snip)

Nice to see this after you mentioned it a couple of weeks ago, Luke! I'm
very slightly in favour of the whole idea, but I'm skeptical about the
amount of work this will require in the DB backend, and what that means
for the ORM's large number of little tricks that people are used to
using. It's going to be a big change for them.

One area I'm particularly interested in (naturally) is the DDL
generation stuff - I'm in the middle of writing new full DDL (i.e.
ALTER, DROP) backends for Django, and obviously that comes with its own
set of DB-specific changes and workarounds.

It looks like SQLAlchemy Core would help that aspect, but only slightly
- most of the code is in mapping operations on models to operations on
tables, and I don't think SQLAlchemy Core doesn't have any support for
mutating SQLite (for that we'd need to turn to SQLAlchemy-Migrate).

Basically, what I'm saying is that while I'd jump behind it if it would
save me this work in the short term, I don't think it will - otherwise,
I'd propose we start the gradual introduction of it in the new DDL
modules, and get the ball rolling. However, it looks like it would end
up resulting in far more work than sticking with what's there (mostly
due to having to fiddle with things like how fields specify types and
relationships and tables work).

Still, I'm +0 on the whole idea.

Andrew


-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Paulo Scardine]

+1: I have my fair share of Django/SQLAlchemy frankensteins. It kind of 
works, but the resulting creature is ugly.

I've used Flask for some projects and I'm really impressed by the power of 
SQLAlchemy.

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-developers/-/SweL7f8fGt0J.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Luke Plant]

On 30/06/12 20:23, Anssi Kääriäinen wrote:

> TL;DR ;) But I intend to do so shortly.
> 
> For now I have to comment on the "clear up sql/query.py" part. I am
> doubtful if moving to SQLAlchemy will solve the sql/query.py and sql/
> compiler.py complexities.
> 
> I have little knowledge of SQLAlchemy, but my understanding is that it
> does not do things like "when filtering on negated multijoin
> condition, push the condition to subquery", automatic grouping by the
> right set of columns or handle what kind of joins are needed by select
> related. It probably doesn't do join promotion ("should this join be
> INNER or LEFT OUTER join, if dealing with an ORed where condition,
> then LEFT OUTER, and so on). The sql/query.py deals mostly with these
> kinds of problems. The sql/compiler.py also deals in part with
> problems like this.

Mike Bayer pointed me to this code, which does something like Django's
join syntax on top of SQLAlchemy:

https://github.com/mitsuhiko/sqlalchemy-django-query/blob/master/sqlalchemy_django_query.py

I really don't know enough to know how well it is approximating what
Django does. It would be surprising if it was really doing the same thing!

I think it's one of those things where we really won't know the impact
until we've got most of the way there, and even then differences of
approach could make all the difference.

Luke


-- 
Parenthetical remarks (however relevant) are unnecessary

Luke Plant || http://lukeplant.me.uk/

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Luke Plant]

On 30/06/12 20:25, Jacob Kaplan-Moss wrote:

> Before we do get too deep into this, however, I want to talk about
> this "Django 2.0" thing:
> 
> Clearly there will be something called "Django 2.0" at some point --
> after 1.9, if we get there, comes 2.0. However, I think it would be a
> mistake to make "Django 2.0" backwards-incompatible. We've seen
> countless examples -- Perl 6, Python 3, Rails 3, ... -- that these
> sorts of "breaks from the past" really alienate and frustrate the
> community. Over the years we've actually gotten really good at
> balancing forward motion with stability. Our reputation as a stable,
> reliable platform is something I'm intensely proud of.
> 
> It's going to take a lot of work to convince me of the value of a
> "break from the past" sort of approach. If this can't be done in a way
> that promises a smooth upgrade path... I'm not sure it's worth doing.

That's exactly the approach I had in making this proposal. The only
publicly documented API that I'm expecting to break or be removed is
QuerySet.extra(), and none of us like that anyway.

The internals I expect will break:

 - anything that relies on manipulating QuerySet.query (I've got one
project where I do that a little bit, I've not seen anyone else do it).

 - DB backend implementations that provide their own SQLCompiler
classes. Actually most external DB backends would probably break.

I'm *not* expecting the vast majority of Model._meta to change in big
ways, which is the biggest 'internal' that people regularly use. Since
it isn't to do with query generation, it doesn't need to change.

Of course, reality can have other ideas with big changes like this, but
that's what I would be aiming for.

For the things that will break, I hope they would be replaced by much
more appealing options - QuerySet.query would be replaced by the
QuerySet.as_sqlalchemy() method that Anssi mentioned, and you won't need
most 3rd party DB backends, or can plug them in via SQLAlchemy's
extension points, which are cleaner than ours from what I can see.

Luke

-- 
Parenthetical remarks (however relevant) are unnecessary

Luke Plant || http://lukeplant.me.uk/

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Anssi Kääriäinen]

On 30 kesä, 22:25, Jacob Kaplan-Moss  wrote:
> Before we do get too deep into this, however, I want to talk about
> this "Django 2.0" thing:
>
> Clearly there will be something called "Django 2.0" at some point --
> after 1.9, if we get there, comes 2.0. However, I think it would be a
> mistake to make "Django 2.0" backwards-incompatible. We've seen
> countless examples -- Perl 6, Python 3, Rails 3, ... -- that these
> sorts of "breaks from the past" really alienate and frustrate the
> community. Over the years we've actually gotten really good at
> balancing forward motion with stability. Our reputation as a stable,
> reliable platform is something I'm intensely proud of.
>
> It's going to take a lot of work to convince me of the value of a
> "break from the past" sort of approach. If this can't be done in a way
> that promises a smooth upgrade path... I'm not sure it's worth doing.

I am sure doing Django 2.0 would result in a _long_ development cycle.
Off my head some candidates for 2.0:
  - Template layer
  - ORM
  - Admin

Probably a lot of things in contrib, some things in model layer, I am
sure there are a couple of issues in forms and so on.

How long till we get every part upgraded to 2.0 and get the code
polished to release quality? -1 to finding that out :)

I wonder if it would be possible to support both a new version
(new_models?) and old version side by side. After long enough
deprecation period, kick the old code out of core, maybe into a
separate repository where interested people could pick it up if they
still need it.

 - Anssi

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: Proposal: use SQLAlchemy Core for query generation

[Jacob Kaplan-Moss]

Wow. There's really a lot to think about here, and I'm only just
starting. Thanks for putting this together, Luke: I know it's been
something that's been discussed a ton, but until now nobody's really
done the due diligence to figure out exactly what the process and
ramifications would be.

Before we do get too deep into this, however, I want to talk about
this "Django 2.0" thing:

Clearly there will be something called "Django 2.0" at some point --
after 1.9, if we get there, comes 2.0. However, I think it would be a
mistake to make "Django 2.0" backwards-incompatible. We've seen
countless examples -- Perl 6, Python 3, Rails 3, ... -- that these
sorts of "breaks from the past" really alienate and frustrate the
community. Over the years we've actually gotten really good at
balancing forward motion with stability. Our reputation as a stable,
reliable platform is something I'm intensely proud of.

It's going to take a lot of work to convince me of the value of a
"break from the past" sort of approach. If this can't be done in a way
that promises a smooth upgrade path... I'm not sure it's worth doing.

Now, that's not a vote against (at least not yet); I think we can find
balance here. I'm certainly not arguing that any backwards
incompatibilities sink the proposal. There's a certain level of
incompatibility that'll be OK, especially when the upside's so great.
External dependencies? If the ecosystem's ready (and it's getting
there), then we can adopt them without affecting most users. Changed
internals? We've already been pretty clear that the internals of the
model system is off-limits, and I think we can tolerate some changes
there.

So: if we're going to go down this path -- and your reasons for why we
should are spot-on -- I say we have to figure out if we can minimize
the upgrade path.

Jacob

On Sat, Jun 30, 2012 at 9:22 AM, Luke Plant  wrote:
> Hi all,
>
> A good while back I put forward the idea of using SQLAlchemy Core in
> Django [1]. Having had more experience working with SQLAlchemy, I'm
> putting that idea forward as a formal proposal, as I mentioned in a more
> recent thread here.
>
> Apologies in advance for the length! I've included a few 'TL;DR'
> summaries and headings in the different sections which you might want to
> scan first.
>
> === Proposal ===
>
> We should re-write our query generation code to use SQLAlchemy Core.
> This includes DDL queries as well as DML (e.g. CREATE TABLE as well as
> SELECT, INSERT etc).
>
> This would also involve replacing our database connection objects with
> SQLAlchemy's. In this proposal, our high-level ORM, with model
> definition and query API, would remain the same - we wouldn't be using
> the SQLAlchemy ORM.
>
> This is a "Django 2.0" proposal i.e. not immediate future, and not fully
> backwards compatible.
>
> === Background - Django 2.0 philosophy ===
>
> TL;DR: "evolution not revolution, some backwards incompatibilities"
>
> Although we haven't really discussed the timing of Django 2.0, or its
> philosophy, I think we should be doing. My own assumption is that it
> should be evolution, not revolution, similar to Python 3, where we break
> stuff that has dogged us in the past, and will hamper us going forward,
> but don't make radical changes where not necessary. This proposal fits
> that basic assumption.
>
> Also, in Django to date we've eschewed external dependencies. That has
> been partly due to the poor and confusing state of Python packaging,
> which is hopefully improving. I think it will make us a very poor Python
> citizen if we don't reverse this policy at some point, and Django 2.0 is
> an obvious point to do it.
>
> This proposal does not represent a move away from being a 'full stack'
> framework. "Full stack" does not mean "no dependencies".
>
> Our current recommended installation method is via pip [2], and we would
> be doing our users a disservice to recommend otherwise. Installation via
> pip actually makes the instructions shorter - manual methods require
> things like removing old versions manually - and for anything beyond
> trivial use of Django you have to know pip anyway.
>
> So, with our recommended installation method, adding a dependency
> doesn't make things any more difficult at all.
>
> === Background - ORM philosophy ===
>
> TL;DR: "Let's make Django's DB layer the best it can be for relational
> databases."
>
> Whether we call it "the ORM" or "the model layer" as some people prefer,
> I think it's fairly certain that the overwhelming majority of our users
> are using relational databases.
>
> Many of the things that make Django a compelling choice,
> including the admin and re-usable apps, either don't work or are of
> little use if you are not using a relational database.
>
> So my philosophy is that we should aim to provide a really excellent
> ORM that will take users as far as possible.
>
> This doesn't preclude having non-relational support in Django. But
> it seems very strange to make 

Re: Proposal: use SQLAlchemy Core for query generation

[Anssi Kääriäinen]

On 30 kesä, 17:22, Luke Plant  wrote:
> Hi all,
>
> A good while back I put forward the idea of using SQLAlchemy Core in
> Django [1]. Having had more experience working with SQLAlchemy, I'm
> putting that idea forward as a formal proposal, as I mentioned in a more
> recent thread here.
>
> Apologies in advance for the length! I've included a few 'TL;DR'
> summaries and headings in the different sections which you might want to
> scan first.

> == Motivation 3: Code cleanup
>
> As mentioned, our own query generation has been pushed to the limit and
> beyond. (I'm talking about classes like Query, SQLCompiler). It has
> grown and grown, so that the Query class is now a 2000 line behemoth,
> containing a constructor with over 40 assignments to 'self'.
>
> Most of the core developers are scared to touch this stuff, AFAICS,
> myself included. It has no virtually no unit tests. While it has very
> high test coverage, it is tested only by tests that check QuerySet and
> other high-level APIs.
>
> As such, it's very difficult to change, and it may well be beyond our
> ability to successfully refactor.
>
> Switching to SQLAlchemy would force us to rewrite this code, which is
> for our own good. In addition, large chunks of it can be dropped
> entirely (i.e. most of database specific stuff). This will reduce our
> maintenance load going forward (eventually).
>
> (BTW, I'm not saying that we should let the existing code continue to
> rot, we should of course try to clean it up as best we can, and that
> effort is not wasted - I'm talking about a longer term strategy here.
> If we can refactor this code, great - this motivation can be dropped
> from the list, but I think the others still stand).

TL;DR ;) But I intend to do so shortly.

For now I have to comment on the "clear up sql/query.py" part. I am
doubtful if moving to SQLAlchemy will solve the sql/query.py and sql/
compiler.py complexities.

I have little knowledge of SQLAlchemy, but my understanding is that it
does not do things like "when filtering on negated multijoin
condition, push the condition to subquery", automatic grouping by the
right set of columns or handle what kind of joins are needed by select
related. It probably doesn't do join promotion ("should this join be
INNER or LEFT OUTER join, if dealing with an ORed where condition,
then LEFT OUTER, and so on). The sql/query.py deals mostly with these
kinds of problems. The sql/compiler.py also deals in part with
problems like this.

What SQLAlchemy could do for us is generate the actual SQL. We would
probably never need to worry about things like how to generate proper
LIMIT queries for different backends, nor would we need to worry about
proper escaping etc. Hopefully we could get rid of most of the dirty
details of supporting different dialects of SQL. This and support for
more backends is reason enough to support this idea. DDL would be a
big bonus, too.

As said, I don't know SQLAlchemy well, so it might be it does more
things usable for our ORM. If so, even more reason to use it.

In addition, I would love if I could do first some operations using
Django's query API, then say "qs.as_sqlalchemy()" and then use
SQLAlchemy for those operations Django's ORM doesn't support.

So, count me in as +1 for this idea. I would not be surprised if it
turns out this will add complexity instead of reducing it. Still, to
me this seem promising.

 - Anssi

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Proposal: use SQLAlchemy Core for query generation

[Luke Plant]

Hi all,

A good while back I put forward the idea of using SQLAlchemy Core in
Django [1]. Having had more experience working with SQLAlchemy, I'm
putting that idea forward as a formal proposal, as I mentioned in a more
recent thread here.

Apologies in advance for the length! I've included a few 'TL;DR'
summaries and headings in the different sections which you might want to
scan first.

=== Proposal ===

We should re-write our query generation code to use SQLAlchemy Core.
This includes DDL queries as well as DML (e.g. CREATE TABLE as well as
SELECT, INSERT etc).

This would also involve replacing our database connection objects with
SQLAlchemy's. In this proposal, our high-level ORM, with model
definition and query API, would remain the same - we wouldn't be using
the SQLAlchemy ORM.

This is a "Django 2.0" proposal i.e. not immediate future, and not fully
backwards compatible.

=== Background - Django 2.0 philosophy ===

TL;DR: "evolution not revolution, some backwards incompatibilities"

Although we haven't really discussed the timing of Django 2.0, or its
philosophy, I think we should be doing. My own assumption is that it
should be evolution, not revolution, similar to Python 3, where we break
stuff that has dogged us in the past, and will hamper us going forward,
but don't make radical changes where not necessary. This proposal fits
that basic assumption.

Also, in Django to date we've eschewed external dependencies. That has
been partly due to the poor and confusing state of Python packaging,
which is hopefully improving. I think it will make us a very poor Python
citizen if we don't reverse this policy at some point, and Django 2.0 is
an obvious point to do it.

This proposal does not represent a move away from being a 'full stack'
framework. "Full stack" does not mean "no dependencies".

Our current recommended installation method is via pip [2], and we would
be doing our users a disservice to recommend otherwise. Installation via
pip actually makes the instructions shorter - manual methods require
things like removing old versions manually - and for anything beyond
trivial use of Django you have to know pip anyway.

So, with our recommended installation method, adding a dependency
doesn't make things any more difficult at all.

=== Background - ORM philosophy ===

TL;DR: "Let's make Django's DB layer the best it can be for relational
databases."

Whether we call it "the ORM" or "the model layer" as some people prefer,
I think it's fairly certain that the overwhelming majority of our users
are using relational databases.

Many of the things that make Django a compelling choice,
including the admin and re-usable apps, either don't work or are of
little use if you are not using a relational database.

So my philosophy is that we should aim to provide a really excellent
ORM that will take users as far as possible.

This doesn't preclude having non-relational support in Django. But
it seems very strange to make that the focus, when we've had
little-to-no support for it so far, or to allow that support to limit
how well we can cater for the 99%.

=== Motivation ===

== Motivation 1: Django's ORM leaves you high and dry when you reach its
limits.

While the ORM can do a surprising number of queries, there are plenty it
can't, and in all the medium-to-large projects I've worked on I've gone
beyond what the ORM can do.

At this point, you've got a few options, from easiest to hardest:

A) Do the aggregation/filtering etc in Python.

B) Write raw SQL.

C) Use SQLAlchemy or some other SQL generation tool.

D) Write a patch to extend the ORM.


None of these is great:

A) Data manipulation in Python, when it could be done in SQL, is
obviously a bad idea since it is usually very inefficient. But I've seen
a lot of code that does this, because it was hard/impossible to get
Django's ORM to do the query needed.

This anti-pattern will also give Django applications the reputation for
being slow. Obviously, we can point the finger at the developer, but if
we've made it hard for the developer to do the right thing, that is unfair.

B) Raw SQL fails if you have dynamic queries i.e. where the shape of the
query can vary.

Example 1: you are writing library code e.g. a re-usable app that knows
nothing about the tables it is actually querying, and may have been
given any arbitrary QuerySet as an input to manipulate in some way.

Example 2: even if you have full knowledge of the tables, you might have
additional WHERE clauses/JOINs/sub queries in some cases, that you want
to programmatically add to the query.

I've come across both these types in projects I've been involved in, and
I know I'm far from the only one.

Raw SQL can also fail if you are manually writing 'static' queries but
need compatibility with multiple DB backends.

C) For SQL generation, SQLAlchemy is the best, but for good reason it
comes with its own database connection objects. Having two sources of
connection objects causes problems, such as