d2a 2.7.0

Auto loading

Just add d2a to settings.INSTALLED_APPS.

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',

    # top or here
    'd2a',

    # example apps
    'books',
    'sales',
]

Then models_sqla (default) in all apps will be imported as a module.

>>> from books import models_sqla
>>> models_sqla.  # tab completion
models_sqla.Author(            models_sqla.BookCategory(      models_sqla.CategoryRelation(  models_sqla.transfer(
models_sqla.Book(              models_sqla.Category(          models_sqla.models

>>> models_sqla.Book
<class 'd2a.book'>

>>> models_sqla.Book.  # tab completion
models_sqla.Book.author       models_sqla.Book.content      models_sqla.Book.metadata     models_sqla.Book.tags
models_sqla.Book.author_id    models_sqla.Book.description  models_sqla.Book.mro(         models_sqla.Book.title
models_sqla.Book.category     models_sqla.Book.id           models_sqla.Book.price
# SQL Expression schema
>>> models_sqla.Book.__table__
Table(
  'book', MetaData(bind=None),
  Column('id', UUID(), table=<book>, primary_key=True, nullable=False, default=ColumnDefault(<function uuid4 at 0x7f3cebe7e598>)),
  Column('price', JSON(astext_type=Text()), table=<book>, nullable=False),
  Column('title', VARCHAR(length=255), table=<book>, nullable=False),
  Column('description', TEXT(), table=<book>),
  Column('author_id', INTEGER(), ForeignKey('author.id'), table=<book>),
  Column('content', BYTEA(), table=<book>, nullable=False),
  Column('tags', ARRAY(VARCHAR()), table=<book>, nullable=False),
  schema=None
)

Also it can extract model declared implicitly depending on m2m field. (in this case, BookCategory)

Per models module

If you want to create a module manually, create a models_sqla.py in the apps.

Write like the following to it:

from d2a import transfer
from . import models
transfer(models, globals())

models_sqla.py exists, auto module creation will be omitted.

And if you create every models_sqla.py manually, it is unnecessary to set d2a to settings.INSTALLED_APPS.

Example:

• project_postgresql/books/models_sqla.py

• You can omit specifying db_type, then it automatically detects a database type from settings.DATABASES['default'].

  • Now postgresql, mysql and oracle are allowed, the other types will be converted to the following types as default type: sqlalchemy/types.py

Per model

If you just want to convert one model, you should use declare function.

>>> from d2a import declare
>>> from sales.models import Sales
>>> sales = declare(Sales)
>>> sales
<class 'd2a.sales'>

>>> sales.__table__
Table(
  'sales', MetaData(bind=None),
  Column('id', BIGINT(), table=<sales>, primary_key=True, nullable=False),
  Column('book_id', UUID(), ForeignKey('book.id'), table=<sales>, nullable=False),
  Column('sold', TIMESTAMP(), table=<sales>, nullable=False),
  Column('reservation', INTERVAL(), table=<sales>),
  Column('source', INET(), table=<sales>),
  schema=None
)

>>> sales.
sales.book         sales.id           sales.mro(         sales.sold
sales.book_id      sales.metadata     sales.reservation  sales.source

Custom fields

If you are using customized field (not built-in), you can register the field as the other field using alias or alias_dict method.

from django.db.models import ImageField

class ExtendedImageField(ImageField):
    """something customizing"""

from d2a import alias
alias(ExtendedImageField, ImageField)

# or
alias_dict({
    ExtendedImageField: ImageField,
})

When the translation rule is not found, it will warn you and continue. (2.6.x later)

You can change the behavior by specifying the following values to D2A_CONFIG['MISSING'].

None:

Ignores the warning.

Field:

Uses the specifying field instead of the unknown field.

e.g. Using CharField

from django.db.models import CharField

D2A_CONFIG = {
    'MISSING': CharField,
}

Querying shortcut

>>> from sqlalchemy import (
...     select,
...     insert,
... )

>>> from d2a import query_expression, execute_expression

# if you try on `project_mysql` demo, you should write ``from books.modelsa import Author``
>>> from books.models_sqla import Author

>>> AuthorTable = Author.__table__

>>> records = [
...     {'name': 'a', 'age': 10},
...     {'name': 'b', 'age': 30},
...     {'name': 'c', 'age': 20},
... ]

>>> # insert
>>> stmt = insert(AuthorTable).values(records)
>>> execute_expression(stmt)
3

>>> # select
>>> stmt = select([
...     AuthorTable.c.id,
...     AuthorTable.c.name,
...     AuthorTable.c.age,
... ]).select_from(AuthorTable).order_by(AuthorTable.c.age)

>>> query_expression(stmt)
[
  OrderedDict([('id', 12), ('name', 'a'), ('age', 10)]),
  OrderedDict([('id', 14), ('name', 'c'), ('age', 20)]),
  OrderedDict([('id', 13), ('name', 'b'), ('age', 30)])
]

>>> # record as tuple
>>> query_expression(stmt, as_col_dict=False)
[(12, 'a', 10), (14, 'c', 20), (13, 'b', 30)]

>>> query_expression(stmt, as_col_dict=False, debug={'printer': print, 'show_explain': True, 'sql_format': True})
====================================================================================================
SELECT author.id,
       author.name,
       author.age
FROM author
ORDER BY author.age
====================================================================================================
Sort  (cost=16.39..16.74 rows=140 width=522) (actual time=0.027..0.028 rows=18 loops=1)
  Sort Key: age
  Sort Method: quicksort  Memory: 25kB
  ->  Seq Scan on author  (cost=0.00..11.40 rows=140 width=522) (actual time=0.007..0.009 rows=18 loops=1)
Planning time: 0.072 ms
Execution time: 0.047 ms
[(12, 'a', 10), (14, 'c', 20), (13, 'b', 30)]

>>> from d2a import make_session
>>> from books.models_sqla import Author

>>> with make_session() as session:
...     # it commits and flushes automatically when the scope exits.
...     a = Author()
...     a.name = 'righ'
...     a.age = 30
...     session.add(a)
...
>>> with make_session() as session:
...     # when the session was rolled back or causes some exception in the context,
...     # it won't register records in the session.
...     a = Author()
...     a.name = 'teruhiko'
...     a.age = 85
...     session.add(a)
...     session.rollback()
...
>>> with make_session() as session:
...     session.query(Author.name, Author.age).all()
...
[('righ', 30)]

start up environment

$ git clone git@github.com:walkframe/d2a.git
$ cd d2a
$ docker-compose up

preparation

$ docker exec -it d2a_app /bin/bash
# python -m venv venv # only first time
# source venv/bin/activate
(venv) # cd project_postgresql/ # (or mysql)
(venv) project_postgresql # ./manage.py migrate

execute

(venv) project_postgresql # ./manage.py shell

>>> from books import models_sqla
>>> book = models_sqla.Book()
>>> author = models_sqla.Author()
>>> book.author = author
>>> author.books
[<d2a.book object at 0x7f3cec539358>]
# And do something you want to do ;)

GeoDjango

• GeoDjango-GeoAlchemy2 translation demo