CODEWITHSUNDEEP
pythonsql-serverpython-3.xazurepymssql
Rohit Karthik
Rohit Karthik

Reputation: 61

Using Python to access SQL Server SQL Database on Azure Gives an Error

I am trying to use the pymssql module in Python to access a SQL Server SQL Database. However, my code gives a traceback error when I run my code on Ubuntu Linux. BTW, the system is actually a Microsoft Azure VM.

Below is my code:

import pymssql

connection1 = pymssql.connect(
                server = 'redworth-test-sql.database.windows.net',
                user = '*********@redworth-test-sql',
                password = '**********',
                database = 'redworth-test-sql-database'
)

sql = connection1.cursor()
cursor.execute('insert into table1 values (\'Rohit\', \'Karthik\', \'2006/08/02\')')

allRows = sql.fetchall()

print(allRows)

connection1.close()

(I am using a * for my username & password for security reasons, but my code on my computer has the actual strings)

(I have also installed my pymssql module on python3 using pip as well)

But running that code gives me the following error on my system:

Traceback (most recent call last):
  File "src/pymssql.pyx", line 636, in pymssql.connect
  File "src/_mssql.pyx", line 1957, in _mssql.connect
  File "src/_mssql.pyx", line 676, in _mssql.MSSQLConnection.__init__
  File "src/_mssql.pyx", line 1683, in _mssql.maybe_raise_MSSQLDatabaseException
_mssql.MSSQLDatabaseException: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (redworth-test-sql.database.windows.net:1433)\n')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "pyMsSQL.py", line 7, in <module>
    database = 'redworth-test-sql-database'
  File "src/pymssql.pyx", line 642, in pymssql.connect
pymssql.OperationalError: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (redworth-test-sql.database.windows.net:1433)\n')

I am not sure what is causing this error.

Thank you in advance for your help.

Upvotes: 0

Views: 867

Answers (1)

Leon Yue
Leon Yue

Reputation: 16401

Congratulations that pyodbc works for you.

Azure document all suggest us using pyodbc to connect to the Azure SQL database, bellow is the example:

import pyodbc
server = '<server>.database.windows.net'
database = '<database>'
username = '<username>'
password = '<password>'
driver= '{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM [SalesLT].[ProductCategory] pc JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

Ref: Create(Python) code to query your database.

Hope this helps.

Upvotes: 1

Related Questions