how insert data in the sqlite3 tables while checking if exist using python

Question

i am trying to store the urerID and the urls they received, so i made a connector table Receive to save the ids of user and url.

There are 2 problems here:-

First problem : The ids are not incremented not by 1 but by the number the code is executed multiplied by the number of urls sent, here this happen after user1 used the code twice : user1 typed in telegram chat memes twice and received 2 memes + 2 memes. then user2 used the bot.

Second problem : How to check in Receive table for the existence of both USER_ID and URL_ID aka : know if the user received the memes ?

Here is the URLS table:

The Tables:

CREATE TABLE USERS ( 
userID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
chatID INT(10) UNIQUE

);
CREATE TABLE URLS ( 
urlID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
linkID TEXT UNIQUE 

);
CREATE TABLE Receive ( 
USER_ID INTEGER ,
URL_ID INTEGER ,
PRIMARY KEY (USER_ID , URL_ID)

)

the code:

def from_red():

for subm in reddit.subreddit('Memes').hot(limit=limit):

        urlId = subm.url[18:][:-4] 
        info = last_data('getUpdates')
        userid = get_chat_id(info)

        #curr.execute('SELECT USER_ID and URL_ID FROM Receive ')
        #e = curr.fetchone()

        curr.execute('INSERT OR IGNORE INTO USERS (chatID) VALUES (?) ', (userid ,))
        curr.execute('SELECT userID FROM USERS WHERE chatID = ? ', (userid , ))
        id1 = curr.fetchone()[0]
        print(id1)

        curr.execute('INSERT OR IGNORE INTO URLS (linkID) VALUES (?) ', (urlId ,))
        curr.execute('SELECT urlID FROM URLS WHERE linkID = ? ', (urlId , ))
        id2 = curr.fetchone()[0]
        print(id2)

        curr.execute('INSERT OR REPLACE INTO Receive (USER_ID , URL_ID) VALUES (? , ?)' ,(id1 , id2))


        send_pic(subm.url , subm.title)
        time.sleep(1.5)

        connection.commit()

Answer 1

For the first problem (ids not incremented by 1) : i removed usrID and urlID and used the rowid instead

curr.executescript('''
CREATE TABLE IF NOT EXISTS USERS ( 

chatID INT(10) UNIQUE,

);
CREATE TABLE IF NOT EXISTS URLS ( 

linkID TEXT UNIQUE 

);
CREATE TABLE IF NOT EXISTS Receive ( 
USER_ID INTEGER ,
URL_ID INTEGER ,
PRIMARY KEY (USER_ID , URL_ID)

)

''')

The fixed codes:

curr.execute('SELECT rowid FROM USERS WHERE chatID = ? ', (userid , ))
id1 = curr.fetchone()[0]

curr.execute('SELECT rowid FROM URLS WHERE linkID = ? ', (urlId , ))
id1 = curr.fetchone()[0]

For the second problem (checking for userID and the urls) :

        try:
            curr.execute('INSERT INTO Receive (USER_ID , URL_ID) VALUES (? , ?)' ,(id1 , id2))
            send_pic(subm.url , subm.title)
            time.sleep(1.5)
            connection.commit()

        except sqlite3.IntegrityError as e:
            ...
            print('Anything else')