CODEWITHSUNDEEP
pythonsqlazureazure-sql-database
mk1337
mk1337

Reputation: 105

Connection to a Azure SQL DB in many modules needed - how to?

I have written a Python Tool with an wxPython GUI which has mainly the task to get a lot of user input regarding Customer Data, Product Data and so on and save it to a SQL Database, at the moment locally with a SQLite3 Database for testing an now switching to MS Azure to have anybody work in the same Database.

As i now plan to use a MS Azure SQL DB i have a few questions an i am hoping this is the right place to ask:

  1. What is the best library to connect to Azure via Python? I found pyodbc and pymssql but i think both need to have an extra driver installed? Is this true and is this a problem in real usecases?
  2. I have many modules, like Manage_Customer.py and Manage_Factory.py and so on. In all of them I connect to my Database. I have no module which is like a SQL Master which handels some overhead.

So my code looks like this most of the time: 

import wx
import sqlite3

SQL_PATH = "Database_Test.db"

class ManageCustomerToDB(wx.Dialog):

    def __init__(self, *args, **kw):
        super(ManageCustomerToDB, self).__init__(*args, **kw)

    def InitUI(self):
    #[GUI an so on...]

        # I do this on time inside a module:
        conn = sqlite3.connect(SQL_PATH)
        self.c = conn.cursor()  

        # Use functions like the ones below...


    def GetCustomerData(self):
        self.c.execute("SELECT * FROM Customer WHERE CustomerID = ?", (self.tc_customer_id.GetValue(),)) 
        customer_data = self.c.fetchall()

        # Do something with Customer Data

    def GetPersonData(self):
        self.c.execute("SELECT * FROM Person WHERE PersonID = ?", (self.tc_person_id.GetValue(),)) 
        person_data = self.c.fetchall()

        # Do something with Person Data

I hope this example shows what i do. Are there any bigger mistakes i do?

  1. After a read in SQL I dont have to close the DB in any way?

Thanks for your help and let me know if i can improve my question or give more details.

Upvotes: 1

Views: 828

Answers (1)

Jack Jia
Jack Jia

Reputation: 5549

It is not a good idea to create a new connection to Azure SQL every time you CRUD. This is a waste of resources, and when the number of accesses reaches a certain number, it will have a large impact on the performance of mssql.

I suggest you use database connection pool. The pool manager will initial several connections to SQL Server instance, and then reuse these connections when requested.

There is an existing package which you can take advantage of. It is DBUtils. You can use the PoolDB from it with pyodbc together.

A sample for showing how database connection pool works:

import pyodbc
from DBUtils.PooledDB import PooledDB


class Database:
    def __init__(self, server, driver, port, database, username, password):
        self.server = server
        self.driver = driver
        self.port = port
        self.database = database
        self.username = username
        self.password = password
        self._CreatePool()

    def _CreatePool(self):
        self.Pool = PooledDB(creator=pyodbc, mincached=2, maxcached=5, maxshared=3, maxconnections=6, blocking=True, DRIVER=self.driver, SERVER=self.server, PORT=self.port, DATABASE=self.database, UID=self.username, PWD=self.password)

    def _Getconnect(self):
        self.conn = self.Pool.connection()
        cur = self.conn.cursor()
        if not cur:
            raise "connection error"
        else:
            return cur
    # query sql

    def ExecQuery(self, sql):
        cur = self._Getconnect()
        cur.execute(sql)
        relist = cur.fetchall()
        cur.close()
        self.conn.close()
        return relist
    # non-query sql

    def ExecNoQuery(self, sql):
        cur = self._Getconnect()
        cur.execute(sql)
        self.conn.commit()
        cur.close()
        self.conn.close()


def main():

    server = 'jackdemo.database.windows.net'
    database = 'jackdemo'
    username = 'jack'
    port=1433
    password = '*********'
    driver= '{ODBC Driver 17 for SQL Server}'
    ms = Database(server=server, driver=driver, port=port, database=database, username=username, password=password)

    resList = ms.ExecQuery("select * from Users")

    print(resList)


if __name__ == '__main__':

    main()

Answers to your questions:

Q1: What is the best library to connect to Azure via Python? I found pyodbc and pymssql but i think both need to have an extra driver installed? Is this true and is this a problem in real usecases?

Answer: Both of then would be OK. ODBC stands for Open Database Connectivity, so it could be used to connect many databases. I see the Microsoft tutorial uses pyodbc, so maybe it is a better choice.

Q2: I have many modules, like Manage_Customer.py and Manage_Factory.py and so on. In all of them I connect to my Database. I have no module which is like a SQL Master which handels some overhead.

Answer: Use database connection pool.

Q3: After a read in SQL I dont have to close the DB in any way?

Answer: If you use database connection pool, the connection will be put back too pool after you call close() method.

Upvotes: 1

Related Questions