problems with mysql reflect

[robert rottermann]

Hi there,

I have a mysql database that I use on several linux boxes.
No I get the following error on on of them:

Traceback (most recent call last):
File "../bin/zopepy", line 317, in <module>
execfile(__file__)
File "t.py", line 38, in <module>
BaseA.metadata.reflect(local_engine_a)
File
"/home/zope/key2go/eggs/SQLAlchemy-0.9.2-py2.6-linux-i686.egg/sqlalchemy/sql/schema.py",
line 3268, in reflect
Table(name, self, **reflect_opts)
File
"/home/zope/key2go/eggs/SQLAlchemy-0.9.2-py2.6-linux-i686.egg/sqlalchemy/sql/schema.py",
line 350, in __new__
table._init(name, metadata, *args, **kw)
File
"/home/zope/key2go/eggs/SQLAlchemy-0.9.2-py2.6-linux-i686.egg/sqlalchemy/sql/schema.py",
line 423, in _init
self._autoload(metadata, autoload_with, include_columns)
File
"/home/zope/key2go/eggs/SQLAlchemy-0.9.2-py2.6-linux-i686.egg/sqlalchemy/sql/schema.py",
line 435, in _autoload
self, include_columns, exclude_columns
File
"/home/zope/key2go/eggs/SQLAlchemy-0.9.2-py2.6-linux-i686.egg/sqlalchemy/engine/base.py",
line 1160, in run_callable
return callable_(self, *args, **kwargs)
File
"/home/zope/key2go/eggs/SQLAlchemy-0.9.2-py2.6-linux-i686.egg/sqlalchemy/engine/default.py",
line 345, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File
"/home/zope/key2go/eggs/SQLAlchemy-0.9.2-py2.6-linux-i686.egg/sqlalchemy/engine/reflection.py",
line 463, in reflecttable
for col_d in self.get_columns(table_name, schema,
**table.dialect_kwargs):
TypeError: get_columns() keywords must be strings



this is the testscript I use to create the error:


from sqlalchemy import create_engine
from sqlalchemy.dialects import mysql

import sys

infoa = {
'username' : 'root',
'pw' : '',
'host' :'localhost',
'drivername' : 'mysql',
'database' : 'energie_2',
'extra_param' : ''
}

LOCAL_DSN_A
="%(drivername)s://%(username)s@%(host)s/%(database)s?charset=utf8%(extra_param)s"
% infoa

local_engine_a = create_engine(LOCAL_DSN_A)

BaseA.metadata.reflect(local_engine_a)


thanks for your help
and thanks for a wonderful library.

robert

[Michael Bayer]

so this code runs fine on many machines, just one machine is doing this? have there been modifications made to the SQLAlchemy library on that one machine? I’m not able to reproduce this case. There’s a path where table.dialect_kwargs gets populated and within the scope of MySQL reflection, the keys are definitely strings. I’ve tried with python 2.6 just in case it’s a 2.6 issue.

if the code works on all machines except one then you need to isolate what is different on that one machine.

In particular, if you ran this code:


try:
BaseA.metadata.reflect(local_engine_a)
except:
import pdb
pdb.post_mortem()

assuming console script, this would drop you into a pdb session. if you then type:

pdb > table.dialect_kwargs

it will show you the contents of the dictionary. send me that.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.

[Paul Molodowitch]

I just ran into the same problem, using python 2.6 + sqlalchemy 0.9.4 / 0.9.6 + MySQL.

The problem in my case IS definitely related to python 2.6 - basically, python 2.6 doesn't allow unicode keywords, while 2.7 does. Ie, if you do this:

def foo(**kwargs):

    print kwargs

foo(**{u'thing':1})

...it will work in 2.7, but give this error in 2.6:

TypeError: foo() keywords must be strings

For reference, these were the table.dialect_kwargs.keys() that were making trouble in 2.6:

[u'mysql_comment', u'mysql_engine', u'mysql_default charset']

Fine, except for the fact that they're unicode...

[Mike Bayer]

OK but this is not a codepath within SQLAlchemy's MySQL reflection code.   I'm PDBing right now into 0.9, using py2.6 + use_unicode=1; the reflected table options are sent directly into table.kwargs, not using the constructor or any **kw system.  the tests pass, and the keys are coming back as u''.

