How to insert string values from a list as date field in Postgre table , in python-CodePudding

In ipython,Below is the insert statement that i am trying to execute, and it returns error:not all arguments converted during string formatting. please note order_Date and ship_date are date fields in the SALES_ORDER postgre table. Is it right to use %s for date field values? if not, how to pass the right format.

 `''
  order_table_insert = ("""INSERT INTO SALES_ORDER(
                     Order_ID ,
                     Customer_ID,
                     Order_Date ,
                     Ship_Date ,
                     Ship_Mode ,
                     Quantity  ) VALUES(%s,%s,%s,%s,%s)""")
  for i, row in sales_order.iterrows():
      cur.execute(order_table_insert,list(row))'
'''

Note: The list of row contains the below output:

'
print(list(row)

  ['CA-2017-152156', 'CG-12520', '08-11-2017', '11-11-2017', 'Second Class', 2]
  ['CA-2017-152156', 'CG-12520', '08-11-2017', '11-11-2017', 'Second Class', 3]
  ['CA-2017-138688', 'DV-13045', '12-06-2017', '16-06-2017', 'Second Class', 2]
  ['US-2016-108966', 'SO-20335', '11-10-2016', '18-10-2016', 'Standard Class', 5]

CodePudding user response：

You can use -

TO_DATE("11-11-2017",  "DD-MM-YYYY")

String will be parsed and format will be selected from 2nd parameter.

So, your query will be -

INSERT INTO SALES_ORDER(Order_ID , Customer_ID, Order_Date, Ship_Date, Ship_Mode , Quantity  ) VALUES(%s, TO_DATE(%s, "DD-MM-YYYY"), TO_DATE(%s, "DD-MM-YYYY"),%s,%s)