Telegram API Question posted in
A comprehensive official documentation can be found here.

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

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.

enter image description here

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 ?

enter image description here

Here is the URLS table:

enter image description here

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

2

Answers


  1. Chosen as BEST ANSWER
    0

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

  2. 0

    The problem is that you keep inserting stuff with the same primary key. All users are inserted with user-id = 1. The line

    curr.execute(
   'INSERT OR IGNORE INTO USERS (ID , chatID) VALUES (1 , ?) ', 
   (userid , ))

    will use the value 1 for the user-id column of the table, and the value of the variable userid for chat-ID. The next time you’ll try to insert a value to the table, it’ll notice that the primary key ‘1’ is already being used, and nothing will be inserted.

    Update:

    Only the first URL is inserted, as according to the log you’re printing, you’re almost always getting into the first branch of the if statement – the one with print('exists'). That’s why you’re not even trying to add new entries to the database.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search