More than once I've come across unicode errors when using a da cursor (see 'ascii' codec can't encode character u'\u201c' ) or some other type of field value manipulation ( Where clause for '\n' ). My latest adventure into these errors is using python to execute a sql query through pyodbc, writing the results to a csv file. The end game to this is to get the data out of the SQL server db and into a fgdb.
Life is all about trade-offs. I prefer to use the python-odbc-to-csv approach as it preserves date-type fields even when the fields have null or no values in them. But, the trade off is you can run into unicode errors like this one:
UnicodeEncodeError<SPAN class="punctuation token">:</SPAN> <SPAN class="string token">'charmap'</SPAN> codec can<SPAN class="string token">'t encode character '</SPAN>\ufffd' <SPAN class="keyword token">in</SPAN> position <SPAN class="number token">226</SPAN><SPAN class="punctuation token">:</SPAN> character maps to <SPAN class="operator token"><</SPAN>undefined<SPAN class="operator token">></SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN></SPAN>
Another tactic is to use Sql Server Management Studio (SSMS) to execute the query and then export the results to a csv; the trade off with this approach is date fields with null or empty values get automagically converted into text fields, rendering useless for date range queries.
Admittedly, I'm not the sharpest knife in the drawer when it comes to codecs and related issues, but I wanted to see if I could figure out a way past the unicode errors, and keep the date fields intact. In my googling I stumbled upon a couple of resources: codecs – String encoding and decoding - Python Module of the Week and Unicode HOWTO — Python 3.8.4rc1 documentation which have helped put a little better edge on my otherwise dull blade.
Here is the code I'm working with:
<SPAN class="keyword token">import</SPAN> pyodbc <SPAN class="keyword token">as</SPAN> odbc
<SPAN class="keyword token">import</SPAN> csv<SPAN class="punctuation token">,</SPAN> os
conn <SPAN class="operator token">=</SPAN> odbc<SPAN class="punctuation token">.</SPAN>connect<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Driver={SQL Server};'</SPAN>
<SPAN class="string token">'Server=xyz;'</SPAN>
<SPAN class="string token">'Database=HANSEN;'</SPAN><SPAN class="punctuation token">)</SPAN>
outFolder <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'C:\Hansen'</SPAN>
query <SPAN class="operator token">=</SPAN> <SPAN class="string token">"""select *
from imsv7.APCASE
"""</SPAN>
cursor <SPAN class="operator token">=</SPAN> conn<SPAN class="punctuation token">.</SPAN>cursor<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
cursor<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN>query<SPAN class="punctuation token">)</SPAN>
rows <SPAN class="operator token">=</SPAN> cursor<SPAN class="punctuation token">.</SPAN>fetchall<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
column_names <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>i<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">.</SPAN>description<SPAN class="punctuation token">]</SPAN>
dotcsv <SPAN class="operator token">=</SPAN> <SPAN class="string token">'APCASE_py.csv'</SPAN>
outFile <SPAN class="operator token">=</SPAN> open<SPAN class="punctuation token">(</SPAN>os<SPAN class="punctuation token">.</SPAN>path<SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>outFolder<SPAN class="punctuation token">,</SPAN>dotcsv<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'w'</SPAN><SPAN class="punctuation token">,</SPAN>newline <SPAN class="operator token">=</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">)</SPAN>
myFile <SPAN class="operator token">=</SPAN> csv<SPAN class="punctuation token">.</SPAN>writer<SPAN class="punctuation token">(</SPAN>outFile<SPAN class="punctuation token">)</SPAN>
myFile<SPAN class="punctuation token">.</SPAN>writerow<SPAN class="punctuation token">(</SPAN>column_names<SPAN class="punctuation token">)</SPAN>
myFile<SPAN class="punctuation token">.</SPAN>writerows<SPAN class="punctuation token">(</SPAN>rows<SPAN class="punctuation token">)</SPAN>
cursor<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>Focus your attention on line 21. By making a slight adjustment I have been able to overcome the error shown above:
outFile <SPAN class="operator token">=</SPAN> open<SPAN class="punctuation token">(</SPAN>os<SPAN class="punctuation token">.</SPAN>path<SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>outFolder<SPAN class="punctuation token">,</SPAN>dotcsv<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'w'</SPAN><SPAN class="punctuation token">,</SPAN>newline <SPAN class="operator token">=</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN>encoding <SPAN class="operator token">=</SPAN> <SPAN class="string token">'utf-16'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN></SPAN>
The addition of encoding = 'utf-16' is apparently all it takes. And, as with life's trade offs, opinions are a dime a dozen and even cheaper now with internet forums: What are reasons to use UTF-16 instead of UTF-8 in some situations? - Quora .
The bottom line for me is I am able to get past the error, maintain my date fields and get the results I'm after. In the future, I'll probably use the encoding = 'utf-16' as my default, to avoid any errors.
