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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/925b2783-cbf3-491f-b4ab-1bda26eae181o%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/925b2783-cbf3-491f-b4ab-1bda26eae181o%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/006b2ba7-5871-4c91-80d9-c746fe706ab6%40www.fastmail.com.
