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 20:47:16
|
I should let you know that I tacked the cur.execute(query) statement
at the end of my code only in this e-mail. I've since edited it (for
the sake of getting my work done at the job) to generate an external
SQL script containing all the update statements. So, for the sake of
troubleshooting, pretend that my code says cur.execute(update_query)
My bad, y'all!
On Fri, Oct 8, 2010 at 3:41 PM, Marco De Paoli <dep...@gm...> wrote:
> 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
>
>
> ------------------------------------------------------------------------------
> 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
|
