why use sqlalchemy declarative api?

Question

New to sqlalchemy and somewhat novice with programing and python. I had wanted to query a table. It seems I can use the all() function when querying but cannot filter without creating a class.

1.) Can I filter without creating a class and using the declarative api? Is the filtering example stated below incorrect? 2.) When would it be appropriate to use declarative api in sqlalchemy and when would it not be appropriate?

import sqlalchemy as sql
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import sessionmaker

db = sql.create_engine('postgresql://postgres:password@localhost:5432/postgres')
engine = db.connect()
meta = MetaData(engine)
session = sessionmaker(bind=engine)
session = session()

files = Table('files',meta,
Column('file_id',Integer,primary_key=True),
Column('file_name',String(256)),
Column('query',String(256)),
Column('results',Integer),
Column('totalresults',Integer),
schema='indeed')

session.query(files).all() #ok
session.query(files).filter(files.file_name = 'test.json') #not ok

univerio · Accepted Answer · 2016-08-03 17:56:27Z

1

If you want to filter by a Table construct, it should be:

session.query(files).filter(files.c.file_name == 'test.json')

You need to create mapped classes if you want to use the ORM features of SQLAlchemy. For example, with the code you currently have, in order to do an update you have to do

session.execute(files.update().values(...))

As opposed to:

file = session.query(File).first()
file.file_name = "new file name"
session.commit()

The declarative API happens to be the easiest way of constructing mapped classes, so use it if you want to use the ORM.

answered Aug 3, 2016 at 17:56

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jimmy Over a year ago

Thanks! It seems that I would have to use execute()for Table/core constructs as I'm getting a 'keyword can't be an expression'.

univerio Over a year ago

@Jimmy "keyword can't be an expression" is a SyntaxError that indicates you are using the assignment operator = instead of the equality comparison operator ==.

Eugenia Benua · Accepted Answer · 2016-08-03 12:24:42Z

1

Filter using declarative api this way:

session.query(files).filter(files.file_name == 'test.json').all()

You can also use raw sql queries (docs).

Whether using declarative api or not may depend on your queries complexity, because sometimes sqlalchemy doesn't optimize them right way.

answered Aug 3, 2016 at 12:24

Eugenia Benua

914 bronze badges

1 Comment

Jimmy Over a year ago

Thanks! It seems that declarative is the way to go as the expressions are simpler. I would have to use execute() and type in raw sql otherwise.

Collectives™ on Stack Overflow

why use sqlalchemy declarative api?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related