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


Possibly Related Threads…
Thread Author Replies Views Last Post
  Azure Function App Configuration Settings jdb1234 1 300 Oct-16-2020, 10:54 AM
Last Post: ndc85430
  Can not point to Selenium Webdriver path for Python Jupyter Notebook on Azure dadadance 4 5,414 Jul-31-2019, 10:00 PM
Last Post: perfringo
  Can i use selenium to automate electron based desktop application UI dharmendraradadiya 0 1,024 Jul-22-2019, 01:20 PM
Last Post: dharmendraradadiya
  Excel Like Web Application using Python ravsha85 1 793 Jun-13-2019, 06:00 PM
Last Post: noisefloor
  General advice web application - flask - database - cms bashage 6 2,488 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