Python Forum
Python Desktop Application that will connect o Azure SQL Database
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python Desktop Application that will connect o Azure SQL Database
#1
Edit: Sorry I may have placed this in the wrong forum OU. Please move if needed. Thank you

Hey everyone,

I'm creating a simple desktop application that is going to manage hardware and software inventory at my workplace. So far I have gotten the main GUI interface and the Azure SQL database created.

I'm now testing the connection with the below code:

import pyodbc

azureServer = "pythonserver5874.database.windows.net"
azureDB = "inventoryDatabase"
userName = "lol"
password = "lol"
driver = "{ODBC Driver 17 for SQL Server}"

with pyodbc.connect(
        "DRIVER=" + driver + ";SERVER=" + azureServer + ";PORT=1433;DATABASE=" + azureDB + ";UID=" + userName +
        ";PWD=" + password) as dbConnection:
    with dbConnection.cursor() as sqlCMD:
        sqlCMD.execute("SELECT TOP 3 name, collation_name FROM sys.databases")
        row = sqlCMD.fetchone()
        while row:
            print(str(row[0]) + " = " + str(row[1]))
            row = sqlCMD.fetchone()
The code works but my question is what exactly is the purpose of the strings in the connection setup on lines 10 & 11? Obviously they are needed since if I take them out I receive the below error. The reason I'm confused is if I take out just the port number "1433" the connection and code still works?

Error:
Traceback (most recent call last): File "tc.py", line 9, in <module> with pyodbc.connect(driver + azureServer + azureDB + userName + password) as dbConnection: pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect); [IM002] [Microsoft][ODBC Driver Manager] Invalid connection string attribu te (0)')
Not exactly sure if I understand this error.
Reply
#2
try the following. I couldn't test, so you may have to correct any errors.
import pyodbc


def connect_db(driver, server, database, user, pwd):
    connect_str = f"DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={user};PWD={pwd}"
    with pyodbc.connect(connect_str) as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT TOP 3 name, collation_name FROM sys.databases")
            row = cursor.fetchone()
            while row:
                print (str(row[0]) + " " + str(row[1]))
                row = cursor.fetchone()

def main():
    azureServer = "pythonserver5874.database.windows.net"
    azureDB = "inventoryDatabase"
    userName = "lol"
    password = "lol"
    driver = "{ODBC Driver 17 for SQL Server}"
    connect_db(driver, azureServer, azureDB, userName, password)


if __name__ == '__main__':
    main()
thewolf likes this post
Reply
#3
Hey Larz so my original code works. My question is I don't really understand why we need the strings in the connection setup here unless for some reason we had planned to print this at some point in time? I did try removing them but if I do the connection doesn't work. Is the azure server needing this for the connection string?


with pyodbc.connect(
        "DRIVER=" + driver + ";SERVER=" + azureServer + ";PORT=1433;DATABASE=" + azureDB + ";UID=" + userName +
        ";PWD=" + password)
Reply
#4
I rewrote it. It's just easier for me read like this being new. I'm just going to assume Azure needs the connection string in that format for it to work.

import pyodbc


def databaseConnection(driver, server, database, user, pwd):
    connectionString = f"DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={user};PWD={pwd}"
    conn = pyodbc.connect(connectionString)
    sqlCMD = conn.cursor()

    sqlCMD.execute("SELECT TOP 3 name, collation_name FROM sys.databases")
    row = sqlCMD.fetchone()
    while row:
        print(str(row[0]) + " " + str(row[1]))
        row = sqlCMD.fetchone()


def main():
    azureServer = "pythonserver5874.database.windows.net"
    azureDB = "inventoryDatabase"
    userName = "lol"
    password = "lol"
    driver = "{ODBC Driver 17 for SQL Server}"
    databaseConnection(driver, azureServer, azureDB, userName, password)


if __name__ == '__main__':
    main()
