- changed status to duplicate
bytes/unicode type error when creating tables on SQL Server using pyodbc
Issue #2692
resolved
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.
Comments (2)
-
repo owner -
repo owner - removed milestone
Removing milestone: 0.8.xx (automated comment)
- Log in to comment
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 thesupports_unicode_statements
dialect flag should always be True in a Py3k scenario, not set up yet in connectors/pyodbc).