What happen if we use PostgreSQL multiple connection without closing it?

Question

connection.py File

def create_connection():
    connection = psycopg2.connect("dbname=suppliers user=postgres password=postgres")
    return connection

def create_cursor():
    connection = create_connection()
    cursor = connection.cursor()
    return cursor

Above example will create a connection 2 times when calling both create_connection() and create_cursor() method

Query File

def common_query():
    sql_query = "INSERT INTO supply VALUES (1, 'test', 'test')"
    conn = create_connection()
    cursor = create_cursor()
    with conn:
         with cursor as cur:
            cur.execute(sql_query)
   conn.close()

Above example will call create_connection and create_cursor method, but as you can see while calling create_connection , connection has already been established and in create_cursor() while calling create_connection() method again it create another connection.

So while execution query it does't show any error nor it insert my data into database. Let me know whats happened in it ?

Answer 1

You create two connections for each call to common_query. One is explicitly closed, the other is closed at some point because it went out of scope. (Python is a garbage collected language)

You don't commit on either one, so whatever work you did gets rolled back automatically. This is unrelated to the first point. The same thing would happen if you had only created one connection (and also didn't commit on it).