Python - TypeError:期望字符串或字节对象 [英] Python - TypeError: expecting string or bytes object
问题描述
经过多次研究,我无法弄清楚为什么我在代码中收到这个错误。
After much research I cannot figure out why I receive this error in my code.
我正在尝试将一个Pandas Dataframe导出到我的Oracle表。我已经在其他数据表上成功完成了数百次,但是这个数据表不断产生错误。
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.
这是我的Dataframe,我使用 pd.read_excel
阅读并附加了我自己的三个列与简单 df ['column_name'] =变量
命令:
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
我的代码在导出行 cursor.executemany(sql_query,exports_data)
导致错误:
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
这是我的相关代码:
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()
以下是 exports_data
的打印输出:
[('DATE' 'TRADE ID','AVAILABLE (04,14,1314,1460,冬季,12000年,回归贸易, (0,0,0,0,0''HADDOCK GOM','2013','4','16'),(0,0,0,0, (0,0,0,0,'WITCH - OFFERS','2013','4','16'),(' (DATE,TRADE ID,DESIRED STOCK,AMOUNT,买入价,买入,0,0,0 '''''''''''''''''''''''''''''''''''''''''''' ),('4/9/13',130012,'COD GBW','UP TO 100,000',0.3,'2013','4','16'),('3/26/13',130007, COD,任何,优惠,2013,4,16)(3/26/13,130001,COD GOM,INQUIRE ','4','16'),('3/26/13',130009,'WINTER GB','ANY ,'提供','2013','4','16'),('4/9/13',130013,'WINTER SNE','UP TO 100,000',0.3,'2013',' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ,130008,黄油国家,任何,优惠,2013,4,16),('3/26/13',130011,黄油 (1,0,0,0,0,'2013','4','16')]
1)我以为错误可能来自很多 NaN
s分散在Dataframe中,所以我用0替换它们,它仍然失败。
1) I thought the error could be from a lot of NaN
s being scattered throughout the Dataframe, so I replaced them with 0's and it still fails.
2)然后,我认为错误可能是尝试导出没有重要信息的前几行,所以我删除了第一个行 df = df.ix [1:]
但仍然失败。
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)我还以为可能会因为我的 email_year / month / day
列中的值而失败,所以在将它们放入数据框之前,将它们全部更改为字符串,但是它仍然失败。
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)我尝试将 exported_data
命令更改为 str
而不是 tuple
,但只将错误更改为 cx_Oracle.DatabaseError:ORA- 01036:非法变量名/数字
。此外,导出其他数据框时,它始终可以作为元组
正常工作。
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) strong>我认为这个错误可能来自我的Oracle列,不允许使用数字或字母,但是它们都设置为所有 VarChar2
,因此不是错误的原因
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.
推荐答案
根据上述导出数据,您遇到的问题是由于一行中的数据与后续行中的数据不同。在你的情况下,在一行中你的值为'04 / 02/13'(作为一个字符串),在下一行你的值为0(整数)。您需要确保所有行中的列的数据类型一致。
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.
这篇关于Python - TypeError:期望字符串或字节对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!