Python sqlalchemy default value

Содержание

SQLAlchemy-Defaults¶
What does it do?¶
Default value attribute of Column in sqlalchemy
The difference between server_default vs. default
Take a small example
Set the default created time and updated time of the table
Reference documents
Hot Categories
Hot Tags

SQLAlchemy-Defaults¶

SQLAlchemy-Defaults is a plugin for SQLAlchemy that provides smart defaults for lazy guys, like me.

What does it do?¶

By setting default values for your int/str/bool columns, SQLAlchemy-Defaults automatically also sets server_default values
All string columns are not nullable by default.
Unlike SQLAlchemy, all boolean columns are not nullable and False by default.
Provides auto_now feature for datetime columns
Automatically assigns names for enum types which doesn’t have the name set
Easy min/max check constraints based on min and max column info arguments
Auto creates indexes for foreign key columns (very useful when using PostgreSQL)

So instead of writing this:

from datetime import datetime import sqlalchemy as sa class User(Base): id = sa.Column(sa.Integer, primary_key=True) name = sa.Column( sa.Unicode(255), nullable=False ) description = sa.Column( sa.Unicode(255), nullable=False, default=u'', server_default=u'' ) is_admin = sa.Column( sa.Boolean, default=False, server_default=sa.sql.expression.false(), nullable=False ) created_at = sa.Column( sa.DateTime, default=datetime.utcnow, server_default=sa.func.now(), ) hobbies = sa.Column( sa.Integer, ) __table_args__ = ( sa.schema.CheckConstraint( 'user.hobbies >= 1' ), sa.schema.CheckConstraint( 'user.hobbies ) )

You can simply write (notice here how we define an empty __lazy_options__ dict):

import sqlalchemy as sa from sqlalchemy_defaults import Column class User(Base): __lazy_options__ = <> id = Column(sa.Integer, primary_key=True) name = Column( sa.Unicode(255), ) description = Column( sa.Unicode(255), default=u'', ) is_admin = Column( sa.Boolean, ) created_at = Column( sa.DateTime, auto_now=True ) hobbies = Column( sa.Integer, min=1, max=4 )

After you’ve defined all your models you need to make desired mapper lazy configured (or you can simple make all mappers lazy configured by passing sa.orm.mapper as the first argument):

from sqlalchemy_defaults import make_lazy_configured make_lazy_configured(sa.orm.mapper)

Versions latest stable Downloads pdf htmlzip epub On Read the Docs Project Home Builds Free document hosting provided by Read the Docs.

Источник

Default value attribute of Column in sqlalchemy

So that you can session.add() , session.commit(), if the value of this field is not provided, it will be automatically set to 0 to the database.

When I used this class to create a table, I found that sqlalchemy did not set the default value in the table structure.

Through the above log, I can clearly find that in fact, the is_deleted is not set to the default value.

Later, it was found that Column also has a property called server_default, The default value will be set when the table structure generated.

But When the default value of server_default is set

class Person(Base): __tablename__ = 'Person' autoincrement=True, primary_key=True) name = Column(String(length=64), comment='full name') # Set up the server here_ Default value is_deleted = Column(Integer,comment="Delete",default=0,server_default=0) def __repr__(self): return "" % \ (self.id, self.name, self.mobile, )

When the table structure is generated, an error is found as follows:

Argument ‘arg’ is expected to be one of type »

sqlalchemy.exc.ArgumentError: Argument 'arg' is expected to be one of type '' or '' or '', got ''

It’s obvious that the parameter is wrong. I’m lost in thought? Why is my parameter wrong?

check Source code of sqlalchemy.sql.schema.py, server_default requires a variable of type string.

from sqlalchemy import Column, Integer, String, text class Person(Base): __tablename__ = 'Person' autoincrement=True, primary_key=True) name = Column(String(length=64), comment='full name') # Note that only server is set here_ default is_deleted = Column(Integer, comment="Delete", server_default=text('0')) def __repr__(self): return "" % \ (self.id, self.name)

After executing the generate TABLE statement, it is found that the table structure can be generated normally, and the default value is set by default.

All right, everything looks perfect.

Therefore, if you want to define orm and generate the table structure, the default value will be automatically generated, and the server must use server_default, and the field is required to be of string type. You can use text.

The difference between server_default vs. default

In sqlalchemy, there can be two default related fields when defining Column field, one is default and the other is server_default, what’s the difference between them?

View source sqlalchemy.sql.schema.py Column

The default attribute is used to generate the orm object by default. If a field does not have a value, it will use the default value and write it to the database.

server_default attribute requires a str, unicode type. When it is used to generate the table structure, it needs to specify the default value of the field.

Take a small example

Let’s take an example as a demonstration. I create a User’s model class, and then have a field password. I set a default property, and then create a table.

from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, create_engine from sqlalchemy.orm import sessionmaker Base = declarative_base() # Create the connection object and use the pymsql engine conn_str = "mysql+pymysql://:@:3306/?charset=utf8mb4" connect_info = conn_str.format(user='root', pwd='123456', host='127.0.0.1', db_name='db1') engine = create_engine(connect_info, max_overflow=5) session_factory = sessionmaker() session_factory.configure(bind=engine) session = session_factory() class User(Base): __tablename__ = 'User' autoincrement=True, primary_key=True) name = Column(String(length=64), comment='full name') mobile = Column(String(length=64), comment='cell-phone number') password = Column(String(length=64), comment='password', default='0000') def __repr__(self): return "" % \ (self.id, self.name, self.mobile, self.password) def create_table(): # Create table structure Base.metadata.create_all(engine) if __name__ == '__main__': create_table() print("create table successfully ")

After the creation, went to the database to check the table structure and found that password was not given a default value.

Table creation statement is as follow:

CREATE TABLE `User` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) DEFAULT NULL COMMENT 'full name', `mobile` varchar(64) DEFAULT NULL COMMENT 'cell-phone number', `password` varchar(64) DEFAULT NULL COMMENT 'password', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

The User table structure does not generate a default value for password.

Let’s use sqlalchemy to insert a user

if __name__ == '__main__': u = User(name='frank',mobile='123xxxx3456') session.add(u) session.commit() session.close()

The database has no problem, and the password field has been automatically filled with 0000.

When executing sql, when the ORM object does not assign a value to a field, sqlalchemy will check whether the default value of the Column attribute has a value. If there is a value, the current value will be used; otherwise the default value will be set.

Therefore, if you want to set the default value when the table structure is generated, use server_default attribute. In addition the value server_default must be a string.

# The correct setting is is_deleted = Column(Integer, default=0, server_default=text('0'))

If the server is not written_ If the default parameter is used, there is a value when a new object is inserted into the database. However, if you look at the table structure in the database, you will find that there is no default value set for the field on the table.

In addition, server_ The value of default must be a string.

Set the default created time and updated time of the table

Sometimes we want to have a created time and an updated time when a table is created. So we can use server_default attribute.

from sqlalchemy import TIMESTAMP, Boolean, Column, Float from sqlalchemy.ext.declarative import declarative_base base = declarative_base() class Base(base): __abstract__ = True __table_args__ = < 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', 'extend_existing': True >primary_key=True, autoincrement=True) create_time = Column(TIMESTAMP, default=None, nullable=True, server_default=text('CURRENT_TIMESTAMP')) update_time = Column(TIMESTAMP, default=None, nullable=True, server_default=text( 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

Reference documents

Posted by doublesmile at Dec 08, 2020 — 2:59 AM Tag: Database orm

Hot Categories