How can I let the id be auto generated in my database?

Question

Here is my code:

import sqlite3

def insert(fields=(), values=()):
    connection = sqlite3.connect('database.db')
    # g.db is the database connection
    cur = connection.cursor()
    query = 'INSERT INTO  this_database (%s) VALUES (%s)' % (
        ', '.join(fields),
        ', '.join(['?'] * len(values))
    )
    cur.execute(query, values)
    connection.commit()
    id = cur.lastrowid
    cur.close()
    print (id)

test example:

insert(fields = ("id", "file_name", "url", "time", "type", "description"), values = (2, "file1", "wwww.test.com", "1", "photo", "my first database test"))

I don't want to give the id manually. I want it to add it+1 automatically.

How can I do that?

You shouldn't be using string interpolation with SQL as that's an SQL injection risk — roganjosh, Sep 03 '18 at 08:03
There is also [autoincrement](https://www.sqlite.org/autoinc.html) — roganjosh, Sep 03 '18 at 08:04
Possible duplicate of [Is there an auto increment in sqlite?](https://stackoverflow.com/questions/7905859/is-there-an-auto-increment-in-sqlite) — roganjosh, Sep 03 '18 at 08:06

score 1 · Accepted Answer · answered Sep 03 '18 at 08:23

You have an INTEGER PRIMARY KEY column, which, if you leave it out when inserting items, automatically increments:

INSERT INTO this_database(file_name, url, time, type, description)
            VALUES (?,?,?,?,?)

Since id is omitted, every time you insert a value using the above statement, it's automatically assigned a number by sqlite.

The documentation explaining this.

How can I let the id be auto generated in my database?

1 Answers1