auvipy/django-sqlalchemy

Django ORM built on top of SQLalchemy core 2.0 for seamless integration of SQLAlchemy with Django 4.2+ PostgreSQL 14+ only for now. [pre POC now]

** Warning: This project is in early design decision stage. you can participate on development process **

The end goal of this project is to build the django ORM on top of SQLalchemy core so that, existing django apps can easily migrate from current ORM of django to SQLalchemy based django ORM with almost no change or as little change as possible.

Motivation: https://rixx.de/blog/djangocon-europe-2019-how-and-why-to-build-a-django-based-project-with-sqlalchemy-core-for-data-analysis/

django-sqlalchemy

A common request over the entire life of Django has been to use Django's forms (and in particular, Django's Admin) with data stores that aren't Django's ORM. SQLAlchemy is a popular choice for those using SQL databases. With the formalization of django Meta API it is now possible to use that formalize API for interfacing with other non-django-orm data stores.

The django-sqlalchemy project aims to work as a seamless integrating of SQLAlchemy with django web framework. Long term goal of this project is to become a drop in replacement for django ORM and all the parts of django should be able to use SQLalchemy easily as they use django ORM today.

To acheive the goal, need all nuts and bolts to make sqlalchemy work well with django. Integration with different django features working with sqlalchemy Like management commands, django Meta compliant layer to interface with django forms/modelforms + Admin panel + Signals etc.

Brief Architectural overview of SQLAlchemy in contrast to django ORM

• The first one is : SQLAlchemy is a deeply layered system, whereas Django's ORM is basically just one layer which is the ORM. In SQLAlchemy you have at the very bottom the engine which with abstracts away connection pools and basic API differences between different databases, on top of that you have the SQL abstraction language, sitting on top of that and the table definitions you have the basic ORM and on top of that you have the declarative ORM which looks very close to the Django ORM.
• Django's ORM is basically quite simple. Each time you do any query it generates a SQL expression for you and sends a query to the database. Then it constructs an object for you. That object can be modified and if you call save() on it, it will update the record in the database with the new values of the attributes. In SQLAlchemy there is an object called the “session” and it basically encapsulates a transaction. Each object is tracked by primary key in this session. As such each object only exists once by primary key. As such you can safely make a lot of queries and you never have things out of sync. When you commit the session it will send all changes at once to the database in correct order, if you rollback the session nothing happens instead.

Proposed solutions and Implementation design specification

As the goal of this project is to create an alternative drop in replacement of django ORM to make sqlalchemy work almost natively on django apps, migrations, forms, modelforms, Admin, contenttyps, signals so the SQLalchemy core should be mapped with django Models properly.

Django-sqlalchmey package specific

1. Basic glues for working well with django application as SQLAlchemy relies on global state for a few things:

• A MetaData instance which tracks all known SQL tables.
• A base class for all models using the ORM.
• A session factory. Every application using SQLAlchemy must provides its own instance of these. This makes it hard to create add-on packages that also use SQLAlchemy, since they either need to have their own SQLAlchemy state, which makes it hard to integrate them into application, or they need to jump through multiple complex hoops to allow them share state with application.

2. Django compatible settings for SQLalchemy package
3. Django derived db management commands for handling django-SQLalchemy
4. Migration Hnadling of the django-alchemy package. For that we have thre options:

• use alembic to handle migrations much like flask-migrate
• use sqlalchmey-migrate which is inspired by ruby-on-rails
• use django's build in migration to handle sqlalchemy migrations[might need to improve django migrations too] I will use the django built in migration framework to detect the change of sqlalchemy schema migrations. Though the migration framework is tightly coupled with django ORM we need to refactor and map the django model with SQLalchemy core to

3. Django model Meta API compliant layer to expose sqlalchemy table definitions on django forms/modelforms and admin app
4. Work Properly with django ContentTypes
5. Work properly with Django GIS
6. Work well with signals

Refactor django internals

The meta API used by admin and model forms should be completely public and stable. The problem is some methods of the meta API return field instances, and the API for the fields (especially for related fields) isn't public nor stable. For that reason works needed to be done to make the related field API stable.

from https://code.djangoproject.com/ticket/24317

