Using or in SQLalchemy

Using OR in SQLAlchemy

From the tutorial:

from sqlalchemy import or_
filter(or_(User.name == 'ed', User.name == 'wendy'))

sqlalchemy how to using AND in OR operation?

The query is semantically the same because AND has precedence over OR, therefore

WHERE Cond1 AND Cond2 OR Cond3 AND Cond4 will produce the same result as

WHERE (Cond1 AND Cond2) OR (Cond3 AND Cond4).

Were you to try the other way around (switch or_ and and_ in your code), you would notice that sqlalchemy does generate parenthesis.

Flask SQLAlchemy filter by value OR another

The following may help:

# app.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'url_or_path/to/database'
db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50), unique=True)
    name = db.Column(db.String(30))

    def __init__(self, name=None, email=None):
        if not name:
            raise ValueError('\'name\' cannot be None')
        if not email:
            raise ValueError('\'email\' cannot be None')
        self.name = name
        self.email = email

class UserQuery(object):
    @staticmethod
    def get_user_id_by_email_or_name(email=None, name=None):
        user = User.query.filter((User.email == email) | (User.name == name)).first()
        return user.id if hasattr(user, 'id') else None

The '|' can be used inside a filter instead of 'or_'. See Using OR in SQLAlchemy.

You can use like this:

>>> from app import db, User, UserQuery
>>> db.create_all()
>>> user = User(name='stan', email='stan@email.com')
>>> db.session.add(user)
>>> db.session.commit()
>>> by_name_id = UserQuery.get_user_id_by_email_or_name(name='stan')
>>> by_email_id = UserQuery.get_user_id_by_email_or_name(email='stan@email.com')
>>> by_name_id == by_email_id
True

SQLAlchemy IN clause

How about

session.query(MyUserClass).filter(MyUserClass.id.in_((123,456))).all()

edit: Without the ORM, it would be

session.execute(
    select(
        [MyUserTable.c.id, MyUserTable.c.name], 
        MyUserTable.c.id.in_((123, 456))
    )
).fetchall()

select() takes two parameters, the first one is a list of fields to retrieve, the second one is the where condition. You can access all fields on a table object via the c (or columns) property.

How do I call a database function using SQLAlchemy in Flask?

If you want to do it without raw sql, you can use func from sqlalchemy:

from sqlalchemy import func

data = db.session.query(func.your_schema.your_function_name()).all()

Simple SELECT statement on existing table with SQLAlchemy

Found this while trying to figure out the same thing.

To select data from a table via SQLAlchemy, you need to build a representation of that table within SQLAlchemy. If Jupyter Notebook's response speed is any indication, that representation isn't filled in (with data from your existing database) until the query is executed.

You need Table to build a table. You need select to select data from the database. You need metadata... for reasons that aren't clear, even in the docs.

from sqlalchemy import create_engine, select, MetaData, Table, and_

engine = create_engine("dburl://user:pass@database/schema")
metadata = MetaData(bind=None)
table = Table(
    'table_name', 
    metadata, 
    autoload=True, 
    autoload_with=engine
)

stmt = select([
    table.columns.column1,
    table.columns.column2
]).where(and_(
    table.columns.column1 == 'filter1',
    table.columns.column2 == 'filter2'
))

connection = engine.connect()
results = connection.execute(stmt).fetchall()

You can then iterate over the results. See SQLAlchemy query to return only n results? on how to return one or only a few rows of data, which is useful for slower/larger queries.

for result in results:
    print(result)

I checked this with a local database, and the SQLAlchemy results are not equal to the raw SQL results. The difference, for my data set, was in how the numbers were formatted. SQL returned float64 (e.g., 633.07), while SQLAlchemy returned objects (I think Decimal, e.g. 633.0700000000.)

Some help from DataCamp's Introduction to Databases in Python

Updating an entity with sqlalchemy ORM

Table objects are not part of SQLAlchemy ORM, they are part of SQLAlchemy Core. In order to use ORM you'll want to do something like this:

from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session

engine = create_engine("sqlite://", echo=True)

# create test environment
with engine.begin() as conn:
    conn.exec_driver_sql("CREATE TABLE my_thing (id int primary key, sell_price int)")
    conn.exec_driver_sql("INSERT INTO my_thing (id, sell_price) VALUES (1, 123)")

Base = automap_base()

class MyThing(Base):
    __tablename__ = "my_thing"

Base.prepare(autoload_with=engine)

# test
with Session(engine) as sess:
    thing_1 = sess.scalar(select(MyThing).where(MyThing.id == 1))
    thing_1.sell_price = 456
    sess.commit()
""" SQL emitted:
UPDATE my_thing SET sell_price=? WHERE my_thing.id = ?
[generated in 0.00032s] (456, 1)
"""

---

Related Topics