raise error on insert/update PK?

[Tim Chen]

Let's say I'm using a uuid PK for my models with a ` server_default` set to `gen_random_uuid()` (in PostgreSQL).  Is there a way to ensure the server_default value?  I would like to catch any INSERT or UPDATE statements that set the PK value and raise an error if possible.

[Mike Bayer]

Sure, I would use a before_cursor_execute() handler that does this.
You can see the parameters and the statement coming in and raise an
error if they have column values you don't want to see.

http://docs.sqlalchemy.org/en/latest/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute

this is cleanest using some of the internal state of the context so
here is a demo:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy import event
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
x = Column(Integer)

e = create_engine("sqlite://", echo=True)


@event.listens_for(e, "before_cursor_execute")
def receive_before_cursor_execute(conn, cursor, statement, parameters,
context, executemany):
if context.isinsert:
table = context.compiled.statement.table
for col in table.primary_key:
if col.key in context.compiled.binds:
raise TypeError("no pk allowed!")


Base.metadata.create_all(e)

s = Session(e)
s.add(A(x=5))
s.commit() # OK

s.add(A(id=2, x=7))
s.commit() # not OK






>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Tim Chen]

Thanks so much! Really appreciate the example.

[jens.t...@gmail.com]

Tim,

I wanted to offload the UUID generation for the PK to the server (server_default instead of just default argument). But I wasn’t able to find gen_random_uuid() documented in the PostgreSQL dialect, should probably be under Column Valued Functions?

So I ended up using server_default=text("gen_random_uuid()")

Is that the (currently) recommended way? It seems to work…

Thanks!

Jens

[Simon King]

You ought to be able to use the "sqlalchemy.func" system:

https://docs.sqlalchemy.org/en/14/core/tutorial.html#functions

server_default=sa.func.gen_random_uuid()

Hope that helps,

Simon

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/96f5ba79-1dd5-487f-b493-32f0144289f4n%40googlegroups.com.