Psycopg2 connection and cursor in a class are closed? And should I close them?

2.8k views Asked by At

I just have some question how to understand cursors and connections inside of a class. Everything is working fine but I dont understand how python handle the connections and the cursors...

I have a class to interact with my database. In the insert methode I also put a "check_connection" method that simply print the cursor and connection objects.

However I close them after using the insert methode. When I call the "check_connection" object after that my class object gives back objects at the same place. When I call the insert methode again new objects are created, i guess?

Insert method:

def insert_new_join(self, user, invitedby):
        try:
            timest = dt.now().strftime('%Y-%m-%d %H:%M:%S')
            query = """ INSERT INTO user_invites (username, invitedby, dates) VALUES (%s,%s,%s) ON CONFLICT DO NOTHING"""
            values = (user, invitedby, timest,)

            self.connect()
            self.cursor.execute(query,values)
            self.con.commit()
            self.check_connection()
            return True

        except (Exception, psy.Error) as e :
            with open("errorlog.txt",'a') as f:
                f.write("db insert_new_join: "+dt.now().strftime('%Y-%m-%d %H:%M:%S')+" "+str(e)+"\n")
            return False
        finally:
           self.disconnect()

Disconnect method:

def disconnect(self):
        if(self.cursor):
            self.cursor.close()
            print("PostgreSQL cursor is closed")
        if(self.con):
            self.con.close()
            print("PostgreSQL connection is closed")

Mytest file:

testdb = dbs.user_db(*getc.get_database_login())
testdb.insert_new_join("test","test")
testdb.check_connection()
print("\nnew insert - methode call\n")
testdb.insert_new_join("test","test")
testdb.check_connection()

Thats my output (the 'xxx' are my database connection and normaly filled):

<connection object at 0x000001B442496D00; dsn: 'xxx', closed: 0>
<cursor object at 0x000001B442223900; closed: 0>
PostgreSQL cursor is closed
PostgreSQL connection is closed
<connection object at 0x000001B442496D00; dsn: 'xxx', closed: 1>
<cursor object at 0x000001B442223900; closed: -1>

new insert - methode call

<connection object at 0x000001B44253F040; dsn: 'xxx', closed: 0>
<cursor object at 0x000001B442223740; closed: 0>
PostgreSQL cursor is closed
PostgreSQL connection is closed
<connection object at 0x000001B44253F040; dsn: 'xxx', closed: 1>
<cursor object at 0x000001B442223740; closed: -1>

*self.connect() starts the connection with: psycopg2.connect() and check_connection() just print the connection and cursor object, the results you see in the output.

So my questions:

  1. Why this calls make new objects? - or did they, I mean there new memmory addresses
  2. Is that the right way to close the connection and cursors?
  3. This calls will be in await function is there a possibility to let the connections open and check if they still work? - would do it like the closing with an if and an exception if the connection still open but doesnt work to create a new one, or is there the possibilty to not only check if its still open also it works ... Or Im complitly wrong and that mean if its open it works?
  4. Why there are diffrent return values of close for the cursor and the connection?
  5. Is there a new connection/cursor object created or why is it a new memory address? Is that right way to do (question 3)?

Thank you guys... mostly I just want to understand, because it works but I try to learn to write clean code :D

0

There are 0 answers