inserting to database from query in python

I have a table of tweet data my_tweets containing these columns: tweet_id, text, created_at, sentiment

My tweet data is being streamed live and simultaneously inserted into the table. I am writing code using Vader to get the sentiment of the text. At the moment, tweet_id, text, and created_at are all populated.

I would like to use this query: SELECT * FROM my_tweets WHERE sentiment is null in MySQL to get rows where sentiment is null and insert the sentiment from Vader.

Any suggestions on how I can go about this?

My program currently looks like this:

getTweetSentiment():
   #my code
   return score

addSentimentToDatabase():
   try:
      con = mysql.connector.connect(host = h, database = db, user = un, password = pw)
      query = ("SELECT * FROM my_tweets WHERE sentiment is null")
      #need to insert sentiment based on what the query returns
   except error as e:
      print(e)
   

Answer

As you put it this cannot be done in one go, you would have to use several steps.

1. Save the data in a DataFrame: Return the results, because you are doing a data science related application and AI I recommend you to use pd.read_sql (the documentation) from Pandas to pass the result to a DataFrame.

import pandas as pd

con = mysql.connector.connect(host = h, database = db, user = un, password = pw)
query = ("SELECT * FROM my_tweets WHERE sentiment is null")
df_query = pd.read_sql(query,con)

2. Perform the operations you make with the data.

3. Insert the results. I recommend you to do it in a different def so that the quality and readability of your code is better. This documentation explains how to make these insertions.

I hope I have helped you!