如何在使用python检查是否存在的同时在sqlite3表中插入数据 [英] how insert data in the sqlite3 tables while checking if exist using python

问题描述

我正在尝试存储 urerID 和他们收到的 url，所以我制作了一个连接器表 Receive 来保存用户和 url 的 id.

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.

这里有两个问题:-

第一个问题:id 不是增加 1 而是增加代码执行的数量乘以发送的 url 数量，这里发生在 user1 两次使用代码之后:user1 在电报聊天 memes 中输入两次并收到2 个模因 + 2 个模因.然后 user2 使用了机器人.

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.

第二个问题:如何检查 Receive 表中是否存在 USER_ID 和 URL_ID 又名:知道用户是否收到了模因?

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 ?

这是URLS表:

表格:

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)

)

代码:

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

推荐答案

对于第一个问题(ids 不加 1):我删除了 usrID 和 urlID 并使用rowid 代替

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)

)

''')

固定代码:

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]

对于第二个问题(检查用户 ID 和网址):

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

这篇关于如何在使用python检查是否存在的同时在sqlite3表中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！