sqlalchemy does lower the case of Table names

[Former user]

Hi!

I have a simple script, which demonstrates my problem: This has happend to the update of 0.2.7 to 0.2.8. I have this probleme everywhere in 0.2.8.

#!/usr/bin/python2.4

# This is for sqlalchemy 0.2.8

from sqlalchemy import *

meta = BoundMetaData('mysql://root:@localhost/test')
meta.engine.execute("""
DROP TABLE IF EXISTS `MyTable`;
""")
meta.engine.execute("""
 CREATE TABLE `MyTable` (
   `ID` int(10) unsigned NOT NULL auto_increment,
   `Nothing` varchar(255) NOT NULL default '',
   PRIMARY KEY  (`ID`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
""")
meta.engine.execute("""
insert into `MyTable` values(1, "Hello"),(2, "World");
""")

meta.engine.echo = True
t = Table("MyTable", meta, autoload=True)
print select([t.c.Nothing](t.c.Nothing), t.c.ID==1).execute().getone()

Where I get the output:

[13:51:28,376](2006-10-09) [engine](engine): show variables like 'lower_case_table_names'[13:51:28,376](2006-10-09) [engine](engine): None
[13:51:28,377](2006-10-09) [engine](engine): describe mytable
[13:51:28,378](2006-10-09) [engine](engine): {}
[13:51:28,379](2006-10-09) [engine](engine): ROLLBACK
Traceback (most recent call last):
  File "./mixedcase.py", line 29, in ?
    t = Table("MyTable", meta, autoload=True)
  File "build/bdist.linux-i686/egg/sqlalchemy/schema.py", line 138, in __call__
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 491, in reflecttable
  File "build/bdist.linux-i686/egg/sqlalchemy/databases/mysql.py", line 325, in reflecttable
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 246, in execute
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 250, in execute_text
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 321, in _execute_raw
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 340, in _execute
sqlalchemy.exceptions.SQLError: (ProgrammingError) (1146, "Table 'test.mytable' doesn't exist") 'describe mytable' {}

I really need this to be fixed in a short time... Please tell me if I can do something else.

Martin - martin dot kaffanke at gmx dot at

[Mike Bayer]

case sensitivity on MySQL , particularly with regards to table reflection (i.e. the autoload=True flag youre using) is based on the underlying OS being used to run the server. it seems your OS has case-insensitive reflection; therefore you either have to use lower case identifiers or you have to not use "autoload=True" and lay out your Table and Column objects explicitly.

for information on mysql's limitations in this area, see:

http://www.mysql.org/doc/refman/5.0/en/name-case-sensitivity.html

[Former user]

But please tell my why this worked with 0.2.7 correctly...

A simple

easy_install -U sqlalchemy

brought up this error. (This caused to update from 0.2.7 to 0.2.8)

[Mike Bayer]

theres a fix in the trunk regarding the "case_sensitive" check for MySQL, which is not in version 0.2.8. either use the trunk, or change line 321 in 0.2.8's sqlalchemy/databases/mysql.py to:

case_sensitive = int(connection.execute("show variables like 'lower_case_table_names'").fetchone()[1](1)) == 0