I cannot export the following Dataframe to an Oracle table multiple times:
final df is: S USTAINABLE H ARVEST S ECTOR| QUOTA LISTING JUN 11 2013 Unnamed: 1 \ 2 6/4/13 130196 3 5/28/13 130158 4 6/4/13 130210 5 5/14/13 130079 6 6/4/13 130187 7 6/4/13 130208 8 6/11/13 130249 9 6/4/13 130204 10 5/28/13 130148 11 5/28/13 130149 12 5/28/13 130157 13 5/21/13 130105 14 5/21/13 130106 15 6/4/13 130205 16 6/11/13 130250 17 6/4/13 130206 18 6/11/13 130248 19 QUOTA TO BUY 0 20 DATE TRADE ID 21 6/11/13 130239 22 5/14/13 130074 23 3/26/13 130006 24 5/14/13 130075 25 5/7/13 130023 26 5/14/13 130039 27 1 0 Unnamed: 2 Unnamed: 3 Unnamed: 4 email_year email_month \ 2 COD GBW 10000 0.6 2013 6 3 COD GBW 300 0.6 2013 6 4 HADDOCK GBE UP TO 30,000 OFFERS 2013 6 5 PLAICE 1000 0.45 2013 6 6 WHITE HAKE UP TO 25,000 0.5 2013 6 7 WHITE HAKE 4000 0.5 2013 6 8 WINTER GB 3300 0.25 2013 6 9 WINTER GB 10000 0.48 2013 6 10 WINTER GB 1U0P 0T,0O00 0.25 2013 6 11 WINTER GB UP TO 10,000 0.4 2013 6 12 WINTER GB 1400 0.25 2013 6 13 WINTER GB 10000 0.5 2013 6 14 WINTER GB 10000 0.5 2013 6 15 WINTER GOM 1000 0.38 2013 6 16 WINTER SNE 6500 0.4 2013 6 17 WINTER SNE 3000 0.63 2013 6 18 YELLOWTAIL GOM 2000 1.25 2013 6 19 0 0 0 2013 6 20 DESIRED STOCK AMOUNT BUY PRICE 2013 6 21 COD GOM UP TO 14,000 2.1 2013 6 22 COD GOM 20000 INQUIRE 2013 6 23 COD GBE ANY 1.5 2013 6 24 HADDOCK GOM 10000 INQUIRE 2013 6 25 HADDOCK GOM UP TO 6,000 0.75 2013 6 26 WHITE HAKE UP TO 100,000 0.3 2013 6 27 0 0 0 2013 6 email_day 2 11 3 11 4 11 5 11 6 11 7 11 8 11 9 11 10 11 11 11 12 11 13 11 14 11 15 11 16 11 17 11 18 11 19 11 20 11 21 11 22 11 23 11 24 11 25 11 26 11 27 11
It does not work on cursor.executemany(sql_query, exported_data) with error
TypeError: expecting numeric data .
My corresponding code is:
cursor = con.cursor() exported_data = [tuple(x) for x in df.values] #exported_data = [str(x) for x in df.values] sql_query = ("INSERT INTO ROUGHTABLE(species, date_posted, stock_id, pounds, money, trade_year, trade_month, trade_day, sector_name, ask)" "VALUES(:3, :1, :2, :4, :5, :6, :7, :8 'Sustainable Harvest Sector', '1')") cursor.executemany(sql_query, exported_data) con.commit() #commit to database cursor.close() con.close()
When I comment on the exported_data line and not comment on the below, it still fails, but this time with an error
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number
Examination of these errors shows that they can be caused by binding variables, invalid date formats, an attempt to export a string by type, etc. I am not a SQL expert and would like to help solve this.
Any help would be appreciated, thanks.