sqlalchemy looking for server version as string, instead of bytes-like object

Question

I don't know what suddenly caused this (I recently reinstalled Anaconda and all my python libraries, but I am back to the same versions as before), but when sqlalchemy tries to connect to the SQL server, it fails because it looks for the server version and tries to run a string operation on it.

The following had no problems prior to my reinstall of packages. I'd connect like so:

sqlalchemy_conn_string = 'mssql+pyodbc://myDSN'
sqlalchemy.create_engine(sqlalchemy_conn_string, module=pypyodbc)

Then it gets all the way to a file called pyodbc.py and fails at this function:

def _get_server_version_info(self, connection):
  try:
    raw = connection.scalar("SELECT SERVERPROPERTY('ProductVersion')")
  except exc.DBAPIError:
    #...
  else:
    version= []
    r = re.compile(r'[.\-]')
    for n in r.split(raw):  # culprit here
      try:
        version.append(int(n))
      except ValueError:
        version.append(n)
    return tuple(version)

Out[1]: TypeError: cannot use a string pattern on a bytes-like object

That's because at this step, raw is not a string that can be split:

# from PyCharm's debugger window
raw = {bytes}b'13.0.5026.0'

At this point, I don't know if I should submit a bug report for sqlalchemy and/or pypyodbc, or if there's something I can do to fix this myself. But I'd like a solution that doesn't involve editing the code for sqlalchemy on my own machine (like handling the bytes-like object specifically), because we have other team members who will also be downloading vanilla sqlalchemy & pypyodbc and won't have the confidence in editing that source code.

Answer 1

I have confirmed the pypyodbc behaviour under Python 3.6.4.

print(pypyodbc.version)  # 1.3.5
sql = """\
SELECT SERVERPROPERTY('ProductVersion')
"""
crsr.execute(sql)
x = crsr.fetchone()[0]
print(repr(x))  # b'12.0.5207.0'

Note that SQLAlchemy's mssql+pyodbc dialect is coded for pyodbc, not pypyodbc, and the two are not guaranteed to be 100% compatible.

The obvious solution would be to use pyodbc instead.

UPDATE:

Check your version of SQLAlchemy. I just looked at the current source code for the mssql+pyodbc dialect and it does

def _get_server_version_info(self, connection):
    try:
        # "Version of the instance of SQL Server, in the form
        # of 'major.minor.build.revision'"
        raw = connection.scalar(
            "SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)")

which should avoid the issue, even when using pypyodbc.

If you are using the latest production release of SQLAlchemy (currently version 1.2.15), then you might have better luck with version 1.3.0b1.