syntax error near to a random word on sqlite3?

Question

I'm currently on a python program where I'm having fun retrieving tweets related to a topic I'm giving away, and then storing them in a sqlite database. The insertion goes very well except that from time to time I have this error that occurs:

sqlite3.OperationalError: near "random word": syntax error

I can't understand the origin of the error and every time I've searched the Internet, it's never been useful to me.

The code below:

import sqlite3
import tweepy
import csv
import pandas as pd
import json
import codecs
import preprocessor as p
import textblob.exceptions
from sqlite3 import Error
from googletrans import Translator
from textblob import TextBlob
import nltk
import time
from nltk.tokenize import word_tokenize



def create_connection(db_file):
    try:
        conn = sqlite3.connect(db_file)
        print("Connecté a la base de données ! ")
    except Error as e:
        print(e)
    finally:
        conn.close()



def create_table(conn, create_table_sql):
    """
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
        print("Table créée")
    except Error as e:
        print(e)

def insert_table(conn, sql):
    try:
        c = conn.cursor()
        c.executemany(sql)
        print("Requete acceptée")
    except Error as e:
        print(e)


def main():

    database = "C:\\Users\TheoLC\AppData\Local\Programs\Python\Python37\lib\sqlite3\pythonsqlite.db"

    try:
        conn = sqlite3.connect(database)
        print("Connecté a la base de données ! ")
    except Error as e:
        print(e)


    auth = tweepy.OAuthHandler("lol")

    auth.set_access_token("lol")

    api = tweepy.API(auth)

    translator = Translator()

    search_word = input("subject ? \n")

    search_word = TextBlob(search_word)

    search_word_finnish = translator.translate(str(search_word), dest='fi')

    search_word_french = translator.translate(str(search_word), dest='fr')

    print("Mot en finnois : " + str(search_word_finnish.text) + " \n")
    print("Mot en français : " + str(search_word_french.text) + " \n")

    searched_tweets = []

    taille = input("nb de tweets ?")

    new_tweets_en = api.search(search_word, count=int(taille)/3)
    new_tweets_fi = api.search(search_word_finnish.text, count=int(taille)/3)
    new_tweets_fr = api.search(search_word_french.text, count=int(taille)/3)


    print("j'ai trouver ", len(new_tweets_en), "tweets en anglais")
    print("j'ai trouver ", len(new_tweets_fi), "tweets en finnois")
    print("j'ai trouver ", len(new_tweets_fr), "tweets en français")

    if not new_tweets_en and not new_tweets_fr and not new_tweets_fi:
        print("pas de tweets trouves")

    new_tweets = new_tweets_en + new_tweets_fr # + new_tweets_fi
    searched_tweets.extend(new_tweets)

    c = conn.cursor()
    c.execute("DELETE FROM tweets")
    conn.commit()

    for tweet in searched_tweets:
        tweet.text = tweet.text.encode('unicode-escape').decode('utf-8')
        # c = conn.cursor()
        c.execute("INSERT INTO tweets (id_tweet, username , content) VALUES (\"%s\", \"%s\", \"%s\");" %(tweet.id, tweet.author.screen_name, tweet.text))
        conn.commit()
    print("Requete acceptée")


if __name__ == "__main__":
    main()

Thank you to those who will have the patience to read lol

Answer 1

The problem is this line:

c.execute("INSERT INTO tweets (id_tweet, username , content) VALUES (\"%s\", \"%s\", \"%s\");" % (tweet.id, tweet.author.screen_name, tweet.text))

The parameter substitution character for sqlite is ?. using '%s' interpolation means values may not be properly escaped, so your SQL statements may be corrupted, or you may be exposed to SQL injection attacks.

The correct code would be:

c.execute("INSERT INTO tweets (id_tweet, username , content) VALUES (?, ?, ?);", 
         (tweet.id, tweet.author.screen_name, tweet.text))

Question marks ? are used as parameter substitution characters, and the execute method handles the substitution (no % operator).