Executing query through pymysql not returning the same results as running in MySQL

Question

I'm new to Python and PyMySQL, so I may have something misconfigured.

I'm connecting to MySQL without any problems. I tested it doing a SELECT and a DESC on a table, and was able to view the results.

I now have a query that I substitute date parameters into and want returned the count of a column (customers) and the total number of customers multiplied times a value.

The count of customers comes back correctly, but the product calculation returns None. Before executing the query, I print it to the console and copy that to MySQLWorkbench to run, and the correct values are returned.

In my main module I connect to the DB and get a cursor. I then get date values to use in the query and call the function that executes the query.

connection = dbConnection()
cursor = connection.cursor()
startDate = input("enter start date (yyyy-mm-dd): ").strip()
endDate = input("enter end date (yyyy-mm-dd): ").strip()
my_queries.queryTotals(cursor, startDate, endDate)
connection.close()

In my my_queries module I have the query and substitute the entered dates into the query string, then execute the query and fetch the results:

totalsSQL = '''select
@total:=count(cus.customer_id) as customers, format(@total * 1.99, 2) as total
from customer cus
join membership mem on mem.membership_id=cus.current_membership_id
where mem.request='START'
and (mem.purchase_date > (unix_timestamp(date('{}'))*1000)  and mem.purchase_date < unix_timestamp(date('{}'))*1000);'''

formattedSQL = totalsSQL.format(startDate, endDate)

cursor.execute(formattedSQL)
result = cursor.fetchone()

I get a result of (32, None) as opposed to getting a numeric value for the 2nd column value.

What am I missing here?

Thanks.

Answer 1

You can't use a variable for an aggregate function, and refer to it later in the same SELECT list. Aggregates don't get their values until all the rows have been selected, but other columns are calculated while selecting rows.

Just use COUNT(*) in both places.

SELECT COUNT(*) AS customers, FORMAT(COUNT(*) * 1.99, 2) AS total
join membership mem on mem.membership_id=cus.current_membership_id
where mem.request='START'
and (mem.purchase_date > (unix_timestamp(date('{}'))*1000) 
and mem.purchase_date < unix_timestamp(date('{}'))*1000)

Also, to prevent SQL injection you should use a parametrized query instead of substituting variables with format().

totalsSQL = '''
    SELECT COUNT(*) AS customers, FORMAT(COUNT(*) * 1.99, 2) AS total
    join membership mem on mem.membership_id=cus.current_membership_id
    where mem.request='START'
    and (mem.purchase_date > (unix_timestamp(date(%s))*1000) 
    and mem.purchase_date < unix_timestamp(date(%s))*1000)
'''
cursor.execute(totalsSQL, (startDate, endDate))