Oracle安装提示无效条目,Oracle错误：无效的标识

最新推荐文章于 2023-11-17 16:39:19 发布

转载最新推荐文章于 2023-11-17 16:39:19 发布 · 715 阅读

文章标签：

这篇博客讲述了作者在尝试使用Python连接Oracle数据库并更新数据时遇到的问题。作者的代码能够成功执行大部分操作，但在调用update_output_table函数的最后一步出现ORA-00904错误，提示'UNCONVENTIONAL'是一个无效的标识符。问题可能出在使用了数据库中不存在的列名或者数据类型不匹配。博客中还展示了创建output表的部分代码，其中UNCONVENTIONAL是产品类别的一条记录。作者寻求解决方案，并承认自己是新手，希望得到帮助。

我正在尝试使用python访问和更新Oracle数据库。以下是我的代码：import cx_Oracle

import pandas as pd

import datetime

import numpy

import math

#import pandasql

conn_str = 'sahil/sahil@52.20.141.126:1521/xe'

conn = cx_Oracle.connect(conn_str)

c = conn.cursor()

def update_output_table(customer_id_list,column_name,column_vlaue_list) :

num_rows_to_add = len(customer_id_list)

conn = cx_Oracle.connect(conn_str)

c = conn.cursor()

for i in range(0,num_rows_to_add,1) :

c.execute("""UPDATE output SET """+column_name+""" = %s WHERE customer_id = %s""" %(column_vlaue_list[i],customer_id_list[i]))

print "Completed updating " + column_name

conn.commit()

total_transaction_df = pd.read_sql("""select distinct b.customer_id,count(a.transaction_id) as total_transaction from transaction_fact a,customer_dim b where a.customer_id = b.customer_id group by b.customer_id""",conn)

# Update this details to the output table

update_output_table(list(total_transaction_df['CUSTOMER_ID']),'TOTAL_TRANSACTION',list(total_transaction_df['TOTAL_TRANSACTION']))

english_movies_df = pd.read_sql("""select b.customer_id, count(a.product_id) as "ENGLISH_MOVIES" from transaction_fact a inner join customer_dim b on a.customer_id = b.customer_id where a.product_id like 'E%' group by b.customer_id""",conn)

# Update this details to the output table

update_output_table(list(english_movies_df['CUSTOMER_ID']),'ENGLISH_MOVIES',list(english_movies_df['ENGLISH_MOVIES']))

hindi_movies_df = pd.read_sql("""select b.customer_id, count(a.product_id) as "HINDI_MOVIES" from transaction_fact a inner join customer_dim b on a.customer_id = b.customer_id where a.product_id like 'H%' group by b.customer_id""",conn)

# Update this details to the output table

update_output_table(list(hindi_movies_df['CUSTOMER_ID']),'HINDI_MOVIES',list(hindi_movies_df['HINDI_MOVIES']))

most_popular_genre_df = pd.read_sql("""select x.customer_id, x.genre as "MOST_POPULAR_GENRE",x.count1 from (select b.customer_id,c.genre,count(a.transaction_id) as count1, RANK() OVER (PARTITION BY b.customer_id order by count(a.transaction_id) desc,c.genre) as Rank1 from transaction_fact a inner join customer_dim b on a.customer_id = b.customer_id inner join product_dim c on a.product_id = c.product_id group by b.customer_id, c.genre)x where x.Rank1 = 1""",conn)

# Update this details to the output table

update_output_table(list(most_popular_genre_df['CUSTOMER_ID']),'MOST_POPULAR_GENRE',list(most_popular_genre_df['MOST_POPULAR_GENRE']))

conn.close()

除了最后一次调用update_output_table之外，整个脚本都在执行中，它给出了ORA-00904 : 'UNCONVENTIONAL' invalid identifier。在

我传递了正确的列名。下面是我创建output表的代码：

^{pr2}$

UNCONVENTIONAL是事实表产品类别的第一个条目。它是选择UNCONVENTIONAL作为我的列名而不是{}？如果是，那为什么呢？在

注：我是个新手，所以如果是个愚蠢的疑问，请直说出来。在