Issue 1:
Migrated issue, originally created by Carson Ip

Create a bakery, bake a query using ShardedSession such that the cached query is of type ShardedQuery. Then query using a normal Session. Raises TypeError on unexpected kwarg "shard_id". The order of querying using ShardedSession and Session matters.

Stack trace:

Traceback (most recent call last):
File "sqlalchemy/test/ext/test_horizontal_shard.py", line 330, in test_baked_mix
t = get_tokyo(sess2)
File "sqlalchemy/test/ext/test_horizontal_shard.py", line 318, in get_tokyo
t = bq(sess).get(tokyo.id)
File "sqlalchemy/test/../lib/sqlalchemy/ext/baked.py", line 458, in get
return query._get_impl(ident, self._load_on_pk_identity)
File "sqlalchemy/test/../lib/sqlalchemy/orm/query.py", line 1004, in _get_impl
return db_load_fn(self, primary_key_identity)
File "sqlalchemy/test/../lib/sqlalchemy/ext/baked.py", line 510, in _load_on_pk_identity
result = list(bq.for_session(self.session).params(**params))
File "sqlalchemy/test/../lib/sqlalchemy/ext/baked.py", line 355, in __iter__
return q._execute_and_instances(context)
File "sqlalchemy/test/../lib/sqlalchemy/ext/horizontal_shard.py", line 61, in _execute_and_instances
partial.extend(iter_for_shard(shard_id))
File "sqlalchemy/test/../lib/sqlalchemy/ext/horizontal_shard.py", line 49, in iter_for_shard
shard_id=shard_id).execute(
File "sqlalchemy/test/../lib/sqlalchemy/orm/query.py", line 2998, in _connection_from_session
conn = self.session.connection(**kw)
File "sqlalchemy/test/../lib/sqlalchemy/orm/session.py", line 1031, in connection
bind = self.get_bind(mapper, clause=clause, **kw)
TypeError: get_bind() got an unexpected keyword argument 'shard_id'

To reproduce, add this test case under test_horizontal_shard: (I know it is a bad place to put the test case, but it is more convenient to reproduce here)

def test_baked_mix(self):
sess = self._fixture_data()

tokyo = sess.query(WeatherLocation).filter_by(city="Tokyo").one()
tokyo.city
sess.expunge_all()

from sqlalchemy.ext.baked import BakedQuery

bakery = BakedQuery.bakery()

def get_tokyo(sess):
bq = bakery(lambda session: session.query(WeatherLocation))
t = bq(sess).get(tokyo.id)
return t

Sess = sessionmaker(class_=Session, bind=db2,
autoflush=True, autocommit=False)
sess2 = Sess()

t = get_tokyo(sess)
eq_(t.city, tokyo.city)

t = get_tokyo(sess2)
eq_(t.city, tokyo.city)

This bug is not related to database drivers / dialects.

Questions about the fix:

Do we really want to cache a ShardedQuery instead of a Query in the bakery?

If yes, do we want to reuse a ShardedQuery for a Query? We can simply add the query class to cache key if we forbid this.

I am willing to work on the fix if I know the directions. If it is easier to fix it immediately, we can just save the trouble (of back and forth discussions about the fix). Thanks!

Issue 2:
Migrated issue, originally created by Anonymous

I have some tables in my database which names start with capital letter, e.g. 'User'.

I try to autoload metadata for it:

engine = create_engine("mysql://root:123@localhost/test", echo='debug')
meta = BoundMetaData(engine)
user = Table('User', meta, autoload=True)

But exception is occurring:

sqlalchemy.exceptions.SQLError: (ProgrammingError) (1146, "Table 'test.user' doesn't exist") 'describe user' {}

I have case-sensitive platform and there's a difference between 'user' and 'User'.

I noticed that SQLAlchemy do some magic in reflecttable() to check whether my platform is case sensitive or not:

connection.execute("show variables like 'lower_case_table_names'").fetchone()[1](1) == 0
if not case_sensitive:
table.name = table.name.lower()
table.metadata.tables[table.name](table.name)= table

But this check is not correct, it should be "0", not 0, because ''fetchone()1'' is a string.