How to fetch column values using SQLAlchemy?


Question

I am using Flask+Python and to check if a username (and email) is already taken or not i am using this logic:

@app.route('/register', methods=['GET', 'POST'])
def register():
    form = SignupForm()
    if form.validate_on_submit():
      user = Users.query.filter_by(username=form.username.data).first()
      email = Users.query.filter_by(email=form.email.data).first()
      if form.username.data in user:
        error = 'Username already taken. Choose another'
      elif form.email.data in email:
        error = 'Email already registered. Login or register with another Email'
      else:
          user = Users( 
          form.username.data,
          form.password.data,
          #form.confirm.data ,
          form.email.data,
          1,
          # form.cityaddress.data,
          # form.countryaddress.data,
          #form.accept_tos.data,
      )
      db.session.add(user)
      db.session.commit()
      return redirect(url_for('index'))

But its giving error like object has no attribute 'username'

I know my logic for fetching data from db is not correct. I have little knowledge of SQLalchemy.

Could you suggest me How can i fetch Username (and Email) column value from table Users and then check them if there are same as form.username.data ?

1
0
4/6/2013 10:46:25 AM

Accepted Answer

Your queries look fine, the return value from first() will be an instance of your User object, or None if there were no results:

  u = Users.query.filter_by(username=form.username.data).first()
  if u is not None:
      print u.username
      print u.email      

So given that, here's what your logic could look like:

  user_by_name = Users.query.filter_by(username=form.username.data).first()
  user_by_email = Users.query.filter_by(email=form.email.data).first()
  if user_by_name:
    error = 'Username already taken. Choose another'
  elif user_by_email:
    error = 'Email already registered. Login or register with another Email'
  else:
      #Unique user and email

You could also do it in one query:

 existing = Users.query.filter((Users.username == form.username.data) | (Users.email == form.email.data)).all()
 if existing:
     error = 'User or email taken'

Note the use of filter rather than filter_by - you cant use the bitwise operators in filter_by. Here's a quick working example

3
4/6/2013 12:11:30 PM

Your error confuses me. That said, your code looks okayish, except for the test. I use this then:

user = Users.query.filter_by(username=form.username.data).first()
...
if user is not None:
    error("user already found")

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon