Sessions are one of the key parts of interacting with the database. They provide a wrapper around a transaction object, providing an API which uses table row instances and query objects to interacting with the database connected to your application.
Creating a new Session
that is bound to the current database interface is simple via the
usage of DatabaseInterface.get_session()
.
# create a session bound to our current database
# this will automatically provide the ability to get transactions from the db
sess = db.get_session()
# alternatively, you can create your own sessions bound to the database interface
# providng a custom subclass or so on
session = Session(bind=db)
Using the session requires beginning it; behind the scenes this will acquire a new transaction object from the current connector, and emit a BEGIN statement to start the transaction.
# begin and connect the session
await session.begin()
The session object also supports the async with
protocol, meaning you can have it
automatically open and close without calling the begin/close methods.
async with session:
...
# or alternatively
async with db.get_session() as sess:
...
The most basic thing you can do with a session is to run some SQL code, using either
Session.execute()
or Session.cursor()
. The former is used for queries without a
result, the latter is used to execute and return a result.
For example, to fetch the result of the sum 1 + 1
, you would use:
cursor = await session.cursor("SELECT 1+1;")
This returns an instance of the the low-level object BaseResultSet
. To fetch the
result, you can use BaseResultSet.fetch_row()
:
result = await cursor.fetch_row()
answer = result["?column?"] # postgres example
answer = list(result.values())[0] # or the list form for cross-db compatability
The session is the one-stop gateway to inserting, updating, or even deleting Row Objects .
There are several methods used: Session.add()
, Session.merge()
, and
Session.remove()
are the high level methods.
Session.add()
is used for new rows, or rows that have been retrieved from a querySession.merge()
is used for rows that already exist in the databaseSession.remove()
is used to delete rows that exist in the database.
For example, to add a user to the DB:
u = User(id=1, name="heck")
await session.add(u)
You can also update a user in the database as long as the row you’re providing has a primary key,
and you use the merge
method:
u = User(id=1)
u.name = "not heck"
await session.merge(u)
See querying for an explanation of how to query using the session object.
asyncqlio.orm.session.
Session
(bind: asyncqlio.db.DatabaseInterface)[source]Bases: object
Sessions act as a temporary window into the database. They are responsible for creating queries, inserting and updating rows, etc.
Sessions are bound to a DatabaseInterface
instance which they use to get a transaction
and execute queries in.
# get a session from our db interface
sess = db.get_session()
Parameters: | bind – The DatabaseInterface instance we are bound to. |
---|
transaction
= NoneThe current BaseTransaction
this Session is associated with.
The transaction is used for making queries and inserts, etc.
select
Creates a new SELECT query that can be built upon.
Returns: | A new SelectQuery . |
---|
insert
Creates a new INSERT INTO query that can be built upon.
Returns: | A new InsertQuery . |
---|
update
Creates a new bulk UPDATE query that can be built upon.
Returns: | A new BulkUpdateQuery . |
---|
delete
Creates a new bulk DELETE query that can be built upon.
Returns: | A new BulkDeleteQuery . |
---|
start
() → asyncqlio.orm.session.Session[source]Starts the session, acquiring a transaction connection which will be used to modify the DB.
This must be called before using the session.
sess = db.get_session()
await sess.start()
Note
When using async with
, this is automatically called.
checkpoint
(checkpoint_name: str)[source]Sets a new checkpoint.
Parameters: | checkpoint_name – The name of the checkpoint to use. |
---|
uncheckpoint
(checkpoint_name: str)[source]Releases a checkpoint.
Parameters: | checkpoint_name – The name of the checkpoint to release. |
---|
commit
()[source]Commits the current session, running inserts/updates/deletes.
This will not close the session; it can be re-used after a commit.
rollback
(checkpoint: str = None)[source]Rolls the current session back. This is useful if an error occurs inside your code.
Parameters: | checkpoint – The checkpoint to roll back to, if applicable. |
---|
close
()[source]Closes the current session.
Warning
This will NOT COMMIT ANY DATA. Old data will die.
fetch
(sql: str, params=None)[source]Fetches a single row.
execute
(sql: str, params: typing.Union[typing.Mapping[str, typing.Any], typing.Iterable[typing.Any]] = None)[source]Executes SQL inside the current session.
This is part of the low-level API.
Parameters: |
|
---|
cursor
(sql: str, params: typing.Union[typing.Mapping[str, typing.Any], typing.Iterable[typing.Any]] = None)[source]Executes SQL inside the current session, and returns a new BaseResultSet.
Parameters: |
|
---|
insert_now
(row: asyncqlio.orm.schema.table.Table) → typing.Any[source]Inserts a row NOW.
Warning
This will only generate the INSERT statement for the row now.
Only Session.commit()
will actually commit the row to storage.
Also, tables with auto-incrementing fields will only have their first field filled in outside of Postgres databases.
Parameters: | row – The Table instance to insert. |
---|---|
Returns: | The row, with primary key included. |
update_now
(row: asyncqlio.orm.schema.table.Table) → asyncqlio.orm.schema.table.Table[source]Updates a row NOW.
Warning
This will only generate the UPDATE statement for the row now.
Only Session.commit()
will actually commit the row to storage.
Parameters: | row – The Table instance to update. |
---|---|
Returns: | The Table instance that was updated. |
delete_now
(row: asyncqlio.orm.schema.table.Table) → asyncqlio.orm.schema.table.Table[source]Deletes a row NOW.
run_select_query
(query: asyncqlio.orm.query.SelectQuery)[source]Executes a select query.
Warning
Unlike the other run_*_query methods, this method should not be used without a good reason; it creates a special class that is used for the query.
Use SelectQuery.first
or SelectQuery.all
.
Parameters: | query – The SelectQuery to use. |
---|---|
Returns: | A _ResultGenerator for this query. |
run_insert_query
(query: asyncqlio.orm.query.InsertQuery)[source]Executes an insert query.
Parameters: | query – The InsertQuery to use. |
---|---|
Returns: | The list of rows that were inserted. |
run_update_query
(query: asyncqlio.orm.query.BaseQuery)[source]Executes an update query.
Parameters: | query – The RowUpdateQuery or BulkUpdateQuery to execute. |
---|
run_delete_query
(query: asyncqlio.orm.query.RowDeleteQuery)[source]Executes a delete query.
Parameters: | query – The RowDeleteQuery or BulkDeleteQuery to execute. |
---|
add
(row: asyncqlio.orm.schema.table.Table) → asyncqlio.orm.schema.table.Table[source]Adds a row to the current transaction. This will emit SQL that will generate an INSERT or UPDATE statement, and then update the primary key of this row.
Warning
This will only generate the INSERT statement for the row now. Only
Session.commit()
will actually commit the row to storage.
Parameters: | row – The Table instance object to add to the transaction. |
---|---|
Returns: | The Table instance with primary key filled in, if applicable. |
merge
(row: asyncqlio.orm.schema.table.Table) → asyncqlio.orm.schema.table.Table[source]Merges a row with a row that already exists in the database.
This should be used for rows that have a primary key, but were not returned from
Session.select()
.
Parameters: | row – The Table instance to merge. |
---|---|
Returns: | The Table instance once updated. |