SQLAlchemy commands

SQLAlchemy
##########
from sqlalchemy import func
from sqlalchemy.orm import Session
from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine

self.db_engine, self.db_conn = create_db_conn()

self.Base = automap_base()
self.Base.prepare(self.db_engine, reflect=True)
self.db_session = Session(self.db_engine)

self.table1_obj = self.Base.classes.table1
self.table2_obj = self.Base.classes.table2

DATABASE_URI = 'postgresql://postgres:password@localhost:5432/test'

def create_db_conn():
    db_engine = None
    db_conn = None
    try:
        sql_alchemy_engine = create_engine(f'{DATABASE_URI}')

    return db_engine, db_conn



COUNT
#####
rows = vobj.db_session.query(self.table1_obj).count()
print(rows)
rows = vobj.db_session.query(self.table2_obj).count()
print(rows)

activities = self.db_session.query(self.table1_obj).order_by(
            self.table1_obj.id).all()

UPDATE
######
ea = self.db_session.query(self.table2_obj).get(self.activity_id)
            ea.status = status
            self.db_session.commit()

GET
####
current_activity = self.db_session.query(self.table1_obj).filter_by(
    activity_id=self.venue_base_activity_id,
    source_id=row['source_id'],
    venue_id=row['vid']).first()
if current_activity:
    link_key = current_activity.link_key
    market_place_df.loc[index, 'link_key'] = link_key

GET Specific Columns
####################
result = db_session.query(table1_obj.source_id, table1_obj.vid, table1_obj.name,
                              table1_obj.city, table1_obj.state)\
            .filter(table1_obj.source_id.in_((1, 2)))\
            .group_by(table1_obj.source_id, table1_obj.vid, table1_obj.name,
                      table1_obj.city, table1_obj.state)\
            .order_by(table1_obj.source_id).all()

for row in result:
    try:
        source_id = row[0]
        name = clean_string(row[2])
        city = clean_string(row[3])
        state = clean_string(row[4])

GET Count
#########
ws_count = self.db_session.query(func.count(self.table1_obj.id))\
                            .filter_by(source_id=market_place2_source_id).scalar()

group_by
#######
HygroRecord.query.group_by(HygroRecord.sensor_uuid)
        .having(func.max(HygroRecord.timestamp)).all())

insert
######
self.db_session.add(
            self.table1(
                id=row_id,
                name=name
            )
        )
self.db_session.commit()

Insert - bulk insert
#####################
ll = []
ll.append(self.table2(
        id=self.activity_id,
        name=name,
        city=city,
        state=state
    )
)
self.db_session.bulk_save_objects(ll)
self.db_session.commit()


flush
########

    f = vobj.table1(
            id1=9,
            id2=18
        )
    vobj.db_session.add(f)
    vobj.db_session.flush() #without commit if you want to get insert id
    print(f.id)
    vobj.db_session.commit()