CODEWITHSUNDEEP
pythonsqlpython-3.xsqlite
James Smith
James Smith

Reputation: 115

SQLite3: Python Input to Table - Trouble with Auto Increment

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

Upvotes: 1

Views: 3217

Answers (1)

Keji Li
Keji Li

Reputation: 60

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

Upvotes: 3

Related Questions