python sql help

[keuninkske]

hello, i am a rather beginner for programming, certainly to python.
i wrote a piece of code in python, helped myself by the numerous tutorials online

the piece of code is getting data from my sql server
it gets the data in via a cursor

within the cursor i am testing the data on several points
for 1 of the points i need to retrieve data from another table in the database

but when i am calling the database again from within the cursor i get following failure code:

Error:
connection-is-busy-with-results-for-another-hstmt

i am sure this is a common problem but cannot find help online
what is a common routine to solve this problem?

things i've been thinking about:
-store the retrieved lines temporarely in another table ==> same problem
-store the retrieved lines in a CSV ==> looks not so neat in my opinion
-...

thanks for your help, directing me to a tutorial where everything is explained would be the most perfect outcome for this question

[Larz60+]

It's not a common problem, usually a query works fairly quickly, or a bad request will time out.
Very hard to say without more information on what the data is, and what the code looks like.
Sometimes a bad query (usually some sort of join) will run for a very very long time. It's possible this is what you have.
Again, without more information from you, it's next to impossible to determine the cause.

[keuninkske]

hello,

my querry doesnt have a join in it
but what i do is like following:

sqlverbinding = pyodbc.connect(
    "Driver={SQL Server};Server=192.168.1.17;Database=Test;Trustedconnection=yes;"
)
cursor = sqlverbinding.cursor()
cursor1 = sqlverbinding.cursor()

def opvragenminimumstock(partnum):
    minimumstock = cursor1.execute("select columna from TABLEA where columnb = '"+ partnum +"'")
    print(minimumstock)
    pass


def sqlread(sqlverbinding):
    print("read")

    cursor.execute("select * from TABLEB where columnA = 'stock' and columnB = 0 and columnC > 0")
    teller = [0, 0, 0 ,0, 0, 0, 0, 0, 0]
    for row in cursor:
        print("begin for = "+ str({row[9]}))
        if {str(row[9])} == {'DEC034'}:
            opvragenminimumstock(row[5])
            teller[0] = teller[0] + 1
        elif {str(row[9])} == {'DEC039'}:
...........
...

so, the problem is that the cursor runs while the query is executing, as soon as the routine "opvragenminimumstock" runs i get an error that the database is busy

what is a neat way to solve this, i cannot immagine that i am first programmer running into this problem...
also i do not find the right search words for looking at matching problems in google

thanks for all the help provided

[SnoopFrogg]

I think the issue is in your connection string in line 1 of your code. I'm assuming that the results of the first query results are open against the same connections as your second query. SQL added a multiple active result sets (MARS) feature to allow you to create multiple cursors using a single connection. Try something like:

sqlverbinding = pyodbc.connect(
    "Driver={SQL Server};Server=192.168.1.17;Database=Test;Trustedconnection=yes;MARS_Connection=yes;"
)

Hopefully that helps.

[keuninkske]

unfortunatly the MARS parameter in the connection string doesnt help me out either, still same HSTMT error
should i do a setting or something for this MARS thing on my server side?

it sounded promising, but didn't helped me out

[SnoopFrogg]

unfortunatly the MARS parameter in the connection string doesnt help me out either, still same HSTMT error
should i do a setting or something for this MARS thing on my server side?

it sounded promising, but didn't helped me out

Sorry, you need a semicolon at the end of the

MARS_Connection=yes;

If that doesn't work you might want to use a JOIN statement to combine both of the tables together.

[keuninkske]

just want you guys to get informed about my final solution on this one

i pulled the results of my first querry in an list of dictionaries
once this list is complete i closed the communication with the sql server

thereafter i can iterate through the list and open for every item a small querry to add the necesary data

works fine for me, if someone knows better, faster or nicer solutions, i am open to learn...

thanks for your guys help!! it helped me on the way

[ndc85430]

def opvragenminimumstock(partnum):
    minimumstock = cursor1.execute("select columna from TABLEA where columnb = '"+ partnum +"'")
    print(minimumstock)
    pass

You should never be building queries by concatenating strings (the term to look up is "SQL injection"). Instead, you should be using the right placeholder character (which is apparently ?, according to the documentation.

In addition, that pass statement is unnecessary.

[keuninkske]

You should never be building queries by concatenating strings (the term to look up is "SQL injection"). Instead, you should be using the right placeholder character (which is apparently ?, according to the documentation.

thanks for making me aware,
i know what SQL injection is, but dont know the methods to avoid it as i am learning to program at the moment

it wasn't necesary for me to dive deeper into this as the application will only be available local and is not intended to be published on the web

with this comment I learned again something, thanks