Re: [sqlalchemy] How to disable the integer col + pk = serial behaviour?

[Mike Bayer]

this is also exactly how its documented, it explicitly mentions PostgreSQL and 
SERIAL, see below.  How did you come to be using the optional=True flag 
otherwise?   

https://docs.sqlalchemy.org/en/13/core/defaults.html?highlight=sequence#sqlalchemy.schema.Sequence.params.optional

"boolean value, when `True`, indicates that this `Sequence` 

 object only needs to be explicitly generated on backends that don’t provide 
another way to generate primary key identifiers. Currently, it essentially 
means, “don’t create this sequence on the PostgreSQL backend, where the SERIAL 
keyword creates a sequence for us automatically”.

On Wed, Aug 5, 2020, at 9:21 PM, Mike Bayer wrote:
> 
> 
> On Wed, Aug 5, 2020, at 7:54 PM, zsol...@gmail.com wrote:
>> Thanks for all the answers.
>> 
>> > add autoincrement=False to the Column
>> 
>> This is actually all I needed, but possibly my findings can help others or 
>> provide improvements.
>> 
>> I'm not using drop_alll and create_all, but table.create() and this results 
>> in the "relation "some_seq" already exists" error.
>> 
>> 99% your snippet, just using create:
>> 
>> from sqlalchemy import Column
>> from sqlalchemy import Integer
>> from sqlalchemy import MetaData
>> from sqlalchemy import Sequence
>> from sqlalchemy import Table
>> 
>> m = MetaData()
>> 
>> seq = Sequence("some_seq")
>> t1 = Table("t1", m, Column("id", Integer, seq, primary_key=True))
>> t2 = Table("t2", m, Column("id", Integer, seq, primary_key=True))
>> 
>> t1.create(pg_engine)
>> t2.create(pg_engine)**
>> 
>> > To render "IF NOT EXISTS" then just invoke the SQL:  
>> > conn.execute(text("CREATE SEQ IF NOT EXISTS ..."))
>> 
>> So in this example, how could I avoid creating the sequence? I'd be happy to 
>> create it once, or not at all, but it seems that table create tries to 
>> create the sequence automatically, no matter what.
> 
> send checkfirst=True to each create() call.
> 
>> 
>> The schema bug was actually the behaviour of the optional=True. I still 
>> don't know what it does, but it behaves like if the whole sequence line 
>> wouldn't be present. It just makes the column a SERIAL type and then 
>> PostgreSQL auto-creates the sequence in the table's schema, not in public. 
>> So with optional=True basically nothing matters, nor the sequence's name, 
>> not the sequence's schema?
> 
> optional=True is used for when you want your database table to have a 
> SEQUENCE created on Oracle and Firebird, which until recently have no syntax 
> for an implicit primary key generator, but on all other databases you don't 
> want an explicit SEQUENCE created.This essentially means, "don't create 
> the sequence on PostgreSQL, use SERIAL instead".
> 
> This dynamic is going to be changing soon as most of these databases are now 
> supporting the GENERATED AS IDENTITY syntax and we'll be adding support for 
> that in 1.4.
> 
> 
> 
> 
> 
>> 
>> Zsolt
>> 
>> 
>> 
>> 
>> 
>> 
>> On Wednesday, 5 August 2020 at 17:36:00 UTC+2 Mike Bayer wrote:
>>> 
>>> 
>>> On Wed, Aug 5, 2020, at 9:25 AM, Zsolt Ero wrote:
 But this would create a different id for each table, wouldn't it? 
