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 <mike...@zzzcomputing.com> 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. > -- 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/85f59a8d-ef50-4f24-aafd-e5f1ed250e65%40www.fastmail.com.
