Re: [sqlalchemy] How to disable the integer col + pk = serial behaviour?
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 rece
[sqlalchemy] Configuring Alembic in SQL-only mode
Hi, I'm using SQLAlchemy 1.3 with PostgreSQL. I'm trying to use alembic in SQL-only mode. I'm using a custom sql-diff script which takes the pg_dump's --schema output and compares it with a clean db setup's one and tells me exactly what changes do I need to make. This way I catch 100% of the db differences, something what no automated tool was able to do in my testing. My question is how should I use Alembic for this scenario? I'd like to input SQL queries only, no Python at all. Here is my concept which works but I'm not sure if it's the right way: *env.py:* def run_migrations_online(): engine = engine_from_config(settings, prefix='sqlalchemy.') connection = engine.connect() context.configure(connection=connection) context.run_migrations() connection.close() *migration script:* commands = [ 'CREATE INDEX...', ] def upgrade(): for command in commands: try: op.execute(sa.text(command)) except Exception as e: print(e) def downgrade(): pass It's important that a failing query should never terminate a script, because I might apply some of them (like CREATE INDEX) to production servers in advance. I was also able to make this work by using "autocommit_block()" but this approach seems simpler to me. I'm surprised on the lack of rollback() in the except part though, but still it seems to work, the failed commands are not blocking any other. Is this good like this? Regards, Zsolt -- 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/c8998719-7ea2-4ce9-bfb0-ef41128010c9n%40googlegroups.com.
[sqlalchemy] low-level commands in 2.0
Hi, I'm trying to run low-level commands like DROP DATABASE / CREATE DATABASE, with psycopg2. In 1.3 I used the following: with pg_engine.connect().execution_options(isolation_level="AUTOCOMMIT",autocommit=True) as conn: conn.execute(command) Now in 2.0 I run into many errors. I've read the migration guide and for me this looks like the best: with pg_engine.connect().execution_options(isolation_level="AUTOCOMMIT") as conn: conn.exec_driver_sql(command) Is this the right one? I'm confused as isolation_level is not written in the migration guide, I just had it from 1.3. But without isolation_level it doesn't work. Also, what is the difference between exec_driver_sql and execute + text()? Zsolt -- 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/d0f06e70-f341-475e-8176-8bf0ac46ff94n%40googlegroups.com.
[sqlalchemy] Re: low-level commands in 2.0
This is what I see with echo=True using the following block:
select pg_catalog.version()
[raw sql] {}
select current_schema()
[raw sql] {}
show standard_conforming_strings
[raw sql] {}
BEGIN (implicit; DBAPI should not BEGIN due to autocommit mode)
DROP DATABASE IF EXISTS md_server
[raw sql] {}
CREATE DATABASE md_server
[raw sql] {}
ROLLBACK using DBAPI connection.rollback(), DBAPI should ignore due to
autocommit mode
On Thursday, 24 November 2022 at 21:24:11 UTC+1 zsol...@gmail.com wrote:
> Hi,
>
> I'm trying to run low-level commands like DROP DATABASE / CREATE DATABASE,
> with psycopg2.
>
> In 1.3 I used the following:
>
> with
> pg_engine.connect().execution_options(isolation_level="AUTOCOMMIT",autocommit=True)
>
> as conn:
> conn.execute(command)
>
> Now in 2.0 I run into many errors. I've read the migration guide and for
> me this looks like the best:
>
> with pg_engine.connect().execution_options(isolation_level="AUTOCOMMIT")
> as conn:
>conn.exec_driver_sql(command)
>
> Is this the right one? I'm confused as isolation_level is not written in
> the migration guide, I just had it from 1.3. But without isolation_level it
> doesn't work.
>
> Also, what is the difference between exec_driver_sql and execute + text()?
>
> Zsolt
>
>
>
>
>
--
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/08dd6622-0bdf-46fc-8179-f18421640af4n%40googlegroups.com.
