sqlalchemy 创建表并设置默认值

models.py

# -*- coding: utf-8 -*-
import datetime
from sqlalchemy import Column, String, Integer, CHAR, TIMESTAMP
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类
Base = declarative_base()


class BaseModel(Base):
    """
    数据基础类
    """
    __abstract__ = True
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    is_delete = Column(Integer, default=0, server_default='0')
    create_time = Column(DateTime, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    # 自动更记录时间戳 需要设置nullable=False
    update_time = Column(TIMESTAMP, nullable=False)


class User(BaseModel):
    # 表的名字
    __tablename__ = 'users'

    # 表的结构
    name = Column(String(20), nullable=False)
    age = Column(Integer, nullable=False)
    sex = Column(CHAR(1), nullable=False)
    
    def __init__(self):
        super().__init__()

main.py

from models import *
from sqlalchemy import  create_engine

engine = create_engine('mysql+mysqlconnector://root:1998423@localhost:3306/lianxi')
#创建表
Base.metadata.create_all(engine)

然后，当我们使用flask-sqlalchemy 的时候 models.py 是这样：

# -*- coding: utf-8 -*-
import datetime
from flask_sqlalchemy import SQLAlchemy

# 这句最好是写在另外一个文件里面方便进行调用
db = SQLAlchemy()

class BaseModel(object):
    """
    数据基础类
    """
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    is_delete = db.Column(db.BOOLEAN, default=False)
    create_time = db.Column(db.DATETIME(6), default=datetime.datetime.now)
    update_time = db.Column(db.DATETIME(6), default=datetime.datetime.now, onupdate=datetime.datetime.now)

额外知识点：

sqlalchemy 执行sql语句

from sqlalchemy import  create_engine, text

engine = create_engine('mysql+mysqlconnector://root:1998423@localhost:3306/lianxi')

with engine.connect() as conn:
    # 声明一个事务块， 也可不声明，若不声明则共同使用同一个事务块
    trans = conn.begin()
    try:
        conn.execute(text("DROP TABLE IF EXISTS `some_table`"))
        conn.execute(text("CREATE TABLE some_table (x int, y int)"))
        conn.execute(text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),[{"x": 1, "y": 1}, {"x": 2, "y": 4}])
        result = conn.execute(text("select * from some_table"))
        for index, row in enumerate(result):
            print(f"x: {row.x}  y: {row.y}")
        trans.commit()
    except:
        trans.rollback()
        print('error')
        raise

sqlalchemy 动态创建表

from sqlalchemy import Column, String, Integer, CHAR, TIMESTAMP, Enum, text, DateTime
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类
Base = declarative_base()


class table_model_cls(Base):
    __abstract__ = True  # 关键语句,定义所有数据库表对应的父类
    __table_args__ = {"extend_existing": True}  # 允许表已存在
    ID = Column(Integer, primary_key=True)
    username = Column(String(24), nullable=False)
    password = Column(String(16), nullable=False, server_default='123456')


def get_table_model_cls(cid, cid_class_dict={}):
    print(1234, cid_class_dict)
    if cid not in cid_class_dict:
        cls_name = table_name = cid
        cls = type(cls_name, (table_model_cls,), {'__tablename__': table_name})
        cid_class_dict[cid] = cls
    return cid_class_dict[cid]


table = get_table_model_cls('table_name')

参考：https://blog.csdn.net/leiwuhen92/article/details/111152965

https://www.osgeo.cn/sqlalchemy/

https://blog.csdn.net/sandorn/article/details/107353862