What is the best practice for closing sql connections?

Question

Say, the server has thousands of request form clients for process their data packages and save to database at the very same moment.

The script has to make a few (let's say 6) sql queries for each data package.

For now, script is open a sql connection once, and closing it when finished each data package had been processed. And all queries runs between those opening and closing database connections.

class Processor:
    def __init__(self, data):
        self.data = data

    def open_sql_connection(self):
        "connection commands, strings etc"

    def insert_to_db(self, table, data):
        "inserting data to table of the database"

    def close_sql_connection(self):
        "disconnecting from database"

    def process()
        self.open_sql_connection()
        "begin data process"
        .....
        self.insert_to_db(table_1, some_of_data)
        "continue to data process"
        .....
        self.insert_to_db(table_2, some_of_data)
        "continue to data process"
        .....
        self.insert_to_db(table_3, some_of_data)
        self.close_sql_connection()

Will I gain any benefit if I make the script open sql connection before the query emerged, and close it immediately after finished for each query. Like speeding things up, make tables less busy and avoiding locking, etc.

class Processor:
    def __init__(self, data):
        self.data = data

    def open_sql_connection(self):
        "connection commands, strings etc"

    def insert_to_db(self, table, data):
        "inserting data to table of the database"

    def close_sql_connection(self):
        "disconnecting from database"

    def process()
        "begin data process"
        .....
        self.open_sql_connection()
        self.insert_to_db(table_1, some_of_data)
        self.close_sql_connection()
        "continue to data process"
        .....
        self.open_sql_connection()
        self.insert_to_db(table_2, some_of_data)
        self.close_sql_connection()
        "continue to data process"
        .....
        self.open_sql_connection()
        self.insert_to_db(table_3, some_of_data)
        self.close_sql_connection()

Answer 1

A Connection does not lock the tables. Best practice is to open the connection and close it after you have done all transactions.

One connection for one statement slows down the speed of your application.

https://dba.stackexchange.com/a/16973