How to store the sum of a column from a mysql database to a python variable?

Question

Kinda long code by complete beginner ahead, please help out

I have a database with the following values:

Sl.No	trips	sales	price
1	5	20	220
2	8	30	330
3	9	45	440
4	3	38	880

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

"I have been trying so hard to find an answer to this for so long" An answer to what, exactly? There doesn't seem to be a clear question or problem statement here - see How to Ask. — esqew
– esqew, Commented Dec 1, 2021 at 20:21
Why wouldn't you just do this in one shot? SELECT SUM(trips), SUM(sales), SUM(price) FROM table_name...? — esqew
– esqew, Commented Dec 1, 2021 at 20:21

Hussain Bohra · Accepted Answer · 2021-12-01 20:14:03Z

1

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.

answered Dec 1, 2021 at 20:14

Hussain Bohra

1,00510 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

isdx · Accepted Answer · 2021-12-01 21:42:36Z

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.

Collectives™ on Stack Overflow

How to store the sum of a column from a mysql database to a python variable?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related