SQL Alchemy default depending on other column

ElLorans 0
Viewed 19

In SQL Alchemy, how do I set a default depending on another column which is set with a default value? I need to create a random uuid and assign it to the new user. I then need to create a default contact url that is f'{base_url}/{user_uuid}'. Unlike in SQLAlchemy set default value of one column to that of another column, get_current_parameters does not work here: it cannot access the uuid, as it is not a parameter (and even then it would not be enough, as I would need to prefix it with the base url).

My code so far:

def same_as(column_name: str) -> Callable:
"""
Return function that calls default value from column_name in same table.
param column_name: str valid column of table
:return:
"""

def sql_alchemy_default(context):
    return context.get_current_parameters()[column_name]

return sql_alchemy_default

def create_uuid() -> str:
    """
    Generate random unique str.
    :return: str
    """
    new_uuid = secrets.token_urlsafe(8).lower()
    if db.session.query(User.user_uuid).filter_by(user_uuid=new_uuid).first() is not None:
        return create_uuid()
    return new_uuid


class User(UserMixin, db.Model):
    user_uuid: str = db.Column(db.String(11), default=lambda: create_uuid(), index=True, unique=True)
    contact_url: str = db.Column(db.String(120), default=same_as('user_uuid'))

user = User() assert user.contact_url == 'base_url' + user.user_uuid # will raise error

sqlalchemy python
1 Answers

The same_as function from this answer by vil works for me. To combine the uuid with a string, pass a prefix and combine with the uuid inside same_as. This example uses SQLAlchemy Core, but Flask-SQLAlchemy will work similarly:

import secrets

import sqlalchemy as sa


def create_uuid():
    return secrets.token_urlsafe(8).lower()


def get_uuid(column_name, prefix=''):
    def func(context):
        return prefix + context.get_current_parameters()[column_name]
    return func


tbl = sa.Table(
        't73760822',
        sa.MetaData(),
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('user_uuid', sa.String, default=create_uuid),
        sa.Column('contact_url', sa.String, default=get_uuid('user_uuid')),
        sa.Column('contact_url2', sa.String, default=get_uuid('user_uuid', prefix='foo-')),
)

engine = sa.create_engine('sqlite://', echo=False, future=True)
tbl.create(engine)

with engine.begin() as conn:
    conn.execute(tbl.insert())
    conn.execute(tbl.insert())

with engine.connect() as conn:
    rows = conn.execute(tbl.select())
    for row in rows:
        print(row)

output

(1, 'lj7mzpnnex0', 'lj7mzpnnex0', 'foo-lj7mzpnnex0')
(2, 'pvlatcjwihs', 'pvlatcjwihs', 'foo-pvlatcjwihs')
snakecharmerb0
Related