问题描述
我正在尝试存储 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表中插入数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!