Database plugins¶

In this page you will find documentation for all plugins that are available.

SQL¶

Initialize database¶

Before you connect to your database, make sure you initialize it first. To do that execute the following command from a directory containing a settings.py file.

You can also privide the settings directory with --settings_path.

dbp initdb

When you run this command with an empty database it will create the following schema:

Migrations¶

Migrations keep track of database changes. To fully init the database with your step configuration run

dbp make_migrations
dbp migrate

This will set the head state for tracking changes on the database and also execute any migrations that might be present.

The first command dbp make_migrations will create migration files according to differences from dbp models and your database.

The seccond command dbp migrate will execute the migrations and update your database.

What migrations can and can’t detect¶

Migrations will detect:

Table additions, removals.
Column additions, removals.
Change of nullable status on columns.
Basic changes in indexes

Migrations can’t detect:

Changes of table name. These will come out as an add/drop of two different tables, and should be hand-edited into a name change instead.
Changes of column name. Like table name changes, these are detected as a column add/drop pair, which is not at all the same as a name change.

Set database Connection¶

from db_plugins.db.sql import SQLConnection
from db_plugins.db.sql.models import *

db_config = {
    "SQL": {
      "ENGINE": "postgresql",
      "HOST": "host",
      "USER": "username",
      "PASSWORD": "pwd",
      "PORT": 5432, # postgresql tipically runs on port 5432. Notice that we use an int here.
      "DB_NAME": "database",
    }
}

db = SQLConnection()
db.connect(config=db_config["SQL"])

The above code will create a connection to the database wich we will later use to store objects.

Create model instances¶

Use get_or_create function to get an instance of a model. The instance will be an object from the database if it already exists or it will create a new instance.

oid = "ZTF_OID"
model_args = {}
model_args["ndethist"] = 0
model_args["ncovhist"] = 0
model_args["mjdstarthist"] = 0.0
model_args["mjdendhist"] = 0.0
model_args["firstmjd"] = 0.0
model_args["lastmjd"] = 0.0
model_args["ndet"] = 0
model_args["deltajd"] = 0.0
model_args["meanra"] = 0.0
model_args["meandec"] = 0.0
model_args["step_id_corr"] = "v1.0.0"
model_args["corrected"] = False
model_args["stellar"] = False

obj, created = db.query(Object).get_or_create(filter_by={"oid": oid}, **model_args)
print(obj, "created: " + str(created))

<AstroObject(oid='ZTFid')> created: False

In the above example we use the object id as a filter since it is the primary key of the Object model. Notice that get_or_create can receive the model as a parameter or it can inherit it from the query parameter.

The important part is that model_args should contain all attributes of the table.

Add multiple objects to the database¶

If you need to insert multiple objects at once, there is a faster way than using get_or_create multiple times. You can use the bulk_insert method.

It will take a list of dictionaries where each dictionary has all the attributes for a table as keys.

db.query(Detection).bulk_insert(prv_detections)

Where prv_detections is a list of dict where each dict contains information to populate Detection table.

Update instances¶

There is a particularity when you make updates to instances. Let’s say that we have an object instance and we want to change its lastmjd.

obj = db.query(Object).get_or_create(filter_by={"oid": "ZTF123"})

obj = db.query().update(obj, {"lastmjd": 12345})

After updating the instance you have to commit the changes. This is done in the following way:

db.session.commit()

DatabaseConnection documentation¶

class db_plugins.db.sql.SQLConnection(config=None, engine=None, Base=None, Session=None, session=None)[source]¶

Methods

`connect`(config[, base, session_options, ...])	Establishes connection to a database and initializes a session.
`create_db`()
`create_session`(use_scoped[, scope_func])	Creates a SQLAlchemy Session object to interact with the database.
`drop_db`()
`query`(*args)	Creates a BaseQuery object that allows you to query the database using the SQLAlchemy API, or using the BaseQuery methods like `get_or_create`

end_session

connect(config, base=None, session_options=None, create_session=True, use_scoped=False, scope_func=None)[source]¶

Establishes connection to a database and initializes a session.

Parameters

configdict

Database configuration. For example:

"SQL": {
    "ENGINE": "postgresql",
    "HOST": "host",
    "USER": "username",
    "PASSWORD": "pwd",
    "PORT": 5432, # postgresql tipically runs on port 5432. Notice that we use an int here.
    "DB_NAME": "database",
}

basesqlalchemy.ext.declarative.declarative_base()

Base class used by sqlalchemy to create tables

session_optionsdict

Options passed to sessionmaker

create_sessionBoolean

Whether to instantiate a session or not. The default value is True since this is the previous behavior. Proper usage should be to pass False and create / close the session on demand inside the application. You should call this method first and then call SQLConnection.create_session(use_scoped)

# scoped session example
conn = SQLConnection()
conn.connect(config, create_session=False)
conn.create_session(use_scoped=True)
# use session
conn.query()
conn.session.remove()

use_scopedBoolean

Whether to use scoped session or not. Use a scoped session if you are using it in a web service like an API. Read more about scoped sessions at: https://docs.sqlalchemy.org/en/13/orm/contextual.html?highlight=scoped

scope_funcfunction

A function which serves as the scope for the session. The session will live only in the scope of that function.

create_session(use_scoped, scope_func=None)[source]¶

Creates a SQLAlchemy Session object to interact with the database.

Parameters

use_scopedBoolean: Whether to use scoped session or not. Use a scoped session if you are using it in a web service like an API. Read more about scoped sessions at: https://docs.sqlalchemy.org/en/13/orm/contextual.html?highlight=scoped
scope_funcfunction: A function which serves as the scope for the session. The session will live only in the scope of that function.

query(*args)[source]¶

Creates a BaseQuery object that allows you to query the database using the SQLAlchemy API, or using the BaseQuery methods like get_or_create

Parameters

argstuple: Args you can pass to SQLALchemy Query class, for example a model.

Examples

# Using SQLAlchemy API
db_conn.query(Probability).all()
# Using db-plugins
db_conn.query(Probability).find(filter_by=**filters)
db_conn.query().get_or_create(model=Object, filter_by=**filters)

Database plugins¶

SQL¶

Initialize database¶

Migrations¶

What migrations can and can’t detect¶

Set database Connection¶

Create model instances¶

Add multiple objects to the database¶

Update instances¶

DatabaseConnection documentation¶

Navigation

Related Topics