Ye olde UnicodeEncodeError printing query results to MS SQL with adodbapi
Python newbie here.
I am using python2.7.2 on Windows7.
I have installed PyWin32 extensions (build 217).
I have usdbapi installed in c:\Python27\Lib\site-packages\adodbapi
I have a very simple module that queries the AdventureWorks2008LT database in MS SQL Server.
import adodbapi
connStr='Provider=SQLOLEDB.1;' \
'Integrated Security=SSPI;' \
'Persist Security Info=False;' \
'Initial Catalog=AVWKS2008LT;' \
'Data Source=.\\SQLEXPRESS'
conn = adodbapi.connect(connStr)
tablename = "[salesLT].[Customer]"
# create a cursor
cur = conn.cursor()
# extract all the data
sql = "select * from %s" % tablename
cur.execute(sql)
# show the result
result = cur.fetchall()
for item in result:
print item
# close the cursor and connection
cur.close()
conn.close()
The AdventureWorks2008LT sample database has tables for customers, products, addresses, and orders (and so on). Some of the string data in these tables is unicode.
The query works for the first lines of the pair. I see the expected result. But then the script fails with this message:
Traceback (most recent call last):
File "C:\dev\python\query-1.py", line 24, in <module>
print item
File "C:\Python27\lib\site-packages\adodbapi\adodbapi.py", line 651, in __str__
return str(tuple([str(self._getValue(i)) for i in range(len(self.rows.converters))]))
UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 19: ordinal not in range(128)
... which doesn't help much. To me.
I understand that adodbapi is trying to encode the u '\ xe9' character in ASCII. I understand why this will fail. I'm assuming he's trying to do this as part of the instruction print
.
Why is it trying to encode the character in ASCII?
How can I tell that it is just using UTF-8?
ps: I am running the script from the cmd.exe prompt on Windows. Does this mean stdout is always ASCII?
eg, \python27\python.exe -c "import sys; print(sys.stdout.encoding)"
gives me "cp437"
source to share
I was able to run a script to print all the extracted lines, changing the output to do this:
# show the result
result = cur.fetchall()
for item in result:
print repr(item)
instead of this:
# show the result
result = cur.fetchall()
for item in result:
print item
So the problem is the use str
in adodbapi as Borealid said in a comment. But this is not necessarily a blocking problem. Usually, when you fetch strings from a database query, people don't just want the string representation of the string; they want to get the values in separate columns. I came to the conclusion that this problem is kind of a man-made problem due to the way I was building the test application.
source to share