• Problems: when using get_fields(), it'll return either a field.rel instance (for reverse side of user defined fields), or a real field instance (for example ForeignKey). These behave differently, so that the user must always remember which one he is dealing with. This creates lots of non-necessary conditioning in multiple places of Django. For example, the select_related descent has one branch for descending foreign keys and one to one fields, and another branch for descending to reverse one to one fields. Conceptually both one to one and reverse one to one fields are very similar, so this complication is non-necessary.

So the idea is to deprecate field.rel, and instead add field.remote_field. The remote_field is just a field subclass, just like everything else in Django.

• The benefits are: Conceptual simplicity - dealing with fields and rels is non-necessary, and confusing. Everything from get_fields() should be a field. Code simplicity - no special casing based on if a given relation is described by a rel or not Code reuse - ReverseManyToManyField is in most regard exactly like ManyToManyField The expected problems are mostly from 3rd party code. Users of _meta that already work on expectation of getting rel instances will likely need updating. Those users who subclass Django's fields (or duck-type Django's fields) will need updating. Examples of such projects include django-rest-framework and django-taggit.

Open Questions on Design specification and implementation

• migration handling[django built in migration/alembic]
• system checks
• sqlalchemy core, orm and declarative extention

License

Django-SQLAlchemy is Free Software: You can use, study share and improve it at your will. Specifically you can redistribute and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

django-sqlalchemy's Issues

django model._meta compatable layer for sqlalchemy core

this can be used as inspiration r4fek/django-cassandra-engine#66

SQLAlchemy based generic views [aligning with vanilla view api]

Relative imports causes reprocessing of models which causes a problem

importing differently, relatively causes it to reprocess the model and tries to 
create a new 
mapper.  We need to work around this.

>>> from categories.models import *
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/mtrier/Development/django-
sqlalchemy/tests/apps/../apps/categories/models.py", line 5, in <module>
    class Category(models.Model):
  File "/Users/mtrier/Development/django-sqlalchemy/django_sqlalchemy/models/base.py", line 
139, in __init__
    cls.__mapper__ = mapper_cls(cls, table, properties=our_stuff, **mapper_args)
  File "/Library/Python/2.5/site-packages/sqlalchemy/orm/__init__.py", line 566, in mapper
    return Mapper(class_, local_table, *args, **params)
  File "/Library/Python/2.5/site-packages/sqlalchemy/orm/mapper.py", line 175, in __init__
    self.__compile_class()
  File "/Library/Python/2.5/site-packages/sqlalchemy/orm/mapper.py", line 784, in 
