Kinda long code by complete beginner ahead, please help out

I have a database with the following values:

I am trying to use mysql-connector and python to get the sum of the columns trips, sales and price as variables and use it to do some calculations in the python script. This is what I have so far:

def sum_fun():

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

        if con.is_connected():
            cursor = con.cursor(buffered=True)
            def sumTrips():
                cursor.execute("SELECT SUM(trips) FROM table_name")
                sum1=cursor.fetchall()[0][0]
                return int(sum1)
            def sumSales():
                cursor.execute("SELECT SUM(sales) FROM table_name")
                sum2=cursor.fetchall()[0][0]
                return int(sum2)
            def sumPrice():
                cursor.execute("SELECT SUM(price) FROM table_name")
                sum3=cursor.fetchall()[0][0]
                return int(sum3)
    except Error as e:
        print(e)
    cursor.close()
    con.close()

I would like to receive the the three sums as three variables sum_trips, sum_sales and sum_price and assign a point system for them such that:

trip_points=20*sum_trips
sales_points=30*sum_sales
price_points=40*sum_price

And then take the three variables trip_points, sales_points, prices_points and insert it into another table in the same database named Points with the column names same as variable names.

I have been trying so hard to find an answer to this for so long. Any help or guidance would be much appreciated. I am a total beginner to most of this stuff so if there's a better way to achieve what I am looking for please do let me know. Thanks

CodePudding user response：

I might be missing something, but Running 3 different queries against the same table (that too in MySQL which is not columnar) is not very efficient, I would probably just execute the following:

CREATE new_table as 
(SELECT 
        SUM(trips) * 20 as trip_points,
        SUM(sales) * 20 as sales_points,
        SUM(price) * 20 as price_points
FROM table_name)

either on MySQL prompt or from Python (if you are attempting some kind of automation)

Let me know if this helps you move forward.

CodePudding user response：

Here is your code, with minor changes that returns a tuple with the three values like (sumTrips, sumSales, sumPrice) but I omitted to close the connection intentionally, (see the second code sniped where the connection gets closed automatically):

def sum_fun():
    def sumTrips(cursor):  # receives the cursor object, for queries
        cursor.execute("SELECT SUM(trips) FROM table_name")
        sum1=cursor.fetchall()[0][0]
        return int(sum1)
    def sumSales(cursor):
        cursor.execute("SELECT SUM(sales) FROM table_name")
        sum2=cursor.fetchall()[0][0]
        return int(sum2)
    def sumPrice(cursor):
        cursor.execute("SELECT SUM(price) FROM table_name")
        sum3=cursor.fetchall()[0][0]
        return int(sum3)
    
    try:
        con = mysql.connector.connect(host='localhost', password='mypasword',
                                      database='twitterdb', user='root',  charset='utf8')
        cursor = con.cursor(buffered=True)
        return (sumTrips(cursor), sumSales(cursor), sumPrice(cursor))

    except Exception as e:
        print(e)
        raise Exception("The connection fail because of this error: {}".format(e))

You may want to invoke it as:

sumTrips, sumSales, sumPrice = sum_fun()

And this code gives the same results but uses with:

def sum_fun():
    def sum_column(cursor, column):
        cursor.execute(f"SELECT SUM({column}) FROM table_name")
        sum1=cursor.fetchall()[0][0]
        return int(sum1)
    
    try:
        with mysql.connector.connect(host='localhost', password='mypasword',
                                     database='twitterdb', user='root',  charset='utf8') as con:
            with con.cursor(buffered=True) as cursor:
                return (sum_column(cursor, "trips"), sum_column(cursor, "sales"), sum_column(cursor, "price"))

    except Exception as e:
        print(e)
        raise Exception("The connection fail because of this error: {}".format(e))

The code above code uses with statement, it will close the connection when the block in with finish, because invokes the __exit__() method which calls close() method to close the connection.