>>> 
>>> 
>>> if you want two tables to have the same sequence then use one Sequence 
>>> object for both.   If the Sequence is present on the Column it will not 
>>> create SERIAL.  I've tried to document this right at the top here: 
>>> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity
>>>   .  Here's a POC with all the things I think you've mentioned so far:
>>> 
>>> from sqlalchemy import Column
>>> from sqlalchemy import create_engine
>>> from sqlalchemy import Integer
>>> from sqlalchemy import MetaData
>>> from sqlalchemy import Sequence
>>> from sqlalchemy import Table
>>> 
>>> 
>>> m = MetaData(schema="test_schema")
>>> 
>>> seq = Sequence("some_seq", schema="public")
>>> t1 = Table(
>>> "t1",
>>> m,
>>> Column(
>>> "id", Integer, seq, primary_key=True
>>> ),
>>> )
>>> 
>>> t2 = Table(
>>> "t2",
>>> m,
>>> Column(
>>> "id", Integer, seq, primary_key=True
>>> ),
>>> )
>>> 
>>> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
>>> 
>>> m.drop_all(e)
>>> m.create_all(e)
>>> 
>>> with e.connect() as conn:
>>> conn.execute(t1.insert())
>>> conn.execute(t2.insert())
>>> 
>>> assert conn.scalar(seq.next_value()) == 3
>>> 
>>> 
>>> 
 
 
 
 
 I'd
 like to use the same ids for matching rows, such that table A's
 primary key is the same as table B's primary key, so that they can
 join-ed together like when it was a single table.
 
 So far the only solution I found is to remove primary_key=True and
 issue an ALTER TABLE ... ADD PRIMARY KEY (...) command manually.
 
 
 
 On Wed, 5 Aug 2020 at 15:20, Mike Bayer  wrote:
 >
 >
 >
 > On Wed, Aug 5, 

Re: [sqlalchemy] How to disable the integer col + pk = serial behaviour?

[Mike Bayer]

On Wed, Aug 5, 2020, at 7:54 PM, zsol...@gmail.com wrote:
> Thanks for all the answers.
> 
> > add autoincrement=False to the Column
> 
> This is actually all I needed, but possibly my findings can help others or 
> provide improvements.
> 
> I'm not using drop_alll and create_all, but table.create() and this results 
> in the "relation "some_seq" already exists" error.
> 
> 99% your snippet, just using create:
> 
> from sqlalchemy import Column
> from sqlalchemy import Integer
> from sqlalchemy import MetaData
> from sqlalchemy import Sequence
> from sqlalchemy import Table
> 
> m = MetaData()
> 
> seq = Sequence("some_seq")
> t1 = Table("t1", m, Column("id", Integer, seq, primary_key=True))
> t2 = Table("t2", m, Column("id", Integer, seq, primary_key=True))
> 
> t1.create(pg_engine)
> t2.create(pg_engine)**
> 
> > To render "IF NOT EXISTS" then just invoke the SQL:  
> > conn.execute(text("CREATE SEQ IF NOT EXISTS ..."))
> 
> So in this example, how could I avoid creating the sequence? I'd be happy to 
> create it once, or not at all, but it seems that table create tries to create 
> the sequence automatically, no matter what.

send checkfirst=True to each create() call.

> 
> The schema bug was actually the behaviour of the optional=True. I still don't 
> know what it does, but it behaves like if the whole sequence line wouldn't be 
> present. It just makes the column a SERIAL type and then PostgreSQL 
> auto-creates the sequence in the table's schema, not in public. So with 
> optional=True basically nothing matters, nor the sequence's name, not the 
> sequence's schema?

optional=True is used for when you want your database table to have a SEQUENCE 
created on Oracle and Firebird, which until recently have no syntax for an 
implicit primary key generator, but on all other databases you don't want an 
explicit SEQUENCE created.This essentially means, "don't create the 
sequence on PostgreSQL, use SERIAL instead".

This dynamic is going to be changing soon as most of these databases are now 
supporting the GENERATED AS IDENTITY syntax and we'll be adding support for 
that in 1.4.