if you can show me where table.kwargs gets used implicitly as a constructor arg i can fix that.

[Paul Molodowitch]

Sure - I think it's the same as the original poster's, but the traceback I'm getting is:

>>> inspect(Project).relationships

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

  File "./sqlalchemy/util/langhelpers.py", line 712, in __get__

    obj.__dict__[self.__name__] = result = self.fget(obj)

  File "./sqlalchemy/orm/mapper.py", line 2037, in relationships

    return self._filter_properties(properties.RelationshipProperty)

  File "./sqlalchemy/orm/mapper.py", line 2054, in _filter_properties

    configure_mappers()

  File "./sqlalchemy/orm/mapper.py", line 2560, in configure_mappers

    mapper._post_configure_properties()

  File "./sqlalchemy/orm/mapper.py", line 1673, in _post_configure_properties

    prop.init()

  File "./sqlalchemy/orm/interfaces.py", line 143, in init

    self.do_init()

  File "./sqlalchemy/orm/relationships.py", line 1510, in do_init

    self._setup_join_conditions()

  File "./sqlalchemy/orm/relationships.py", line 1586, in _setup_join_conditions

    can_be_synced_fn=self._columns_are_mapped

  File "./sqlalchemy/orm/relationships.py", line 1849, in __init__

    self._determine_joins()

  File "./sqlalchemy/orm/relationships.py", line 1915, in _determine_joins

    consider_as_foreign_keys=consider_as_foreign_keys

  File "<string>", line 2, in join_condition

  File "./sqlalchemy/sql/selectable.py", line 692, in _join_condition

    b.foreign_keys,

AttributeError: 'tuple' object has no attribute 'foreign_keys'

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/E3MhX1m8QqQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.

For more options, visit https://groups.google.com/d/optout.

[Paul Molodowitch]

Whoops! Just noticed this was the totally wrong traceback!

Here's the correct trace:

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

  File "test.py", line 155, in <module>

    metadata.reflect(db.engine, only=tables)

  File "./sqlalchemy/sql/schema.py", line 3277, in reflect

    Table(name, self, **reflect_opts)

  File "./sqlalchemy/sql/schema.py", line 352, in __new__

    table._init(name, metadata, *args, **kw)

  File "./sqlalchemy/sql/schema.py", line 425, in _init

    self._autoload(metadata, autoload_with, include_columns)

  File "./sqlalchemy/sql/schema.py", line 437, in _autoload

    self, include_columns, exclude_columns

  File "./sqlalchemy/engine/base.py", line 1198, in run_callable

    return callable_(self, *args, **kwargs)

  File "./sqlalchemy/engine/default.py", line 355, in reflecttable

    return insp.reflecttable(table, include_columns, exclude_columns)

  File "./sqlalchemy/engine/reflection.py", line 463, in reflecttable

[Mike Bayer]

with metadata.reflect(), OK.  Can you please make a very short and self-contained test case and post a bug report?  thanks.

[Paul Molodowitch]

Done:

https://bitbucket.org/zzzeek/sqlalchemy/issue/3123/mysql-reflection-on-python-26-causes-error

FYI, for now we're patching sqlalchemy/engine/reflection.py, changing Inspector.reflectable after it gets tbl_opts (line 450 in my code):

        # reflect table options, like mysql_engine
        tbl_opts = self.get_table_options(table_name, schema, **table.dialect_kwargs)
        # Python 2.6 doesn't except using dicts with unicode keys for kwargs,
        # ie, myFunc(**{u'foo':1}) will raise an error
        if sys.version_info < (2, 7):
            for key, val in tbl_opts.items():
                if isinstance(key, unicode):
                    del tbl_opts[key]

                    tbl_opts[str(key)] = val

There's likely a much better / more elegant way to handle this, but it seems to do the trick for us...

Let me know anything's not clear, or you're having troubles replicating, or there's anything else I can do to help!

- Paul

[Paul Molodowitch]

oops, that line should read "Python 2.6 doesn't accept", not "except".

My hands are too used to typing "except SomeError:"...