Inserting row into SQLite table

Question

I'm trying to add a row (post submitted by user) into a SQLite table (posts). I need to add the new post with the timestamp being the current time and date and then i need to return the id for this new post.

My code so far (incomplete as im not sure how to return the id and if im inserting the right info):

def post_add(conn, username, message):

    cursor = conn.cursor()
    cursor.execute("INSERT INTO posts (timestamp, username, message) VALUES (CURRENT_TIMESTAMP,?,?)"
                   , (username, message))
    db.commit()

The table:

CREATE TABLE posts (
            id integer unique primary key autoincrement,
            timestamp text default CURRENT_TIMESTAMP,
            username text,
            message text,
            FOREIGN KEY(username) REFERENCES users(name)
);

Have a look at http://stackoverflow.com/questions/6242756/how-to-retrieve-inserted-id-after-inserting-row-in-sqlite-using-python for retrieving the id of the last inserted row in sqlite. As for the timestamp, since you have a default value, you can ommit it in the insert query and it will be automatically added. — stellasia, May 06 '15 at 08:38
@stellasia thanks the first link helped with the return. I also need to set a length limit to the message being posted, whereby if the message is to long it shouldn't allow the post to be inserted. So does that mean i dont need to add CURRENT_TIMESTAMP into VALUES ? — c.m, May 06 '15 at 08:44

score 3 · Accepted Answer · answered May 06 '15 at 08:41

3

The cursor object has the last insert ID in its lastrowid attribute.

cursor.execute('INSERT ...')
new_id = cursor.lastrowid
db.commit()
return new_id

answered May 06 '15 at 08:41

Daniel Roseman

588,541
66
880
895

score 1 · Answer 2 · answered May 06 '15 at 08:55

You can use something like :

def post_add(db, usernick, message):
    cursor = db.cursor()
    cursor.execute("INSERT INTO posts (usernick, content) VALUES (?,?)"
               ,(usernick, message))
    inserted_id = cursor.lastrowid
    db.commit()
    return inserted_id

As for the text size limit, I am not aware of a way to do this in sqlite, but you can check it in python, for example adding these two lines at the begining of your function :

if len(message) > MAX_ALLOWED_LENGTH:
    return None

Inserting row into SQLite table

2 Answers2