can sqlalchemy use the "from only" clause of postgresql?

The dialect supports PostgreSQL’s ONLY keyword for targeting only a particular table in an inheritance hierarchy. This can be used to produce the SELECT ... FROM ONLY, UPDATE ONLY ..., and DELETE FROM ONLY ... syntaxes. It uses SQLAlchemy’s hints mechanism:,The dialect supports PG 8.2’s INSERT..RETURNING, UPDATE..RETURNING and DELETE..RETURNING syntaxes. INSERT..RETURNING is used by default for single-row INSERT statements in order to fetch newly generated primary key identifiers. To specify an explicit RETURNING clause, use the _UpdateBase.returning() method on a per-statement basis:,It is recommended that you use the EXPLAIN ANALYZE... tool from PostgresSQL to ensure that you are generating queries with SQLAlchemy that take full advantage of any indexes you may have created for full text search.,Boolean expression. Returns true if the right hand operand, which can be an element or a range, is contained within the column.

Table('sometable', metadata,
   Column('id', Integer, Sequence('some_id_seq'), primary_key = True)
)

engine = create_engine(
   "postgresql+pg8000://scott:tiger@localhost/test",
   isolation_level = "READ UNCOMMITTED"
)

connection = engine.connect()
connection = connection.execution_options(
   isolation_level = "READ COMMITTED"
)

test => CREATE TABLE test_schema.referred(id INTEGER PRIMARY KEY);
CREATE TABLE
test => CREATE TABLE referring(
      test( > id INTEGER PRIMARY KEY,
         test( > referred_id INTEGER REFERENCES test_schema.referred(id)); CREATE TABLE test => SET search_path TO public, test_schema; test => SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM test - > pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n test - > ON n.oid = c.relnamespace test - > JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid test - > WHERE c.relname = 'referring'
         AND r.contype = 'f'
         test - > ; pg_get_constraintdef
         -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
         FOREIGN KEY(referred_id) REFERENCES referred(id)
         (1 row)

test => SET search_path TO public;
SET

test => SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM
test - > pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
test - > ON n.oid = c.relnamespace
test - > JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
test - > WHERE c.relname = 'referring'
AND r.contype = 'f';
pg_get_constraintdef
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
FOREIGN KEY(referred_id) REFERENCES test_schema.referred(id)
   (1 row)

When using "AND" in where clause get error "argument must be type boolean",Can SQLAlchemy use the "from only" clause of PostgreSQL?,Error when creating a PostgreSQL database using python, sqlalchemy and psycopg2,Group by clause in mySQL and postgreSQL, why the error in postgreSQL?

@main.route('/searchresults/<years>')
   def yearresults(years):
   # convert years to list
   years = years.replace('[', '')
   years = years.replace(']', '')
   years = years.split(',')

   entries = db.session.query(Post).filter(Post.fiscalyear.in_(years)).all()
   return render_template('yearsearch.html', entries=entries)

One convenient way to create a compatible table that will later be made a new child is to use the LIKE clause in CREATE TABLE. This creates a new table with the same columns as the source table. If there are any CHECK constraints defined on the source table, the INCLUDING CONSTRAINTS option to LIKE should be specified, as the new child must have constraints matching the parent to be considered compatible.,Here the ONLY keyword indicates that the query should apply only to cities, and not any tables below cities in the inheritance hierarchy. Many of the commands that we have already discussed — SELECT, UPDATE and DELETE — support the ONLY keyword.,ALTER TABLE will propagate any changes in column data definitions and check constraints down the inheritance hierarchy. Again, dropping columns that are depended on by other tables is only possible when using the CASCADE option. ALTER TABLE follows the same rules for duplicate column merging and rejection that apply during CREATE TABLE.,In some cases you might wish to know which table a particular row originated from. There is a system column called tableoid in each table which can tell you the originating table:

CREATE TABLE cities(
   name text,
   population float,
   elevation int--in feet
);

CREATE TABLE capitals(
   state char(2)
) INHERITS(cities);

SELECT name, elevation
FROM cities
WHERE elevation > 500;

   name | elevation
      -- -- -- -- -- - + -- -- -- -- -- -
      Las Vegas | 2174
   Mariposa | 1953
   Madison | 845

SELECT name, elevation
FROM cities *
   WHERE elevation > 500;

SELECT c.tableoid, c.name, c.elevation
FROM cities c
WHERE c.elevation > 500;

The filter clause extends aggregate functions (sum, avg, count, …) by an additional where clause. The result of the aggregate is built from only the rows that satisfy the additional where clause too.,When using a set quantifier (distinct or all) it must remain in the aggregate function prior the case expression.,As the above described alternative with the case expression is very widely supported I recommend using that approach rather than the proprietary alternatives offered by some products.,If an aggregate function is used as a window function (over clause), the syntactic order is: aggregate function, filter clause, over clause:

SUM(<expression>) FILTER(WHERE <condition>)

SUM(...) FILTER(WHERE...) OVER(...)

SUM(<expression>) FILTER(WHERE <condition>)

COUNT(*) FILTER (WHERE <condition>)

COUNT(*) FILTER (WHERE <condition>)

COUNT(CASE WHEN <condition> THEN 1 END)