User Guide¶
DBBase is a base implementation for creating SQLAlchemy models in a similar fashion to Flask-SQLAlchemy, but for use outside of the Flask environment. By using this it provides continuity to the code base, and maintains some the design advantages that Flask-SQLAlchemy implements.
DBBase also embodies default serialization / deserialization services as part of the Model class to cut down on total coding. Since much of the purpose of serialization is the creation of JSON objects that are to be used by JavaScript, it also can automatically convert the keys to camelCase (and back) as well.
Introduction¶
SQLAlchemy’s design implements a clear separation between the session object, connections to a database, and the table definitions, the model. With such a separation, normal database usage involves carrying the session object into each program or function and combining it with the table class model for the appropriate actions.
Flask-SQLAlchemy uses SQLAlchemy, but provides an alternate approach to the design by embodying the session object into a primary database object (usually db), and suffusing the query into the table models as well. By doing so, many of the tools for interacting with the database are present and available within a table class.
DBBase maintains some continuity with the Flask-SQLAlchemy coding style.
Database Connections¶
Connecting to a database can be done as follows:
from dbbase import DB
db = DB(config, model_class=None, checkfirst=True, echo=False)
The config is the database URI. There is a convenience function, db_config that can help configure this URI. For example:
from dbbase import DB, db_config
config = db_config(
base="postgresql://{user}:{pass}@{host}:{port}/{dbname}",
config_vars={
'user': 'auser',
'pass': '123',
'host': 'localhost',
'port': 5432,
'dbname': 'mydatadb'
}
)
db = DB(config=config, checkfirst=True, echo=False)
# create any tables that are new, create a session object
# that is found at db.session
db.create_all()
db_config just combines a base string with dict of variables, resulting in a string that is fed into DB to configure the connection. So it can be complicated if necessary, but probably will not have to be.
model_class can substitute a different Model than this package, but it is expected that normally the default would suffice.
checkfirst if True, will not recreate a table if it already exists in the database.
echo if True logs the database interactions.
Unlike Flask-SQLAlchemy, DB does not default to an environment variable SQLALCHEMY_DATABASE_URI. However, you could still use it by extracting it from the environment and feeding it into DB.
Model Interactions¶
The following code compares typical table definition styles:
# SQLAlchemy
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(30), nullable=False)
addresses = relationship(
"Address", backref="user", lazy='immediate')
# Flask-SQLAlchemy
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), nullable=False)
addresses = db.relationship(
"Address", backref="user", lazy='immediate')
# DBBase
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), nullable=False)
addresses = db.relationship(
"Address", backref="user", lazy='immediate')
Now if the only difference is a standard as to whether to explicitly import Column, and other classes, or have it ride around on db, no one would care.
The differences can be seen when interacting with the database. There is a ready conduit to the database within each class. For example, below shows filtering under all three scenarios.
# SQLAlchemy
qry = session.query(User).filter_by(name='Bob').all()
# Flask-SQLAlchemy
qry = User.query.filter_by(name='Bob').all()
# DBBase
qry = User.query.filter_by(name='Bob').all()
The query object is conveniently available for use in the Model class.
First, we will create a user:
# both Flask-SQLAlchemy and DBBase
user = User(name='Bob')
db.session.add(user)
db.session.commit()
DBBase also has a reference for convenience to the db variable within the Model class and the object instance.
# or DBBase via Model class
user = User(name='Bob')
User.db.session.add(user)
User.db.session.commit()
# or instance object
user = User(name='Bob')
user.db.session.add(user)
user.db.session.commit()
Or, saving can be done via:
# DBBase
user = User(name='Bob')
User.save()
# or even shorter
user = User(name='Bob').save()
Deletion can also be done via the instance.
# DBBase
user = User(name='Bob')
User.save()
# then delete
user.delete()
Record Validation¶
A minor check can be performed prior to saving a record to ensure that all required fields have values. If there are any default values for the table, those will be ignored, but otherwise you can get a quick list of required columns without values.
status, errors = self.validate_record()
if status:
self.save()
else:
return errors
Suppose you have a user table with required first and last names. A user is created, but for some reason the last name is not filled in.
status, errors = user.validate_record()
>> False, {"missing_values": ["last_name"]}
For consistency when communicating from an API to a front end application, a conversion to camel case can be done as well.
status, errors = user.validate_record(camel_case=True)
>> False, {"missingValues": ["lastName"]}
Caveat
DBBase objects provide access to the SQLAlchemy query object, not the Flask-SQLAlchemy query object. Therefore you would not expect User.query.get_or_404 to be available.
Serialization¶
For convenience building RESTful APIs, a default serialization function is available for outputting JSON style strings. In addition, by default it converts the keys to camelCase style to correspond to JavaScript conventions.
To illustrate some of the features, we will look at two examples: The first will be two tables, one for users and one for addresses
After the initial import and db creation, we create two tables. The users table has a relationship with addresses where the user_id entered into the address table must be found in the users table.
As an aside, note that the password column is a WriteOnlyColumn for discussion later.
# create db that is sqlite in memory
from dbbase import DB
db = DB(config=':memory:')
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(50), nullable=False)
last_name = db.Column(db.String(50), nullable=False)
password = db.WriteOnlyColumn(db.String, nullable=False)
addresses = db.relationship(
"Address", backref="user", lazy='immediate')
def full_name(self):
return '{first} {last}'.format(
first=self.first_name, last=self.last_name)
class Address(db.Model):
__tablename__ = 'addresses'
id = db.Column(db.Integer, primary_key=True)
email_address = db.Column(db.String, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
db.create_all()
user = User(first_name='Bob', last_name='Smith')
user.save()
address1 = Address(
email_address='email1@example.com',
user_id=user.id
)
address2 = Address(
email_address='email2@example.com',
user_id=user.id
)
db.session.add(address1)
db.session.add(address2)
db.session.commit()
Accordingly, it makes sense that when the user data is to be pulled from an API the relevant addresses be included.
>>> print(user.serialize(indent=2))
{
"addresses": [
{
"userId": 3,
"emailAddress": "email1@example.com",
"id": 1
},
{
"userId": 3,
"emailAddress": "email2@example.com",
"id": 2
}
],
"lastName": "Smith",
"fullName": "Bob Smith",
"id": 3,
"firstName": "Bob"
}
The default serialization opts for the keys to be put into camelCase. In addition, it walks the object dictionary and recursively evaluates any relationships as well, under the assumption that it would minimize the number of trips to the API from the front end.
WriteOnlyColumn¶
The user table above includes a password column using a db.WriteOnlyColumn. By using this type of column, a table can automatically exclude that column from serialization once the value has been filled in. This avoids an awkward mistake accidentally outputting even an encrypted password, yet including the field when None for forms to a front-end application adding a new user.
Controlling Serialization¶
You have the ability to limit or expand the items that are included.
SERIAL_STOPLIST is a Model class variable that is a list of fields to ignore
SERIAL_FIELDS is a Model class variable that is a list of fields that would be included. Additional methods can be included in this list to enable fields like fullname in place of first_name and last_name.
SERIAL_FIELD_RELATIONS is a Model class variable that is a dictionary. With this variable you can specify what fields a relation will show. While you could go to the table definition for that relation and specify it directly, using the SERIAL_FIELD_RELATIONS variable enables you to show the specific fields appropriate for when that relationship is included with the current table, but have a standard method when show the relation table directly. An example below will help explain that further.
serialize of course can be overwritten in your class model so if either method is not right for your situation it is easy enough to set right for that particular class yet use the defaults for other tables.
To reduce ambiguity, if SERIAL_FIELDS is used, serialization assumes that the list is explicitly what you want, and ignores the SERIAL_STOPLIST.
From examining the output that we have above, suppose we decide that we will just present the full name and not first_name and last_name.
In that case would do the following:
class User(db.Model):
__tablename__ = 'users'
SERIAL_STOPLIST = ['first_name', 'last_name']
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(50), nullable=False)
last_name = db.Column(db.String(50), nullable=False)
addresses = db.relationship(
"Address", backref="user", lazy='immediate')
def full_name(self):
return '{first} {last}'.format(
first=self.first_name, last=self.last_name)
Now when we run serialize, the fields first_name and last_name are filtered out.
>>> print(user.serialize(indent=2))
{
"addresses": [
{
"userId": 3,
"emailAddress": "email1@example.com",
"id": 1
},
{
"userId": 3,
"emailAddress": "email2@example.com",
"id": 2
}
],
"id": 3,
"fullName": "Bob Smith"
}
Now since user_id in addresses is redundant, we can also filter that out. Let us remove id from addresses as well. We could do this by adding both to a stop list by:
Address.SERIAL_STOPLIST = ['id', 'user_id']
But instead, we can minimize our typing by instead adding just the email address to SERIAL_FIELDS. As in the following:
class Address(db.Model):
__tablename__ = 'addresses'
SERIAL_FIELDS = ['email_address']
id = db.Column(db.Integer, primary_key=True)
email_address = db.Column(db.String, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
Running user.serialize() again we have a more compact result
>>> print(user.serialize(indent=2))
{
"addresses": [
{
"emailAddress": "email1@example.com"
},
{
"emailAddress": "email2@example.com"
}
],
"id": 3,
"fullName": "Bob Smith"
}
Dictionary keys have not been guaranteed to print in a particular order, although that is changing. If the order of the keys in serialization are important in your application, you can control that by putting those variables in SERIAL_FIELDS.
User.SERIAL_FIELDS = ['id', 'first_name', 'last_name', 'addresses']
>>> print(user.serialize(indent=2))
{
"id": 3,
"firstName": "Bob",
"lastName": "Smith",
"addresses": [
{
"userId": 3,
"emailAddress": "email1@example.com",
"id": 1
},
{
"userId": 3,
"emailAddress": "email2@example.com",
"id": 2
}
]
}
Example of SERIAL_FIELD_RELATIONS
In the example above we controlled the output for Address by using Address.SERIAL_FIELDS = [‘email_address’]. That means that any time an API would call for an addres only the email address would be returned. If it is only going to be returned in conjunction with a user, that may be acceptable. However, there are many secondary relationships where more control would be helpful.
In the following code block we will see our tables created again, but with the use of the SERIAL_FIELD_RELATIONS variable to help us to a more refined output.
class User(db.Model):
__tablename__ = 'users'
SERIAL_STOPLIST = ['first_name', 'last_name']
SERIAL_FIELD_RELATIONS = {
"Address": ["id", "email_address"]
}
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(50), nullable=False)
last_name = db.Column(db.String(50), nullable=False)
addresses = db.relationship(
"Address", backref="user", lazy='immediate')
def full_name(self):
return '{first} {last}'.format(
first=self.first_name, last=self.last_name)
class Address(db.Model):
__tablename__ = 'addresses'
id = db.Column(db.Integer, primary_key=True)
email_address = db.Column(db.String, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
>>> print(user.serialize(indent=2))
{
"id": 3,
"firstName": "Bob",
"lastName": "Smith",
"addresses": [
{
"emailAddress": "email1@example.com",
"id": 1
},
{
"emailAddress": "email2@example.com",
"id": 2
}
]
}
When a field is found to be an instance of a class that is a key in the SERIAL_FIELD_RELATIONS dictionary, the corresponding list is used for that class in place of the default class.
Ad Hoc Variables
Both the serialize and to_dict functions have parameters of serial_fields and serial_field_relations. If not used, the default class variables are used. However, with these variables, you can generate custom serializations on the fly to better match specific requirements.
A natural fall-out of this approach means also that such things as views can be easily created for different audiences.
Recursive Serialization¶
For this next section, let us start by first revoking the stop lists and serial lists that we have and take a look at the process in a different way.
# assume the class variables are set on the defaults in their class definitions.
User.SERIAL_FIELDS = None
User.SERIAL_STOPLIST = None
User.SERIAL_FIELD_RELATIONS = None
Address.SERIAL_FIELDS = None
Address.SERIAL_STOPLIST = None
Address.SERIAL_FIELD_RELATIONS = None
>>> print(user.serialize(indent=2))
{
"addresses": [
{
"userId": 3,
"emailAddress": "email1@example.com",
"id": 1
},
{
"userId": 3,
"emailAddress": "email2@example.com",
"id": 2
}
],
"lastName": "Smith",
"fullName": "Bob Smith",
"id": 3,
"firstName": "Bob"
}
As a note, if you change such class variables on the fly such as User.SERIAL_FIELDS, you might well have unintended effects.
See how the address serialization digs back into the user object. This is due to the relationship that Address has with User. But, serializatin does not go back to User once again when you run user.serialize(). The reason is that are there are limits in place to avoid going into an endless loop.
However, there are situations where it is entirely desirable.
We now create a table for holding network nodes. A node can be connected to other nodes in a relationship to form tree structures for example. Because of that, the relationships are self-referential. Where in the example above, we needed to stop serialization before it turns back in on itself, now we want to follow the relationships all the way down.
To show this we first create the table and a few nodes, and connect them together. Let’s model .. code-block:
node 1
| |
node 2 node 5
| | |
node 3 node 4 node 6
class Node(db.Model):
"""self-referential table"""
__tablename__ = 'nodes'
id = db.Column(db.Integer, primary_key=True)
parent_id = db.Column(db.Integer, db.ForeignKey('nodes.id'))
data = db.Column(db.String(50))
children = db.relationship(
"Node",
lazy="joined",
order_by="Node.id",
join_depth=10)
SERIAL_FIELDS = ['id', 'parent_id', 'data', 'children']
db.create_all()
node1 = Node(id=1, data='this is node1')
node2 = Node(id=2, data='this is node2')
node3 = Node(id=3, data='this is node3')
node4 = Node(id=4, data='this is node4')
node5 = Node(id=5, data='this is node5')
node6 = Node(id=6, data='this is node6')
db.session.add(node1)
db.session.commit()
node1.children.append(node2)
db.session.commit()
node2.children.append(node3)
db.session.commit()
node2.children.append(node4)
db.session.commit()
node1.children.append(node5)
db.session.commit()
node5.children.append(node6)
db.session.commit()
So the nodes are all linked up with node1 as the root. So when we serialize node 1 we get:
>>> node1.serialize(indent=2)
{
"id": 1,
"parentId": null,
"data": "this is node1",
"children": [
{
"id": 2,
"parentId": 1,
"data": "this is node2",
"children": [
{
"id": 3,
"parentId": 2,
"data": "this is node3",
"children": []
},
{
"id": 4,
"parentId": 2,
"data": "this is node4",
"children": []
}
]
},
{
"id": 5,
"parentId": 1,
"data": "this is node5",
"children": [
{
"id": 6,
"parentId": 5,
"data": "this is node6",
"children": []
}
]
}
]
}
By the way, showing examples of serialization in printed form is much better if the JSON version is indented. In fact, the default is the more compact form to reduce overall size of the output. For example, the above emitted from an API would be:
>>> node1.serialize()
{“id”: 1, “parentId”: null, “data”: “this is node1”, “children”: [{“id”: 2, “parentId”: 1, “data”: “this is node2”, “children”: [{“id”: 3, “parentId”: 2, “data”: “this is node3”, “children”: []}, {“id”: 4, “parentId”: 2, “data”: “this is node4”, “children”: []}]}, {“id”: 5, “parentId”: 1, “data”: “this is node5”, “children”: [{“id”: 6, “parentId”: 5, “data”: “this is node6”, “children”: []}]}]}
Document Dictionaries¶
Document Dictionaries introspect the model classes you have built and present the data objects in a format similar to Swagger / OpenAPI. This enables a method for communicating the details of the models. In addition, the functions could be wrapped into parsing functions that evaluate query strings and form data, directly from the table characteristics. This avoids the extra work of defining tables, and then coding a separate schema just to evaluate incoming and outgoing data. Finally, the doc functions could be used as a basis for unit/integration tests to ensure that all the requirements for the data have been met.
Table Documentation¶
Suppose we were to describe the two tables created above, User, and Address.
>>> db.doc_tables(to_camel_case=True)
This would yield the following output that details object models similarly to OpenApi. It details the column characteristics for each table. Note how the foreign key is annotated in Address. If defaults had been defined for the tables, it would detail the default type including server defaults.
In the example shown, the column names have been converted to camel case. Front end developers, used to JavaScript notation may be more comfortable in this format.
{
"definitions": {
"Address": {
"type": "object",
"properties": {
"id": {
"type": "integer",
"format": "int32",
"primary_key": true,
"nullable": false,
"info": {}
},
"email_address": {
"type": "string",
"nullable": false,
"info": {}
},
"user_id": {
"type": "integer",
"format": "int32",
"nullable": true,
"foreign_key": "users.id",
"info": {}
},
"user": {
"readOnly": false,
"relationship": {
"type": "single",
"entity": "User",
"fields": {
"id": {
"type": "integer",
"format": "int32",
"primary_key": true,
"nullable": false,
"info": {}
},
"full_name": {
"readOnly": true
}
}
}
}
},
"xml": "Address"
},
"User": {
"type": "object",
"properties": {
"id": {
"type": "integer",
"format": "int32",
"primary_key": true,
"nullable": false,
"info": {}
},
"addresses": {
"readOnly": false,
"relationship": {
"type": "list",
"entity": "Address",
"fields": {
"id": {
"type": "integer",
"format": "int32",
"primary_key": true,
"nullable": false,
"info": {}
},
"email_address": {
"type": "string",
"nullable": false,
"info": {}
},
"user_id": {
"type": "integer",
"format": "int32",
"nullable": true,
"foreign_key": "users.id",
"info": {}
}
}
}
},
"full_name": {
"readOnly": true
}
},
"xml": "User"
}
}
}
Filtering for Form Data and Query Strings¶
If you have used Flask-Restful, one of the features involves argument parsing of form data, query strings, etc. This involves entering arguments such as:
parser = reqparse.RequestParser()
parser.add_argument("id", type=int, required=False)
parser.add_argument("firstName", type=str, required=True)
parser.add_argument("lastName", type=str, required=True)
The kind of characteristics necessary to process that data is already available from the above objects. The creators of Flask-Restful have indicated that they are going to eventually drop support of the reqparse class would does the above.
Using generated objects such as above can be wrapped into a parsing mechanism reducing the need for a duplication of effort. You have already done the work of carefully crafting your tables. Using the document dictionaries you can get more mileage out of it.
A Larger Example of a Document Dictionary¶
To get an idea of the range of support it helps to see a more complicated table. The following table is basically a zoo of different combinations of column types and parameters.
status_codes = [
[0, "New"],
[1, "Active"],
[2, "Suspended"],
[3, "Inactive"],
]
class StatusCodes(types.TypeDecorator):
"""
Status codes are entered as strings and converted to
integers when saved to the database.
"""
impl = types.Integer
def __init__(self, status_codes, **kw):
self.choices = dict(status_codes)
super(StatusCodes, self).__init__(**kw)
def process_bind_param(self, value, dialect):
"""called when saving to the database"""
return [
k
for k, v in self.choices.items()
if v == value
][0]
def process_result_value(self, value, dialect):
"""called when pulling from database"""
return self.choices[value]
class BigTable(db.Model):
"""Test class with a variety of column types"""
__tablename__ = "main"
id = db.Column(
db.Integer,
primary_key=True,
nullable=True,
comment="Primary key with a value assigned by the database",
info={"extra": "info here"},
)
status_id = db.Column(
StatusCodes(status_codes),
nullable=False,
comment="Choices from a list. String descriptors "
"change to integer upon saving. Enums without the headache.",
)
@db.orm.validates("status_id")
def _validate_id(self, key, id):
"""_validate_id
Args:
id: (int) : id must be in cls.choices
"""
if id not in dict(status_codes):
raise ValueError("{} is not valid".format(id))
return id
# nullable / not nullable
name1 = db.Column(
db.String(50), nullable=False, comment="This field is required"
)
name2 = db.Column(
db.String(50),
nullable=True,
comment="This field is not required",
)
# text default
name3 = db.Column(
db.Text,
default="test",
nullable=False,
comment="This field has a default value",
index=True,
)
item_length = db.Column(
db.Float, nullable=False, comment="This field is a float value"
)
item_amount = db.Column(db.Numeric(17, 6), default=0.0)
# integer and default
some_small_int = db.Column(
db.SmallInteger,
default=0,
nullable=False,
comment="This field is a small integer",
)
some_int = db.Column(
db.Integer,
default=0,
nullable=False,
comment="This field is a 32 bit integer",
)
some_big_int = db.Column(
db.BigInteger,
default=0,
nullable=False,
comment="This field is a big integer",
)
fk_id = db.Column(
db.Integer,
db.ForeignKey("other_table.id"),
nullable=False,
comment="This field is constrained by a foreign key on"
"another table",
)
today = db.Column(
db.Date,
doc="this is a test",
info={"test": "this is"},
comment="This field defaults to today, created at model level",
default=date.today,
)
created_at1 = db.Column(
db.DateTime,
default=datetime.now,
comment="This field defaults to now, created at model level",
)
created_at2 = db.Column(
db.DateTime,
server_default=db.func.now(),
comment="This field defaults to now, created at the server"
"level",
)
update_time1 = db.Column(
db.DateTime,
onupdate=datetime.now,
comment="This field defaults only on updates",
)
update_time2 = db.Column(
db.DateTime,
server_onupdate=db.func.now(),
comment="This field defaults only on updates, but on the"
"server",
)
unique_col = db.Column(
db.String(20),
unique=True,
comment="This must be a unique value in the database.",
)
# adapted from sqlalchemy docs
abc = db.Column(
db.String(20),
server_default="abc",
comment="This field defaults to text but on the server.",
)
index_value = db.Column(
db.Integer,
server_default=db.text("0"),
comment="This field defaults to an integer on the server.",
)
__table_args = (
db.Index("ix_name1_name2", "name1", "name2", unique=True),
)
class OtherTable(db.Model):
__tablename__ = "other_table"
id = db.Column(db.Integer, primary_key=True, nullable=True)
..
From that table we can generation our document dictionary:
{
"BigTable": {
"type": "object",
"properties": {
"item_amount": {
"type": "numeric(17, 6)",
"nullable": true,
"default": {
"for_update": false,
"arg": 0.0
},
"info": {}
},
"name2": {
"type": "string",
"maxLength": 50,
"nullable": true,
"comment": "This field is not required",
"info": {}
},
"abc": {
"type": "string",
"maxLength": 20,
"nullable": true,
"server_default": {
"for_update": false,
"arg": "abc",
"reflected": false
},
"comment": "This field defaults to text but on the server.",
"info": {}
},
"fk_id": {
"type": "integer",
"format": "int32",
"nullable": false,
"foreign_key": "other_table.id",
"comment": "This field is constrained by a foreign key onanother table",
"info": {}
},
"some_small_int": {
"type": "integer",
"format": "int8",
"nullable": false,
"default": {
"for_update": false,
"arg": 0
},
"comment": "This field is a small integer",
"info": {}
},
"some_int": {
"type": "integer",
"format": "int32",
"nullable": false,
"default": {
"for_update": false,
"arg": 0
},
"comment": "This field is a 32 bit integer",
"info": {}
},
"update_time2": {
"type": "date-time",
"nullable": true,
"server_onupdate": {
"for_update": true,
"arg": "db.func.now()",
"reflected": false
},
"comment": "This field defaults only on updates, but on theserver",
"info": {}
},
"index_value": {
"type": "integer",
"format": "int32",
"nullable": true,
"server_default": {
"for_update": false,
"arg": "db.text(\"0\")",
"reflected": false
},
"comment": "This field defaults to an integer on the server.",
"info": {}
},
"update_time1": {
"type": "date-time",
"nullable": true,
"onupdate": {
"for_update": true,
"arg": "datetime.now"
},
"comment": "This field defaults only on updates",
"info": {}
},
"today": {
"type": "date",
"nullable": true,
"default": {
"for_update": false,
"arg": "date.today"
},
"doc": "this is a test",
"comment": "This field defaults to today, created at model level",
"info": {
"test": "this is"
}
},
"id": {
"type": "integer",
"format": "int32",
"primary_key": true,
"nullable": true,
"comment": "Primary key with a value assigned by the database",
"info": {
"extra": "info here"
}
},
"created_at1": {
"type": "date-time",
"nullable": true,
"default": {
"for_update": false,
"arg": "datetime.now"
},
"comment": "This field defaults to now, created at model level",
"info": {}
},
"name1": {
"type": "string",
"maxLength": 50,
"nullable": false,
"comment": "This field is required",
"info": {}
},
"created_at2": {
"type": "date-time",
"nullable": true,
"server_default": {
"for_update": false,
"arg": "db.func.now()",
"reflected": false
},
"comment": "This field defaults to now, created at the serverlevel",
"info": {}
},
"some_big_int": {
"type": "integer",
"format": "int64",
"nullable": false,
"default": {
"for_update": false,
"arg": 0
},
"comment": "This field is a big integer",
"info": {}
},
"unique_col": {
"type": "string",
"maxLength": 20,
"nullable": true,
"unique": true,
"comment": "This must be a unique value in the database.",
"info": {}
},
"item_length": {
"type": "float",
"nullable": false,
"comment": "This field is a float value",
"info": {}
},
"status_id": {
"type": "integer",
"format": "int32",
"choices": {
"0": "New",
"1": "Active",
"2": "Suspended",
"3": "Inactive"
},
"nullable": false,
"comment": "Choices from a list. String descriptors change to integer upon saving. Enums without the headache.",
"info": {}
},
"name3": {
"type": "text",
"nullable": false,
"default": {
"for_update": false,
"arg": "test"
},
"index": true,
"comment": "This field has a default value",
"info": {}
}
},
"xml": "BigTable"
}
}
This output illustrates a range of some of what is available and how the parameters are formatted.
BigTable:
type: object
properties:
abc:
comment: This field defaults to text but on the server.
info: {}
maxLength: 20
nullable: true
server_default:
arg: abc
for_update: false
reflected: false
type: string
created_at1:
comment: This field defaults to now, created at model level
default:
arg: datetime.now
for_update: false
info: {}
nullable: true
type: date-time
created_at2:
comment: This field defaults to now, created at the serverlevel
info: {}
nullable: true
server_default:
arg: db.func.now()
for_update: false
reflected: false
type: date-time
fk_id:
comment: This field is constrained by a foreign key onanother table
foreign_key: other_table.id
format: int32
info: {}
nullable: false
type: integer
id:
comment: Primary key with a value assigned by the database
format: int32
info:
extra: info here
nullable: true
primary_key: true
type: integer
index_value:
comment: This field defaults to an integer on the server.
format: int32
info: {}
nullable: true
server_default:
arg: db.text("0")
for_update: false
reflected: false
type: integer
item_amount:
default:
arg: 0.0
for_update: false
info: {}
nullable: true
type: numeric(17, 6)
item_length:
comment: This field is a float value
info: {}
nullable: false
type: float
name1:
comment: This field is required
info: {}
maxLength: 50
nullable: false
type: string
name2:
comment: This field is not required
info: {}
maxLength: 50
nullable: true
type: string
name3:
comment: This field has a default value
default:
arg: test
for_update: false
index: true
info: {}
nullable: false
type: text
some_big_int:
comment: This field is a big integer
default:
arg: 0
for_update: false
format: int64
info: {}
nullable: false
type: integer
some_int:
comment: This field is a 32 bit integer
default:
arg: 0
for_update: false
format: int32
info: {}
nullable: false
type: integer
some_small_int:
comment: This field is a small integer
default:
arg: 0
for_update: false
format: int8
info: {}
nullable: false
type: integer
status_id:
choices:
0: New
1: Active
2: Suspended
3: Inactive
comment: Choices from a list. String descriptors change to integer upon
saving. Enums without the headache.
format: int32
info: {}
nullable: false
type: integer
today:
comment: This field defaults to today, created at model level
default:
arg: date.today
for_update: false
doc: this is a test
info:
test: this is
nullable: true
type: date
unique_col:
comment: This must be a unique value in the database.
info: {}
maxLength: 20
nullable: true
type: string
unique: true
update_time1:
comment: This field defaults only on updates
info: {}
nullable: true
onupdate:
arg: datetime.now
for_update: true
type: date-time
update_time2:
comment: This field defaults only on updates, but on theserver
info: {}
nullable: true
server_onupdate:
arg: db.func.now()
for_update: true
reflected: false
type: date-time
xml: BigTable
DBBase is compatible with Python >=3.6 and is distributed under the MIT license.