SQLAlchemy Tips

sqlalchemy-tips.md

SQLAlchemy Tips

查询返回的数据集非常大

文档

q = sess.query(Object).yield_per(100).enable_eagerloads(False)
for x in q:
    pass

sqlalchemy table reflection

Reflecting Database Objects

from sqlalchemy import inspect
def get_table_info(table_cls):
    """get sqlalchemy table info by table schema class name"""
    mapper = inspect(table_cls)
    info = defaultdict(list)
    for c in mapper.columns:
        if c.primary_key:
            info['primary_key'].append(c)
        elif c.foreign_keys:
            info['foreign_keys'].append(c)
        elif not c.nullable:
            info['required'].append(c)
        elif c.nullable or c.default:
            info['option'].append(c)

        if c.default:
            info['has_default'].append(c)

    return mapper.columns, info

alembic

alembic migrate Enum 已经存在

在 postgresql 中碰到该文件，解决方法

from sqlalchemy.dialects.postgresql import ENUM

sa.Column('pay_channel', ENUM('WECHAT', 'ALIPAY', 'BALANCE', name='paychannel', create_type=False), nullable=False)

添加一列并设置默认值

数据库的表中已经存在一些数据了，所以 server_default='value' 让已有数据也正确的设置默认值。

参考：https://gist.github.com/zzzeek/6600104

def upgrade():
    op.add_column("foo", sa.Column('data', sa.String(50), nullable=False, server_default='value'))

另一种方法：https://coderwall.com/p/8zw7bq/modifying-nullable-columns-in-alembic

from sqlalchemy.sql import table, column

def upgrade():
    op.add_column('role', sa.Column('role_name', sa.String(length=30), nullable=True))
    role = table('role', column('role_name'))
    op.execute(role.update().values(role_name=''))
    op.alter_column('role', 'role_name', nullable=False)