CODEWITHSUNDEEP
pythonpostgresql
question guy so poor
question guy so poor

Reputation: 7

'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

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

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.

Upvotes: 1

Related Questions