-1

I want to create a dashboard widget in my web app. The first step is to count the frequency of pos, neg and neu in mysql from two table. I tried to find the solution in Flask, but not many. Hope u can help me.

The error that I got is:

MySQLdb._exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)')

Table in mysql:

ques9

enter image description here

ques10

enter image description here

My code:

@app.route('/we/<string:programid>')
def we(programid):
# Create cursor
cur = mysql.connection.cursor()

result = """SELECT(
(SELECT programid,sentiment, COUNT(*) 
FROM ques9 AS question9  
WHERE programid= %s
GROUP BY sentiment),
(SELECT programid,q10_sentiment, COUNT(*) 
FROM ques10 AS question10 
WHERE programid=%s
GROUP BY q10_sentiment ))"""

data_tuple = (programid, programid)
cur.execute(result, data_tuple)

program = cur.fetchall()

mysql.connection.commit()


if result > 0:
    return render_template('we.html',program=program)
else:
    msg = 'No Results Found'
    return render_template('we.html', msg=msg)
# Close connection
cur.close()
huhu
  • 53
  • 6
  • Because your queries are wrong. `SELECT programid,sentiment, COUNT(*) FROM ques9 WHERE programid=%s GROUP BY programid,sentiment` might be an alternative for the first one as an example. – Barbaros Özhan Jan 31 '20 at 17:36
  • I have tried this method, but still got error – huhu Jan 31 '20 at 17:41

1 Answers1

0

The group by has to be after the where clause So i posted all the python code, i thought about adding a try, but that you can look up

Your sql has some problems like the group an his own,l but your python code has also flaws, as you can see below. The variables for sql query and the data to send, i out there so that it looks somewhat cleanber

connection =   mysql.connector.connect(host='localhost',
                                     database='test_db',
                                     user='user',
                                     password='password')
cur = connection.cursor(prepared=True)
sql_update_query = """SELECT(
(SELECT programid,sentiment, COUNT(*) 
FROM ques9 AS question9  
WHERE programid= %s
GROUP BY sentiment),
(SELECT programid,q10_sentiment, COUNT(*) 
FROM ques10 AS question10 
WHERE programid=%s
GROUP BY q10_sentiment ))"""

data_tuple = (programid, programid)
cur .execute(sql_update_query, data_tuple)
connection.commit()
if (connection.is_connected()):
    cur.close()
    connection.close()
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Given that programid must be 'test', it seems almost superfluous to include it in the SELECT – Strawberry Jan 31 '20 at 17:40
  • Hi select is not valid fist the first SELECT ( is wrong, so i give him a example how ot should look. because i hope he has mor text to come. – nbk Jan 31 '20 at 17:43
  • What should I do to count the frequency for two table? – huhu Jan 31 '20 at 17:45
  • I put Select with bracket as I want to count the frequency for two table...I got it from https://stackoverflow.com/a/36498752/7729836 – huhu Jan 31 '20 at 17:55
  • so i corrected the complete sql code and also edited the python script you could also add a trx for problems too – nbk Jan 31 '20 at 18:23
  • @nbk Why I got `MySQLdb._exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)')` – huhu Feb 01 '20 at 00:25
  • please edit your post and add your new actual code. I am not sure where it hangs, because my code has no methods that need a column. Did you run your code in mysql workbench or phpmyadmin ? – nbk Feb 01 '20 at 00:50
  • I see, but you miss the part where you defined the cursor, it has a parameter. more i don't see. – nbk Feb 01 '20 at 01:10
  • When I write `cur = mysql.connection.cursor(prepared=True)`. I got this error `TypeError: cursor() got an unexpected keyword argument 'prepared'` – huhu Feb 01 '20 at 01:13