cx-oracle-users

[cx-oracle-users] which row is causing problem ? (with executemany and cx_Oracle.DatabaseError)

[Quiche O. <qui...@gm...>]

Hello,

I am trying to import millions of rows in a table with executemany.

Fiew of them are rejected by oracle because of various problems (not null
constraint, format number ...).

If I do this :

##################################################################
insert = """ INSERT INTO mytable (col1,col2,col3)   VALUES (:1, :2, :3)"""
L = []
reader = csv.reader(open(infile), delimiter=';')
i=0
for row in reader:
    L.append(row)
    if(i>10000):
        try:
            cursor.prepare(insert)
            cursor.executemany(None, L)
            cursor.commit()
            i=0
        except cx_Oracle.DatabaseError,exc:
            error, = exc.args
            mess="\nOracle-Error-Code:"+str(error.code)
            mess+="\nOracle-Error-Message:"+str(error.message)
            mess+="\nOracle-Error-context:"+str(error.context)
            mess+="\nTOUS:"+str(exc)
            sys.exit(mess)
    i+=1

##################################################################

I don't know the way to find out in the DatabaseError which row is causing
the exception.

Any idea ?

Re: [cx-oracle-users] which row is causing problem ? (with executemany and cx_Oracle.DatabaseError)

[Anthony T. <ant...@gm...>]

Hi,

On the error object there is an "offset" value which will give you
that information. Also, the rowcount is set to the number of rows that
were successfully processed if I recall correctly.

Anthony

On Thu, Oct 20, 2011 at 7:49 AM, Quiche Obscure
<qui...@gm...> wrote:
> Hello,
>
> I am trying to import millions of rows in a table with executemany.
>
> Fiew of them are rejected by oracle because of various problems (not null
> constraint, format number ...).
>
> If I do this :
>
> ##################################################################
> insert = """ INSERT INTO mytable (col1,col2,col3)   VALUES (:1, :2, :3)"""
> L = []
> reader = csv.reader(open(infile), delimiter=';')
> i=0
> for row in reader:
>     L.append(row)
>     if(i>10000):
>         try:
>             cursor.prepare(insert)
>             cursor.executemany(None, L)
>             cursor.commit()
>             i=0
>         except cx_Oracle.DatabaseError,exc:
>             error, = exc.args
>             mess="\nOracle-Error-Code:"+str(error.code)
>             mess+="\nOracle-Error-Message:"+str(error.message)
>             mess+="\nOracle-Error-context:"+str(error.context)
>             mess+="\nTOUS:"+str(exc)
>             sys.exit(mess)
>     i+=1
>
> ##################################################################
>
> I don't know the way to find out in the DatabaseError which row is causing
> the exception.
>
> Any idea ?
>
> ------------------------------------------------------------------------------
> The demand for IT networking professionals continues to grow, and the
> demand for specialized networking skills is growing even more rapidly.
> Take a complimentary Learning@Ciosco Self-Assessment and learn
> about Cisco certifications, training, and career opportunities.
> http://p.sf.net/sfu/cisco-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] Re : Re: which row is causing problem ? (with executemany and cx_Oracle.DatabaseError)

[<qui...@gm...>]

Thank you for your reply.<br>I'll try this offset tomorrow (it is the evening where I leave)&nbsp;<br><span style="font-family:Prelude, Verdana, san-serif;"><br><br></span><span id="signature"><div style="font-family: arial, sans-serif; font-size: 12px;color: #999999">-- Envoyé depuis mon HP TouchPad</div></span><span style="color:navy; font-family:Prelude, Verdana, san-serif; "><hr align="left" style="width:75%">Le 20 oct. 2011 18:05, Anthony Tuininga &lt;ant...@gm...&gt; a écrit&nbsp;: <br></span>Hi,
<br>
<br>On the error object there is an "offset" value which will give you
<br>that information. Also, the rowcount is set to the number of rows that
<br>were successfully processed if I recall correctly.
<br>
<br>Anthony
<br>
<br>On Thu, Oct 20, 2011 at 7:49 AM, Quiche Obscure
<br>&lt;qui...@gm...&gt; wrote:
<br>&gt; Hello,
<br>&gt;
<br>&gt; I am trying to import millions of rows in a table with executemany.
<br>&gt;
<br>&gt; Fiew&nbsp;of them are rejected by oracle because of various problems (not null
<br>&gt; constraint, format number ...).
<br>&gt;
<br>&gt; If I do this :
<br>&gt;
<br>&gt; ##################################################################
<br>&gt; insert = """ INSERT INTO mytable (col1,col2,col3)&nbsp;&nbsp; VALUES (:1, :2, :3)"""
<br>&gt; L = []
<br>&gt; reader = csv.reader(open(infile), delimiter=';')
<br>&gt; i=0
<br>&gt; for row in reader:
<br>&gt; &nbsp;&nbsp;&nbsp; L.append(row)
<br>&gt; &nbsp;&nbsp;&nbsp; if(i&gt;10000):
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; try:
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cursor.prepare(insert)
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cursor.executemany(None, L)
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cursor.commit()
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i=0
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; except cx_Oracle.DatabaseError,exc:
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; error, = exc.args
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mess="\nOracle-Error-Code:"+str(error.code)
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mess+="\nOracle-Error-Message:"+str(error.message)
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mess+="\nOracle-Error-context:"+str(error.context)
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mess+="\nTOUS:"+str(exc)
<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sys.exit(mess)
<br>&gt; &nbsp;&nbsp;&nbsp; i+=1
<br>&gt;
<br>&gt; ##################################################################
<br>&gt;
<br>&gt; I don't know the way to find out in the DatabaseError which row is causing
<br>&gt; the exception.
<br>&gt;
<br>&gt; Any idea ?
<br>&gt;
<br>&gt; ------------------------------------------------------------------------------
<br>&gt; The demand for IT networking professionals continues to grow, and the
<br>&gt; demand for specialized networking skills is growing even more rapidly.
<br>&gt; Take a complimentary Learning@Ciosco Self-Assessment and learn
<br>&gt; about Cisco certifications, training, and career opportunities.
<br>&gt; http://p.sf.net/sfu/cisco-dev2dev
<br>&gt; _______________________________________________
<br>&gt; cx-oracle-users mailing list
<br>&gt; cx-...@li...
<br>&gt; https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
<br>&gt;
<br>&gt;
<br>
<br>------------------------------------------------------------------------------
<br>The demand for IT networking professionals continues to grow, and the
<br>demand for specialized networking skills is growing even more rapidly.
<br>Take a complimentary Learning@Ciosco Self-Assessment and learn 
<br>about Cisco certifications, training, and career opportunities. 
<br>http://p.sf.net/sfu/cisco-dev2dev
<br>_______________________________________________
<br>cx-oracle-users mailing list
<br>cx-...@li...
<br>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users