pymssql dialect doesn't encode binary params correctly in Python 3

The module.Binary function is set to str but when given a bytes object in Python 3, this will emit a string with a b in it and double backslashes.

Take the following program:

from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import Table, Column
from sqlalchemy import Integer, String, PickleType
from sqlalchemy.dialects import mssql

engine = create_engine(
    'mssql+pymssql://...?charset=UTF-8',
    echo=False)
user_table = Table(
    'user', MetaData(),
    Column('id', Integer, primary_key=True),
    Column('data', PickleType()))
user_table.drop(engine, checkfirst=True)
user_table.create(engine)

insert_stmt = user_table.insert().values(id=1, data=[Jones']('Ed))

engine.echo = True
engine.execute(insert_stmt)

The output of running this in Python 3 is:

marca@marca-mac:~/dev/hg-repos/pymssql$ .tox/py33/bin/python my_sqlalchemy_bug.py
2013-03-18 13:11:30,824 INFO sqlalchemy.engine.base.Engine SET IDENTITY_INSERT [user](user) ON
2013-03-18 13:11:30,824 INFO sqlalchemy.engine.base.Engine ()
2013-03-18 13:11:30,825 INFO sqlalchemy.engine.base.Engine INSERT INTO [user](user) (id, data) VALUES (%(id)s, %(data)s)
2013-03-18 13:11:30,825 INFO sqlalchemy.engine.base.Engine {'id': 1, 'data': "b'\\x80\\x03]q\\x00X\\x08\\x00\\x00\\x00Ed Jonesq\\x01a.'"}
2013-03-18 13:11:30,827 INFO sqlalchemy.engine.base.Engine ROLLBACK

Note that data == "b'\\x80\\x03]q\\x00X\\x08\\x00\\x00\\x00Ed Jonesq\\x01a.'". I think that it should be b'\x80\x03]q\x00X\x08\x00\x00\x00Ed Jonesq\x01a.'.

I plan to submit a pull request to fix this momentarily...

Comments (4)