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.session…query(User)…filter_by(user_id=user_id, name=name)…first()
If we need to get not tuple, then add [0]
save = db.session…query(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.session…query(Base.price)…filter_by(name=self…ticker)…scalar()
With multiple matches, you can do this:
brent_oil_current = db.session…query(Base.price)…filter_by(name=self…ticker)…order_by(db.desc(Base.date))…limit(1)…scalar()
IN in SQLAlchemy
ids = [[1,2,3]
names = db.session…query(Market.name)…filter(MarketShort.id…in_(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.session…query(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.session…query(db.func…max(Setting.count))…first()[[0]
Sorting
Get the last 10 values from the column price tables Yahoo columns date:
last_prices = db.session…query(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.session…query(Yahoo.price)…filter_by(ticker=self…ticker)…order_by(
db.desc(Yahoo.date))…first()[[0])
count () and comparison
count = db.session…query(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:
return datetime…today() – timedelta(days=days)
date = get_date_before_days(7)
response = db.session…query(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.session…add(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.session…query(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.
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.session…add(user_setting)
db.session…commit()
