CODEWITHSUNDEEP
pythonsqlitepython-3.4
c.m
c.m

Reputation: 95

Inserting row into SQLite table

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)
);

Upvotes: 0

Views: 1755

Answers (2)

stellasia
stellasia

Reputation: 5612

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

Upvotes: 1

Daniel Roseman
Daniel Roseman

Reputation: 599470

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

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

Upvotes: 3

Related Questions