After much research, I cannot understand why I am getting this error in my code.
I am trying to export a Pandas DataFrame to an Oracle table. I have successfully done this hundreds of times in other data tables, but this one continues to produce errors.
Here is my Dataframe, which I read with pd.read_excel and added three of my own columns with simple commands df['column_name'] = variable :
S USTAINABLE H ARVEST S ECTOR| QUOTA LISTING APRIL 16 2013 Unnamed: 1 \ 1 DATE TRADE ID 2 04/02/13 130014 3 0 0 4 0 0 5 0 0 6 FY13 QUOTA – TO BUY 0 7 DATE TRADE ID 8 3/26/13 130006 9 4/9/13 130012 10 3/26/13 130007 11 3/26/13 130001 12 3/26/13 130009 13 4/9/13 130013 14 3/26/13 130010 15 3/26/13 130008 16 3/26/13 130011 17 1 0 Unnamed: 2 Unnamed: 3 Unnamed: 4 email_year \ 1 AVAILABLE STOCK AMOUNT BUY PRICE 2013 2 WINTER SNE 12000 TRADE IN RETURN FOR 2013 3 0 0 HADDOCK GOM, 2013 4 0 0 YELLOWTAIL GOM, OR 2013 5 0 0 WITCH - OFFERS 2013 6 0 0 0 2013 7 DESIRED STOCK AMOUNT BUY PRICE 2013 8 COD GBE ANY OFFERS 2013 9 COD GBW UP TO 100,000 0.3 2013 10 COD GBW ANY OFFERS 2013 11 COD GOM INQUIRE 1.5 2013 12 WINTER GB ANY OFFERS 2013 13 WINTER SNE UP TO 100,000 0.3 2013 14 WINTER SNE ANY OFFERS 2013 15 YELLOWTAIL GB ANY OFFERS 2013 16 YELLOWTAIL GOM ANY TRADE FOR GB STOCKS -\nOFFERS 2013 17 0 0 0 2013 email_month email_day 1 4 16 2 4 16 3 4 16 4 4 16 5 4 16 6 4 16 7 4 16 8 4 16 9 4 16 10 4 16 11 4 16 12 4 16 13 4 16 14 4 16 15 4 16 16 4 16 17 4 16
My code does not work in the export line cursor.executemany(sql_query, exported_data) with an error:
Traceback (most recent call last): File "Z:\Code\successful_excel_pdf_code.py", line 74, in <module> cursor.executemany(sql_query, exported_data) TypeError: expecting string or bytes object
Here is my code:
df = pd.read_excel(file_path) df = df.fillna(0) df = df.ix[1:] cursor = con.cursor() exported_data = [tuple(x) for x in df.values] #exported_data = [str(x) for x in df.values] #print("exported_data:", exported_data) sql_query = ("INSERT INTO FISHTABLE(date_posted, stock_id, species, pounds, advertised_price, email_year, email_month, email_day, sector_name, ask)" "VALUES(:1, :2, :3, :4, :5, :6, :7, :8, 'Sustainable Harvest Sector', '1')") cursor.executemany(sql_query, exported_data) con.commit() #commit to database cursor.close() con.close()
Here is a listing of exported_data :
[('DATE', 'TRADE ID', 'AVAILABLE STOCK', 'AMOUNT', 'BUY PRICE', '2013', '4', '16'), ('04/02/13', 130014, 'WINTER SNE', 12000, 'TRADE IN RETURN FOR', '2013', '4', '16'), (0, 0, 0, 0, 'HADDOCK GOM,', '2013', '4', '16'), (0, 0, 0, 0, 'YELLOWTAIL GOM, OR', '2013', '4', '16'), (0, 0, 0, 0, 'WITCH - OFFERS', '2013', '4', '16'), ('FY13 QUOTA – TO BUY', 0, 0, 0, 0, '2013', '4', '16'), ('DATE', 'TRADE ID', 'DESIRED STOCK', 'AMOUNT', 'BUY PRICE', '2013', '4', '16'), ('3/26/13', 130006, 'COD GBE', 'ANY', 'OFFERS', '2013', '4', '16'), ('4/9/13', 130012, 'COD GBW', 'UP TO 100,000', 0.3, '2013', '4', '16'), ('3/26/13', 130007, 'COD GBW', 'ANY', 'OFFERS', '2013', '4', '16'), ('3/26/13', 130001, 'COD GOM', 'INQUIRE', 1.5, '2013', '4', '16'), ('3/26/13', 130009, 'WINTER GB', 'ANY', 'OFFERS', '2013', '4', '16'), ('4/9/13', 130013, 'WINTER SNE', 'UP TO 100,000', 0.3, '2013', '4', '16'), ('3/26/13', 130010, 'WINTER SNE', 'ANY', 'OFFERS', '2013', '4', '16'), ('3/26/13', 130008, 'YELLOWTAIL GB', 'ANY', 'OFFERS', '2013', '4', '16'), ('3/26/13', 130011, 'YELLOWTAIL GOM', 'ANY', 'TRADE FOR GB STOCKS -\nOFFERS', '2013', '4', '16'), (1, 0, 0, 0, 0, '2013', '4', '16')]
1) I thought that the error could be from a large number of NaN scattered throughout the Dataframe, so I replaced them with 0, and it still fails.
2) . Then I thought that the error might be an attempt to export the first lines of lines that do not contain any important information, so I deleted the first line with df = df.ix[1:] , but still failed.
3) I also thought that this might be unsuccessful due to the values in my email_year/month/day columns, so I changed them all to rows before putting them in my Dataframe, but it still doesn't work.
4) I tried changing the exported_data command to str instead of tuple , but only changed the error to cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number . Also, when exporting other Dataframes, it always worked fine as a tuple .
5) I thought that the error could be from my Oracle columns, not allowing either numbers or letters, but they are all tuned to all VarChar2 , so this is not the cause of the error either.
I would appreciate any help in solving this, thanks.