__compile_class
    raise exceptions.ArgumentError("Class '%s' already has a primary mapper defined with entity 
name '%s'.  Use non_primary=True to create a non primary Mapper.  
clear_mappers() will remove 
*all* current mappers from all classes." % (self.class_, self.entity_name))
ArgumentError: Class '<class 'apps.categories.models.Category'>' already has a 
primary mapper 
defined with entity name 'None'.  Use non_primary=True to create a non primary 
Mapper.  
clear_mappers() will remove *all* current mappers from all classes.
>>> from apps.categories.models import *

Original issue reported on code.google.com by [email protected] on 9 Apr 2008 at 11:32

Implement reset Management Command

The reset management command drops all tables and recreates them for a 
specified application(s).

Original issue reported on code.google.com by [email protected] on 22 Mar 2008 at 10:07

Develop Roadmap for Contributors

Up to this point everything has been sort of just sitting around in our heads 
and now we have a 
good enough idea of how this will play.  We need to consolidate this into a 
roadmap document that 
makes it easy for contributors to get involved.

Original issue reported on code.google.com by [email protected] on 17 Mar 2008 at 4:08

add wheel support for package

Queries across related items don't work

The following fails:

{{{
p = Post.objects.filter(category__name__icontains='r')
}}}

This is because the filter logic in query_utils doesn't move the join point.

Original issue reported on code.google.com by [email protected] on 10 Apr 2008 at 1:27

compat with both python 3.x and 2.x using django six utils

django models and sqlalchemy mapping core

make sqlalchemy work with django apps

pk Doesn't Work in Querysets

The following doesn't work in querysets:

{{{
p = Post.objects.get(pk=1)
}}}

If *id* is used it works fine.  We need to figure out how to map this properly 
in the parse_filter 
function.

Original issue reported on code.google.com by [email protected] on 20 Mar 2008 at 4:53

Implement the dbshell Management Command

Need to implement the dbshell management command.  This will need to support 
all the databases 
that SA supports if possible.

http://www.djangoproject.com/documentation/django-admin/#dbshell

Original issue reported on code.google.com by [email protected] on 22 Mar 2008 at 10:08

Adjacency list relationships do not work

The setup for adjacency list relationships (self-referential) is different on 
SA than for normal Many 
to One.  The current declarative parts do not handle this properly.

[http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_selfrefe
rential_quer]
y

Original issue reported on code.google.com by [email protected] on 10 Apr 2008 at 1:30

make it possible source setup.py install asap

syncdb needs to support loading custom sql and fixtures

Currently syncdb is just passing off the creation of missing tables to SA.  We 
need to also structure 
this is such a way that custom sql can be run for those tables that were 
created.  This is going to be 
a bit challenging since we need to find a way to get the list of tables back 
out of SA.  

Additionally we are not loading in any fixtures which happens with syncdb.

Original issue reported on code.google.com by [email protected] on 26 Mar 2008 at 1:26

django_sqlalchemy missing DJANGO_SQLALCHEMY_DBURI causes dump

If the user specifies:
{{{
from django_sqlalchemy import models
}}}

but does not set the a DJANGO_SQLALCHEMY_DBURI settings in the settings.py 
module the 
following dump occurs. Look into provided a cleaner message that indicates the 
problem:

{{
mtrier@michael-triers-computer:~/Development/django-sqlalchemy/original$ 
./manage.py 
syncdb
Traceback (most recent call last):
  File "./manage.py", line 11, in <module>
    execute_manager(settings)
  File "/Library/Python/2.5/site-packages/django/core/management/__init__.py", line 272, in 
execute_manager
    utility.execute()
  File "/Library/Python/2.5/site-packages/django/core/management/__init__.py", line 219, in 
execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Library/Python/2.5/site-packages/django/core/management/base.py", line 72, in 
run_from_argv
    self.execute(*args, **options.__dict__)
  File "/Library/Python/2.5/site-packages/django/core/management/base.py", line 85, in 
execute
    self.validate()
  File "/Library/Python/2.5/site-packages/django/core/management/base.py", line 112, in 
validate
    num_errors = get_validation_errors(s, app)
  File "/Library/Python/2.5/site-packages/django/core/management/validation.py", line 28, in 
get_validation_errors
    for (app_name, error) in get_app_errors().items():
  File "/Library/Python/2.5/site-packages/django/db/models/loading.py", line 126, in 
get_app_errors
    self._populate()
  File "/Library/Python/2.5/site-packages/django/db/models/loading.py", line 55, in _populate
    self.load_app(app_name, True)
  File "/Library/Python/2.5/site-packages/django/db/models/loading.py", line 70, in load_app
    mod = __import__(app_name, {}, {}, ['models'])
  File "/Users/mtrier/Development/django-sqlalchemy/original/../original/bar/models.py", line 
2, in <module>
    from django_sqlalchemy import models
  File "/Users/mtrier/Development/django-sqlalchemy/django_sqlalchemy/models/__init__.py", 
line 5, in <module>
    from django_sqlalchemy.models.fields.related import ForeignKey, ManyToManyField
  File "/Library/Python/2.5/site-packages/django_sqlalchemy/models/fields/related.py", line 2, 
in <module>
    from django_sqlalchemy.backend import metadata, Session
  File "/Users/mtrier/Development/django-sqlalchemy/django_sqlalchemy/backend/__init__.py", 
line 2, in <module>
    from base import engine, Session, metadata
  File "/Users/mtrier/Development/django-sqlalchemy/django_sqlalchemy/backend/base.py", 
line 19, in <module>
    engine = create_engine(settings.DJANGO_SQLALCHEMY_DBURI, convert_unicode=True)
  File "/Library/Python/2.5/site-packages/django/conf/__init__.py", line 32, in __getattr__
    return getattr(self._target, name)
AttributeError: 'Settings' object has no attribute 'DJANGO_SQLALCHEMY_DBURI'
}}

Original issue reported on code.google.com by [email protected] on 5 Apr 2008 at 10:08

Implement the inspectdb Management Command

This command needs to be implemented to support all of the backends that SA 
supports.  Hopefully 
we can use the introspection capabilities of SA to get the information we need. 

See:

http://www.djangoproject.com/documentation/django-admin/#inspectdb

Original issue reported on code.google.com by [email protected] on 22 Mar 2008 at 10:18

Implement the dumpdata Management Command

We need to implement the dumpdata management command.  This may work as is, but 
we need to 
at least verify it.

Original issue reported on code.google.com by [email protected] on 22 Mar 2008 at 10:12

django system check functionality

Implement the createcachetable Management Command

Need to implement the createcachetable management command.

[http://www.djangoproject.com/documentation/django-admin/#createcachetable-table
name]

Original issue reported on code.google.com by [email protected] on 22 Mar 2008 at 10:10

sqlalchemy based auth backend

Re base existing work against django master after initial design decision

consider using attr library

this should help reduce boilerplate in class

Finalize initial design decision

schema migrations using django migrations and alembic

Sql Management Command Does Not Respect the App Name

Instead of using only the specific app for the create statements we're dumping 
it for all apps.
{{{
$ ./manage.py sql foo

CREATE TABLE foo_category (
    id INTEGER NOT NULL, 
    created_at TIMESTAMP NOT NULL, 
    name VARCHAR(100) NOT NULL, 
    PRIMARY KEY (id)
)


CREATE TABLE bar_book (
    id INTEGER NOT NULL, 
    pub_date TIMESTAMP NOT NULL, 
    name VARCHAR(100) NOT NULL, 
    PRIMARY KEY (id)
)
}}}

Original issue reported on code.google.com by [email protected] on 15 Mar 2008 at 9:16

Property based and BDD based testing automation

Investigate Colorize for Management Commands

We need to investigate colorization down the road so the "user experience" is 
similar to Django

Original issue reported on code.google.com by [email protected] on 20 Mar 2008 at 4:29

django model fields and sqlalchemy fields mapping

Implement the flush Management Command

The flush command needs to be implemented.  It's possible that it may work if 
it's implemented 
using reset and syncdb.  

See:

http://www.djangoproject.com/documentation/django-admin/#flush

Original issue reported on code.google.com by [email protected] on 22 Mar 2008 at 10:15

Implement the loaddata Management Command

We need to make loaddata work properly with the SA backends.  This could work 
fine already 
depending on the implementation, but we need to verify it.

Original issue reported on code.google.com by [email protected] on 22 Mar 2008 at 10:13

Datamapper based Active Record

http://stackoverflow.com/questions/42447259/flask-sqlalchemy-data-mapper-vs-active-record-pattern

'DatabaseWrapper' object has no attribute 'operators'

I installed django_sqlalchemy in a old website with latest django version.

I not changed the models after the installation of django_sqlalchemy.

Sometime, when I do some query like filter(), all(), get() ecc., an error
appears. I do not know why.

"""
django/db/models/sql/where.py in make_atom(self, child, qn)
    117             extra = ''
    118
--> 119         if lookup_type in connection.operators:
    120             format = "%s %%s %s" %
(connection.ops.lookup_cast(lookup_type),
    121                     extra)
<type 'exceptions.AttributeError'>: 'DatabaseWrapper' object has no
attribute 'operators'
"""

So, I investigated below...

I opened django/db/backends/postgresql/base.py and I saw the attribute
"operators":
"""
operators = {
    'exact': '= %s',
    'iexact': 'ILIKE %s',
    'contains': 'LIKE %s',
    'icontains': 'ILIKE %s',
    'regex': '~ %s',
    'iregex': '~* %s',
    'gt': '> %s',
    'gte': '>= %s',
    'lt': '< %s',
    'lte': '<= %s',
    'startswith': 'LIKE %s',
    'endswith': 'LIKE %s',
    'istartswith': 'ILIKE %s',
    'iendswith': 'ILIKE %s',
}
"""

But the backend of django_sqlalchemy have not this attribute
(django_sqlalchemy/backend/base.py).

Versions used:
Django trunk r7519
Sqlalchemy v0.4.5
django_sqlalchemy mainline "commit 481f7d6aa3688fcbedcd19c72072c130cc2e5a09
Sun Apr 27 09:36:57 2008 -0400"

Original issue reported on code.google.com by [email protected] on 7 May 2008 at 6:39

sqlalchemy based session back end