How to keep connection to sql server using python pyodbc?

Question

Python

I'm making a python application ( tkinter , pyodbc ) and I would like to know if it's possible to keep the connection to the sql server in order not to connect to the server many times?

import pandas
import pyodbc
import getpass

data = pandas.read_csv(f'C:\\Users\\{getpass.getuser()}}\\Documents\\InClass\\table.csv', sep=';', encoding='utf-8')
df = pandas.DataFrame(data, columns=['group_id','group_description','group_size'])

connection_string = (
    'Driver={SQL Server};'
    'Server=User;'
    'Database=ServerName;'
    'Trusted_Connection = yes;'
)

conn = pyodbc.connect(connection_string)

cursor = conn.cursor()

for row in df.itertuples():
    cursor.execute('''
                INSERT INTO InClassServer.dbo.groups (group_id, group_description, group_size)
                VALUES (?,?,?)
                ''',
                row.group_id,
                row.group_description,
                row.group_size
                )
conn.commit()

Answer 1

First, you can simply leave the connection open. Or use the same global connection variable, or for convenience, create a class that provides work with the base, like this:

# пример был для sqlite, но принцип одинаков
class DataBase():
    def __init__(self, database_name):
        self.conn = sqlite3.connect(database_name)
        self.cursor = self.conn.cursor()

    def commit(self):
        self.conn.commit()

    def close(self):
        self.conn.close()

# затем создаем один раз объект-соединение и с ним уже работаем
db = DataBase('...')
c = db.cursor
c.execute('CREATE TABLE ...)')
c.execute('INSERT INTO TABLE ...)')
db.commit()
# где нужно используем один и тот же объект-соединение

Secondly, to perform multiple operations (as in this case, insert), instead of execute, there is the executemany command. It just passes a nested list of rows of data, and it will be executed much more efficiently than calling execute again on each row.

Answer 2

Don't make the connection a local variable.