> 
> Zsolt
> 
> 
> 
> 
> 
> 
> On Wednesday, 5 August 2020 at 17:36:00 UTC+2 Mike Bayer wrote:
>> 
>> 
>> On Wed, Aug 5, 2020, at 9:25 AM, Zsolt Ero wrote:
>>> But this would create a different id for each table, wouldn't it? 
>> 
>> 
>> if you want two tables to have the same sequence then use one Sequence 
>> object for both.   If the Sequence is present on the Column it will not 
>> create SERIAL.  I've tried to document this right at the top here: 
>> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity
>>   .  Here's a POC with all the things I think you've mentioned so far:
>> 
>> from sqlalchemy import Column
>> from sqlalchemy import create_engine
>> from sqlalchemy import Integer
>> from sqlalchemy import MetaData
>> from sqlalchemy import Sequence
>> from sqlalchemy import Table
>> 
>> 
>> m = MetaData(schema="test_schema")
>> 
>> seq = Sequence("some_seq", schema="public")
>> t1 = Table(
>> "t1",
>> m,
>> Column(
>> "id", Integer, seq, primary_key=True
>> ),
>> )
>> 
>> t2 = Table(
>> "t2",
>> m,
>> Column(
>> "id", Integer, seq, primary_key=True
>> ),
>> )
>> 
>> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
>> 
>> m.drop_all(e)
>> m.create_all(e)
>> 
>> with e.connect() as conn:
>> conn.execute(t1.insert())
>> conn.execute(t2.insert())
>> 
>> assert conn.scalar(seq.next_value()) == 3
>> 
>> 
>> 
>>> 
>>> 
>>> 
>>> 
>>> I'd
>>> like to use the same ids for matching rows, such that table A's
>>> primary key is the same as table B's primary key, so that they can
>>> join-ed together like when it was a single table.
>>> 
>>> So far the only solution I found is to remove primary_key=True and
>>> issue an ALTER TABLE ... ADD PRIMARY KEY (...) command manually.
>>> 
>>> 
>>> 
>>> On Wed, 5 Aug 2020 at 15:20, Mike Bayer  wrote:
>>> >
>>> >
>>> >
>>> > On Wed, Aug 5, 2020, at 8:59 AM, Zsolt Ero wrote:
>>> >
>>> > Hi,
>>> >
>>> > I've split a table into two tables, for performance reasons. I'd like to 
>>> > insert into both tables using the same sequence. I'm inserting using 
>>> > executemany_mode='values'.
>>> >
>>> > My idea is to call nextval() on the sequence before insert and fill in 
>>> > the values client side, before inserting.
>>> >
>>> > select nextval('mysql') FROM generate_series(1,...)
>>> >
>>> > Everything looks good, except for the default behaviour of SQLAlchemy to 
>>> > turn an integer + pk column into a SERIAL.
>>> >
>>> >
>>> > add autoincrement=False to the Column
>>> >
>>> >
>>> >
>>> > As an alternative I'm also looking at using Sequence('myseq') from 
>>> > https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity,
>>> >  but this is broken for issuing "CREATE SEQUENCE myseq" before the table 
>>> > creation, which is missing the IF NOT EXISTS 

Re: [sqlalchemy] How to disable the integer col + pk = serial behaviour?

[zsol...@gmail.com]

Thanks for all the answers.

> add autoincrement=False to the Column

This is actually all I needed, but possibly my findings can help others or 
provide improvements.

I'm not using drop_alll and create_all, but table.create() and this results 
in the "relation "some_seq" already exists" error.

99% your snippet, just using create:

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Sequence
from sqlalchemy import Table

m = MetaData()

seq = Sequence("some_seq")
t1 = Table("t1", m, Column("id", Integer, seq, primary_key=True))
t2 = Table("t2", m, Column("id", Integer, seq, primary_key=True))

t1.create(pg_engine)
t2.create(pg_engine)

> To render "IF NOT EXISTS" then just invoke the SQL:  
conn.execute(text("CREATE SEQ IF NOT EXISTS ..."))

