Cursors in a Loop

Discussion in 'Python' started by t_rectenwald, Jan 4, 2008.

  1. t_rectenwald

    t_rectenwald Guest

    I have a python script that uses the cx_Oracle module. I have a list
    of values that I iterate through via a for loop and then insert into
    the database. This works okay, but I'm not sure whether I can use one
    cursor for all inserts, and define it outside of the loop, or
    instantiate and close the cursor within the loop itself. For example,
    I have:

    for i in hostlist:
    cursor = connection.cursor()
    sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
    cursor.execute(sql)
    cursor.close()

    And I've also tried:

    cursor = connection.cursor()
    for i in hostlist:
    sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
    cursor.execute(sql)
    cursor.close()

    Both work fine, and execute in the same amount of time. I'm just
    trying to understand what is the "correct" approach to use.

    Thanks,
    Tom
     
    t_rectenwald, Jan 4, 2008
    #1
    1. Advertising

  2. t_rectenwald

    t_rectenwald Guest

    On Jan 3, 7:47 pm, t_rectenwald <> wrote:
    > I have a python script that uses the cx_Oracle module.  I have a list
    > of values that I iterate through via a for loop and then insert into
    > the database.  This works okay, but I'm not sure whether I can use one
    > cursor for all inserts, and define it outside of the loop, or
    > instantiate and close the cursor within the loop itself.  For example,
    > I have:
    >
    > for i in hostlist:
    >     cursor = connection.cursor()
    >     sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
    >     cursor.execute(sql)
    >     cursor.close()
    >
    > And I've also tried:
    >
    > cursor = connection.cursor()
    > for i in hostlist:
    >     sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
    >     cursor.execute(sql)
    > cursor.close()
    >
    > Both work fine, and execute in the same amount of time.  I'm just
    > trying to understand what is the "correct" approach to use.
    >
    > Thanks,
    > Tom


    I think I have this one figured out. The answer would be the second
    option, i.e. keep the cursor instantion and close outside of the
    loop. I wasn't aware that one cursor could be used for multiple
    executes.

    Regards,
    Tom
     
    t_rectenwald, Jan 4, 2008
    #2
    1. Advertising

  3. On Thu, 2008-01-03 at 17:25 -0800, t_rectenwald wrote:
    > On Jan 3, 7:47 pm, t_rectenwald <> wrote:
    > > I have a python script that uses the cx_Oracle module. I have a list
    > > of values that I iterate through via a for loop and then insert into
    > > the database. This works okay, but I'm not sure whether I can use one
    > > cursor for all inserts, and define it outside of the loop, or
    > > instantiate and close the cursor within the loop itself. For example,
    > > I have:
    > >
    > > for i in hostlist:
    > > cursor = connection.cursor()
    > > sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
    > > cursor.execute(sql)
    > > cursor.close()
    > >
    > > And I've also tried:
    > >
    > > cursor = connection.cursor()
    > > for i in hostlist:
    > > sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
    > > cursor.execute(sql)
    > > cursor.close()
    > >
    > > Both work fine, and execute in the same amount of time. I'm just
    > > trying to understand what is the "correct" approach to use.


    Actually, the correct approach would be "neither." You should NEVER use
    string formatting to fill values into an SQL query. (Doing so causes
    security vulnerabilities and performance problems. See, for example,
    http://informixdb.blogspot.com/2007/07/filling-in-blanks.html for
    detailed explanations.) Instead, you should use a parametrized query.

    With a parametrized query, your code becomes this:

    cursor = connection.cursor()
    for i in hostlist:
    cursor.execute("insert into as_siebel_hosts_temp values(?)", (i,) )
    cursor.close()

    Since this will save the database engine from having to re-parse the
    query every time, it will run much faster if the list is long.

    Even better would be to use executemany:

    cursor = connection.cursor()
    cursor.executemany("insert into as_siebel_hosts_temp values(?)",
    [(i,) for i in hostlist] )
    cursor.close()

    Depending on whether cx_Oracle allows this, the list comprehension in
    that example could be replaced by the generator expression
    ((i,) for i in hostlist), but I don't know if cx_Oracle allows
    executemany with an arbitrary iterable.

    Hope this helps,

    --
    Carsten Haese
    http://informixdb.sourceforge.net
     
    Carsten Haese, Jan 4, 2008
    #3
  4. t_rectenwald

    Chris Guest

    On Jan 4, 5:11 am, Carsten Haese <> wrote:
    > On Thu, 2008-01-03 at 17:25 -0800, t_rectenwald wrote:
    > > On Jan 3, 7:47 pm, t_rectenwald <> wrote:
    > > > I have a python script that uses the cx_Oracle module. I have a list
    > > > of values that I iterate through via a for loop and then insert into
    > > > the database. This works okay, but I'm not sure whether I can use one
    > > > cursor for all inserts, and define it outside of the loop, or
    > > > instantiate and close the cursor within the loop itself. For example,
    > > > I have:

    >
    > > > for i in hostlist:
    > > > cursor = connection.cursor()
    > > > sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
    > > > cursor.execute(sql)
    > > > cursor.close()

    >
    > > > And I've also tried:

    >
    > > > cursor = connection.cursor()
    > > > for i in hostlist:
    > > > sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
    > > > cursor.execute(sql)
    > > > cursor.close()

    >
    > > > Both work fine, and execute in the same amount of time. I'm just
    > > > trying to understand what is the "correct" approach to use.

    >


    > Even better would be to use executemany:
    >
    > cursor = connection.cursor()
    > cursor.executemany("insert into as_siebel_hosts_temp values(?)",
    > [(i,) for i in hostlist] )
    > cursor.close()
    >
    > Depending on whether cx_Oracle allows this, the list comprehension in
    > that example could be replaced by the generator expression
    > ((i,) for i in hostlist), but I don't know if cx_Oracle allows
    > executemany with an arbitrary iterable.


    You should bind all variables to save the pool.

    cursor = connection.cursor()
    cursor.executemany("""insert into as_siebel_hosts_temp
    values :)whole, :lot, :eek:f, :bind, :variables)
    """
    ,[(i,)[0] for i in hostlist]
    )
    connection.commit()
    connection.close()
     
    Chris, Jan 4, 2008
    #4
  5. On Fri, 2008-01-04 at 00:03 -0800, Chris wrote:
    > You should bind all variables to save the pool.
    >
    > cursor = connection.cursor()
    > cursor.executemany("""insert into as_siebel_hosts_temp
    > values :)whole, :lot, :eek:f, :bind, :variables)
    > """
    > ,[(i,)[0] for i in hostlist]
    > )
    > connection.commit()
    > connection.close()


    Huh? In the OP's example, the table one has one column. I'll openly
    admit that I don't know anything about Oracle, but that code doesn't
    make sense to me. Maybe you're trying to execute a multi-row insert, but
    that would be done with execute(), not executemany(), wouldn't it?

    Also, isn't "[(i,)[0] for i in hostlist]" exactly the same as "[i for i
    in hostlist]" which in turn is exactly the same as "hostlist"?

    --
    Carsten Haese
    http://informixdb.sourceforge.net
     
    Carsten Haese, Jan 4, 2008
    #5
  6. t_rectenwald

    Chris Guest

    On Jan 4, 4:32 pm, Carsten Haese <> wrote:
    > On Fri, 2008-01-04 at 00:03 -0800, Chris wrote:
    > > You should bind all variables to save the pool.

    >
    > > cursor = connection.cursor()
    > > cursor.executemany("""insert into as_siebel_hosts_temp
    > > values :)whole, :lot, :eek:f, :bind, :variables)
    > > """
    > > ,[(i,)[0] for i in hostlist]
    > > )
    > > connection.commit()
    > > connection.close()

    >
    > Huh? In the OP's example, the table one has one column. I'll openly
    > admit that I don't know anything about Oracle, but that code doesn't
    > make sense to me. Maybe you're trying to execute a multi-row insert, but
    > that would be done with execute(), not executemany(), wouldn't it?
    >
    > Also, isn't "[(i,)[0] for i in hostlist]" exactly the same as "[i for i
    > in hostlist]" which in turn is exactly the same as "hostlist"?
    >
    > --
    > Carsten Haesehttp://informixdb.sourceforge.net


    The OPs example has a formatted string, no idea what is in it...
    My example creates a tuple out of each of the records you want to
    insert and uses them in the bind variables.

    You can do a loop through hostlist and do a single execute on each one
    if you want. It won't make a large impact.
    The [(i,)[0] for i in hostlist] was mainly directed to you because
    your structure ends up being a tuple inside a list which doesn't work
    for cx_Oracle. You need a straight tuple to bind to the statement.

    My code creates a series of usable tuples for the executemany
    function.
    HTH,
    Chris
     
    Chris, Jan 5, 2008
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. TaeHo Yoo
    Replies:
    2
    Views:
    2,223
    Rhys Gravell
    Jun 26, 2003
  2. Kenneth

    Database cursors in .NET 2003

    Kenneth, Aug 7, 2003, in forum: ASP .Net
    Replies:
    3
    Views:
    1,217
    Cowboy \(Gregory A. Beamer\)
    Aug 7, 2003
  3. et
    Replies:
    3
    Views:
    405
    Karl Seguin
    Nov 18, 2004
  4. Lei Yang
    Replies:
    0
    Views:
    347
    Lei Yang
    Oct 8, 2005
  5. Isaac Won
    Replies:
    9
    Views:
    419
    Ulrich Eckhardt
    Mar 4, 2013
Loading...

Share This Page