Issue 1:
Migrated issue, originally created by Anonymous

(original reporter: ged) Continuing my speed optimization frenzy, here is a (very) experimental patch to optimize the ORM speed by using a custom ResultProxy which only returns dicts with whatever will be used by the ORM.

Once again, the results are encouraging:

~26% improvement on large queries

~11% improvement on very small queries

Remember that I am timing total time, including the time for the actual DBAPI work.

A few comments about the patch:

The approach needs to be able to specify a custom ResultProxy class per query (or at least from within the ORM code path). I think this can also be useful outside the ORM, if the user wants to have its results returned in a specific way (I've had a couple times where I needed that and did it on top of the standard ResultProxy but this was not very efficient, but I didn't think to this solution at the time). Within the patch, I've only made the minimum possible modification so that it would work for my test case (attached). It would of course need to be completed, but I can't do that since I am a bit lost within that part of the code (the different ways to create a connection, and the code path it goes through the dialect etc...). I am hoping for some help here.

I know the custom ResultProxy approach is not compatible with the specific result proxies used by the various dialects, but I am sure it is possible to refactor the code to make that work (I haven't thought about it much yet).

There are two parts of that patch which are ugly to my eyes:

the way I'm guessing what exactly will serve as a key in the dictionary. I am hoping there could be an official "contract" that for example the first element of the result_map is what is used by the ORM.

I have duplicated the functionality of _process_row in fetchall. This is because the overhead of the function call is significant. This is ugly, I know, but worth it IMO for such a critical part.

Attachments: NamedTuple_opt.patch | orm_uses_col_indexes.patch | querying_columns_only_opt.patch | fastdict_resultproxy.patch | kwargs_are_inefficient.patch | stress_orm.py

Issue 2:
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.