question guy so poor question guy so poor - 1 year ago 102
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\
ON Book.BookID = Orderline.BookID\
LEFT JOIN Publisher\
ON Publisher.PublisherID = Book.PublisherID\
Where = %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 Source

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 = %s
                  ORDER BY MIN(ShopOrder.OrderDate)""", [publisherName])

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download