Re: Support for CTAS statement with ORM API?

[Markus Elfring]

> To both of these - the Django ORM doesn't work in the same way as SQLAlchemy.

I guess that it can become interesting to clarify software differences a bit 
more.


> Django does not have such a representation,

Thanks for such background information.


> it bundles everything in the Query class.

Will this aspect trigger any further concerns for software evolution?


> I've tidied my code snippet a bit and posted it on my blog
> at https://adamj.eu/tech/2019/04/29/create-table-as-select-in-django/ .

I am curious if the development attention will grow also for this area.

Regards,
Markus

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/ca05644f-e736-33ab-ea81-6043f4a86a01%40web.de.
For more options, visit https://groups.google.com/d/optout.

Re: Support for CTAS statement with ORM API?

[Adam Johnson]

Hi Markus

It seems that the functionality is already usable by this class library
> in a convenient way.


How do you think about the addition of a base class like “table”

with the method “create(parameters)” (if it would not be provided so far)?


To both of these - the Django ORM doesn't work in the same way as
SQLAlchemy. SQLAlchemy has a representation of the query underneath that's
close to SQL. This is how an extra CreateTableAs class can be added that
neatly fits.

Django does not have such a representation, it bundles everything in the
Query class. I think my code snippet is the closest we can get to a way of
integrating. However, it is less code since rather than creating a class,
as it performs direct string manipulation on the SQL.

Would any contributors like to adjust the software situation a bit more
> here?


I am not sure such a function belongs in Django, at least at this time,
since your request is the first I can find in its history. We're always a
bit conservative about adding new features since they will need maintaining
forever.

I've tidied my code snippet a bit and posted it on my blog at
https://adamj.eu/tech/2019/04/29/create-table-as-select-in-django/ . If it
gets some more coverage maybe that's an indicator it's worth adding into
Django core.

Thanks,

Adm

On Sun, 28 Apr 2019 at 12:43, Markus Elfring  wrote:

> > AFAIU from the link, the source code is extending SQLAlchemy to support
> CTAS,
> > so it's not built-in there.
>
> It seems that the functionality is already usable by this class library
> in a convenient way.
>
>
> > It's similarly possible to do a CTAS in Django with a little raw SQL
> > and the ORM's internal API (which is undocumented).
>
> Would any contributors like to adjust the software situation a bit more
> here?
>
>
> > I guess this could be wrapped up in a function,
>
> This is likely.
>
>
> > like "create_table_as(table_name, queryset)" ? Thoughts?
>
> How do you think about the addition of a base class like “table”
> with the method “create(parameters)” (if it would not be provided so far)?
>
> Regards,
> Markus
>


-- 
Adam

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CAMyDDM1Q0PJQdVhrpWM1h42o-gQD2AfTBCmTE9pwPfz%2BFg3fQg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Support for CTAS statement with ORM API?

[Markus Elfring]

> AFAIU from the link, the source code is extending SQLAlchemy to support CTAS,
> so it's not built-in there.

It seems that the functionality is already usable by this class library
in a convenient way.


> It's similarly possible to do a CTAS in Django with a little raw SQL
> and the ORM's internal API (which is undocumented).

Would any contributors like to adjust the software situation a bit more here?


> I guess this could be wrapped up in a function,

This is likely.


> like "create_table_as(table_name, queryset)" ? Thoughts?

How do you think about the addition of a base class like “table”
with the method “create(parameters)” (if it would not be provided so far)?

Regards,
Markus

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/6096c7f6-70a3-f936-0275-6848fe9040c5%40web.de.
For more options, visit https://groups.google.com/d/optout.

Re: Support for CTAS statement with ORM API?

[Adam Johnson]

>
> This is an usual challenge for such software extensions, isn't it?


It's more around having an API that makes intuitive sense to ORM users.
Django is very model-focused and the ORM is mostly designed to work with
tables that are created by migrations.

I published examples where data processing approaches are shown

(also together with applications of the class library “SQLAlchemy”).


AFAIU from the link, the source code is extending SQLAlchemy to support
CTAS, so it's not built-in there.

It's similarly possible to do a CTAS in Django with a little raw SQL and
the ORM's internal API (which is undocumented). You can to compile the
query for a QuerySet and prefix it with CTAS syntax before running it. For
example:

In [1]: from gargoyle.models import Switch

