IntegrityError: datatype mismatch in Python using praw

Question

I am trying to write a reddit bot that takes the word "fuck" and see how many people say that on reddit. Here is the code:

import praw
import time
import re
import sqlite3

username = "LewisTheRobot"
password = "lewismenelaws"

conn = sqlite3.connect('reddit')
c = conn.cursor()
r = praw.Reddit(user_agent = "Reddit bot")

word_to_match = [r'\bfuck\b', r'\bfucking\b', r'\bfucks\b']

storage = []

r.login(username, password)

def run_bot():
    subreddit = r.get_subreddit("all")
    print("Grabbing subreddit!")
    comments = subreddit.get_comments(limit=200)
    print("Grabbing comments!")
    for comment in comments:
        comment_text = comment.body.lower()
        isMatch = any(re.search(string, comment_text) for string in word_to_match)
        if comment.id not in storage and isMatch and comment.author not in storage:
            print("We have found a fuck boy. Storing username: " + str(comment.author) + "into database.")
            storage.append(comment.author)
            c.execute("INSERT INTO users (id, Username, subreddit, comment) VALUES(?,?,?,?)", (str(comment.id), str(comment.author), str(comment.subreddit), str(comment.body)))
            conn.commit()

    print("There are currently " + str(len(storage)) + " fuck boys on reddit at the moment.")



while True:
    run_bot()
    time.sleep(2)

Whenever I run the bot it crashes whenever it finds a match and gives me this error.

From what I understand it means that something in the database isnt the right datatype to be inserted into my database. I am pretty sure it is the str(comment.body) section of the SQL setting. My SQLITE database has the comment field as a text field. Thanks for the help.

Here are the database credentials shown in DB Browser.

Solutrean

Please include the SQL statements that define the database schema i.e. the ones beginning with CREATE TABLE. – Lacilacie

@DanD. I am using DB Browser so I am unsure how to show the CREATE statement but I will show the table. edited OP. – Solutrean

Answer 1

The IntegrityError results from the insert statement using str(comment.id) as ID which is a string and not a integer as the schema requires.

But as that didn't work. As comment.id is as you give cogqssp, you'll need to change your schema. The field ID to be of type TEXT. And then your original code will work.

Lacilacie

Gave this a try and get an error: "ValueError: invalid literal for int() with base 10: 'cogqssp' – Solutrean

This answer is correct , IntegrityError has to main reason : 1 - The one describe here (not respecting type ) 2 - Not unique records – Consanguineous

Answer 2

Alright figured it out. My datatype for the ID field was an integer however reddit's ID for comments. example: "cogr0o4". So I changed that to the datatype "TEXT" and made sql statement convert it into a string.

Solutrean