Cannot Connect to SQL Instance IF Server Name is from input()

Question

I have been playing around with pyodbc and for some reason when trying to connect if I get the Server property from a input() it cannot find the server, but if I take the same server name and declare it as a variable before hand it works fine with the exact same code. Does anyone know what is going on here?

Code with input()

import pyodbc 

driver = '{ODBC Driver 17 for SQL Server}'
instance = input("Please Enter your SQL Instance: ")
connectionstring = f'Driver={driver}; Server={instance}; Trusted_Connection=yes'
conn = pyodbc.connect(connectionstring)
cursor = conn.cursor()
cursor.execute('SELECT name FROM sys.databases')

for row in cursor:
    print(row)

Output Code output server name from Input()

Code with Variable

import pyodbc 

driver = '{ODBC Driver 17 for SQL Server}'
instance = 'DESKTOP-J7PBL8S\\NORTHWIND'
connectionstring = f'Driver={driver}; Server={instance}; Trusted_Connection=yes'
conn = pyodbc.connect(connectionstring)
cursor = conn.cursor()
cursor.execute('SELECT name FROM sys.databases')

for row in cursor:
    print(row)

Output Code output server name from variable

I have tried using str() on the input with no luck. Not really sure why it doesn't like when I get the server name from an input because it returns a string and the connection string is exactly the same as the code that works.

I am kind of curious why this is the case not really looking to use input() on any real project

You don't need to double-up the backslash when typing the instance name at the input prompt. — Gord Thompson
– Gord Thompson, Commented Mar 5, 2020 at 17:45

shayan rok rok · Accepted Answer · 2020-03-05 17:52:00Z

2

I tried:

ins1 = input("Please Enter your SQL Instance: ")
ins2 = 'DESKTOP-J7PBL8S\\NORTHWIND'

print(ins1)
print(ins2)

Then pass the DESKTOP-J7PBL8S\\NORTHWIND as input and the output was:

DESKTOP-J7PBL8S\\NORTHWIND
DESKTOP-J7PBL8S\NORTHWIND

So I think we found the problem. The input() returns the raw string without considering \ special meaning.

So if you use just one \-> DESKTOP-J7PBL8S\NORTHWIND, you should be able to connect to the intended server.

answered Mar 5, 2020 at 17:52

shayan rok rok

5504 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot Connect to SQL Instance IF Server Name is from input()

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related