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: Marco De P. <dep...@gm...> - 2010-10-08 20:41:55
|
there something strange: on the final lines you have just defined an
update_query string and you call cur.execute(query)
probably you intended "cur.execute(update_query)" in place of
"cur.execute(query)" (may be a copy/paste problem?)
in this case, you are executing two different sql statements using the same
cursor: while you are fetching rows from a select, you use *the same cursor*
to execute an update
Marco
On Fri, Oct 8, 2010 at 6:01 PM, John Nielson <mr....@gm...>wrote:
> 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
>
>
> ------------------------------------------------------------------------------
> 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
>
|
