Re: [sqlalchemy] raise error on insert/update PK?

[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

On Fri, Nov 19, 2021 at 6:21 AM jens.t...@gmail.com
 wrote:
>
> 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
>
> On Thursday, January 4, 2018 at 7:50:21 AM UTC+10 timc...@gmail.com wrote:
>>
>> Thanks so much! Really appreciate the example.
>>
>>
>>
>>
>> On Wednesday, January 3, 2018 at 3:46:47 PM UTC-5, Mike Bayer wrote:
>>>
>>> On Wed, Jan 3, 2018 at 1:18 PM, Tim Chen  wrote:
>>> > 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.
>>>
>>> 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.
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/96f5ba79-1dd5-487f-b493-32f0144289f4n%40googlegroups.com.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfO7aCbih4%2BWOPdScCsm5t99gVR1yXUJ-iCz65yurdCCg%40mail.gmail.com.

Re: [sqlalchemy] raise error on insert/update PK?

[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

On Thursday, January 4, 2018 at 7:50:21 AM UTC+10 timc...@gmail.com wrote:

> Thanks so much! Really appreciate the example.
>
>
>
>
> On Wednesday, January 3, 2018 at 3:46:47 PM UTC-5, Mike Bayer wrote:
>>
>> On Wed, Jan 3, 2018 at 1:18 PM, Tim Chen  wrote: 
>> > 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. 
>>
>> 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. 
>>
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/96f5ba79-1dd5-487f-b493-32f0144289f4n%40googlegroups.com.

Re: [sqlalchemy] raise error on insert/update PK?

[Tim Chen]

Thanks so much! Really appreciate the example.



On Wednesday, January 3, 2018 at 3:46:47 PM UTC-5, Mike Bayer wrote:
>
> On Wed, Jan 3, 2018 at 1:18 PM, Tim Chen  
> wrote: 
> > 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. 
>
> 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. 
>

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] raise error on insert/update PK?

[Mike Bayer]

On Wed, Jan 3, 2018 at 1:18 PM, Tim Chen  wrote:
> 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.

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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] 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.

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.