pyodbc ? variable and unicode characters - potential bug

This is the place for queries that don't fit in any of the other categories.

pyodbc ? variable and unicode characters - potential bug

Postby TheFunk » Mon Sep 30, 2013 2:17 pm

Howdy!

I posted this in the pyodbc Google group because I figured it was an issue with my syntax, but as I was reading, I came across a user with a similar problem, and now I'm not entirely sure this is a syntax problem. I haven't heard back yet from the pyodbc group, could anyone here verify if this is my error or if there really is something wrong with how pyodbc handles Unicode? If it turns out that it's not my syntax, I'll report it as an issue on the site.

Original post:

I have been using pyodbc to connect to MS SQL Server 2008 R2. The connection was working well when my domain account and password were hardcoded in my program, however in order to be more secure and allow for other users to make connections, I altered my connection string's parameters to the ? variable, as shown on the Getting Started page/in the documentation. My program hated this, and now I'm getting error 28000. The exact code of my connection string is:

Code: Select all
    def conndb(self, server, database, usernm, passwd):
            self.cnxn = pyodbc.connect(r'DRIVER={SQL Server Native Client 10.0};SERVER=?;DATABASE=?;Trusted_Connection=yes;User ID=?;PWD=?', var1=server, var2=database, var3=usernm, var4=passwd)



The conndb function is called by a lambda connected to a buttonbox in PyQT.
The exact error is:

Code: Select all
    Traceback (most recent call last):
      File "C:\Users\isnyder\Documents\SQL Server Metrics\dbwindow.py", line 69, in <lambda>
        QtCore.QObject.connect(self.buttonBox, QtCore.SIGNAL(_fromUtf8("accepted()")),lambda: connector.conndb(str(self.comboBox.currentText()), str(self.lineEdit_2.text()), str(self.lineEdit_3.text()), str(self.lineEdit_4.text())))
      File "C:\Python33\lib\misc\metrics.py", line 15, in conndb
        self.cnxn = pyodbc.connect(r'DRIVER={SQL Server Native Client 10.0};SERVER=?;DATABASE=?;Trusted_Connection=yes;User ID=?;PWD=?', var1=server, var2=database, var3=usernm, var4=passwd)
    pyodbc.Error: ('28000', "[28000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'MYDOMAIN\\myuser'. (18456) (SQLDriverConnect)")


Does anyone know what might be causing this?
TheFunk
 
Posts: 27
Joined: Fri Aug 30, 2013 5:46 pm

Re: pyodbc ? variable and unicode characters - potential bug

Postby stranac » Mon Sep 30, 2013 3:48 pm

I think the last bit of that line shouldn't be:
Code: Select all
var1=server, var2=database, var3=usernm, var4=passwd
only:
Code: Select all
server, database, usernm, passwd


I have no experience with pyodbcm so I could be wrong...
Friendship is magic!

R.I.P. Tracy M. You will be missed.
User avatar
stranac
 
Posts: 1144
Joined: Thu Feb 07, 2013 3:42 pm

Re: pyodbc ? variable and unicode characters - potential bug

Postby Yoriz » Mon Sep 30, 2013 3:55 pm

This shows what/how to pass to the connect method
pyobdc - connect
New Users, Read This
Join the #python-forum IRC channel on irc.freenode.net!
Spam topic disapproval technician
Windows7, Python 2.7.4., WxPython 2.9.5.0., some Python 3.3
User avatar
Yoriz
 
Posts: 842
Joined: Fri Feb 08, 2013 1:35 am
Location: UK

Re: pyodbc ? variable and unicode characters - potential bug

Postby TheFunk » Mon Sep 30, 2013 6:01 pm

stranac, both ways work, the difference is with the extra variables and equals sign I'm passing keyword arguments instead of positional arguments, which in my case is what I want. I'm almost positive the error has something to do with the format of the data coming from the GUI, because it's all Unicode encoded. I've used the ? in queries before and always had perfect results. I just double checked that the variables being passed were correct syntactically.

I also converted my results from unicode to ascii, and that's when I noticed something interesting. After the conversion the variable usernm which had originally held the value u'MYDOMAIN\myname' had changed to b'MYDOMAIN\\myname'. Note the double backslash. I think this could have something to do with why I don't get a connection. I think pyodbc might try to convert my text to ascii, and thus screw up my connection string. The SQL Server error (18456) that I receive when trying to connect to the DB could indicate a bad username. The state isn't included in the error message, so I can't be sure.

Aaaaaand...(processing...) yep, my username after being passed to pyodbc now has two backslashes instead of 1. That must be what it's doing. I'll convert to ascii before passing the data, remove the extra backslash and see if that solves the issue. If it does, we'll know:

1 - Not to pass unicode characters to pyodbc
2 - free round of drinks on me if you made it this far, I know I'm wordy
TheFunk
 
Posts: 27
Joined: Fri Aug 30, 2013 5:46 pm

Re: pyodbc ? variable and unicode characters - potential bug

Postby DrSockMonkee » Mon Sep 15, 2014 4:22 pm

I think I am dealing with a similar issue. I have a connection which works going to a copy of the AdventureWorks DB and brings back a selection of one of my tables. I want to parse/format the results to create a collection of objects which I can pass to my JavaScript Web mapping application. It runs but... does not give the information I want.

Code: Select all
cursor.execute('''select PERSON.Address_Posits.Lat,
              PERSON.Address_Posits.Long,
              PERSON.Address_Posits.City
               from PERSON.Address_Posits''')
valuesColl = []
for row in cursor:
    rowObject = {}  #creating a dictionary to hold pair values
    rowObject['Lat'] = row.Lat
    rowObject['Long'] = row.Long
    rowObject['City'] = row.City

print valuesColl


Sample of the data returned... the 'City': u'Bothell value is a prob, how do I make sure I do not get the "u" (unicode?) designator?

{'Lat': 47.7869921906598, 'City': u'Bothell', 'Long': -122.164644615406, 'AddressLine1': u'1970 Napa Ct.'}
Last edited by stranac on Mon Sep 22, 2014 2:04 pm, edited 1 time in total.
Reason: First post lock.
DrSockMonkee
 
Posts: 1
Joined: Thu Sep 11, 2014 5:18 pm


Return to General Coding Help

Who is online

Users browsing this forum: Google [Bot] and 4 guests