77161

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

<h3>Question</h3>

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()
<h3>Answer1:</h3>

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.

<h3>Update:</h3>

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.


<h3>Answer2:</h3>

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

来源:https://stackoverflow.com/questions/62234373/how-insert-data-in-the-sqlite3-tables-while-checking-if-exist-using-python

Recommend

  • How stick to with Adapter position while it's scrolling in Android?
  • covariance/contravariance problem in C# generic delegate
  • why does resharper default to warning if you don't declare using var?
  • Oracle Client and networking components were not found - Excel VBA
  • entity framework code first - shared 1-to-many entity
  • MVC get details from partial view to show in textboxes
  • Apache Flink Streaming window WordCount
  • Grails Acegi manual login
  • finding contiguous Subset with Largest Sum
  • How application get update from apple store?
  • I keep getting this error, XML Parsing error: syntax error but still the website runs fine
  • changing src attributes
  • How to remove item from Panel
  • FormsAuthentication.CookieDomain property
  • Ruby on Rails - Settting up Reviews functionality
  • Tortoise SVN conflict occurs but no edit conflict button is shown
  • Open a cmd program with full functionality (i/o)
  • Special characters (ë) in JSON-response
  • using JQuery autocomplete from database with PHP (CodeIgniter)
  • Jenkins - could not read Username for 'https://github.com': No such device or address
  • jQuery Ajax call to WCF service returning “Method not allowed (405)”
  • Yii Bootstrap not loading JS files
  • Getting Microsoft Calibri font on Amazon EC2 ubuntu
  • Ruby on Rails: Get mediaplayer information (iTunes, TRAKTOR, Cog; current song + playlist)
  • All Event listing on specified date in Google Calender api (V3) in java?
  • Why is ordered choice in pyparsing failing for my use case?
  • Neo4j…how to get a visual representation of my data?
  • reshape/remould data frame to create normalized bar chart and pie chart
  • matrix multiplication apache pig
  • Google App Engine Datastore: Dealing with eventual consistency
  • Background transfer download task failed when app was closed
  • multiple button click in asp.net MVC 3
  • Accessing Arguments, Workflow Variables from custom activities
  • Write to .csv file with PHP (Commas in Data Error)
  • Running R's aov() mixed effects model from Python using rpy2
  • ReferenceError: TextEncoder is not defined