Divide string from query column and insert results into the same table

Question

I have a table in database, where one column contains first name and second name together (divided by space).

I am trying to load these data, split each row and save it again into the table. It is possible to do that a display that data, but when I try to save it back, I am getting

'NoneType' object has no attribute 'split'.

import sys, os, pyodbc

conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:/Users/vlcek/Desktop/pokusdb.accdb;'
    )
connection = pyodbc.connect(conn_str)
cursor = connection.cursor()
cursor2 = connection.cursor()

sql="Select whole_name from people"

cursor.execute(sql)

for change in cursor:
    devided=change[0].split()
    print(devided[0]+"--"+devided[1])

    sql2="Insert into people (user_id, Name, Surname) values (27, ?,?)"
    cursor2.execute(sql2,(devided[0],devided[1]))


connection.close()

Without those two lines if would work fine and I can show the data in console, so there is problem in those tow lines:

sql2="Insert into people (user_id, Name, Surname) values (27, ?,?)"
cursor2.execute(sql2,(devided[0],devided[1]))

I tried to create also two connection.cursor objects, but it doesn't work even when I worked just with

cursor = connection.cursor()

Thank you for your advice,

Vaclav

I would do a `cursor.execute(sql).fetchall()` and then iterate over the result — igon, Aug 21 '18 at 22:03
Your code works when you omit the insert statements because it is only iterating over the original rows in the table. Once you start inserting rows into that same table you run the risk of iterating over those rows too. See [my comment here](https://stackoverflow.com/questions/51957010/divide-string-from-query-and-save-it-in-database#comment90867133_51957380) for more details. — Gord Thompson, Aug 22 '18 at 00:34
Well, that would be interesting to iterate over the new inserted rows. However, would be possible in this case? sql="Select whole_name from people" is out of the loop and then I just work in the loop... cursor.execute(sql) is no executed anymore. Or am I wrong? — Vaclav Vlcek, Aug 22 '18 at 22:01

score 1 · Answer 1 · answered Aug 21 '18 at 22:09

1

The problem is most likely that you are keeping two cursors open on a single connection. Try obtaining all the rows for the first query rows = cursor.execute(sql).fetchall() and then iterate over these.

You can check if this is the culprit of your problem (as suggested here) by running:

how_many = connection.getinfo(pyodbc.SQL_MAX_CONCURRENT_ACTIVITIES)
print(how_many)

answered Aug 21 '18 at 22:09

igon

3,016
1
22
37

Your suggestion is the right one, but it's not simply a problem with "two cursors open on a single connection". It's that the code is using one cursor to iterate over a table while a second cursor is adding rows to the same table. Eventually the first cursor will start reading the rows that the second cursor wrote - given the transaction isolation of the Access ODBC driver - and the process will never stop unless an error condition occurs, such as in this case when `whole_name` is null because the inserted rows omit that column. – Gord Thompson Aug 22 '18 at 00:29
1

Right. I guess I wrote my answer too hastily :) I think the idea by the poster was that keeping two cursors would allow him to iterate and write at the same time. Really you should not write on something when iterating over it as you said. – igon Aug 22 '18 at 04:51
Thank you for your advices, I tried to do that so: cursor = connection.cursor() cursor2 = connection.cursor sql="Select whole_name from people" cursor.execute(sql).fetchall() it still doesn't work unfortunately. – Vaclav Vlcek Aug 22 '18 at 22:20
Ok, I used your solution, I do not get that message anymore, but it doesn't save it into the table anyway. I guess, I have the problem with "insert into" itself. I am solving it here: https://stackoverflow.com/questions/51976146/python-insert-into-ms-access-table – Vaclav Vlcek Aug 22 '18 at 22:46

score 0 · Answer 2 · answered Aug 23 '18 at 22:33

so unfortunately fetchall() doesn't work either. This was finally my solution

 import sys, os, pyodbc, copy

conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:/Users/vlcek/Desktop/pokusdb.accdb;'
    )
connection = pyodbc.connect(conn_str)
cursor = connection.cursor()

sql="Select whole_name from people"

cursor.execute(sql)
mylist1=list(cursor)
mylist2=copy.deepcopy(mylist1)


for change in mylist2:
    devided=change[0].split()
    print(devided[0]+"--"+devided[1])

    sql2="Insert into people (user_id, Name, Surname) values (27, ?,?)"
    cursor.execute(sql2,(devided[0],devided[1]))

connection.commit()
connection.close()

So I:

copied cursor into the list -> mylist1=list(cursor)
made deepcopy of the list -> mylist2=copy.deepcopy(mylist1)
iterated through that deep copied list
I was also missing connection.commit() at the end

But thank you for your help anyway.

Vaclav

Divide string from query column and insert results into the same table

2 Answers2