FLASK SQlite Python - Checking if username is already in database

First, I think I have a working code sample for the original problem. However, I think you can solve this duplicate user problem better using constraints within the database. See the bottom of my answer.

First let's check the current code. There are a couple of issues I can see here:

1. Incorrect indent for try/finally means there's no connection active during the try/finally.
2. The way you're checking for whether there's an entry with the username isn't correct.

Indent

In the current code, the try/finally block at line 6 needs to be indented one further in order to be able to use the connection established by the connection made in the with statement on line 4.

As the code currently stands, the connection will have been closed by the time it's used, so all database access will fail.

Checking for a user

The code used to check for a user will fail, throwing an exception which will cause the finally to be hit and the rollback to be executed. The return value from execute throws an exception when you call len, whether or not there are any entries.

Here's what I get from a python shell to show what I mean:

>>> int(len(cur.execute("select * from people where name_last=:who", {"who": "mike"})))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: object of type 'sqlite3.Cursor' has no len()

Instead, to check whether a select statement returns a value, use fetchone and see if it returns None:

# No users named `fred`:
>>> cur.execute("select * from people where name_last=:who", {"who": "fred"}) 
<sqlite3.Cursor object at 0x10bbd0180>
>>> cur.fetchone() is None
True

# At least one user named `mike`:
>>> cur.execute("select * from people where name_last=:who", {"who": "mike"})
<sqlite3.Cursor object at 0x10bbd0180>
>>> cur.fetchone() is None
False

I think therefore something like this might work:

def regist():
    if request.method == "POST":
        with sql.connect("database.db") as con:
            cur = con.cursor()
            try:
                # ... Collecting form info ...

                cur.execute("SELECT * FROM users WHERE name = ?", (username))

                if cur.fetchone() is not None:
                    flash("That username is already taken...")
                    return render_template('register.html')
                else:
                    cur.execute("INSERT INTO users (name,password,email) VALUES (?,?,?)",(username,passwordencr,email) )
                    con.commit()
                    flash (...)
             except:
                 con.rollback()

             finally:
                 session['logged_in'] = True
                 session['username'] = username
                 # ... mailing code ...

Alternative approach

A more robust approach would be to let the database take responsibility for preventing duplicate users.

Make the name column unique when creating the table. Then inserting a record with the same username will throw an exception. Taking an example from the sqlite3 docs:

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("create table person (id integer primary key, firstname varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# con.rollback() is called after the with block finishes with an exception, the
# exception is still raised and must be caught
try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print "couldn't add Joe twice"