More efficient way to do a SQL query?

Question

Is there a better way to do the following calculation of sales - returns = total?

exchange_rates = self.rates_dict

self.DBConnection.cursor.execute("SELECT royalty_price, royalty_currency FROM sales_raw WHERE provider='%s' AND sales_or_return = 'S' AND date='%s'"%(provider, self.date))
data = list(self.DBConnection.cursor.fetchall())
sales_in_usd = sum(float(price) * exchange_rates[currency] for price, currency in data)

self.DBConnection.cursor.execute("SELECT royalty_price, royalty_currency FROM sales_raw WHERE provider='%s' AND sales_or_return = 'R' AND date='%s'"%(provider, self.date))
data = list(self.DBConnection.cursor.fetchall())
returns_in_usd = sum(float(price) * exchange_rates[currency] for price, currency in data)

total_in_usd = sales_in_usd - returns_in_usd

SELECT SUM(royalty_price), SUM(royalty_currency) FROM sales_raw WHERE.... — bumperbox, May 14 '12 at 22:03
Why.... WHY do you use the `%` operator? NEVER use it for SQL queries. It enables SQL injection. use `WHERE field = %s` without quotes and pass the list of params as a second argument to the `execute` function! — ThiefMaster, May 14 '12 at 22:11
I think this is very similar to your other question: http://stackoverflow.com/questions/10507577/matrix-multiplication-in-python-and-mysql The only apparent difference is you want to subtract the results of two subselects. And, yes, you can do this in SQL. — Mark Byers, May 14 '12 at 22:16
Every dbapi implementation needs to support params. `%s` might be wrong but using the `%` *operator* is *always* wrong. — ThiefMaster, May 14 '12 at 22:18
Having said all of that, @ThiefMaster, OP is doing it wrong, so good catch :) I deleted my comment string since I ultimately see what you were saying and I just blabbered on basically supporting your argument. My apologies. — swasheck, May 14 '12 at 22:41

swasheck · Answer 1 · 2012-05-14T22:33:11.000

SELECT    
    royalty_currency,
    SUM (
          CASE sales_or_return 
          when 'R' then royalty_price * -1
          else royalty_price
       ) as TotalSales
  WHERE provider=%s AND date = %s AND sales_or_return IN ('S', 'R')
    GROUP BY royalty_currency

I'm not 100% sure this will compile on MySQL try it first. If it does ... great ... if not, let me know since I have another idea. Once you get this, multiply by the exchange rate for that given currency.

EDIT: added AND sales_or_return IN ('S', 'R') per @ypercube's recommendation. Additionally, this assumes that all currency is the same. You will still need to iterate the results according to their exchange rates. If the exchange rates are stored in a database then you could join the data on the exchange rate table. Not being a great accountant, I don't know if you'd want to capture the exchange rate at time of sale or if everything is calculated as of current exchange rates. My assumption is that, at this point, you're calculating sales against current exchange rates that you're obtaining via a web service.

An alternative query could be:

SELECT sales.royalty_currency, sales.GrossSales, returns.GrossReturns, sales.GrossSales - returns.GrossReturns, 
(SELECT sum(royalty_price) GrossSales, royalty_currency 
    FROM sales_raw 
    WHERE provider=%s AND sales_or_return = 'S' AND date=%s) sales
JOIN 
(SELECT sum(royalty_price) GrossReturns, royalty_currency 
    FROM sales_raw 
    WHERE provider=%s AND sales_or_return = 'R' AND date=%s) returns
on sales.royalty_currency = returns.royalty_currency

This one could probably be refined a bit more, also. It also gets you some other information that might be useful for other reporting purposes.

You also need `AND sales_or_return IN ('S', 'R')` in the Where clause. — ypercubeᵀᴹ, May 14 '12 at 22:15
The `%s` references shouldn't be quoted -- they should be used unquoted, and the database driver itself (not Python's string formatting operators!) should be used to substitute actual values. (Some other DB-API drivers require `?` in place of `%s` -- check the docs, or use a layer such as SQLAlchemy's query builder which abstracts these details away). Doing this the wrong way can result in SQL injection attacks. — Charles Duffy, May 14 '12 at 22:30
@CharlesDuffy Indeed. MySQLdb and psycopg2 both accept `%s`. I'm personally more comfortable with the `?` but have grown accustomed to tossing in `%s` as long as it's formatted properly within the context of a db connection. — swasheck, May 14 '12 at 22:33

score 3 · Answer 2 · answered May 14 '12 at 22:02

3

Sure, let the database do the work (by using sum directly in your sql statement).

EDIT: You can group by currency type to have one total per currency type.

answered May 14 '12 at 22:02

ChristopheD

112,638
29
165
179

2

Might be more difficult than just using SUM if he doesn't have the exchange rates in the database. – Mark Byers May 14 '12 at 22:03
Could group them by currency, so at least there would be less work to do after the query. – Scott Hunter May 14 '12 at 22:05
Math says that all he has to do is get the base difference of the sum and then multiply by the rates. – swasheck May 14 '12 at 22:05
@Mark Byers: true remark, but at this point the question is a little underspecified I guess. You could use a simple `group by` to combine values of the same currency then. – ChristopheD May 14 '12 at 22:05
I just realized that perhaps each sale could be at a different exchange rate. That complicates things. – swasheck May 14 '12 at 22:12
@swasheck: that would be a definite case for getting those exchange rates in the database too... – ChristopheD May 14 '12 at 22:14
@ChristopheD exchange rates are dynamic. it depends on GAAP, with which I am certainly not familiar. – swasheck May 14 '12 at 22:23

score 0 · Answer 3 · answered May 14 '12 at 22:17

Not the least lines of code, but intuitive and readable, can be condensed to skip steps if needed

Put the exchange rates in your database. Lets assume its in the database with columns royalty_currency and exrate
Join the exchange rates table with the sales_raw table

(SELECT sales_raw.provider, sales_raw.royalty_price, sales_raw.royalty_currency, sales_raw.sales_or_return, exchange_rates.exrate FROM sales_raw INNER JOIN exchange_rates ON sales_raw.royalty_currency = exchange_rates.royalty_currency) sales
Pivot up by sales and returns

(SELECT provider, royalty_currency, exrate CASE sales_or_return WHEN 'S' THEN royalty_price ELSE 0 AS royalty_sales CASE sales_or_return WHEN 'R' THEN royalty_price ELSE 0 AS royalty_returns FROM sales) pivoted_sales
Calculate the total

SELECT SUM(royalty_sales * exrate) - SUM(royalty_returns * exrate) AS total_sum FROM pivoted_sales

More efficient way to do a SQL query?

3 Answers3