pypyodbc - Reusing a Database Connection

Question

I have two questions:

QUESTION-1:

Suppose I have two .py File-1 and File-2

File-1 contains the following statements:

try:
        cononnection = pypyodbc.connect('DRIVER={SQL Server};'
                                            'SERVER=........'
                                            'DATABASE=......'
                                            'UID=......;PWD=......')
except:
        print("I am unable to connect to the SQL SERVER Database")

In File-2, if I use the connection defined in File-1 in the following way:

import AnotherPythonFile as File1

def query():     
        conxn1 = File1.cononnection 
        conxn2 = File1.cononnection
        ...
        ...

Will conxn1 and conxn2 use the same connection opened in File-1 or they will open two more new connections?

QUESTION-2:

If a 'pypyodbc.DatabaseError' occurs while executing the following statements, then will the connection close automatically (due to the error)?

    cursr = connection.cursor()
    cursr.execute(queryStr)

By your syntax, It means only one connection is created. – nehem Dec 16 '15 at 00:22 — nehem, Dec 16 '15 at 00:22

Parfait · Answer 1 · 2015-12-16T14:27:04.363

-1

You will have to wrap the imported module in a defined function and have function return the connection object:

# FILE 1
import pypyodbc

def dbconn():
    try:
        cononnection = pypyodbc.connect('DRIVER={SQL Server};'
                                            'SERVER=........'
                                            'DATABASE=......'
                                            'UID=......;PWD=......')
        return connection 
    except:
        print("I am unable to connect to the SQL SERVER Database")

Then

import AnotherPythonFile as File1   

def query():     
     # EACH WILL USE DIFFERENT INSTANCES OF SAME CONNECTION OBJECT
     conxn1 = File1.dbconn()
     conxn2 = File1.dbconn()

As for second question, no, neither cursor nor database connection is closed automatically when a SQL query execution fails. In fact, you can test this assertion. Purposely, force an error:

cursr.execute("SLEECT * FROM Table;")

Then, see that you can still call cursr.close() and connection.close() in Python's console after raised traceback error.

edited Dec 16 '15 at 14:27

answered Dec 16 '15 at 02:31

Parfait

104,375
17
94
125

No, you are wrong. conxn1 and conxn2 will have two different contentions as a new connection will be created every time the function is called. However, after some fiddling around the issue, I am almost certain that conxn1 and conxn2 will share the same contention in the context shown in my original post. But I need corroboration. – Newbie Dec 16 '15 at 13:23
I re-phrased my code comment. Both will share same connection object but in different instances. As for your original post you will need to use `from AnotherPythonFile import connection` but even then you open a different instance with each call to `connection`. RDMS's (I know MySQL does) tracks number of users no doubt based on timestamped instances for record-level locking and other needs. I cannot see how more than one user, including same client/script can share an open connection. – Parfait Dec 16 '15 at 14:51

pypyodbc - Reusing a Database Connection

1 Answers1