1

I understand that the primary key autoincrements the primary key for each record inserted, but when I insert data using option 1, then display the data using option 2, it shows the PK as 1,2,3... etc. However, I would like it to store the PK as 4 digits, E.G. 0001,0002,0003 rather than 1,2,3. Is there any way to do this? PLEASE NOTE: this is example simplified code.

import sqlite3

connection = sqlite3.connect('database.db')
cursor = connection.cursor()
create_table = '''
CREATE TABLE IF NOT EXISTS employee (
staff_ID INTEGER PRIMARY KEY NOT NULL,
fname VARCHAR(20) NOT NULL,
lname VARCHAR(30) NOT NULL,
gender VARCHAR(20) NOT NULL);'''
cursor.execute(create_table)

print('press 1 to add new user')
print('press 2 to show all users')
option = input('1 or 2: ')

if option == '1':
    #userid = input('User ID: ')
    first_name = input('First name : ')
    surname = input('Surname: ')
    gender = input('Gender: ')

    add_staff = '''INSERT INTO employee (fname, lname, gender)
        VALUES (?, ?, ?, ?);'''
    cursor.execute(add_staff, (first_name, surname, gender))

elif option == '2':
    cursor.execute('SELECT * FROM employee')
    print('fetchall:')
    result = cursor.fetchall()
    for r in result:
    print(r)
    cursor.execute('SELECT * FROM employee')    

connection.commit()

connection.close()

Also, how do I make sure that when the first name and surname are entered, they are only text values? I tried using 'text' in the CREATE section... any ide

jackdomleo7
  • 328
  • 3
  • 16

3 Answers3

2

You can use zfill method.

key=1
str(key).zfill(4)
out[1]: '0001'

key=10
str(key).zfill(4)
out[1]: '0010'
Spandan Brahmbhatt
  • 3,774
  • 6
  • 24
  • 36
0

integers are just numbers. You can't control the way they are stored. You could, however, pad them with zeroes when you query the table:

SELECT TO_CHAR(staff_id, 'fm0000'), fname, lname, gender
FROM   employee
Mureinik
  • 297,002
  • 52
  • 306
  • 350
0

Also, how do I make sure that when the first name and surname are entered, they are only text values? I tried using 'text' in the CREATE section... any ide

With the exception of the rowid any column can contain any type of value as one of the following storage classes :-

  • Null
  • Integer (up to 8 bytes, but the minimum required)
  • Real as a 8 byte IEEE floating-point number
  • Text according to the database encoding
  • BLOB (byte array)

However, how the storage class is determined is according to the value itself and the column's declared type/affinifty.

VARCHAR(30) is not different to VARCHAR(SOMESTUPIDAMOUNT) nor to CHARISMA, as due to the occurrence of CHAR (and without the occurrence of INT) then the 2nd rule of column affinity is applied and thus the column's affinity is TEXT.

  • Rule 2 is

  • If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

More here about SQLite 3 Datatypes - See 3.1 Determination of Column Affinity for the rules

So for the VARCHAR columns the column affinity is TEXT so then it depends upon the value and the rule for column's with TEXT affinity which is (3. Type Affinity) :-

A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.

So really you do not need to anything.

HOWEVER, as important, is how you retrieve the data, which would be via one of the get???? methods. In short if you store 1 and use the getInt method you will get an integer of 1. If you store something and use the getInt method, you will get 0. Using getString you'd get 1 and something.

There's a little more along with examples here

MikeT
  • 51,415
  • 16
  • 49
  • 68