Python - TypeError: expecting string or bytes object

Question

After much research I cannot figure out why I receive this error in my code.

I'm trying to export a Pandas Dataframe to my Oracle table. I have successfully done this hundreds of times on other data tables but this one keeps producing errors.

Here is my Dataframe, which I read in with pd.read_excel and appended three of my own columns with simple df['column_name'] = variable commands:

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 fails on the export line cursor.executemany(sql_query, exported_data) with the 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 relevant 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 printout 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 the error could be from a lot of NaNs being scattered throughout the Dataframe, so I replaced them with 0's and it still fails.

2) I then thought the error could be from trying to export the first couple rows which held no valuable information, so I deleted the first row with df = df.ix[1:] but it still fails.

3) I also thought it could be failing because of the values in my email_year/month/day columns, so I changed them all to strings before putting them into my Dataframe, but it still fails.

4) I tried changing the exported_data command to a str instead of a tuple but that only changed the error to cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number. Also, it has always worked fine as a tuple when exporting other Dataframes.

5) I thought the error could be from my Oracle columns not allowing either numbers or letters, but they are all set to all VarChar2 so that isn't the cause of the error either.

I'd appreciated any help solving this, thanks.

Can you create a standalone test case that can be run? The problem is likely due to changing types. For a particular column, one row may be an string and for the next row it may be an integer. That is one reason for that error to be raised. — Anthony Tuininga
– Anthony Tuininga, Commented Mar 6, 2017 at 21:44
I actually thought that that could be a reason the error occurred....but I don't know how to isolate or capture single columns from this Dataframe in particular because the columns are not named and trying print("row 1:", ['DATE'] fails — theprowler
– theprowler, Commented Mar 6, 2017 at 21:48
@AnthonyTuininga so I've been able to print each column individually, all the unnamed columns and the first one that starts with S USTAINABLE, and I've confirmed they're all dtype: object. I don't know if that's good or bad or even significant at all — theprowler
– theprowler, Commented Mar 7, 2017 at 15:35

Anthony Tuininga · Accepted Answer · 2017-03-08 15:46:12Z

12

Based on the export data noted above, the problem you are experiencing is due to the fact that the data in one row is not the same type as the data in subsequent rows. In your case, in one row you have the value '04/02/13' (as a string) and in the next row you have the value 0 (as an integer). You will need to make sure that the data type is consistent for the column across all rows.

answered Mar 8, 2017 at 15:46

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

theprowler Over a year ago

Ohh ok that makes enough sense... is there an easy enough way to do that? To change all datatypes of a Dataframe to the same one?

theprowler Over a year ago

So I figured it out and was able to change all the values to strings. Who knows why creating the Dataframe made some values strings and others integers. But thanks a ton for pointing out what was wrong with it, now I can proceed with exporting a ton of data

John Prawyn Over a year ago

Data type should be consistent across all rows is important.

Quentin · Accepted Answer · 2023-10-25 12:49:12Z

0

Like it was said before, the problem could come from a column that has values from different types. To catch the problematic columns you can try this command :

for col in df.columns:
if len(df[col].apply(type).drop_duplicates().tolist())>1:
    print(col)

answered Oct 25, 2023 at 12:49

Quentin

112 bronze badges

Collectives™ on Stack Overflow

Python - TypeError: expecting string or bytes object

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related