Search code examples
pythonodbcwxpythonpypyodbc

pypyodbc connects with explicit string but not variable

I'm reading a connection string for an SQL Server database from the user, represented by randomstring here. When I print randomstring to the console it looks perfectly formed, and I've been over it a dozen times to check. However, the connection fails with

pypyodbc.Error: ('IM002', '[IM002] [Microsoft]

If I explicitly pass the exact same string to the cursor, the connection works fine. Do I need to do anything specific with the string variable to be able to use it ? 

    randomstring = ShowConnString(self.cframe).connstringtext
    print(randomstring)
    self.conn = pypyodbc.connect(randomstring)
    #self.conn = pypyodbc.connect(driver='{SQL Server}', server='(local)', database='Audit', uid='sa', pwd='password')

Edit: print (randomstring) outputs:

pypyodbc.connect(driver='{SQL Server}', server='(local)', database='Audit', uid='sa', pwd='password')

Solution

  • The first thing to note is that your second (commented out) example is not connecting with an "explicit [connection] string", it is using a series of keyword arguments ("kwargs") to specify driver, server, etc.. Note the subtle difference between the keyword arguments form ...

    self.conn = pypyodbc.connect(driver='{SQL Server}', server='(local)', database='Audit', uid='sa', pwd='password')

    ... and the connection string form:

    self.conn = pypyodbc.connect('driver={SQL Server};server=(local);database=Audit;uid=sa;pwd=password')

    Now if your randomstring variable really does contain

    pypyodbc.connect(driver='{SQL Server}', server='(local)', database='Audit', uid='sa', pwd='password')

    then it is definitely not a valid ODBC connection string. Even if it just contained

    driver='{SQL Server}', server='(local)', database='Audit', uid='sa', pwd='password'

    that would still not be a valid connection string because the items are separated by commas (not semicolons) and there are quotes around the values. You will need to transform the contents of your randomstring value into a proper ODBC connection string before you pass it to the .connect method.