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 stackoverflow.com/questions/6242756/… 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
– stellasia, Commented May 6, 2015 at 8: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
– c.m, Commented May 6, 2015 at 8:44

Daniel Roseman · Accepted Answer · 2015-05-06 08:41:23Z

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 6, 2015 at 8:41

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

stellasia · Accepted Answer · 2015-05-06 08:55:32Z

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

Collectives™ on Stack Overflow

Inserting row into SQLite table

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related