This code is writing only one row in DB?

Question

This code is writing only one row in DB

I find no error in this code . . .

But why is this not inserting more than the first row ?

def transremovechars():
    cur.execute('drop table transforms')
    char_cfg = config.get('Transform_Variables', 'Chars_to_be_removed')      #Reads all the special chars to be removed from specialchars.txt#
    cur.execute('select * from originallist')
    for row in cur:                                                         #Applies transformation to remove chars for each row in a loop#
        company = row[0]
        for specialchars in char_cfg:
            company =  company.replace(specialchars, '')
        cur.execute('Insert into transforms (Transresult1) values (\'' + company + '\')')
    con.commit()

You should add `results = cur.fetchall()` and loop over `results` and not the cursor. — Niclas Nilsson, Feb 13 '12 at 13:26
glglgl: Ok. That's just how I always have done it. So I supposed that was the way to go. But ok, then I know now, you can loop over the cursor. — Niclas Nilsson, Feb 13 '12 at 13:30
glglgl: You removed your comment? Can or can't you iterate directly over the cursor? — Niclas Nilsson, Feb 13 '12 at 13:34
@NiclasNilsson I am not sure; I only know it from MySQL and thought it was standard. Then I saw that it was about sqlite, which I don't know. — glglgl, Feb 13 '12 at 13:39
@NiclasNilsson According to [PEP 249](http://www.python.org/dev/peps/pep-0249/), the cursor's support for iteration protocol is optional. But I have tested it now; the `sqlite3` module supports it. — glglgl, Feb 13 '12 at 13:43

score 2 · Answer 1 · answered Feb 13 '12 at 13:25

You forgot the cur.fetchall():

def transremovechars():
    cur.execute('drop table transforms')
    char_cfg = config.get('Transform_Variables', 'Chars_to_be_removed')      #Reads all the special chars to be removed from specialchars.txt#
    cur.execute('select * from originallist')
    for row in cur.fetchall():                                                         #Applies transformation to remove chars for each row in a loop#
        company = row[0]
        for specialchars in char_cfg:
            company =  company.replace(specialchars, '')
        cur.execute('Insert into transforms (Transresult1) values (\'' + company + '\')')
    con.commit()

by 'printing' you mean 'inserting a row into transforms table'? — warvariuc, Feb 15 '12 at 08:03

glglgl · Answer 2 · 2012-02-13T13:46:48.357

You seem to drop your table transforms before working with it. Are you sure you want that? Or maybe have you forgotten to show the code which creates it again?

Your select * might e overkill if you only use the 1st column. Maybe you want to name that field in the SELECT.

Besides, you should replace your INSERT line with

cur.execute('Insert into transforms (Transresult1) values (?)', company)

Iterating over the cursor should be fine, however. Maybe you could insert some print statements into your for loop...

score 1 · Answer 3 · edited May 23 '17 at 11:56

Comments to the effect that you should cur.fetchall() and iterate over that will work and be OK. The real fault in your code is that once you use cur to insert, it is a "new thing" and the original generator is reset (cur has the next()) method.

You can use cur without doing fetchall as you wanted, just create a second cursor ins_cur = con.curson() and use both. Many more advanced effects can be accomplished by iterating or using multiple cursors open on one connection.

And yes please use the correct variable binding for your dbapi module.

This code is writing only one row in DB?

3 Answers3