So in this example, how could I avoid creating the sequence? I'd be happy 
to create it once, or not at all, but it seems that table create tries to 
create the sequence automatically, no matter what.

The schema bug was actually the behaviour of the optional=True. I still 
don't know what it does, but it behaves like if the whole sequence line 
wouldn't be present. It just makes the column a SERIAL type and then 
PostgreSQL auto-creates the sequence in the table's schema, not in public. 
So with optional=True basically nothing matters, nor the sequence's name, 
not the sequence's schema?

Zsolt







On Wednesday, 5 August 2020 at 17:36:00 UTC+2 Mike Bayer wrote:

>
>
> On Wed, Aug 5, 2020, at 9:25 AM, Zsolt Ero wrote:
>
> But this would create a different id for each table, wouldn't it? 
>
>
>
> if you want two tables to have the same sequence then use one Sequence 
> object for both.   If the Sequence is present on the Column it will not 
> create SERIAL.  I've tried to document this right at the top here: 
> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity
>   
> .  Here's a POC with all the things I think you've mentioned so far:
>
> from sqlalchemy import Column
> from sqlalchemy import create_engine
> from sqlalchemy import Integer
> from sqlalchemy import MetaData
> from sqlalchemy import Sequence
> from sqlalchemy import Table
>
>
> m = MetaData(schema="test_schema")
>
> seq = Sequence("some_seq", schema="public")
> t1 = Table(
> "t1",
> m,
> Column(
> "id", Integer, seq, primary_key=True
> ),
> )
>
> t2 = Table(
> "t2",
> m,
> Column(
> "id", Integer, seq, primary_key=True
> ),
> )
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
>
> m.drop_all(e)
> m.create_all(e)
>
> with e.connect() as conn:
> conn.execute(t1.insert())
> conn.execute(t2.insert())
>
> assert conn.scalar(seq.next_value()) == 3
>
>
>
>
>
>
> I'd
> like to use the same ids for matching rows, such that table A's
> primary key is the same as table B's primary key, so that they can
> join-ed together like when it was a single table.
>
> So far the only solution I found is to remove primary_key=True and
> issue an ALTER TABLE ... ADD PRIMARY KEY (...) command manually.
>
>
>
> On Wed, 5 Aug 2020 at 15:20, Mike Bayer  wrote:
> >
> >
> >
> > On Wed, Aug 5, 2020, at 8:59 AM, Zsolt Ero wrote:
> >
> > Hi,
> >
> > I've split a table into two tables, for performance reasons. I'd like to 
> insert into both tables using the same sequence. I'm inserting using 
> executemany_mode='values'.
> >
> > My idea is to call nextval() on the sequence before insert and fill in 
> the values client side, before inserting.
> >
> > select nextval('mysql') FROM generate_series(1,...)
> >
> > Everything looks good, except for the default behaviour of SQLAlchemy to 
> turn an integer + pk column into a SERIAL.
> >
> >
> > add autoincrement=False to the Column
> >
> >
> >
> > As an alternative I'm also looking at using Sequence('myseq') from 
> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity,
>  
> but this is broken for issuing "CREATE SEQUENCE myseq" before the table 
> creation, which is missing the IF NOT EXISTS part.
> >
> >
> > there's a "checkfirst" flag on Sequence.create() or Table.create() that 
> will check for the object ahead of time, using a separate query.  To render 
> "IF NOT EXISTS" then just invoke the SQL:  conn.execute(text("CREATE SEQ IF 
> NOT EXISTS ..."))
> >
> >
> >
> >
> > How can I either:
> > - turn off the automatic behaviour of making a pg + int = serial?
> > - add a IF NOT EXISTS to the Sequence()?
> >
> > Or any alternative ideas?
> >
> >
> > --
> > 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 

Re: [sqlalchemy] How to disable the integer col + pk = serial behaviour?

[Mike Bayer]

On Wed, Aug 5, 2020, at 9:25 AM, Zsolt Ero wrote:
> But this would create a different id for each table, wouldn't it? 


if you want two tables to have the same sequence then use one Sequence object 
for both.   If the Sequence is present on the Column it will not create SERIAL. 
 I've tried to document this right at the top here: 
https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity
  .  Here's a POC with all the things I think you've mentioned so far:

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Sequence
from sqlalchemy import Table


m = MetaData(schema="test_schema")

seq = Sequence("some_seq", schema="public")
t1 = Table(
"t1",
m,
Column(
"id", Integer, seq, primary_key=True
),
)

t2 = Table(
"t2",
m,
Column(
"id", Integer, seq, primary_key=True
),
)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

m.drop_all(e)
m.create_all(e)

with e.connect() as conn:
conn.execute(t1.insert())
conn.execute(t2.insert())

assert conn.scalar(seq.next_value()) == 3



> 
> 
> 
> I'd
> like to use the same ids for matching rows, such that table A's
> primary key is the same as table B's primary key, so that they can
> join-ed together like when it was a single table.
> 
> So far the only solution I found is to remove primary_key=True and
> issue an ALTER TABLE ... ADD PRIMARY KEY (...) command manually.
> 
> 
> 
> On Wed, 5 Aug 2020 at 15:20, Mike Bayer  wrote:
> >
> >
> >
> > On Wed, Aug 5, 2020, at 8:59 AM, Zsolt Ero wrote:
> >
> > Hi,
> >
> > I've split a table into two tables, for performance reasons. I'd like to 
> > insert into both tables using the same sequence. I'm inserting using 
> > executemany_mode='values'.
> >
> > My idea is to call nextval() on the sequence before insert and fill in the 
> > values client side, before inserting.
> >
> > select nextval('mysql') FROM generate_series(1,...)
> >
> > Everything looks good, except for the default behaviour of SQLAlchemy to 
> > turn an integer + pk column into a SERIAL.
> >
> >
> > add autoincrement=False to the Column
> >
> >
> >
> > As an alternative I'm also looking at using Sequence('myseq') from 
> > https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity,
> >  but this is broken for issuing "CREATE SEQUENCE myseq" before the table 
> > creation, which is missing the IF NOT EXISTS part.
> >
> >
> > there's a "checkfirst" flag on Sequence.create() or Table.create() that 
> > will check for the object ahead of time, using a separate query.  To render 
> > "IF NOT EXISTS" then just invoke the SQL:  conn.execute(text("CREATE SEQ IF 
> > NOT EXISTS ..."))
> >
> >
> >
> >
> > How can I either:
> > - turn off the automatic behaviour of making a pg + int = serial?
> > - add a IF NOT EXISTS to the Sequence()?
> >
> > Or any alternative ideas?
> >
> >
> > --
> > 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/7860f5ab-64a4-481d-8e72-9b40d2ab2527o%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 a topic in the 
> > Google Groups "sqlalchemy" group.
> > To unsubscribe from this topic, visit 
> > https://groups.google.com/d/topic/sqlalchemy/jAvSFG55leA/unsubscribe.
> > To unsubscribe from this group and all its topics, send an email to 
> > sqlalchemy+unsubscr...@googlegroups.com.
> > To view this discussion on the web visit 
> > https://groups.google.com/d/msgid/sqlalchemy/74ad0d47-328f-4c3b-9afd-9425f72942f2%40www.fastmail.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 

Re: [sqlalchemy] Re: How to disable the integer col + pk = serial behaviour?

[Mike Bayer]

On Wed, Aug 5, 2020, at 9:07 AM, Zsolt Ero wrote:
> I'm lost in two places:
> 
> sa.Column(
>  'trip_num',
>  sa.Integer,
>  sa.Sequence('trip_num_seq', schema='public', optional=True),
>  primary_key=True,
> )
> 
> 
> 1. I'm specifying schema='public', yet the sequence gets created under 
> Metadata's schema.

Removing the optional=True flag which will prevent this Sequence from being 
created on a PG database, I'm not able to reproduce.

test case, noting "public" is the PG default schema, which I thought here might 
lead to this issue, but in 1.3.18 it's fine:

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Sequence
from sqlalchemy import Table


m = MetaData(schema="test_schema")

t = Table(
"t",
m,
Column(
"id", Integer, Sequence("some_seq", schema="public"), primary_key=True
),
)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

m.drop_all(e)
m.create_all(e)


output shows the sequence created in "public":

DROP TABLE test_schema.t
{}
COMMIT
SELECT relname FROM pg_class c join pg_namespace n on n.oid=c.relnamespace 
where relkind='S' and n.nspname=%(schema)s and relname=%(name)s
{'schema': 'public', 'name': 'some_seq'}
DROP SEQUENCE public.some_seq
{}
COMMIT
select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace 
where n.nspname=%(schema)s and relname=%(name)s
{'schema': 'test_schema', 'name': 't'}
SELECT relname FROM pg_class c join pg_namespace n on n.oid=c.relnamespace 
where relkind='S' and n.nspname=%(schema)s and relname=%(name)s
{'schema': 'public', 'name': 'some_seq'}
CREATE SEQUENCE public.some_seq
{}
COMMIT

CREATE TABLE test_schema.t (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)


{}
COMMIT












> 2. I'm trying this optional=True, however all it does is make the Integer 
> back into the Serial, which I'm trying to avoid.
> 
> 
> 
> On Wednesday, 5 August 2020 14:59:03 UTC+2, Zsolt Ero wrote:
>> Hi,
>> 
>> I've split a table into two tables, for performance reasons. I'd like to 
>> insert into both tables using the same sequence. I'm inserting using 
>> executemany_mode='values'.
>> 
>> My idea is to call nextval() on the sequence before insert and fill in the 
>> values client side, before inserting.
>> 
>> select nextval('mysql') FROM generate_series(1,...)
>> 
>> Everything looks good, except for the default behaviour of SQLAlchemy to 
>> turn an integer + pk column into a SERIAL. 
>> 
>> As an alternative I'm also looking at using Sequence('myseq') from 
>> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity,
>>  but this is broken for issuing "CREATE SEQUENCE myseq" before the table 
>> creation, which is missing the IF NOT EXISTS part.
>> 
>> How can I either:
>> - turn off the automatic behaviour of making a pg + int = serial?
>> - add a IF NOT EXISTS to the Sequence()?
>> 
>> Or any alternative ideas?
> 

> --
> 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/925b2783-cbf3-491f-b4ab-1bda26eae181o%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/006b2ba7-5871-4c91-80d9-c746fe706ab6%40www.fastmail.com.

Re: [sqlalchemy] How to disable the integer col + pk = serial behaviour?

[Zsolt Ero]

But this would create a different id for each table, wouldn't it? I'd
like to use the same ids for matching rows, such that table A's
primary key is the same as table B's primary key, so that they can
join-ed together like when it was a single table.

So far the only solution I found is to remove primary_key=True and
issue an ALTER TABLE ... ADD PRIMARY KEY (...) command manually.



On Wed, 5 Aug 2020 at 15:20, Mike Bayer  wrote:
>
>
>
> On Wed, Aug 5, 2020, at 8:59 AM, Zsolt Ero wrote:
>
> Hi,
>
> I've split a table into two tables, for performance reasons. I'd like to 
> insert into both tables using the same sequence. I'm inserting using 
> executemany_mode='values'.
>
> My idea is to call nextval() on the sequence before insert and fill in the 
> values client side, before inserting.
>
> select nextval('mysql') FROM generate_series(1,...)
>
> Everything looks good, except for the default behaviour of SQLAlchemy to turn 
> an integer + pk column into a SERIAL.
>
>
> add autoincrement=False to the Column
>
>
>
> As an alternative I'm also looking at using Sequence('myseq') from 
> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity,
>  but this is broken for issuing "CREATE SEQUENCE myseq" before the table 
> creation, which is missing the IF NOT EXISTS part.
>
>
> there's a "checkfirst" flag on Sequence.create() or Table.create() that will 
> check for the object ahead of time, using a separate query.  To render "IF 
> NOT EXISTS" then just invoke the SQL:  conn.execute(text("CREATE SEQ IF NOT 
> EXISTS ..."))
>
>
>
>
> How can I either:
> - turn off the automatic behaviour of making a pg + int = serial?
> - add a IF NOT EXISTS to the Sequence()?
>
> Or any alternative ideas?
>
>
> --
> 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/7860f5ab-64a4-481d-8e72-9b40d2ab2527o%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 a topic in the Google 
> Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit 
> https://groups.google.com/d/topic/sqlalchemy/jAvSFG55leA/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/74ad0d47-328f-4c3b-9afd-9425f72942f2%40www.fastmail.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/CAKw-smCTjWL_1qeAKRcZp7ex_ecmgpd_iXx2MEUhGDnei9%2B8MA%40mail.gmail.com.

Re: [sqlalchemy] How to disable the integer col + pk = serial behaviour?

[Mike Bayer]

On Wed, Aug 5, 2020, at 8:59 AM, Zsolt Ero wrote:
> Hi,
> 
> I've split a table into two tables, for performance reasons. I'd like to 
> insert into both tables using the same sequence. I'm inserting using 
> executemany_mode='values'.
> 
> My idea is to call nextval() on the sequence before insert and fill in the 
> values client side, before inserting.
> 
> select nextval('mysql') FROM generate_series(1,...)
> 
> Everything looks good, except for the default behaviour of SQLAlchemy to turn 
> an integer + pk column into a SERIAL. 

add autoincrement=False to the Column


> 
> As an alternative I'm also looking at using Sequence('myseq') from 
> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity,
>  but this is broken for issuing "CREATE SEQUENCE myseq" before the table 
> creation, which is missing the IF NOT EXISTS part.

there's a "checkfirst" flag on Sequence.create() or Table.create() that will 
check for the object ahead of time, using a separate query.  To render "IF NOT 
EXISTS" then just invoke the SQL:  conn.execute(text("CREATE SEQ IF NOT EXISTS 
..."))



> 
> How can I either:
> - turn off the automatic behaviour of making a pg + int = serial?
> - add a IF NOT EXISTS to the Sequence()?
> 
> Or any alternative ideas?
> 

> --
> 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/7860f5ab-64a4-481d-8e72-9b40d2ab2527o%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/74ad0d47-328f-4c3b-9afd-9425f72942f2%40www.fastmail.com.

Re: [sqlalchemy] How to disable the integer col + pk = serial behaviour?

['Michael Mulqueen' via sqlalchemy]

I've just done something like this the other day, but it was with an
existing sequence. We're using Alembic for schema updates, so I'm not sure
whether SQLAlchemy's built-in create_all would behave the same way. You
should still be able to use a similar approach.

shared_sequence = Sequence('shared_id__seq')


class ModelA(Base):
shared_id = Column(Integer,
   primary_key=True,
   default=shared_sequence.next_value(),
   server_default=shared_sequence.next_value())

class ModelB(Base):
shared_id = Column(Integer,
   primary_key=True,
   default=shared_sequence.next_value(),
   server_default=shared_sequence.next_value())

This seems to be working fine.

Before that I'd tried providing Sequence as an arg to Column like you have
and I'd run into some problems and this seemed like an easier option.


On Wed, 5 Aug 2020 at 13:59, Zsolt Ero  wrote:

> Hi,
>
> I've split a table into two tables, for performance reasons. I'd like to
> insert into both tables using the same sequence. I'm inserting using
> executemany_mode='values'.
>
> My idea is to call nextval() on the sequence before insert and fill in the
> values client side, before inserting.
>
> select nextval('mysql') FROM generate_series(1,...)
>
> Everything looks good, except for the default behaviour of SQLAlchemy to
> turn an integer + pk column into a SERIAL.
>
> As an alternative I'm also looking at using Sequence('myseq') from
> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity,
> but this is broken for issuing "CREATE SEQUENCE myseq" before the table
> creation, which is missing the IF NOT EXISTS part.
>
> How can I either:
> - turn off the automatic behaviour of making a pg + int = serial?
> - add a IF NOT EXISTS to the Sequence()?
>
> Or any alternative ideas?
>
> --
> 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/7860f5ab-64a4-481d-8e72-9b40d2ab2527o%40googlegroups.com
> 
> .
>


-- 

Michael Mulqueen

*Method B Ltd*
m...@method-b.uk / +44 (0)330 223 0864
http://www.method-b.uk/

Method B Limited is registered in England and Wales with the company number
9189496. Registered office: 28-29 Richmond Place, Brighton, East Sussex,
England, BN2 9NA

-- 
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/CAHxMHYXTJ8zLobCdW%2BAtTMaUHGFKkub-9L8cEJMJhYBfPUO%3DQA%40mail.gmail.com.

[sqlalchemy] Re: How to disable the integer col + pk = serial behaviour?

[Zsolt Ero]

I'm lost in two places:

sa.Column(
 'trip_num',
 sa.Integer,
 sa.Sequence('trip_num_seq', schema='public', optional=True),
 primary_key=True,
)


1. I'm specifying schema='public', yet the sequence gets created under 
Metadata's schema.
2. I'm trying this optional=True, however all it does is make the Integer 
back into the Serial, which I'm trying to avoid.



On Wednesday, 5 August 2020 14:59:03 UTC+2, Zsolt Ero wrote:
>
> Hi,
>
> I've split a table into two tables, for performance reasons. I'd like to 
> insert into both tables using the same sequence. I'm inserting using 
> executemany_mode='values'.
>
> My idea is to call nextval() on the sequence before insert and fill in the 
> values client side, before inserting.
>
> select nextval('mysql') FROM generate_series(1,...)
>
> Everything looks good, except for the default behaviour of SQLAlchemy to 
> turn an integer + pk column into a SERIAL. 
>
> As an alternative I'm also looking at using Sequence('myseq') from 
> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity,
>  
> but this is broken for issuing "CREATE SEQUENCE myseq" before the table 
> creation, which is missing the IF NOT EXISTS part.
>
> How can I either:
> - turn off the automatic behaviour of making a pg + int = serial?
> - add a IF NOT EXISTS to the Sequence()?
>
> Or any alternative ideas?
>

-- 
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/925b2783-cbf3-491f-b4ab-1bda26eae181o%40googlegroups.com.

[sqlalchemy] How to disable the integer col + pk = serial behaviour?

[Zsolt Ero]

Hi,

I've split a table into two tables, for performance reasons. I'd like to 
insert into both tables using the same sequence. I'm inserting using 
executemany_mode='values'.

My idea is to call nextval() on the sequence before insert and fill in the 
values client side, before inserting.

select nextval('mysql') FROM generate_series(1,...)

Everything looks good, except for the default behaviour of SQLAlchemy to 
turn an integer + pk column into a SERIAL. 

As an alternative I'm also looking at using Sequence('myseq') from 
https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity,
 
but this is broken for issuing "CREATE SEQUENCE myseq" before the table 
creation, which is missing the IF NOT EXISTS part.

How can I either:
- turn off the automatic behaviour of making a pg + int = serial?
- add a IF NOT EXISTS to the Sequence()?

Or any alternative ideas?

-- 
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/7860f5ab-64a4-481d-8e72-9b40d2ab2527o%40googlegroups.com.