Menu
I can't seem to be able to translate the following query into SQLAlchemy.
I would like to translate the following query:
SELECT date_trunc('day', time), "PositionReport".callsign FROM tvh_aircraft."PositionReport" WHERE "PositionReport".reg = 'PH-BVA'
GROUP BY 1, "PositionReport".callsign
I've tried the following, but with no luck.
flight_days = session\
.query(PositionReport)\
.filter(PositionReport.reg == reg) \
.group_by(func.date_trunc('day', PositionReport.time))\
.group_by('1')\
.all()
trunc_date = func.date_trunc('day', PositionReport.time)
flight_days = session.query(trunc_date, PositionReport.callsign) \
.filter(PositionReport.reg == reg) \
.group_by("date_trunc_1")
Thanks in advance for your help.
809
This SQLAlchemy engine is a global object which can be created and configured once and use the same engine object multiple times for different operations. The first step in establishing a connection with the PostgreSQL database is creating an engine object using the create_engine() function of SQLAlchemy.
The DATE_TRUNC function truncates a timestamp expression or literal based on the date part that you specify, such as hour, day, or month.
The psycopg2 is over 2x faster than SQLAlchemy on small table. This behavior is expected as psycopg2 is a database driver for postgresql while SQLAlchemy is general ORM library.
SQLAlchemy is a library that facilitates the communication between Python programs and databases. Most of the times, this library is used as an Object Relational Mapper (ORM) tool that translates Python classes to tables on relational databases and automatically converts function calls to SQL statements.
session.query(func.date_trunc('day', PositionReport.time),
PositionReport.callsign) \
.filter(PositionReport.reg=='PH-BVA') \
.group_by(func.date_trunc('day', PositionReport.time),
PositionReport.callsign).all()
or if you need exactly GROUP BY 1
from sqlalchemy import text
session.query(func.date_trunc('day', PositionReport.time),
PositionReport.callsign) \
.filter(PositionReport.reg=='PH-BVA') \
.group_by(text('1'),
PositionReport.callsign).all()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