Reply
#5
Ignore my last question. Think I have it under control now. Thanks Larz
Reply
#6
the string that I showed is f-string, the new and proper way to construct strings.
The outcome (if I did it properly) should be the same.

Glad you got it running.
Please post you final script so that others may benefit.
Reply
#7
This is simply just inserting a new row of data in one of my tables. I'm thinking about leaving this(db connection) in it's own py file. My main gui file will call on it for the db connection and functions/sql statements.

import pyodbc


def databaseConnection(driver, server, database, user, pwd):
    connectionString = f"DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={user};PWD={pwd}"
    conn = pyodbc.connect(connectionString)
    sqlCMD = conn.cursor()

    sqlCMD.execute('SELECT * FROM inventoryDatabase.dbo.Hardware')
    sqlCMD.execute('''
                    INSERT INTO inventoryDatabase.dbo.Hardware(serialNumber, modelName, userName, machineActive)
                    VALUES
                    ('B69745', 'L7410', 'BWILSON', 'False')
                    ''')
    conn.commit()


def start():
    azureServer = "pythonserver5874.database.windows.net"
    azureDB = "inventoryDatabase"
    userName = "lol"
    password = "lol"
    driver = "{ODBC Driver 17 for SQL Server}"
    databaseConnection(driver, azureServer, azureDB, userName, password)


if __name__ == '__main__':
    start()
Reply
#8
Hey guys like I said I'm working on a very simple inventory management app for keeping up with hardware and software at work.

I have two py files in my project currently. mainWindow.py and dbConnection.py. mainWindow.py will of course hold my GUI and object coding. dbConnection.py will hold the database connection and sql statement functions. Do I need two files? No but I'm learning and wanted to practice importing and calling methods from my own py file.

Currently I've only gotten so far to where if you click the "Enter New Hardware Button" on the main window app it calls on the enterNewHardware function on the dbConnection.py file. I guess you would call it a method using it this way. Line 35.

So I'm going to have to pull user's input from the textboxes and pass that data to the enterNewHardware function/method. Sure I can figure this out on my own. I will have a few functions/methods on my dbConnection.py file with different SQL statements that my main window is going to call on. Please see code of dbConnection.py. My question is there a better way to centralize my db connection instead of typing the connection setup in every function? Guess turning into it's own function and then using it as a method for the other functions?

Edit. I would need to create the connection outside the functions and make sqlCMD which is conn.cursor() a global variable and then all the functions that will have SQL statements can access it? Maybe?


import dbConnection
import sys
from PyQt5.QtWidgets import (QLabel, QPushButton, QLineEdit, QApplication, QCheckBox, QMainWindow, QWidget,
                             QVBoxLayout, QTabWidget, QStatusBar)


class mainWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        self.resize(385, 323)
        self.setWindowTitle("HARDWARE | SOFTWARE MANAGER")
        self.statusBar = QStatusBar()
        self.setStatusBar(self.statusBar)

        self.tabForm = QTabWidget()
        self.tabForm.addTab(hardwareTab(), "HARDWARE")
        self.tabForm.addTab(softwareTab(), "SOFTWARE")
        self.tabForm.addTab(reportingTab(), "REPORTING")
        self.setCentralWidget(self.tabForm)


class hardwareTab(QWidget):
    def __init__(self):
        super().__init__()
        self.snLabel = QLabel("SERIAL NUMBER")
        self.snTextBox = QLineEdit()
        self.modelLabel = QLabel("MODEL")
        self.modelTextBox = QLineEdit()
        self.userLabel = QLabel("USER")
        self.userTextBox = QLineEdit()
        self.enButton = QPushButton("ENTER NEW HARDWARE")
        self.cfButton = QPushButton("CLEAR FIELDS")
        self.seButton = QPushButton("SEARCH/EDIT HARDWARE")
        self.activeCheckbox = QCheckBox("ACTIVE")
        self.enButton.clicked.connect(dbConnection.enterNewHardware)

        layout = QVBoxLayout(self)
        layout.addWidget(self.snLabel)
        layout.addWidget(self.snTextBox)
        layout.addWidget(self.modelLabel)
        layout.addWidget(self.modelTextBox)
        layout.addWidget(self.userLabel)
        layout.addWidget(self.userTextBox)
        layout.addWidget(self.activeCheckbox)
        layout.addWidget(self.enButton)
        layout.addWidget(self.cfButton)
        layout.addWidget(self.seButton)


