CODEWITHSUNDEEP
pythonsqlitetransactionsflask
JCL
JCL

Reputation: 189

Transactions in SQLite with Flask

I'm writing a Python web app using Flask with SQLite as the database

I have a table with an auto-incrementing PK and filenames. I want to generate the filenames as I'm inserting into this table, so that the first file is named file1, second file2, etc. What is the best way to do this? My first thought was to just do a transaction where I figure out the next auto-inc, generate the filename, then insert into the database and commit. Problem is I can't seem to find anything about transactions in Flask, it sounds like it's done automatically, but I'm not sure if I can rely on the id lookup and insert being atomic. The other alternative I guess would be somehow using the auto-inc value in the insert statement, is that new value available at that point somehow? Any insight into these approaches or an alternative would be appreciated!

Upvotes: 1

Views: 1877

Answers (1)

bsa
bsa

Reputation: 2801

If I understand your question correctly, you have a table something like this:

CREATE TABLE files (
  file_id INTEGER PRIMARY KEY AUTOINCREMENT,
  filename TEXT
)

You would like to atomically:

  1. Create a new row in the table with an auto-incremented primary key value.
  2. Get that primary key.
  3. Set the filename for the row with a name derived from the key value.

With sqlite3 in Python, using a transaction (as you requested):

import sqlite3
con = sqlite3.connect('files.db')
with con:   # Context manager on the connection manages the transaction
    cur = con.cursor()
    cur.execute('INSERT INTO files DEFAULT VALUES')
    file_id = cur.lastrowid   # Get the primary key of the new row
    cur.execute(
        'UPDATE files SET filename="file"||file_id WHERE file_id=?',
        (file_id,)
    )

See here for more details on the context manager:

http://docs.python.org/2/library/sqlite3.html

After running:

sqlite> SELECT * FROM files;
1|file1

There is no real difference due to the fact that you are using Flask. However, you would probably put your call to connect in before_request and assign the result to a global as shown here:

http://flask.pocoo.org/docs/patterns/sqlite3/

Finally, other approaches you may consider are a VIEW on the table (if the filename will never change, and is always simply derived from the primary key), or an AFTER INSERT trigger for the table.

Upvotes: 2

Related Questions