question guy so poor question guy so poor - 7 months ago 31
Python Question

'YYYY-MM' in python while using SQL code

cur.execute('SELECT to_char(ShopOrder.OrderDate,'YYYY-MM') AS "Order Date",\
Book.BookID, Book.title,\
COUNT(ShopOrder.ShopOrderID) AS "Total number of order",\
SUM(Orderline.Quantity) AS "Total quantity",\
SUM(Orderline.UnitSellingPrice * Orderline.Quantity) AS "Total selling value (Order value)",\
SUM(Book.Price * Orderline.Quantity) AS "Total selling value(Retail value)\
FROM ShopOrder\
LEFT JOIN Orderline\
ON Orderline.ShopOrderID = ShopOrder.ShopOrderID\
LEFT JOIN Book\
ON Book.BookID = Orderline.BookID\
LEFT JOIN Publisher\
ON Publisher.PublisherID = Book.PublisherID\
Where Publisher.name = %s\
ORDER BY MIN(ShopOrder.OrderDate)', [publisherName])


The above code is used in my python program.The only problem I have is the 'YYYY-MM' as the single quote is not valid in python language. What should I change to make it valid? Already tried to use import datetime

Answer

Python has so many string delimiters that it is actually confusing. But easy to switch between. Triple double quotes would be the more typical quoting for long query strings:

   cur.execute("""SELECT to_char(ShopOrder.OrderDate,'YYYY-MM') AS "Order Date",
                        Book.BookID, Book.title,
                        COUNT(ShopOrder.ShopOrderID) AS "Total number of order",
                        SUM(Orderline.Quantity) AS "Total quantity",
                        SUM(Orderline.UnitSellingPrice * Orderline.Quantity)  AS "Total selling value (Order value)",
                        SUM(Book.Price * Orderline.Quantity) AS "Total selling value(Retail value)
                FROM ShopOrder
                LEFT JOIN Orderline
                    ON Orderline.ShopOrderID = ShopOrder.ShopOrderID
                LEFT JOIN Book
                    ON Book.BookID = Orderline.BookID
                LEFT JOIN Publisher
                    ON Publisher.PublisherID = Book.PublisherID
                Where Publisher.name = %s
                  ORDER BY MIN(ShopOrder.OrderDate)""", [publisherName])

The back slashes at the ends of the lines are also unnecessary.

Comments