How to Perform Arithmetic Operations Across Columns of a MySQL Table Using Python

In this article, we show how to perform arithmetic operations across columns of a MySQL table using Python.

This has application for a lot of things, such as use in a shopping cart for a user.

Imagine, you have a table called Orders, representing customer orders. The columns of this table include the customer first name, last name, orderID number, the price of the item, and the quantity of the item ordered.

Now, let's imagine a customer buys 3 packages of hanes undershirts. Each package costs $12.99. So, in the price colum contains the price of $12.99 and the quantity column contains 3 (since the customer ordered 3 packages).

So, in order to get the total amount in dollars that these will cost, you will need to multiply $12.99 by 3. Thus, we using multiplication between these 2 columns in order to compute the total.

So, you can see the importance of needing to do arithmetic different columns in a MySQL table.

If you need to know how to install MySQL, see How to Install MySQL in Python 3.

So, the general code to perform arithmetic across columns of a MySQL table using Python is shown below. In this example specifically, we are doing multiplication.

cursor.execute("SELECT Concat(itemprice * quantity) AS total FROM Table_name") result = cursor.fetchall() for i in result: print(i[0])

So, the code above multiples the itemprice column by the quantity column in the table, Table_name.

We then print out the result of the multiplication operation.

If we save the product in a variable so that you can use it in other areas of the script, the code would be as that shown below.

cursor.execute("SELECT Concat(itemprice * quantity) AS total FROM Table_name") result = cursor.fetchall() for i in result: product= float(i[0])

Now, the product of the multiplication of the itemprice column and the quantity column is stored in the variable product.

So, this is how the code can be written.

We will now go through a full, complete example of multiplying columns of a MySQL table with Python.

So let's say we have a table called CustomerOrders and the table, shown below, represents a customer buying an item that costs $100 and the customer has a quantity of 7.

Below is a MySQL table representing this table.

This table is named CustomerOrders.

We want to write a Python script so that we multiply the itemprice column by the quantity column to get the subtotal of the order.

So the code below is the full Python script.

import pymysql pymysql.install_as_MySQLdb() import MySQLdb db= MySQLdb.connect("hostname", "username", "password", "database_name") cursor= db.cursor() cursor.execute("SELECT Concat(itemprice * quantity) AS total FROM Table_name") result = cursor.fetchall() for i in result: print(i[0]) db.close()

So, this is all the code that is needed to find the average of the price column of the CustomerOrders table.

So we first must import MySQLdb. Once that is imported, we gain connection to the MySQL database using the MySQLdb.connect() function. To see a full-length article on connecting to a MySQL database, see How to Connect to a MySQL Database in Python.

We then have to create a cursor for the table.

Next, we execute our function to multiply the itemprice column with the quantity column using the cursor.execute() function. Inside of this function, we place in the line, "SELECT Concat(itemprice * quantity) AS total FROM Table_name". So the whole line of code, is, cursor.execute("SELECT Concat(itemprice * quantity) AS total FROM Table_name")

We then create a variable named result and set it equal to cursor.fetchall().

We then use a for loop and print out i[0], which represents the product of the 2 columns multiplied together.

We then close the database once we've done what we've needed to.

Again, you can use a variable and save the product in this variable.

The program prints out 700.00, because this is the product of the 2 columns multiplied together.

---

MySQL Arithmetic Operators

So we've shown the multiplication operator.

Below is a list of the most common MySQL arithmetic operators.

MySQL Arithmetic Operator	Mathematical Operation
+	Addition
-	Subtraction
*	Multiplication
/	Division
%  (or MOD)	Modulus (finds remainder of division operation)

Related Resources

How to Show All Tables of a MySQL Database in Python

How to Count the Number of Rows in a MySQL Table in Python