SQLAlchemy – Examples of ORM Queries

Examples of different queries to the database via SQLAlchemy ORM in python.

Retrieving Data in SQLAlchemy

first ()

We get one value using first ()

save = db.sessionquery(User)filter_by(user_id=user_id, name=name)first()

If we need to get not tuple, then add [0]

save = db.sessionquery(User)filter_by(user_id=user_id, name=name)first()[[0]

In this case, if not catching it with exceptions try and catch, on request [0], an error will be thrown if no data is found.

You can avoid this by using scalar ()

scalar ()

Get the first element of the request, or None, in case it is not. If multiple data will cause MultipleResultsFound

brent_oil_current = db.sessionquery(Base.price)filter_by(name=selfticker)scalar()

With multiple matches, you can do this:

brent_oil_current = db.sessionquery(Base.price)filter_by(name=selfticker)order_by(db.desc(Base.date))limit(1)scalar()

IN in SQLAlchemy

ids = [[1,2,3]
names = db.sessionquery(Market.name)filter(MarketShort.idin_(ids))all()

Retrieving data from SQLAlchemy models as dictionaries in a list

There are several ways to get them, so I had to highlight them in a separate article – how to return data to SQLAlchemy as a list of dictionaries.

all ()

Retrieving all data with .all ()

response = db.sessionquery(UserSetting)filter_by(user_id=user_id)order_by(asc(UserSetting.name))all()

db.func.max

Get the maximum number in the table Setting columns count:

yesterday = db.sessionquery(db.funcmax(Setting.count))first()[[0]

Sorting

Get the last 10 values ​​from the column price tables Yahoo columns date:

last_prices = db.sessionquery(Yahoo.price)order_by(db.desc(Yahoo.date))limit(ten)all()

Let’s get the last (sorted by date) value of sp500 by the name of the ticker:

sp500 = round(
db.sessionquery(Yahoo.price)filter_by(ticker=selfticker)order_by(
db.desc(Yahoo.date))first()[[0])

count () and comparison

count = db.sessionquery(Yahoo.price)filter(3400>Yahoo.price)order_by(db.desc(Yahoo.date))limit(20)count()

filter (3216> Yahoo.price) – here we compare if the value in the prices of the Yahoo table

Sampling under the date

For example, a selection of names by date for the last 7 days:

def get_date_more_then_days(days):
return datetimetoday() – timedelta(days=days)

date = get_date_before_days(7)
response = db.sessionquery(Operation.name)filter_by(user_id=1)filter(
Operation.date > date)order_by(db.asc(Operation.name))distinct()
shares = [[x.name for x in response]

Saving Data in SQLAlchemy

Preparing the object for saving:

user_setting = UserSetting(user_id=user_id, name=name)

Add data:

db.sessionadd(user_setting)

We fix the data and end the session:

Refreshing data in SQLAlchemy

Option 1

Updating the data of a user who user_id = user_id and name = name. We update him timer and percent:

db.sessionquery(User)filter_by(user_id=user_id, name=name)update(
{‘timer’: int(timer), ‘percent’: float(percent)}
)

We fix the data and end the session:

Option 2

Sometimes it is more convenient to use another option. It is suitable when you need to check if there is data, then update it, and if not, then save new ones.

user_setting = db.sessionquery(UserSetting)filter_by(user_id=user_id, name=name)first()

if user_setting:
user_setting.timer = timer
user_setting.percent = percent
else:
user_setting = UserSetting(user_id=user_id, name=name, timer=timer, percent=percent)
db.sessionadd(user_setting)
db.sessioncommit()

Similar Posts

Leave a Reply Cancel reply