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

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.

asked Jan 2 at 14:30

Excel Help

415

1

Can you add the full traceback to your answer, and also the versions of python and sqlalchemy that you are using

– snakecharmerb
Jan 2 at 18:18

add a comment |

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'

asked Jan 2 at 14:30

Excel Help

415

1

Can you add the full traceback to your answer, and also the versions of python and sqlalchemy that you are using

– snakecharmerb
Jan 2 at 18:18

add a comment |

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'

asked Jan 2 at 14:30

Excel Help

415

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'

python windows sqlalchemy pypyodbc

asked Jan 2 at 14:30

Excel Help

415

asked Jan 2 at 14:30

Excel Help

415

asked Jan 2 at 14:30

Excel Help

415

asked Jan 2 at 14:30

Excel Help

415

asked Jan 2 at 14:30

Excel Help

415

1

Can you add the full traceback to your answer, and also the versions of python and sqlalchemy that you are using

– snakecharmerb
Jan 2 at 18:18

add a comment |

1

Can you add the full traceback to your answer, and also the versions of python and sqlalchemy that you are using

– snakecharmerb
Jan 2 at 18:18

Can you add the full traceback to your answer, and also the versions of python and sqlalchemy that you are using

– snakecharmerb
Jan 2 at 18:18

add a comment |

1 Answer
1

active

oldest

votes

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.

edited Jan 3 at 14:57

answered Jan 3 at 13:34

Gord Thompson

78.8k1497225

I did upgrade SQLAlchemy from 1.1 to 1.2, and the issue is cleared up now. I'm glad to see they addressed it.

– Excel Help
Jan 8 at 17:31

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54008134%2fsqlalchemy-looking-for-server-version-as-string-instead-of-bytes-like-object%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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.

edited Jan 3 at 14:57

answered Jan 3 at 13:34

Gord Thompson

78.8k1497225

I did upgrade SQLAlchemy from 1.1 to 1.2, and the issue is cleared up now. I'm glad to see they addressed it.

– Excel Help
Jan 8 at 17:31

add a comment |

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.

edited Jan 3 at 14:57

answered Jan 3 at 13:34

Gord Thompson

78.8k1497225

I did upgrade SQLAlchemy from 1.1 to 1.2, and the issue is cleared up now. I'm glad to see they addressed it.

– Excel Help
Jan 8 at 17:31

add a comment |

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.

edited Jan 3 at 14:57

answered Jan 3 at 13:34

Gord Thompson

78.8k1497225

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.

edited Jan 3 at 14:57

answered Jan 3 at 13:34

Gord Thompson

78.8k1497225

edited Jan 3 at 14:57

answered Jan 3 at 13:34

Gord Thompson

78.8k1497225

answered Jan 3 at 13:34

Gord Thompson

78.8k1497225

answered Jan 3 at 13:34

Gord Thompson

78.8k1497225

I did upgrade SQLAlchemy from 1.1 to 1.2, and the issue is cleared up now. I'm glad to see they addressed it.

– Excel Help
Jan 8 at 17:31

add a comment |

I did upgrade SQLAlchemy from 1.1 to 1.2, and the issue is cleared up now. I'm glad to see they addressed it.

– Excel Help
Jan 8 at 17:31

I did upgrade SQLAlchemy from 1.1 to 1.2, and the issue is cleared up now. I'm glad to see they addressed it.

– Excel Help
Jan 8 at 17:31

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu