0

I am currently having an issue with importing data to a sqlite3 table. In my TEST program, users input their fake information when asked for an input. I then take that input and put it in my Table, however, I am having an issue with the AutoIncrementing "User ID". Each user gets their own ID, and so far there are 5 users. When a new User inputs their data, how do I make it so it automatically sets "UserID" to the next number, in this case 6.

Everything works if I manually put "6" in the first Value (in the following code), but how do I make that automatic?

conn = sqlite3.connect('xxxxxxx.db')
c=conn.cursor()
NameCreate = input("Please enter your First and Last name: ")
UserNameCreate = input("Please enter your desired User Name: ")
PasswordCreate = input("Please enter your desired Password: ")
DOBCreate = input("Please enter your date of birth [DD.MM.YYYY]: ")
FavouriteArtistCreate = input("Please enter your favourite Arist: ")
FavouriteGenreCreate = input("Please enter your favourite Genre: ")

c.execute("INSERT INTO Users VALUES (AUTOINCREMENT, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}')".format(NameCreate, DOBCreate, UserNameCreate, PasswordCreate, FavouriteArtistCreate, FavouriteGenreCreate))
conn.commit()
James Smith
  • 95
  • 1
  • 2
  • 7
  • Please add a [mcve] to the question itself, not all of your code, and not in an external link. – abarnert Mar 26 '18 at 19:58
  • 1
    1) What `CREATE TABLE` statement was used to define `Users`? 2) Don't build SQL like this. https://xkcd.com/327/ –  Mar 26 '18 at 19:58
  • Meanwhile, the usual way to do this is to define the column as `AUTOINCREMENT` in the `CREATE TABLE` column-constraint. Have you done that? If not, why not? If so, in what way is it not working as expected? – abarnert Mar 26 '18 at 19:59
  • 1
    Possible duplicate of [Set start value for AUTOINCREMENT in SQLite](https://stackoverflow.com/questions/692856/set-start-value-for-autoincrement-in-sqlite) –  Mar 26 '18 at 19:59
  • @roganjosh Although I think if he knows he wants "Auto Increment" and couldn't figure out how to set the column `AUTOINCREMENT`, there's a good chance you're right that he also didn't set it `PRIMARY KEY`… – abarnert Mar 26 '18 at 20:04

1 Answers1

3

It's not enough to show your operations on the database. You need to show your database schema.

We start with two pieces of warning from sqlite doc:

  1. The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

  2. In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

With that out of the way, the problem with your code is that autoincrement is specified at table creation time, not insertion time.

See a minimal example:

import sqlite3

conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)")

NameCreate = 'a'
c.execute("INSERT INTO users ('name') VALUES (?)", (NameCreate, ))
conn.commit()
print(c.execute('select * from users').fetchall())
NameCreate = 'b'
c.execute("INSERT INTO users ('name') VALUES (?)", (NameCreate, ))
conn.commit()
print(c.execute('select * from users').fetchall())

note the CREATE TABLE line with AUTOINCREMENT, although it's not necessary as sqlite3 will do AUTOINCREMENT on any INTEGER PRIMARY KEY. So you will need to migrate your database to a new schema with that in your table.

A bad manual solution without migration can go as follows (only for stopgap!), in the above example:

c.execute("INSERT INTO users ('id', 'name') VALUES ((SELECT MAX(id) + 1 FROM users), ?)", (NameCreate, ))
Keji Li
  • 62
  • 1
  • 7
  • 1
    Right, the error I am having when I do that is "There is 7 Columns but 1 Value Supplied" - This is because one of the Columns is "User ID", which I want to Auto-Increment, without any User Input. – James Smith Mar 26 '18 at 21:08
  • So two ways to fix: either list all column names except user ID in your insert, or don't have the Id column at all when you create the table. In sqlite if you don't have a integer primary key you have a hidden column called "rowid" you can query with. – Keji Li Mar 26 '18 at 22:14
  • I faced the same problem reported by @JamesSmith: I was sending a list of parameters without the AUTOINCREMENT-ed key, and SQLite complained about 8 columns in VALUE in a table with 9 columns. The solution was not to mention the primary key in the column list. – Hilton Fernandes Apr 08 '21 at 03:24