Issue #1 new

get reflection to work

Mike Bayer
repo owner created an issue

not sure how to go about this, but the attached access.py from three years ago illustrates how to do it using a DAO engine. This is not the preferred way to go as we'd like to just use views that are queryable through pyodbc, but putting it here for reference in case it's required.

Comments (9)

  1. pwaller

    Hi Mike,

    I've had some success using the tables() and columns() functions documented here:

    https://code.google.com/p/pyodbc/wiki/Features

    like so:

        @reflection.cache
        def get_table_names(self, connection, schema=None, **kw):
            raw = connection.engine.raw_connection()
    
            try:
                c = raw.cursor()
                return [t.table_name for t in c.tables() if not t.table_name.startswith("MSys")]
            finally:
                raw.close()
    
        @reflection.cache
        def get_columns(self, connection, table_name, schema=None, **kw):
            raw = connection.engine.raw_connection()
    
            columns = []
    
            c = raw.cursor()
            try:
                for column in c.columns(table=table_name):
                    columns.append(todo(column))
    
                return columns
            finally:
                raw.close()
    

    What do you think of this approach? If I sent a pull request, could we iterate?

    Any chance you could setup a github mirror where I can send pull requests? I'm just that much more comfortable using it. If it is too much work I'll send it here though.

    Thanks.

  2. Mike Bayer reporter

    what's pyodbc doing that perhaps we could do in SQL, and thus not have to code it in the pyodbc module? sure I can set up a mirror, will try to get to that...

  3. Mike Bayer reporter

    ouch we're still in mercurial here. can you deal with mercurial /bitbucket for now? to switch to git I have to basically blow up this project entirely and make a new one.

  4. pwaller

    If there is any chance of doing it maybe it would be better sooner rather than later before there is too much invested?

    With respect to .tables(), it appears to be an "ODBC Function" called SQLTables. I can't figure out if that happens sql-side or not. Querying MSys* doesn't seem to work very well for me. Is there a strong reason to prefer doing it via SQL rather than via the functions which pyodbc exposes?

  5. pwaller

    Hi Mike, I've had quite a bit of luck. I've found a couple of things which needed fixing and I'd like to get them upstream before I move on and forget about this current project. So please let me know if it would be possible to use git.

    The current problem is that for some reason the query is being encoded with utf_8_encode via dialect._encoder and this is causing TypeError: The first argument to execute must be a string or unicode query. when the statement is passed to pyodbc. Any idea why? (I'm on sqlalchemy 0.8.3).

    The other strange thing is that although I did pip install --editable it seems to copy the source in sqlalchemy/build/lib/sqlalchemy, so my changes to sqlalchemy aren't having an effect on the running code unless I re-run them. Do you know anything about that? It's pip 1.4.1 and Python 3.3 on windows via wine.

  6. Mike Bayer reporter

    dialects have a flag .supports_unicode_statements which if True means the DBAPI's execute() method will accept a Python unicode object and work correctly. Pyodbc is always controversial on this point because it's behavior is different based on what kind of ODBC driver it's talking to. The only way to know if a Python unicode is acceptable is through experimentation, otherwise it has to be encoded.

    in any case, _encode() produces a string object. So the error message doesn't make sense - the object passed is definitely a string or unicode unless something else has gone wrong.

    I'm not familiar with "pip install --editable", sounds scary. I'm not sure why you'd need such a thing.

    if you can work out the changes you want, just attach a patchfile here I can commit like that.

  7. pwaller

    Just to document it, the specific problem I'm having using direct queries to the MSysObjects table and others is that I don't seem to have permission. I get: sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'msysobjects'. (-1907) (SQLExecDirectW)") 'select * from msysobjects' ().

    Where the odbc cursor().tables works fine.

  8. Log in to comment