In [2]: Switch.objects.filter(pk__gt=1)
Out[2]: 

In [7]: from django.db import DEFAULT_DB_ALIAS, connection

In [8]:
connection.ops.compiler('SQLCompiler')(Switch.objects.filter(pk__gt=1).query,
connection, DEFAULT_DB_ALIAS).as_sql()
Out[8]:
('SELECT "gargoyle_switch"."key", "gargoyle_switch"."value",
"gargoyle_switch"."label", "gargoyle_switch"."date_created",
"gargoyle_switch"."date_modified", "gargoyle_switch"."description",
"gargoyle_switch"."status" FROM "gargoyle_switch" WHERE
"gargoyle_switch"."key" > %s',
 ('1',))

In [9]: sql, params =
connection.ops.compiler('SQLCompiler')(Switch.objects.filter(pk__gt=1).query,
connection, DEFAULT_DB_ALIAS).as_sql()

In [10]: sql = 'CREATE TABLE foobar AS ' + sql

In [11]: connection.cursor().execute(sql, params)
Out[11]: 

In [12]: cursor = connection.cursor()

In [13]: cursor.execute('SELECT * FROM foobar')
Out[13]: 

In [14]: list(cursor.fetchall())
Out[14]: []

I guess this could be wrapped up in a function, like
"create_table_as(table_name, queryset)" ? Thoughts?

On Fri, 26 Apr 2019 at 12:03, Markus Elfring  wrote:

> > CTAS can be useful in some cases,
>
> Thanks for such feedback.
>
>
> > but I can't imagine how we'd fit it into the Django ORM nicely.
>
> This is an usual challenge for such software extensions, isn't it?
>
>
> > There wouldn't be a model for the correspondingly created table.
>
> Can a data model be determined from its creation parameters?
>
>
> > Have you got a suggestion for the way you'd like to see it working?
>
> I published examples where data processing approaches are shown
> (also together with applications of the class library “SQLAlchemy”).
> I would like to be able to choose between the use of materialized views
> (or snapshots) and a concrete database table.
> How often will different development and software run time characteristics
> matter for these interfaces?
>
> Regards,
> Markus
>


-- 
Adam

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CAMyDDM1BB63GwrxhE90WryYXzhk-mD0oGPO2x3ph9dXYRWaqQw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Support for CTAS statement with ORM API?

[Markus Elfring]

> CTAS can be useful in some cases,

Thanks for such feedback.


> but I can't imagine how we'd fit it into the Django ORM nicely.

This is an usual challenge for such software extensions, isn't it?


> There wouldn't be a model for the correspondingly created table.

Can a data model be determined from its creation parameters?


> Have you got a suggestion for the way you'd like to see it working?

I published examples where data processing approaches are shown
(also together with applications of the class library “SQLAlchemy”).
I would like to be able to choose between the use of materialized views
(or snapshots) and a concrete database table.
How often will different development and software run time characteristics
matter for these interfaces?

Regards,
Markus

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/a460432c-495d-e6c1-4633-031f85bb5e0f%40web.de.
For more options, visit https://groups.google.com/d/optout.

Re: Support for CTAS statement with ORM API?

[Adam Johnson]

Hi Markus

CTAS can be useful in some cases, but I can't imagine how we'd fit it into
the Django ORM nicely. There wouldn't be a model for the correspondingly
created table. Have you got a suggestion for the way you'd like to see it
working?

Thanks,

Adam

On Wed, 24 Apr 2019 at 14:03, Markus Elfring  wrote:

> Hello,
>
> I contributed the feature request “Creating database tables from queries
> based on ORM API” .
> Today it was requested to continue the clarification for mentioned
> software aspects in this forum.
>
> Can the support for the SQL standard statement “CREATE TABLE AS SELECT”
> (CTAS) grow besides the application of materialized views (or snapshots)?
>
> By the way:
> This functionality is supported by the class library “SQLAlchemy 1.3.2”
>  (to
> some degree).
>
> Regards,
> Markus
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-developers.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/ac948a00-2a5e-445a-bffd-80172210378e%40googlegroups.com
> 
> .
> For more options, visit https://groups.google.com/d/optout.
>


-- 
Adam

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CAMyDDM1pab%2BJJuQcohMVDNjOj5_QTLUQDX6Mzkc_LPUM6pOcbw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.