CODEWITHSUNDEEP
pythonsqliteflask
commadelimited
commadelimited

Reputation: 5119

SQLite insert statement failing silently using Flask

I'm following along with the Flask/SQLite tutorial on the Flask website: http://flask.pocoo.org/docs/patterns/sqlite3/

All of my select queries are working fine, but when I try an insert statement nothing happens. I get no error, but I also don't get an inserted row in my database. I'm not sure how to debug this when no error is happening. Does anyone have thoughts?

Here's my setup code:

def connect_db():
    return sqlite3.connect(DATABASE)

@application.before_request
def before_request():
    g.db = connect_db()

@application.teardown_request
def teardown_request(exception):
    if hasattr(g, 'db'):
        g.db.close()

def query_db(query, args=(), one=False):
    cur = g.db.execute(query, args)
    rv = [dict((cur.description[idx][0], value)
               for idx, value in enumerate(row)) for row in cur.fetchall()]
    return (rv[0] if rv else None) if one else rv

Here's an example of a query that works:

@application.route('/api/galleries')
def get_galleries():
    query = query_db('select id, title, thumbnail from galleries')
    return json.dumps(query)

And here's my insert statement. This one does nothing, with no errors:

g.db.execute('insert into photos (is_favorite, galleries_id, filename) values (?, ?, ?)', [is_favorite, galleries_id, filename])

Upvotes: 3

Views: 4370

Answers (2)

mpettis
mpettis

Reputation: 3329

Adding to the answer by @paul ...

From here: https://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager

You can use a context manager (with) and it will commit on exit from the context:

with g.db:
    g.db.execute('insert into photos (is_favorite, galleries_id, filename) values (?, ?, ?)', [is_favorite, galleries_id, filename])

Also, at that link, you can see how to use that in conjunction with try/catch.

Upvotes: 0

Paul
Paul

Reputation: 811

Are you committing after the INSERT?

g.db.execute('insert into photos (is_favorite, galleries_id, filename) values (?, ?, ?)', [is_favorite, galleries_id, filename])
g.db.commit()

Upvotes: 13

Related Questions