I can't update database from python

Question

I have an sql table which i want to update based on a function this is my code so far:

def read(conn):
    cursor = conn.cursor()
    #cursor2 = conn.cursor()
    cursor.execute("SELECT All [id],[keyword],[result],[status],[createddate] FROM [Table1].[dbo].[aa]")
    index = 1
    cursor.execute("set nocount on")
    for row in cursor:
        s = row[1]
        s = re.sub(r'[^\w\s]', '', s)
        a=do_func(s)
        if a:
            cursor.execute("update [Table1].[dbo].[aa] set status = 'completed', result = 'True' where id ={}".format(index))
        else:
            cursor.execute("update [Table1].[dbo].[aa] set status = 'completed', result = 'False' where id ={}".format(index))
        if index == 10:
            break
        index += 1

i get pyodbc.ProgrammingError: No results. Previous SQL was not a query. I added "set nocount on" but didn't solve i also tried making a second cursor but also didn't solve problem

Why do you need to append `.format(index)` the SQL query string? — vcp, Mar 28 '19 at 10:21
@vcp i am not sure i just copied the code from youtube, if you have a better idea to do please show some code — hadesfv, Mar 28 '19 at 10:22
you will need to split the cursor for select and cursor for update, you cannot use both at the same time. And after update, you will need to commit. — MEdwin, Mar 28 '19 at 10:33

MEdwin · Accepted Answer · 2019-03-28T10:44:45.627

1

okay, see the code: you will need to split the cursor for select and cursor for update, you cannot use both at the same time. And after update, you will need to commit. Let me know if it works.

def read(conn):
    selectcursor = conn.cursor()
    updatecursor = conn.cursor()
    selectcursor.execute("SELECT [id],[keyword],[result],[status],[createddate] FROM [Table1].[dbo].[aa]")
    index = 1
    result = selectcursor.fetchall()
    for row in result:
        s = row[1]
        s = re.sub(r'[^\w\s]', '', s)
        a=do_func(s)
        if a:
            updatecursor.execute("update [Table1].[dbo].[aa] set status = 'completed', result = 'True' where id ={}".format(index))
            updatecursor.commit()
        else:
            updatecursor.execute("update [Table1].[dbo].[aa] set status = 'completed', result = 'False' where id ={}".format(index))
            updatecursor.commit()
        if index == 10:
            break
        index += 1
        selectcursor.close()
        updatecursor.close()

edited Mar 28 '19 at 10:44

answered Mar 28 '19 at 10:35

MEdwin

2,940
1
14
27

`updatecursor.execute("update [Table1].[dbo].[a] set status = 'completed', resulturl = 'False' where id ={}".format(index)) pyodbc.Error: ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (0) (SQLExecDirectW)')` – hadesfv Mar 28 '19 at 10:39
it seems there is a lock on that table – MEdwin Mar 28 '19 at 10:44
what do you mean by Lock ? – hadesfv Mar 28 '19 at 10:44
database tables are locked when you are trying to update it, so that only one person can finish the update. so after update, the connection needs to be closed. I have modified the script, have a look. – MEdwin Mar 28 '19 at 10:45
the solution is to add cursor.close() – MEdwin Mar 28 '19 at 10:47
do you want to go to a discussion chat? – MEdwin Mar 28 '19 at 10:51
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/190826/discussion-between-hadesfv-and-medwin). – hadesfv Mar 28 '19 at 10:53
Don't use string formatting to put values into query strings; use parameters. And there's no need to define seperate queries for `a` being `True` or `False`. Just hand in the string representation of `a`'s value. The whole `if a: [...] else: [...]` block should be replaced by `updatecursor.execute("update [Table1].[dbo].[aa] set status = 'completed', result = ? where id = ?", (str(a), index)); updatecursor.commit()`. – shmee Mar 28 '19 at 10:56
very good suggestion, but he seems to be trying to add a level of logic on the value of a (do_func is created for that)... – MEdwin Mar 28 '19 at 11:15
`do_func` returns `True` or `False` according to the OP. It's return value is assigned to `a` and then `a` is used in a condition to solely decide if the table column `result` will be set to either the string `True` or the string `False`. There's no point in having two query strings to do that :) Plus, my main point of critique is the use of `format()`, although I know, that this was taken from the OP's code. – shmee Mar 28 '19 at 11:19

I can't update database from python

1 Answers1