CODEWITHSUNDEEP
pythonsqlite
Vittawat Laorungroj
Vittawat Laorungroj

Reputation: 99

How to add auto increment id with character for python sqlite

import sqlite3

conn = sqlite3.connect('carlist.db')
c = conn.cursor()

c.execute("""CREATE TABLE IF NOT EXISTS carlist(
        ID INTEGER PRIMARY KEY AUTOINCREMENT,
        Brand text NOT NULL,
        Model text NOT NULL,
        License Plate text NOT NULL,
        Year INTEGER)""")

I'm trying to make auto increment ID with the character when I add the new data

Example for ID : AFK000001, AFK000002, AFK000003 and etc. This should be auto field.

Upvotes: 1

Views: 1213

Answers (2)

forpas
forpas

Reputation: 164204

If you are using SQLite 3.31.0+ you can define a generated column:

aID TEXT GENERATED ALWAYS AS ('AFK' || SUBSTR('000000' || ID, -6))

This column as it is defined will not be stored in the table, but will be generated every time you query the table.
If you want it to be stored then you can add the keyword STORED at the end of its definition.

Upvotes: 1

Younes
Younes

Reputation: 421

The AUTO_INCREMENT is exclusive for integers and there is no way to do that automatically, however you can make a little script to achieve that by :

  • Store last ID
  • Get the integer subString
  • Increment its value and add it to the character subString
  • Store it in table

Upvotes: 0

Related Questions