CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Mark Warburton
Mark Warburton

Reputation: 517

SQLAlchemy view handling

Please tell me how to select and manipulate rows from a VIEW similarly to how I use SQLAlchemy on a TABLE.

Here's how I would do the query if name_address_vw were a table (Engine already set up using JDBC Psycopg2 to a PostgreSQL database and not shown):

meta = MetaData(Engine,reflect=True)
nav = meta.tables['name_address_vw']
Session = sessionmaker(bind=engine)
session = Session()
navRow = session.query(
        nav
    ).filter(
        nav.c.address.like("%pad%")
    ).first()
print (navRow.name)

SQLAlchemy fails because name_address_vw is not a key member of meta.tables I understand this is because it is a view, not a table.

Here are the SQL statements to set up and populate the backend PostgreSQL:

CREATE TABLE name
(
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE address
(
    id SERIAL PRIMARY KEY,
    idA INTEGER REFERENCES name(id),
    address VARCHAR
);

CREATE VIEW name_address_vw AS SELECT
    A.id,
    A.name,
    B.address
FROM name A
LEFT JOIN address B ON A.id = B.idA;

INSERT INTO name(
id, name)
VALUES 
    (1, 'John Soap'),
    (2, 'Jane Doe');

INSERT INTO address(
id, idA, address)
VALUES
    (1, 1, 'John''s place'),
    (2, 2, 'Jane''s pad');

In case my SQLAlchemy code above is unclear, here is the PostreSQL query equivalent:

SELECT name FROM name_address_vw WHERE address LIKE '%pad%' LIMIT 1;

The example I have given is highly simplified and I need to be able to query other columns on the view rows once I have retrieved them, so please provide me with a general solution rather than one that works solely with this example—many thanks.

Upvotes: 7

Views: 11008

Answers (2)

Giu Belli
Giu Belli

Reputation: 66

Since 2.0 binding the Metadata object to an Engine is deprecated, the correct way would be:

meta = MetaData()
meta.reflect(bind=Engine, views=True)
nav = meta.tables['name_address_vw']

Link to doc: https://docs.sqlalchemy.org/en/14/changelog/migration_20.html#implicit-and-connectionless-execution-bound-metadata-removed .

Upvotes: 3

Ilja Everilä
Ilja Everilä

Reputation: 52929

The use of reflect parameter of MetaData has been deprecated since version 0.8 and should be replaced with the more expressive MetaData.reflect() method, which allows reflecting views as well:

meta = MetaData(Engine)
meta.reflect(views=True)
nav = meta.tables['name_address_vw']

Upvotes: 15

Related Questions