pandas Python - 类型错误：需要字符串或字节对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42635198/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python - TypeError: expecting string or bytes object
提问by theprowler
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.
我正在尝试将 Pandas 数据框导出到我的 Oracle 表。我已经在其他数据表上成功完成了数百次，但这个不断产生错误。
Here is my Dataframe, which I read in with pd.read_exceland appended three of my own columns with simple df['column_name'] = variablecommands:
这是我的数据框，我pd.read_excel用简单的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 fails on the export line cursor.executemany(sql_query, exported_data)with the error:
我的代码在导出行失败cursor.executemany(sql_query, exported_data)并出现错误：
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:
这是打印输出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')]
[('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.
1）我认为错误可能来自NaN散布在整个 Dataframe 中的很多s，所以我用 0 替换了它们，但它仍然失败。
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.
2）然后我认为错误可能是由于尝试导出没有有价值信息的前几行，所以我删除了第一行，df = df.ix[1:]但它仍然失败。
3)I also thought it could be failing because of the values in my email_year/month/daycolumns, so I changed them all to strings before putting them into my Dataframe, but it still fails.
3）我还认为它可能会因为我的email_year/month/day列中的值而失败，所以我在将它们放入我的 Dataframe 之前将它们全部更改为字符串，但它仍然失败。
4)I tried changing the exported_datacommand to a strinstead of a tuplebut that only changed the error to cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number. Also, it has always worked fine as a tuplewhen exporting other Dataframes.
4）我尝试将exported_data命令更改为 astr而不是 atuple但仅将错误更改为cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number. 此外，tuple在导出其他数据帧时，它始终运行良好。
5)I thought the error could be from my Oracle columns not allowing either numbers or letters, but they are all set to all VarChar2so that isn't the cause of the error either.
5)我认为错误可能来自我的 Oracle 列不允许数字或字母，但它们都设置为 all，VarChar2因此这也不是错误的原因。
I'd appreciated any help solving this, thanks.
我很感激任何帮助解决这个问题，谢谢。
回答by Anthony Tuininga
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.
根据上述导出数据，您遇到的问题是由于一行中的数据与后续行中的数据类型不同。在您的情况下，在一行中，您的值为 '04/02/13'（作为字符串），而在下一行中，您的值为 0（作为整数）。您需要确保所有行的列的数据类型一致。
