Columns error migrating to autoload_with

[Demitri Muna]

Hello,

I’m working on migrating my existing code to the updated APIs. My class definitions for tables before looked like this:

class MyTable(Base):
__tablename__ = ‘my_table’
__table_args__ = {'autoload' : True, 'schema' : ‘my_schema’}

I’m trying to implement declarative mapping via reflected tables, as described here:
https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#mapping-declaratively-with-reflected-tables

This is my new code:

engine = create_engine(“postgresql:…")
metadata = MetaData()
metadata.reflect(bind=dbc.engine, schema='my_schema’)

class MyTable(Base):
__table__ = Table(name=‘my_table', schema=‘my_schema',
metadata=metadata, autoload_with=engine)

This is the (truncated) error I’m getting:

AttributeError: 'Table' object has no attribute '_columns'
> /usr/custom/anaconda/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py(747)columns()
745 self._init_collections()
746 self._populate_column_collection()
--> 747 return self._columns.as_immutable()
748
749 @property

Is there more that’s needed to be done to define the class? I can create a MWE, but thought I’d check to see if I’m missing something new.

Related to this, I currently pickle my metadata to reuse since the schema changes infrequently. When I have this, I use:

metadata = <load from pickle>

class MyTable(Base):
__table__ = metadata.tables[‘my_schema.my_table’]

Will this still work? Although from what I read from the docs, I wondered if I can use the Table(…) line above unchanged in this case as Metadata won’t go to the server if the 'schema.table’ is in the class?

Cheers,
Demitri

[Mike Bayer]

hi there -

would need a complete example and SQLAlchemy version in use, Table does have a "_columns" attribute and both patterns described below are things that should work without an error like this, additionally the complete stack trace would also be helpful.     Unpickling the metadata is a place where it is more plausible that the state of the Table is broken but that does not apply when you are just doing autoload_with.

-- 

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/027BCB9A-B700-457A-8A6A-2217C6927A0E%40gmail.com.

 

[demitr...@gmail.com]

Hi Mike,

Thanks for the reply. Below is a minimal complete example on SQLAlchemy v1.4.2, Python 3.8.3.

PostgreSQL schema:

CREATE SCHEMA autoload_test AUTHORIZATION postgres;

ALTER DEFAULT PRIVILEGES IN SCHEMA autoload_test GRANT ALL ON TABLES TO demitri;

CREATE TABLE autoload_test.my_table

(

    pk integer NOT NULL,

    label text COLLATE pg_catalog."default" NOT NULL,

    CONSTRAINT my_table_pkey PRIMARY KEY (pk)

);

ALTER TABLE autoload_test.my_table OWNER to postgres;

    

Python script:

#!/usr/bin/env python

from sqlalchemy.orm import registry

from sqlalchemy.schema import Table

from sqlalchemy import create_engine, MetaData

engine = create_engine("postgresql://postgres@localhost:5432/postgres")

metadata = MetaData()

metadata.reflect(bind=engine, schema='autoload_test')

mapper_registry = registry()

Base = mapper_registry.generate_base()

class MyTable(Base):

    __table__ = Table(name='my_table', schema='autoload_test',

                      metadata=metadata, autoload_with=engine)

I'm also curious how to accomplish the same with a @mapper_registry.mapped decorator, but first things first. :)

And this is the traceback I get:

Traceback (most recent call last):

  File "/Users/demitri/autoload_mve/autoload_test.py", line 14, in <module>

    class MyTable(Base):

  File "/usr/local/anaconda/lib/python3.8/site-packages/sqlalchemy/orm/decl_api.py", line 75, in __init__

    _as_declarative(reg, cls, dict_)

  File "/usr/local/anaconda/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 126, in _as_declarative

    return _MapperConfig.setup_mapping(registry, cls, dict_, None, {})

  File "/usr/local/anaconda/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 177, in setup_mapping

    return cfg_cls(registry, cls_, dict_, table, mapper_kw)

  File "/usr/local/anaconda/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 314, in __init__

    self._early_mapping(mapper_kw)

  File "/usr/local/anaconda/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 200, in _early_mapping

    self.map(mapper_kw)

  File "/usr/local/anaconda/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 971, in map

    mapper_cls(self.cls, self.local_table, **self.mapper_args),

  File "<string>", line 2, in __init__

  File "/usr/local/anaconda/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 298, in warned

    return fn(*args, **kwargs)

  File "/usr/local/anaconda/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 684, in __init__

    self._configure_properties()

  File "/usr/local/anaconda/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 1421, in _configure_properties

    for column in self.persist_selectable.columns:

  File "/usr/local/anaconda/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 1095, in __get__

    obj.__dict__[self.__name__] = result = self.fget(obj)

  File "/usr/local/anaconda/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py", line 747, in columns

    return self._columns.as_immutable()

AttributeError: 'Table' object has no attribute '_columns'

Cheers,

Demitri

[Mike Bayer]

the constructor for Table is not expecting that you pass the "name" and "metadata" argument as keyword arguments and this is causing the reflection process to not occur at all.

specify the table like this:

Table('my_table', metadata, schema='test_schema', autoload_with=engine)

There's some unsmoothness here in SQLAlchemy both not warning about the keyword arguments and being ungraceful when no columns are present so https://github.com/sqlalchemy/sqlalchemy/issues/6135 is added.

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/bf439736-6195-449b-8256-5afd355e7161n%40googlegroups.com.

[Demitri Muna]

Ah, interesting. I updated my code and it works great, thanks!

Demitri