Query shortcut
There are roughly two type functions.
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)]
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 (opens in a new tab) (default: True)
- autocommit: It is the same as sessionmaker (opens in a new tab) (default: False)
- expire_on_commit: It is the same as sessionmaker (opens in a new tab) (default: True)
- info: It is the same as sessionmaker (opens in a new tab) (default: None)
All arguments can be omitted.
Supported auto-detecting db types are the following:
- PostgreSQL
- MySQL
- Oracle