Re: [cx-oracle-users] cx_Oracle.InterfaceError: not a query
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] cx_Oracle.InterfaceError: not a query
|
From: John N. <mr....@gm...> - 2010-10-08 16:02:08
|
tab_col_map = {
'E_APDS':['APD_CORRECTION_REMARK'],
'E_APD_ATTACHMENTS':['ATT_NAME'],
'E_APD_NOTES':['NOTE_SUBJECT','NOTE_TEXT'],
'E_APD_QAS':['QA_RESPONSE_TXT'],
'E_APD_STATUSES':['APD_STATUS_RMKS'],
'E_APMS':['APM_OP_DESC','CORRECTION_DESC'],
'E_APM_ATTACHMENTS':['ATT_NAME'],
'E_APM_NOTES':['NOTE_TEXT'],
'E_APM_QAS':['QA_RESPONSE_TXT'],
'E_APM_STATUSES':['APM_STATUS_RMKS'],
'E_CORRECTIONS':['CORRECTION_DESC'],
'E_CORRECTION_STATUSES':['STATUS_REMARKS'],
'E_END_WELL_OP_REPORTS':['OPERATIONAL_NARRATIVE'],
'E_EOR_COMPLETIONS':['REMARK'],
'E_EOR_HC_BEARING_INTERVALS':['INTERVAL_NAME'],
'E_EOR_NOTES':['NOTE_TEXT'],
'E_EOR_STATUSES':['STATUS_REMARKS'],
'E_INCIDENT_REPORT_ATTACHMENTS':['ATT_NAME'],
'E_INCIDENT_REPORT_CONTACTS':['EMAIL_ADDR'],
'E_INCIDENT_REPORT_STATUSES':['STATUS_REMARKS'],
'E_PIPELINE_DAMAGES':['PPL_DAMAGE_DESC'],
'E_REG_DEPARTURES':['REG_DEPARTURE_TXT'],
'E_RIG_MOVE_NOTIFICATIONS':['REMARKS','RIG_OBSTRUCTION_DESC','RIG_REP_PHONE'],
'E_STORM_PIPELINE_DAMAGES_HIST':['DAMAGE_DESC'],
'E_STORM_PLATFORM_DAMAGES_HIST':['ADDITIONAL_REMARKS','DAMAGE_DESC'],
'E_STORM_POLLUTIONS_HIST':['POLLUTION_REMARKS'],
'E_STORM_REPORT_STATUSES_HIST':['STORM_REPORT_STATUS_RMKS'],
'E_STORM_STR_PROD_CURTAILS_HIST':['CURTAILMENT_REMARKS'],
'E_WAR_NOTES':['NOTE_TEXT'],
'E_WAR_STATUSES':['STATUS_REMARKS'],
'E_WELL_ACTIVITY_REPORTS':['WAR_CORRECTION_NARRATIVE','WAR_SIGNIF_EVENT_NARRATIVE']
}
for table in tab_col_map:
print table
#
# First, get the primary key column of the table we're analyzing
con_query = "select owner, column_name " + \
"from all_cons_columns " + \
"where constraint_name = ( " + \
"select constraint_name " + \
"from all_constraints " + \
"where table_name = '" + table + "' " + \
"and constraint_type = 'P') " + \
"and table_name = '" + table + "'"
constraints = cur.execute(con_query)
for constraint in constraints:
owner = constraint[0]
uid_col = constraint[1]
full_name = owner + "." + table
print full_name + " : " + uid_col
#
# Next, check each column in list mapped to current table to see
# if it contains any unsupported characters.
for column in tab_col_map[table]:
print "\t%s" % column
#
# Oracle dump() function is used to find 8-bit ASCII code for
# each character returned
query = "select " + uid_col + ", dump(" + column + ") " + \
"from " + full_name
try:
rows = cur.execute(query)
except cx_Oracle.DatabaseError, e:
print "\t" + str(e)
continue
count = 0
for row in rows:
#
# update_flag = False if row doesn't contain
# the unsupported characters
update_flag = False
text_dump = row[1]
uid = row[0]
if text_dump.strip() != 'NULL':
codes = text_dump.split(':')[1].strip().split(',')
returned_string = ""
for code in codes:
code = int(code)
if code in char_map.keys():
count += 1
returned_string += char_map[code]
if code != 39:
#
# 39 is the code for the straight single quote.
# While this needs to be accounted for (i.e.
# single quotes must be escaped to
avoid terminating
# the update string), the presence of a single
# straight quote in a row does not
necessitate an
# update.
update_flag = True
else:
returned_string += chr(code)
if update_flag:
#
# If an update is necessary, print row's PK value
print "\tChange needed for %s = %d" % (uid_col, uid)
update_query = "update " + full_name + " " + \
"set " + column + \
" = '" + returned_string + "' " +\
"where " + uid_col + \
" = " + str(uid)
cur.execute(query)
conn.close()
On Fri, Oct 8, 2010 at 10:55 AM, Anthony Tuininga
<ant...@gm...> wrote:
>
> Ok, can you show the code that is causing the problem? Is it
> consistently failing with that one statement or does it have to be
> after a bunch of other statements first? If you can send me a simple
> script that demonstrates the problem I'll look at it. I've never run
> into that problem myself unless I accidentally attempted to fetch from
> the cursor -- but that doesn't mean there isn't some strange bug
> lurking.... :-)
>
> Anthony
>
> On Fri, Oct 8, 2010 at 8:52 AM, John Nielson <mr....@gm...> wrote:
> > Right...
> >
> > I'm using cursor.execute(my_statement) to perform the update. I am aware
> > that update statements aren't queries; however, I'm also aware that the
> > execute method is used to perform updates. The script iterates through a
> > whole slew of update statements that work just fine before blowing up on
> > this one.
> >
> >
> >
> > On Thu, Oct 7, 2010 at 10:28 AM, Anthony Tuininga
> > <ant...@gm...> wrote:
> >>
> >> Hi,
> >>
> >> Not sure exactly what you are trying to accomplish but showing the
> >> exact Python code you are executing would be helpful. In any case, the
> >> statement shown below is definitely not a query -- it looks very much
> >> like an update statement which does not query information but updates
> >> information in the database. So you should be simply doing this
> >>
> >> cursor.execute(your_statement)
> >>
> >> Do not attempt to fetch from the cursor, though. That will generate
> >> the "not a query" exception. Make sense?
> >>
> >> Anthony
> >>
> >> On Thu, Oct 7, 2010 at 8:29 AM, John Nielson <mr....@gm...>
> >> wrote:
> >> > All:
> >> >
> >> > I have a script that performs a bunch of updates. However, after
> >> > attempting
> >> > to perform the following query (carriage returns intentional):
> >> >
> >> > update NIELSONJ.MYTABLE set COLUMN_1 = '"double quotes"
> >> > ''single quotes''
> >> > En - dash
> >> > Em-dash
> >> > . bullet
> >> > ' where COLUMN_2 = -2395
> >> >
> >> >
> >> > I get the following error:
> >> >
> >> > cx_Oracle.InterfaceError: not a query
> >> >
> >> >
> >> > I've copied and pasted the query into my IDE, executed it, and it runs
> >> > just
> >> > fine. It's definitely a query! I am mystified.
> >> >
> >> > Please note what kinds of quotes those are and that all single quotes
> >> > within
> >> > the query are doubled to avoid terminating the update string. The
> >> > script is
> >> > intended to cleanse the database of data containing weird characters
> >> > like
> >> > smart quotes (e.g. left single quotes are replaced with straight single
> >> > quotes).
> >> >
> >> > --
> >> > John Nielson
> >> > mr....@gm...
> >> > (504) 344.7225
> >> >
> >> >
> >> > ------------------------------------------------------------------------------
> >> > Beautiful is writing same markup. Internet Explorer 9 supports
> >> > standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
> >> > Spend less time writing and rewriting code and more time creating great
> >> > experiences on the web. Be a part of the beta today.
> >> > http://p.sf.net/sfu/beautyoftheweb
> >> > _______________________________________________
> >> > cx-oracle-users mailing list
> >> > cx-...@li...
> >> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >> >
> >> >
> >>
> >>
> >> ------------------------------------------------------------------------------
> >> Beautiful is writing same markup. Internet Explorer 9 supports
> >> standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
> >> Spend less time writing and rewriting code and more time creating great
> >> experiences on the web. Be a part of the beta today.
> >> http://p.sf.net/sfu/beautyoftheweb
> >> _______________________________________________
> >> cx-oracle-users mailing list
> >> cx-...@li...
> >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> >
> > --
> > John Nielson
> > mr....@gm...
> > (504) 344.7225
> >
> > ------------------------------------------------------------------------------
> > Beautiful is writing same markup. Internet Explorer 9 supports
> > standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
> > Spend less time writing and rewriting code and more time creating great
> > experiences on the web. Be a part of the beta today.
> > http://p.sf.net/sfu/beautyoftheweb
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
>
> ------------------------------------------------------------------------------
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
> Spend less time writing and rewriting code and more time creating great
> experiences on the web. Be a part of the beta today.
> http://p.sf.net/sfu/beautyoftheweb
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
--
John Nielson
mr....@gm...
(504) 344.7225
|
