Query shortcut

Back to doc index

There are roughly two type functions.

• Expression

• ORM

Expression

There are two functions.

query_expression:

To retrieve SELECT results, and returns a list containing record.

execute_expression:

To execute INSERT, DELETE, UPDATE statements, and returns num of records having been affected.

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

>>> from d2a import query_expression, execute_expression

>>> from 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)]

Note

I added argument of query_expression() to see debugging information.

Specify options as dict type like the following:

query_expression(stmt, debug={  # all options can be skipped.
    'show_sql': True, # if showing the sql query or not.
    'show_explain': False, # if showing explain for the sql query or not.
    'sql_format': False, # if formatting the sql query or not.
    'sql_reindent': True, # if setting indent the sql query or not.
    'sql_keyword_case': 'upper', # A rule converting reserved words.
    'explain_prefix': depends on the database type. unless you specify it, an appropriate prefix will be automatically used.
    'printer': printer, # printing method. Try to use `logger.info` function for example.
    'delimiter': '=' * 100, # characters dividing debug informations.
    'database': 'default' # django database
})

Default is {} (An empty dict means disabling debug.)

ORM

There is a function named make_session for ORM mode.

>>> from d2a import make_session
>>> from 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)]

It receives the following arguments:

engine:

engine object or database-type (string) (default: None). When it is omitted, it guesses database type and gets an engine automatically.

autoflush:

It is the same as sessionmaker (default: True)

autocommit:

It is the same as sessionmaker (default: False)

expire_on_commit:

It is the same as sessionmaker (default: True)

info:

It is the same as sessionmaker (default: None)

All arguments can be omitted.

Warning

Supported auto-detecting db types are the following:

• PostgreSQL

• MySQL

• Oracle