Home > Software engineering >  Fetch two columns from db using SQLalchemy
Fetch two columns from db using SQLalchemy

Time:01-06

I am learning python and trying to create a drop-down in my form with the data from another table. here is my code

Models.py

class empmasterModel(db.Model):
__tablename__ = "empmaster"
 
Empnum = db.Column(db.Integer, primary_key=True)
Employee_Number = db.Column(db.Integer(),primary_key=True,unique = True)
Employee_Name = db.Column(db.String())
Employee_Type = db.Column(db.String())
Coreid = db.Column(db.String())
EmailId = db.Column(db.String())

def __init__(self, Employee_Number,Employee_Name,Employee_Type,Coreid,EmailId):
    self.Employee_Number = Employee_Number
    self.Employee_Name = Employee_Name
    self.Employee_Type = Employee_Type
    self.Coreid = Coreid
    self.EmailId = EmailId
  
def __repr__(self):
    return f"{self.Employee_Number}:{self.Employee_Name}:{self.Employee_Type}:{self.Coreid}:{self.EmailId}"     
 

Above is my models.py code with class empmaster from which DB table I need the employee number and employee name.

app.py

@app.route('/component/add', methods=['GET', 'POST'])
def componentadd():
    error_msg = ''
    success_msg = ''
    if request.method == 'GET':
        empmaster_data = empmasterModel.query.all()
        print(empmaster_data , "dbbbbbbbbbbbbb")
        return render_template('component/add.html', empmaster_data=empmaster_data)

Above is the app.py code where I am trying to fetch data from empmaster table. But here I am getting whole table data but not two-column data. I tried two column names in brackets after the filter too but it did not work. I searched for many solutions but was not getting desired results. Can somebody help me?

CodePudding user response:

If you have session management, you can do something like:

session.query(
    empmasterModel.Employee_Number.label("Employee_Number"), 
    empmasterModel.Employee_Name.label("Employee_Name")
).all()

If you use Flask with no session management, you can get the session from the SQLAlchemy instance.

db = SQLAlchemy() # Usually in you app.py

And then:

db.session.query(
    empmasterModel.Employee_Number.label("Employee_Number"), 
    empmasterModel.Employee_Name.label("Employee_Name")
).all()
  • Related