how do you auto generate a random ID for an SQLite database table-CodePudding

Im in the process of creating a website with python flask and html. I want it to have a register, login, edit, delete, and home page. I want to store the users login data in an sqlite database that can then be called to veify the login of the user. in order to have different users have the same username and/or password, I need to give each user a unique ID. I use this ID in the url, and I want the user to be able to use this ID for other things in the website. because of this, when a new user is added I need to give them a random (unique) ID (probably 8 digits). I saw a bunch of stuff about AUTO INCRAMENT being random, but I didnt understand it very much, and I tried it, and it gave me consecutive numbers (which is not what I want) I also tried RANDOM() and RAND() but they both threw syntax errors. can anyone tell me how to generate an sqlite column for an random unique 8 digit ID?

here's what I have:

schema.sql

DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INTEGER PRIMARY KEY --insert random generator here,
    username TEXT NOT NULL,
    password1 TEXT NOT NULL,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

heres the python that runs the schema:

init_db.py

import sqlite3

connection = sqlite3.connect('database.db')


with open('schema.sql') as f:
   connection.executescript(f.read())

cur = connection.cursor()

cur.execute("INSERT INTO users (username, password1) VALUES (?, ?)",
           ('user1', 'pass1')
           )

cur.execute("INSERT INTO users (username, password1) VALUES (?, ?)",
           ('user2', 'pass2')
           )

connection.commit()
connection.close()

thanks in advance for any help

CodePudding user response：

okay. Ive fixed this by generating a random number in the python code. when a user registers, I take their username and password, then generate a random number between 10000000 and 99999999. then I check if its in the database already, and if so, generate another one, until I generate a unique number. then I add username, password and ID to the database at the same time.

heres what I have now

(disclaimer: i am only showing the relevant code, so some of it might not make sense)


CREATE TABLE users (
    id INTEGER,
    username TEXT NOT NULL,
    password1 TEXT NOT NULL,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

the init_db.py is the same,

heres the python:

from flask import Flask, render_template, url_for, request, flash, redirect, abort#imports
import sqlite3
import random
app = Flask(__name__)

def get_db_connection():
    conn = sqlite3.connect('database.db')
    conn.row_factory = sqlite3.Row

    return conn

def get_username(usernamex):#def and pass usernamex
    validated_uname = False#reset progress variable
    conn = get_db_connection()#connect to database
    cur = conn.cursor()#create a cursor

    try:
        cur.execute('SELECT * FROM users WHERE username = ?',(usernamex,))#select the row in the users table where username == the usernamex variable
        uname = cur.fetchone()[1]#uname = the second column of that row
        validated_uname = True#update progress variable
    except:#if the above failed: (most likely because usernamex was not in the database):
        validated_uname = False#reset progress variable

    if validated_uname == True:#if the try function passed:
        return(uname)#update uname and send it back
        
    conn.commit()#commit and close database
    conn.close()

def get_password1(password1x):#def and pass password1x
    validated_upass = False#reset progress variable
    conn = get_db_connection()#connect to database
    cur = conn.cursor()#create a cursor

    try:
        cur.execute('SELECT * FROM users WHERE password1 = ?',(password1x,))#select the row in the users table where password1 == the password1x variable
        upass = cur.fetchone()[2]#upass = the third column of that row
        validated_upass = True#update progress variable
    except:
        validated_upass = False#reset progress variable

    if validated_upass == True:#if the try function passed:
        return(upass)#update upass and send it back
        
    conn.commit()#commit and close database
    conn.close()

app.config['SECRET_KEY'] = '013ecbdd4aae3899c7feed1bf36dee4e'#secret key

@app.route("/register", methods=('GET', 'POST'))#url, and pass the get and post vaiables to make forms
def register():
    if request.method == 'POST':#when request method is post in the html page: #(after they press submit)
        usernamex = request.form['username']#take the username entered from html
        password1x = request.form['password1']#same for pass1 and pass2
        password2x = request.form['password2']
        if not usernamex:#if nothing was entered for username:
            flash('Username is required!')#error message
        elif not password1x:#if nothing was entered for pass1:
            flash('Password is required!')#error message
        elif not password2x:#if nothing was entered for pass2:
            flash('Valdiated password is required!')#error message
        elif password1x != password2x:# if pass1 and pass2 dont match:
            flash('Passwords do not match!')#error message
        else:#otherwise
            conn = get_db_connection()#connect to database
            cur = conn.cursor()#create cursor
            loop = True
            while loop == True:
                rand_id = random.randint(10000000, 99999999)#generate random number (8 digits)
                try:
                    cur.execute('SELECT * FROM users where id = ?',(rand_id,))#select the row in the users table where id == the rand_id variable
                    r_id = cur.fetchone()[0]#r_id = the first column from that row ## this is just here to throw the error
                except:
                    cur.execute('INSERT INTO users (id, username, password1) VALUES (?, ?, ?)',(rand_id, usernamex, password1x))#make a new row, and put in ID, username and password1 in their respective places
                    loop = False#break the loop
                
            conn.commit()#commit and close database
            conn.close()
            id = rand_id#for the home url
            #id = home_id(usernamex, password1x)#id = [call the home_id function {pass usernamex and password1x}]
            return redirect(url_for('home', id=id))#go to the home page and pass id for the url
    return render_template('register.html', title = 'Register')#render the template from register.html

thanks for everyones help :)