Python Forum

I want to create sentiment analysis using Vader in Python and Mysql. The problem is, I got the error on mysql, especially when to insert the data into the database. The error that I got is

Error:
Failed to insert record into MySQL table Failed executing the operation; Python type tuple cannot be converted

Here are the code:

 sql_select_Query = "select a from table1 union select b from table1"
    cursor = connection.cursor()
    cursor.execute(sql_select_Query)
    records = cursor.fetchall()


    for row in records:
        print(row)

        sid_obj = SentimentIntensityAnalyzer() 

        sentiment_dict = sid_obj.polarity_scores(row) 

        if sentiment_dict['compound'] >= 0.05 : 
            sentiment = 'positive'

        elif sentiment_dict['compound'] <= - 0.05 : 
            sentiment = 'negative'

        else : 
            sentiment = 'neutral'

        mySql_insert_query = """INSERT INTO sent (q,polarity,senti) VALUES (%s,%s,%s) """

        records_to_insert = [(row,sentiment_dict['compound'], sentiment)]

        cursor = connection.cursor()
        cursor.executemany(mySql_insert_query, records_to_insert)
        connection.commit()
        print(cursor.rowcount, "Record inserted successfully into table")

except mysql.connector.Error as error:
    print("Failed to insert record into MySQL table {}".format(error))


except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if  (connection.is_connected()):
        cursor.close()
        connection.close()

try to change line 25 to

records_to_insert = (row,sentiment_dict['compound'], sentiment)

i.e. 3-element tuple, not single element list which element is tuple.

also I don't think you need cursor.executemany(), just cursor.execute()

I got

Error:
Failed to insert record into MySQL table Failed executing the operation; Not enough parameters for the SQL statement

when do this code:

records_to_insert = (row,sentiment_dict['compound'], sentiment)

And, when I change cursor.executemany() to just cursor.execute(). I got this error:

Error:
---------------------------------------------------------------------------
MySQLInterfaceError                       Traceback (most recent call last)
<ipython-input-174-703ad1ca3c2d> in <module>
     50 
     51             cursor = connection.cursor()
---> 52             cursor.execute(mySql_insert_query, records_to_insert)
     53             connection.commit()
     54             print(cursor.rowcount, "Record inserted successfully into table")

~\Anaconda3\lib\site-packages\mysql\connector\cursor_cext.py in execute(self, operation, params, multi)
    246 
    247         if params:
--> 248             prepared = self._cnx.prepare_for_mysql(params)
    249             if isinstance(prepared, dict):
    250                 for key, value in prepared.items():

~\Anaconda3\lib\site-packages\mysql\connector\connection_cext.py in prepare_for_mysql(self, params)
    607         """
    608         if isinstance(params, (list, tuple)):
--> 609             result = self._cmysql.convert_to_mysql(*params)
    610         elif isinstance(params, dict):
    611             result = {}

MySQLInterfaceError: Python type tuple cannot be converted

probably because row is tuple
try records_to_insert = (str(row),sentiment_dict['compound'], sentiment) if you really want to insert the whole row tuple