Query shortcut

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

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
})

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