Using SELECT and VALUES together in INSERT statement-CodePudding

I have a query as part of a python code where the query requires making use of variables in the program as well as values from another database. I am making use of mysql-connector-python.

The values of name is present in the python program and I want to use that and values from the table metrics to insert values into the table points.

This is what I have tried so far:

class summer:
    def __init__(self, name):
        self.name = name
    def sum_fun(self, name):

        try:
            con = mysql.connector.connect(host='localhost',
            database='databasename', user='root', password='mypasword', charset='utf8')

            if con.is_connected():
                cursor = con.cursor(buffered=True)
                def create_points_table():
                        query=("INSERT INTO points(username, sale,comm,dank) VALUES (%s, (SELECT SUM(regional   intnl) * 150, SUM(commission) * 50, SUM(thank_discount) * 10 FROM Metrics))")
                    cursor.execute(query, (name))
                    con.commit()
                create_points_table();

        except Error as e:
            print(e)
        cursor.close()
        con.close()

The query:

query=("INSERT INTO points(username, sale,comm,dank) 
       VALUES (%s, 
       (SELECT 
          SUM(regional   intnl) * 150, 
          SUM(commission) * 50, 
          SUM(thank_discount) * 10 FROM Metrics))")

Query call:

cursor.execute(query, (name))

None of the values are being inserted into the points table. What am I doing wrong here?

CodePudding user response：

You can't mix values and a subquery like that, but you could query the value you meant to have in the values clause instead:

query=("INSERT INTO points(username, sale,comm,dank) 
       (SELECT 
          %s, -- Here!
          SUM(regional   intnl) * 150, 
          SUM(commission) * 50, 
          SUM(thank_discount) * 10 FROM Metrics))")