bytes/unicode type error when creating tables on SQL Server using pyodbc

[sqlalchemy-bot]

Migrated issue, originally created by Brad Kittenbrink (@brad)

WinPython distribution, Python 3.3, 64-bit, Windows 7 64-bit, SQLAlchemy 0.8, SQL Server 2012

When trying to create a table on an existing SQL Server database, the do_execute method in engine\default.py receives a bytes object in the statement argument rather than a str/unicode object.

Patching the method like this allows the table creation to work:

    def do_execute(self, cursor, statement, parameters, context=None):
        #begin addition
        if isinstance(statement,bytes): statement = statement.decode('utf-8')
        #end addition
        cursor.execute(statement, parameters)

Code to reproduce error (requires and existing SQL Server database):

# -*- coding: utf-8 -*-
from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Table1(Base):

    __tablename__ = 'tblTable1'
    MyID   = Column(Integer     , primary_key = True, autoincrement = True, nullable = False)
    Name   = Column(String(255) , nullable = False     , server_default = "", default="")
       
url = 'mssql+pyodbc://{server}/{db}'.format(server='servermachine\\servername',db='testdb')
eng = create_engine(url)    
Base.metadata.create_all(eng)

---

Text of error message:

Traceback (most recent call last):
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 757, in _do_get
    return self._pool.get(wait, self._timeout)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\util\queue.py", line 166, in get
    raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "D:\src\bug_reports\sqlalchemy_bug.py", line 24, in <module>
    Base.metadata.create_all(eng)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\schema.py", line 2787, in create_all
    tables=tables)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 1488, in _run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\contextlib.py", line 48, in __enter__
    return next(self.gen)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 1481, in _optional_conn_ctx_manager
    with self.contextual_connect() as conn:
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 1671, in contextual_connect
    self.pool.connect(),
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 272, in connect
    return _ConnectionFairy(self).checkout()
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 425, in __init__
    rec = self._connection_record = pool._do_get()
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 777, in _do_get
    con = self._create_connection()
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 225, in _create_connection
    return _ConnectionRecord(self)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 322, in __init__
    exec_once(self.connection, self)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\event.py", line 390, in exec_once
    self(*args, **kw)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\event.py", line 407, in __call__
    fn(*args, **kw)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\strategies.py", line 168, in first_connect
    dialect.initialize(c)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\connectors\pyodbc.py", line 138, in initialize
    super(PyODBCConnector, self).initialize(connection)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1125, in initialize
    super(MSDialect, self).initialize(connection)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\default.py", line 172, in initialize
    self._get_default_schema_name(connection)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1141, in _get_default_schema_name
    user_name = connection.scalar("SELECT user_name()")
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 597, in scalar
    return self.execute(object, *multiparams, **params).scalar()
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 664, in execute
    params)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 808, in _execute_text
    statement, parameters
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 871, in _execute_context
    context)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
TypeError: The first argument to execute must be a string or unicode query.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

pyodbc + python3 is a TODO; this is #2355. there's several areas that bytes/str need to be correctly established (but all within mssql/, the core of SQLAlchemy including default.py supports py3k fully. a "bytes" would never be present here because the supports_unicode_statements dialect flag should always be True in a Py3k scenario, not set up yet in connectors/pyodbc).

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: duplicate
• changed status to closed