How to find all columns with a particular number as an attribute?

Question

I have the following class for a table in SQLAlchemy:

class pinCodes(db.Model):
id = db.Column(db.Integer, primary_key=True)
officename = db.Column(db.String(60), nullable=False)
pincode = db.Column(db.Integer, nullable=False)
taluk = db.Column(db.String(50), nullable=False)
district = db.Column(db.String(50), nullable=False)
state = db.Column(db.String(50), nullable=False)
__tablename__ = "pincodes"

def __init__(self, officename, pincode, taluk, district, state):
    self.officename = officename
    self.pincode = pincode
    self.taluk = taluk
    self.district = district
    self.state = state 

def __repr__(self):
    return '<officename {}>'.format(self.officename)

Now I want to return all the columns with "pincode" as a particular value: Here is the code:

def fmt_pin(code):
    return code.replace(" ","").replace("-","").replace("_","").strip()

def int_pin(code):
    code = fmt_pin(code)
    return int(code)



@app.route('/find_pin_codes', methods=['POST','GET'])
def pincodes():
    if request.method == 'POST':  

        if request.form.get("pincode", "") != "":
            pincode = request.form['pincode']
            try:
                num = pinCodes.query.filter(pincode = int_pin(pincode)).all()
                return render_template('pincodes.html', 
                                        bypincode = num

                                        )
            except Exception as e:
                return render_template('pincodes.html',
                                        error = e)

But when I search for a number I get the following error: filter() got an unexpected keyword argument 'pincode'.

EDIT: After I used filter_by I'm getting no results. Here is my Jinja template code:

  {% if pincode is defined %}

    <thead>
        <tr>
            <td><h4>Pincode</h4></td>
            <td><h4>Office Name</h4></td>
            <td><h4>Taluk</h4></td>
            <td><h4>District</h4></td>
            <td><h4>State</h4></td>
        </tr>
    </thead>

    <tbody>

    {% for o in bypincode %}
        <tr>
            <td><h5>{{ o.pincode }}</h5></td>
            <td><h5>{{ o.officename }}</h5></td>
            <td><h5>{{ o.taluk }}</h5></td>
            <td><h5>{{ o.district }}</h5></td>
             <td><h5>{{ o.state }}</h5></td>
        </tr>
    {% endfor %}  
    {% endif %}

By the way, you don't need `!= ''` as an empty string is false in Python. — Burhan Khalid, Jan 18 '15 at 07:53

warmsea · Accepted Answer · 2015-01-18T08:07:11.817

1

Try filter_by instead of filter.

filter_by(**kwargs) works just as you expected:

pinCodes.query.filter_by(pincode = int_pin(pincode))

But filter(*criterion) takes no keyword arguments, you should use it like this:

pinCodes.query.filter(pinCodes.pincode == int_pin(pincode))

edited Jan 18 '15 at 08:07

answered Jan 18 '15 at 07:51

warmsea

431
3
9

By the way, may be you want to create some INDEX on pincode. Otherwise, the database will scan the entire table every time you filter it. – warmsea Jan 18 '15 at 08:19
Thanks for clarifying on fliter and fliter_by. BTW what do you mean by creating an INDEX on pincode. I don't know what that means. Thank you! – Wally Jan 18 '15 at 12:16
`pincode = db.Column(db.Integer, index=True, nullable=False)` can create an index on pincode. – warmsea Jan 20 '15 at 01:29
1

With an index, searching a column could be faster. But insert or update could be slower. You can search for when to use index for detail. For example, http://stackoverflow.com/questions/79241/how-do-you-know-what-a-good-index-is – warmsea Jan 20 '15 at 01:36

How to find all columns with a particular number as an attribute?

1 Answers1