Re: [sqlalchemy] Re: Conditional insert in one transaction
Additional background: In the proposed code snippet, I placed the try/except around the `begin_nested()` block instead of just the `orm_session.add()` statement because the `add()` might not get flushed to database until the savepoint is released in the event the session is not auto-flushing. I also came across a nice graphical illustration of the race condition associated with concurrent INSERT here: http://rachbelaid.com/handling-race-condition-insert-with-sqlalchemy/ On Thursday, February 4, 2021 at 5:42:40 PM UTC-8 Vitaly Kruglikov wrote: > Thanks Mike! I followed up on your suggestion concerning savepoints and > came up with the following code snippet. Does this look right to you? > > ``` > try: > with orm_session.begin_nested(): > orm_session.add(record) > except psycopg2.errors.UniqueViolation as error: # or is it > `sqlalchemy.exc.IntegrityError`??? > # Matching metadata row already inserted by someone else > # Fetch and return existing record > > # We inserted it! > return record > ``` > -- 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/ded97aab-d4f3-498b-aea9-af89d2e411fan%40googlegroups.com.
Re: [sqlalchemy] Re: Conditional insert in one transaction
Thanks Mike! I followed up on your suggestion concerning savepoints and came up with the following code snippet. Does this look right to you? ``` try: with orm_session.begin_nested(): orm_session.add(record) except psycopg2.errors.UniqueViolation as error: # or is it `sqlalchemy.exc.IntegrityError`??? # Matching metadata row already inserted by someone else # Fetch and return existing record # We inserted it! return record ``` -- 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/b3002fec-c8ac-4b41-8101-1283455bfe7dn%40googlegroups.com.
Re: [sqlalchemy] Re: Conditional insert in one transaction
How can the query/merge or query/add be performed atomically? What would happen if between the two calls another process inserted a row with the same unique key? Wouldn't the `s.merge()` then trigger a `psycopg2.errors.UniqueViolation` exception (in postgres case) when the new row insert is flushed? Is there a way to make the execution of the ORM atomic so that the `s.merge()` would be guaranteed to succeed (barring network errors, etc.)? -- 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/d117fb88-2fd5-4006-a60f-424cc25c4bd7n%40googlegroups.com.
[sqlalchemy] How to identify unique constraint violation without dependency on a specific database driver?
My table has a unique index on the column named "tag". When I attempt to insert a row with a tag value that already exists in the table, sqlalchemy raises the generic exception `IntegrityError`. `IntegrityError` may be raised for various scenarios, including Foreign Key Violation and Unique Index Violation. One of the benefits of using sqlalchemy is the ability to write code that may be independent of the underlying backend or db-api driver. So, I am hoping that there is a programmatic way to distinguish between the underlying causes of IntegrityError (such as unique constraint violation) without having to depend explicitly on a specific driver package (e.g., psycopg2). Thank you, Vitaly -- 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/718de06d-97ae-4f73-8614-a4fd0626826fn%40googlegroups.com.
Re: [sqlalchemy] Re: ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert
Hi Richard. I wish it was that simple, but it's not. Here is an example of how using a builtin name breaks: ``` In [3]: unique = object() ...: class TestId: ...: id = 'something else' ...: unique_id = id(unique) ...: ...: --- TypeError Traceback (most recent call last) in 1 unique = object() > 2 class TestId: 3 id = 'something else' 4 unique_id = id(unique) 5 in TestId() 2 class TestId: 3 id = 'something else' > 4 unique_id = id(unique) 5 6 TypeError: 'str' object is not callable ``` On Saturday, August 22, 2020 at 8:09:19 AM UTC-7 Richard Damon wrote: > On 8/22/20 10:46 AM, Vitaly Kruglikov wrote: > > I suspect this has something to do with the combination of the > > explicit definition of the `id_` column and reflection, but don't know > > how to fix. I really need to keep the explicit `id_` descriptor and > > shouldn't rename it to `id` because that's a reserved python word. > > > I would note that 'id' is NOT a reserved word (aka key-word) in Python, > but the name of a built-in. As such id(xx) [which uses the built in] and > obj.id [which can reference the id member of that object] are not > incompatible. Don't use it as a variable name, as that would cause > issues, but in an explicit scope like a class it works. > > -- > Richard Damon > > -- 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/9fb87feb-203f-43a5-a449-77fe815262b3n%40googlegroups.com.
[sqlalchemy] Re: ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert
Please note, that I posted, then deleted a message with the same subject in
order to correct some relevant information, and this post replaces the one
I deleted.
On Saturday, August 22, 2020 at 7:43:42 AM UTC-7 Vitaly Kruglikov wrote:
> Dear all,
>
> I am using:
> sqlalchemy==1.3.18
> psycopg2==2.8.4
> connection url schema: "postgresql+psycopg2://..."
> postgres 10.x
>
>
> My code looks like this:
>
> ```
> _AutomapBase = automap.automap_base()
>
> class Model1(_AutomapBase):
> __tablename__ = 'model1"
>
> id_ = sa.Column('id', sa.Integer, primary_key=True, autoincrement=True,
>key='id_')
> tag = sa.Column(sa.String())
>
> _AutomapBase.metadata.reflect(bind=ENGINE, only=['model1'],
> extend_existing=True)
> _AutomapBase.prepare()
>
> row = Model1(tag='tag1')
> orm_session.add(attempt)
> orm_session.flush()
> ```
>
> I get the exception `ArgumentError: Only one Column may be marked
> autoincrement=True, found both id and id.` when I run the following insert:
> ```
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 2523, in flush
> self._flush(objects)
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 2664, in _flush
> transaction.rollback(_capture_exception=True)
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
>
> line 68, in __exit__
> compat.raise_(
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py",
> line 178, in raise_
> raise exception
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 2624, in _flush
> flush_context.execute()
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>
> line 422, in execute
> rec.execute(self)
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>
> line 586, in execute
> persistence.save_obj(
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
>
> line 239, in save_obj
> _emit_insert_statements(
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
>
> line 1135, in _emit_insert_statements
> result = cached_connections[connection].execute(
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
> line 1014, in execute
> return meth(self, multiparams, params)
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>
> line 298, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
> line 1108, in _execute_clauseelement
> compiled_sql = elem.compile(
> File "", line 1, in
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>
> line 476, in compile
> return self._compiler(dialect, bind=bind, **kw)
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>
> line 482, in _compiler
> return dialect.statement_compiler(dialect, self, **kw)
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>
> line 590, in __init__
> Compiled.__init__(self, dialect, statement, **kwargs)
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>
> line 319, in __init__
> self.string = self.process(self.statement, **compile_kwargs)
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>
> line 350, in process
> return obj._compiler_dispatch(self, **kwargs)
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py",
>
> line 95, in _compiler_dispatch
> return meth(self, **kw)
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
>
> line 2427, in visit_insert
> crud_params = crud._setup_crud_params(
> File
> "/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py",
> line 64, in _setup_crud_params
> return _get_crud_params(compiler, stmt, **kw)
> File
> "/tmp/empower-wf-venv/lib
[sqlalchemy] ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert
Dear all,
I am using:
sqlalchemy==1.3.18
psycopg2==2.8.4
connection url schema: "postgresql+psycopg2://..."
postgres 10.x
My code looks like this:
```
_AutomapBase = automap.automap_base()
class Model1(_AutomapBase):
__tablename__ = 'model1"
id_ = sa.Column('id', sa.Integer, primary_key=True, autoincrement=True,
key='id_')
tag = sa.Column(sa.String())
_AutomapBase.metadata.reflect(bind=ENGINE, only=['model1'],
extend_existing=True)
_AutomapBase.prepare()
row = Model1(tag='tag1')
orm_session.add(attempt)
orm_session.flush()
```
I get the exception `ArgumentError: Only one Column may be marked
autoincrement=True, found both id and id.` when I run the following insert:
```
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
line 2523, in flush
self._flush(objects)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
line 2664, in _flush
transaction.rollback(_capture_exception=True)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
line 68, in __exit__
compat.raise_(
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py",
line 178, in raise_
raise exception
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
line 2624, in _flush
flush_context.execute()
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
line 422, in execute
rec.execute(self)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
line 586, in execute
persistence.save_obj(
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
line 239, in save_obj
_emit_insert_statements(
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
line 1135, in _emit_insert_statements
result = cached_connections[connection].execute(
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
line 1014, in execute
return meth(self, multiparams, params)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
line 1108, in _execute_clauseelement
compiled_sql = elem.compile(
File "", line 1, in
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
line 476, in compile
return self._compiler(dialect, bind=bind, **kw)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
line 482, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
line 590, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
line 319, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
line 350, in process
return obj._compiler_dispatch(self, **kwargs)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py",
line 95, in _compiler_dispatch
return meth(self, **kw)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py",
line 2427, in visit_insert
crud_params = crud._setup_crud_params(
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py",
line 64, in _setup_crud_params
return _get_crud_params(compiler, stmt, **kw)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py",
line 158, in _get_crud_params
_scan_cols(
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py",
line 346, in _scan_cols
_append_param_insert_pk_returning(
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/crud.py",
line 457, in _append_param_insert_pk_returning
elif c is stmt.table._autoincrement_column or c.server_default is not
None:
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/schema.py",
line 779, in _autoincrement_column
return self.primary_key._autoincrement_column
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
line 883, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
"/tmp/empower-wf-venv/lib/python3.8/site-packages/sqlalchemy/sql/schema.py",
line 3706, in _autoincrement_column
raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Only one Column may be marked
autoincrement=True, found both id and id.
```
I suspect this has something to do with the combination
[sqlalchemy] ArgumentError: Only one Column may be marked autoincrement=True, found both id and id
Dear all,
I am using:
sqlalchemy==1.3.18
psycopg2==2.8.4
connection url schema: "postgresql+psycopg2://..."
postgres 10.x
when I define an explicit AutomapBase-derived model for
'sqa_global_context' table with only the primary key, I expected that
running `Base.metadata.reflect(bind=database.ENGINE,
only=['sqa_global_context']); Base.prepare()` would backfill missing
columns and relationships in that table. However, after running
`Base.prepare(engine, reflect=True)`, the missing columns and relationships
are not populated in my table.
My code looks like this:
```
_AutomapBase = automap.automap_base()
class Model1(_AutomapBase):
__tablename__ = 'model1"
id_ = sa.Column('id', sa.Integer, primary_key=True, autoincrement=True,
key='id_')
tag = sa.Column(sa.String())
_AutomapBase.metadata.reflect(bind=ENGINE, only=['model1'],
extend_existing=True)
_AutomapBase.prepare()
```
I get the exception `ArgumentError: Only one Column may be marked
autoincrement=True, found both id and id.` when I run the following insert:
```
row = Model1(tag='tag1')
orm_session.add(attempt)
orm_session.flush()
```
I suspect this has something to do with the combination of the explicit
definition of the `id_` column and reflection, but don't know how to fix. I
really need to keep the explicit `id_` descriptor and shouldn't rename it
to `id` because that's a reserved python word.
Please help. Many thanks in advance!
--
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/426d0281-27ee-448d-829f-72f137532fd2n%40googlegroups.com.
Re: [sqlalchemy] `Base.prepare()` doesn't backfill missing columns and relationships in AutomapBase-derived models
Thanks Mike! `extend_existing=True` is having the partially-specified
explicit models pick up all the remaining columns.
On Tuesday, August 18, 2020 at 11:29:39 AM UTC-7 Mike Bayer wrote:
> your reflect() call requires extend_existing=True in this case otherwise
> existing Table objects as the one you are creating with SqaGlobalContext
> will not be affected.
>
>
>
> On Tue, Aug 18, 2020, at 1:59 PM, Vitaly Kruglikov wrote:
>
> Dear all,
>
> I am using:
> sqlalchemy==1.3.18
> psycopg2==2.8.4
> connection url schema: "postgresql+psycopg2://..."
> postgres 10.x
>
> when I define an explicit AutomapBase-derived model for
> 'sqa_global_context' table with only the primary key, I expected that
> running `Base.metadata.reflect(bind=database.ENGINE,
> only=['sqa_global_context']); Base.prepare()` would backfill missing
> columns and relationships in that table. However, after running
> `Base.prepare(engine, reflect=True)`, the missing columns and relationships
> are not populated in my table.
>
> My code looks like this:
>
> ```
> _AutomapBase = automap.automap_base()
>
> class SqaGlobalContext(_AutomapBase):
> """Type template for the SqaGlobalContext table."""
> __tablename__ = 'sqa_global_context'
>
> key = sa.Column(
> sa.Integer, primary_key=True, server_default=sa.text('0'))
>
> _AutomapBase.metadata.reflect(bind=ENGINE,
> only=['ewf_sqa_global_context', ])
> _AutomapBase.prepare()
> ```
>
> The reason I use `_AutomapBase.metadata.reflect(ENGINE, only=[...])`
> followed by `_AutomapBase.prepare()` instead of just
> `_AutomapBase.prepare(ENGINE, reflect=True)` is because
> `_AutomapBase.prepare(ENGINE, reflect=True)` ends up reflecting **all** the
> tables in the database and I wanted to avoid the unnecessary overhead of
> reflecting the many additional tables that are managed by another subsystem
> not of interest to my application.
>
> So, after running the above code, I expected `SqaGlobalContext` model to
> be populated with additional columns (`created_at`, `updated_at`, etc.) and
> relationships based on foreign keys (see below for actual table DDL). But
> the missing columns and relationships didn't get added:
>
> ```
>
> dir(SqaGlobalContext)
>
> ['__abstract__', '__class__', '__delattr__', '__dict__', '__dir__',
> '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__',
> '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__',
> '__mapper__', '__module__', '__ne__', '__new__', '__reduce__',
> '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__',
> '__subclasshook__', '__table__', '__tablename__', '__weakref__',
> '_decl_class_registry', '_sa_class_manager', '_sa_decl_prepare',
> '_sa_raise_deferred_config', 'classes', 'key', 'metadata', 'prepare']
>
> ```
>
>
> As you can see from `dir(SqaGlobalContext)` - the additional columns
> didn't get populated in `SqaGlobalContext`. So, how can I make this work
> without reflecting the all the tables in the database?
>
> Alembic outputs this when generating this table:
>
> ```
> INFO [sqlalchemy.engine.base.Engine]
> CREATE TABLE ewf_sqa_global_context (
> key INTEGER DEFAULT 0 NOT NULL,
> created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
> updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
> testing_suspended BOOLEAN DEFAULT false NOT NULL,
> metadata_tag VARCHAR(256) DEFAULT NULL,
> attempt_number INTEGER DEFAULT NULL,
> attempt_context_id INTEGER DEFAULT NULL,
> CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key),
> CONSTRAINT fk_ewf_sqa_global_context_metadata_tag_ewf_empower_metadata
> FOREIGN KEY(metadata_tag) REFERENCES ewf_empower_metadata (tag) ON DELETE
> RESTRICT ON UPDATE CASCADE,
> CONSTRAINT fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183
> FOREIGN KEY(attempt_context_id) REFERENCES ewf_sqa_attempt_context (key) ON
> DELETE RESTRICT ON UPDATE CASCADE
> ```
>
> Actual Schema in database.
> ```
> CREATE TABLE public.ewf_sqa_global_context
> (
> key integer NOT NULL DEFAULT 0,
> created_at timestamp with time zone NOT NULL DEFAULT now(),
> updated_at timestamp with time zone NOT NULL DEFAULT now(),
> testing_suspended boolean NOT NULL DEFAULT false,
> metadata_tag character varying(256) COLLATE pg_catalog."default"
> DEFAULT NULL::character varying,
> attempt_number integer,
> attempt_context_id integer,
> CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key),
> CONSTRAINT
> fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183 FOREIGN KEY
> (attempt_context_id)
>
[sqlalchemy] Re: `Base.prepare()` doesn't backfill missing columns and relationships in AutomapBase-derived models
I just noticed a TYPO - but google groups doesn't let me edit my post. In a couple of places in my post, I accidentally entered `sqa_global_context` as the table name instead of `ewf_sqa_global_context`. But in actual code that I am executing, it's `ewf_sqa_global_context` everywhere. -- 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/09007648-48b7-4d3f-bb84-7b9cc4e1a35bo%40googlegroups.com.
[sqlalchemy] `Base.prepare()` doesn't backfill missing columns and relationships in AutomapBase-derived models
Dear all,
I am using:
sqlalchemy==1.3.18
psycopg2==2.8.4
connection url schema: "postgresql+psycopg2://..."
postgres 10.x
when I define an explicit AutomapBase-derived model for
'sqa_global_context' table with only the primary key, I expected that
running `Base.metadata.reflect(bind=database.ENGINE,
only=['sqa_global_context']); Base.prepare()` would backfill missing
columns and relationships in that table. However, after running
`Base.prepare(engine, reflect=True)`, the missing columns and relationships
are not populated in my table.
My code looks like this:
```
_AutomapBase = automap.automap_base()
class SqaGlobalContext(_AutomapBase):
"""Type template for the SqaGlobalContext table."""
__tablename__ = 'sqa_global_context'
key = sa.Column(
sa.Integer, primary_key=True, server_default=sa.text('0'))
_AutomapBase.metadata.reflect(bind=ENGINE,
only=['ewf_sqa_global_context', ])
_AutomapBase.prepare()
```
The reason I use `_AutomapBase.metadata.reflect(ENGINE, only=[...])`
followed by `_AutomapBase.prepare()` instead of just
`_AutomapBase.prepare(ENGINE, reflect=True)` is because
`_AutomapBase.prepare(ENGINE, reflect=True)` ends up reflecting **all** the
tables in the database and I wanted to avoid the unnecessary overhead of
reflecting the many additional tables that are managed by another subsystem
not of interest to my application.
So, after running the above code, I expected `SqaGlobalContext` model to be
populated with additional columns (`created_at`, `updated_at`, etc.) and
relationships based on foreign keys (see below for actual table DDL). But
the missing columns and relationships didn't get added:
```
dir(SqaGlobalContext)
['__abstract__', '__class__', '__delattr__', '__dict__', '__dir__',
'__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__',
'__hash__', '__init__', '__init_subclass__', '__le__', '__lt__',
'__mapper__', '__module__', '__ne__', '__new__', '__reduce__',
'__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__',
'__subclasshook__', '__table__', '__tablename__', '__weakref__',
'_decl_class_registry', '_sa_class_manager', '_sa_decl_prepare',
'_sa_raise_deferred_config', 'classes', 'key', 'metadata', 'prepare']
```
As you can see from `dir(SqaGlobalContext)` - the additional columns didn't
get populated in `SqaGlobalContext`. So, how can I make this work without
reflecting the all the tables in the database?
Alembic outputs this when generating this table:
```
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE ewf_sqa_global_context (
key INTEGER DEFAULT 0 NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
testing_suspended BOOLEAN DEFAULT false NOT NULL,
metadata_tag VARCHAR(256) DEFAULT NULL,
attempt_number INTEGER DEFAULT NULL,
attempt_context_id INTEGER DEFAULT NULL,
CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key),
CONSTRAINT fk_ewf_sqa_global_context_metadata_tag_ewf_empower_metadata
FOREIGN KEY(metadata_tag) REFERENCES ewf_empower_metadata (tag) ON DELETE
RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183
FOREIGN KEY(attempt_context_id) REFERENCES ewf_sqa_attempt_context (key) ON
DELETE RESTRICT ON UPDATE CASCADE
```
Actual Schema in database.
```
CREATE TABLE public.ewf_sqa_global_context
(
key integer NOT NULL DEFAULT 0,
created_at timestamp with time zone NOT NULL DEFAULT now(),
updated_at timestamp with time zone NOT NULL DEFAULT now(),
testing_suspended boolean NOT NULL DEFAULT false,
metadata_tag character varying(256) COLLATE pg_catalog."default"
DEFAULT NULL::character varying,
attempt_number integer,
attempt_context_id integer,
CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key),
CONSTRAINT fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183
FOREIGN KEY (attempt_context_id)
REFERENCES public.ewf_sqa_attempt_context (key) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT fk_ewf_sqa_global_context_metadata_tag_ewf_empower_metadata
FOREIGN KEY (metadata_tag)
REFERENCES public.ewf_empower_metadata (tag) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT
```
--
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/280b0e8e-99e7-4c53-af65-297ad1ed5ba4o%40googlegroups.com.
