'YYYY-MM' in python while using SQL code

Question

    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

This is Python. Use triple double quotes for the string delimiter. — Gordon Linoff
– Gordon Linoff, Commented May 3, 2016 at 1:01

Gordon Linoff · Accepted Answer · 2016-05-03 01:03:20Z

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.

Collectives™ on Stack Overflow

'YYYY-MM' in python while using SQL code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related