class softwareTab(QWidget):
    def __init__(self):
        super().__init__()
        self.snLabel = QLabel("SERIAL NUMBER / KEY")
        self.snTextbox = QLineEdit()
        self.nameLabel = QLabel("APPLICATION NAME")
        self.nameTextBox = QLineEdit()
        self.userLabel = QLabel("USER")
        self.userTextBox = QLineEdit()
        self.enButton = QPushButton("ENTER NEW SOFTWARE")
        self.cfButton = QPushButton("CLEAR FIELDS")
        self.seButton = QPushButton("SEARCH/EDIT SOFTWARE")

        layout = QVBoxLayout(self)
        layout.addWidget(self.snLabel)
        layout.addWidget(self.snTextbox)
        layout.addWidget(self.nameLabel)
        layout.addWidget(self.nameTextBox)
        layout.addWidget(self.userLabel)
        layout.addWidget(self.userTextBox)
        layout.addWidget(self.enButton)
        layout.addWidget(self.cfButton)
        layout.addWidget(self.seButton)


class reportingTab(QWidget):
    def __init__(self):
        super().__init__()


if __name__ == "__main__":
    APP = QApplication(sys.argv)
    WINDOW = mainWindow()
    WINDOW.show()
    sys.exit(APP.exec_())
import pyodbc


def enterNewHardware():
    azureServer = "pythonserver5874.database.windows.net"
    azureDB = "inventoryDatabase"
    userName = "lol"
    password = "lol"
    driver = "{ODBC Driver 17 for SQL Server}"

    connectionString = f"DRIVER={driver};SERVER={azureServer};PORT=1433;DATABASE={azureDB};UID={userName};PWD={password}"
    conn = pyodbc.connect(connectionString)
    sqlCMD = conn.cursor()

    sqlCMD.execute('SELECT * FROM inventoryDatabase.dbo.Hardware')
    sqlCMD.execute('''
                    INSERT INTO inventoryDatabase.dbo.Hardware(serialNumber, modelName, userName, machineActive)
                    VALUES
                    ('D69745', 'L7410', 'BBARKER', 'False')
                    ''')
    conn.commit()
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Can I develop a live video streaming application using python? mtrkhan 3 4,378 Nov-15-2021, 10:42 PM
Last Post: Larz60+
  Show HTML in Python application and handle click SamHobbs 2 2,663 Sep-28-2021, 06:27 PM
Last Post: SamHobbs
  Flask web app on Azure help dangermaus33 2 2,610 Aug-10-2021, 12:04 PM
Last Post: kashcode
  Azure Function App Configuration Settings jdb1234 1 2,083 Oct-16-2020, 10:54 AM
Last Post: ndc85430
  Can not point to Selenium Webdriver path for Python Jupyter Notebook on Azure dadadance 4 10,016 Jul-31-2019, 10:00 PM
Last Post: perfringo
  Can i use selenium to automate electron based desktop application UI dharmendraradadiya 0 2,876 Jul-22-2019, 01:20 PM
Last Post: dharmendraradadiya
  Excel Like Web Application using Python ravsha85 1 2,281 Jun-13-2019, 06:00 PM
Last Post: noisefloor
  Application like simpler facebook wall in python framework seidman 1 3,274 Mar-11-2018, 05:07 PM
Last Post: wavic
  General advice web application - flask - database - cms bashage 6 4,610 Jan-31-2018, 06:07 PM
Last Post: frostbite

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020