Python pyodbc: encoding of the connection string (Windows)

Question

I have a problem connecting to a local Access file that has accented characters in both the path and file name. I am new to Python, so this is what I have managed so far:

# coding=utf-8
from __future__ import absolute_import, division, print_function, unicode_literals
import os
import pyodbc
scriptDir = os.path.dirname(os.path.realpath(__file__)).decode('mbcs')
dbRelPath = "MøreCase_v2.accdb"
dbAbsPath = scriptDir + '\\' + dbRelPath
print (os.path.exists(dbAbsPath))
dbConStr = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + dbAbsPath
print (dbConStr)
cnxn = pyodbc.connect(dbConStr)

The first print returns 'True', the other one prints the full file name, but the connection fails with the following error:

Exception UnicodeEncodeError: UnicodeEncodeError('ascii', u'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\xxx\case\M\xf8re\M\xf8reCase_v2.accdb', 121, 122, 'ordinal not in range(128)') in ignored

I tried to decode the connection string back to the system encoding

cnxn = pyodbc.connect(dbConStr.encode('mbcs'))

but then I get the following error instead:

Traceback (most recent call last): File "mwe.py", line 11, in cnxn = pyodbc.connect(dbConStr.encode('mbcs')) UnicodeDecodeError: 'ascii' codec can't decode byte 0xf8 in position 121: ordina l not in range(128)

I tried also 'cp1252' and 'utf-8', but it gives the same error (only the character code is different in utf-8).

More info: the script file is saved as utf-8. I am on 64-bit English Windows 7 with Norwegian 'locales'.

Thanks in advance.

Answer 1

Update - July 2019

This is no longer an issue with current versions of pyodbc. The following code works fine:

# -*- coding: utf-8 -*-
import os
import pyodbc
import sys

print(sys.version)
# 2.7.16 (v2.7.16:413a49145e, Mar  4 2019, 01:30:55) [MSC v.1500 32 bit (Intel)]
print(pyodbc.version)
# 4.0.26

script_dir = os.path.dirname(os.path.realpath(__file__))
db_relative_path = u"MøreCase_v2.accdb"
db_absolute_path = script_dir + '\\' + db_relative_path
print(repr(db_absolute_path))
# u'C:\\Users\\gord\\PycharmProjects\\py2pyodbc_test\\M\xf8reCase_v2.accdb'
print('File exists? ' + str(os.path.exists(db_absolute_path)))
# File exists? True

connection_string = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + db_absolute_path
cnxn = pyodbc.connect(connection_string)
print('Connection established.')
# Connection established.

---

(previous answer - November 2013)

I was unable to get your test case to work using pyodbc. I could get the connection string assembled correctly, but when I tried to connect I got the error message

cnxn = pyodbc.connect(dbConStr)

UnicodeDecodeError: 'ascii' codec can't decode byte 0xf8 in position 69: ordinal not in range(128)

It appears that pyodbc tries to convert the connection string to 'ascii', so any characters above 0x7F are forbudt.

However, I was able to get it to work using pypyodbc:

# -*- coding: cp1252 -*-
import os
import pypyodbc
scriptDir = os.path.dirname(os.path.realpath(__file__))
print scriptDir
dbRelPath = "MøreCase_v2.accdb"
print dbRelPath
dbAbsPath = scriptDir + '\\' + dbRelPath
print (os.path.exists(dbAbsPath))
dbConStr = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + dbAbsPath
print (dbConStr)
cnxn = pypyodbc.connect(dbConStr)
print 'Connection established.'

Output:

C:\Users\Gord>\Python27\python.exe c:\__tmp\test\foo.py
c:\__tmp\test
M°reCase_v2.accdb
True
DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=c:\__tmp\test\M°reCase_v2.accdb
Connection established.