SQLAlchemy: How to do Table Reflection and MySQL?

Discussion in 'Python' started by Nick Sabalausky, Oct 21, 2012.

  1. Hi, I'm fairly new to Python, and I'm trying to figure out how to use
    SQLAlchemy to connect to a MySQL DB and use table reflection to set up
    SQLAlchemy's tables. But the SQLAlchemy documentation is gigantic and
    frankly kinda making my head spin, so I'm having trouble even finding
    any information on how to use its table reflection, mostly just that it
    exists and *can* be done, but not so much "how". My web searching has
    just been turning up examples of SQLite and manually describing the
    tables in Python and having SQLAlchemy create the tables, which isn't
    what I'm looking for.

    Is there a simple way to do this somehow? To just connect to a MySQL DB
    and use table reflection?
     
    Nick Sabalausky, Oct 21, 2012
    #1
    1. Advertising

  2. Nick Sabalausky

    darnold Guest

    On Oct 20, 6:24 pm, Nick Sabalausky
    <> wrote:
    > Hi, I'm fairly new to Python, and I'm trying to figure out how to use
    > SQLAlchemy to connect to a MySQL DB and use table reflection to set up
    > SQLAlchemy's tables. But the  SQLAlchemy documentation is gigantic and
    > frankly kinda making my head spin, so I'm having trouble even finding
    > any information on how to use its table reflection, mostly just that it
    > exists and *can* be done, but not so much "how". My web searching has
    > just been turning up examples of SQLite and manually describing the
    > tables in Python and having SQLAlchemy create the tables, which isn't
    > what I'm looking for.
    >
    > Is there a simple way to do this somehow? To just connect to a MySQL DB
    > and use table reflection?


    i'm not brave enough to dig too deeply into SQLAlchemy, but maybe this
    will help? :

    http://kashififtikhar.blogspot.com/2010/07/using-sqlalchemy-reflection-with-pylons.html

    that came up from googling "sqlalchemy table reflection tutorial".
     
    darnold, Oct 22, 2012
    #2
    1. Advertising

  3. On Mon, 22 Oct 2012 14:35:23 -0700 (PDT)
    darnold <> wrote:
    >
    > i'm not brave enough to dig too deeply into SQLAlchemy, but maybe this
    > will help? :
    >
    > http://kashififtikhar.blogspot.com/2010/07/using-sqlalchemy-reflection-with-pylons.html
    >
    > that came up from googling "sqlalchemy table reflection tutorial".


    Thanks, your view of Google seems to be far better tailored for Python
    than mine is, that doesn't come up for me anywhere on the first five
    pages of results for that query.

    Unfortunately the info on that page doesn't seem to work for me:

    ----------------------------------
    from sqlalchemy import *
    from sqlalchemy.orm import sessionmaker

    engine = create_engine(my connection string)
    meta = MetaData()
    meta.bind = engine
    meta.reflect()

    Session = sessionmaker(bind=engine)
    session = Session()

    res = session.query(user).filter(user.name=="bert").first()
    print res.name
    ----------------------------------

    That just gives me:

    NameError: name 'user' is not defined

    (And yes, the code given on that page to print out the table info
    *does* indicate a table named 'user' was found.)

    I also tried this which also fails:

    res =
    session.query(meta.tables["user"]).filter(meta.tables["user"].name=="bert").first()

    sqlalchemy.exc.ArgumentError: filter() argument must be of type
    sqlalchemy.sql.ClauseElement or string

    The page you linked to appears to get around the matter by manually
    setting up tables filled with the reflected info, but that seems to
    defeat much of the point for me. I may as well just set up the tables
    manually without the reflection, which is what I'll probably do.

    Maybe I just misunderstood what was meant in the SQLAlchemy docs here?:

    "but note that SA can also “import†whole sets of Table objects
    automatically from an existing database (this process is called table
    reflection)." --
    http://docs.sqlalchemy.org/en/rel_0_7/core/tutorial.html

    It said that but then didn't say how and didn't link to any info on how.
     
    Nick Sabalausky, Oct 23, 2012
    #3
  4. On Tue, 23 Oct 2012 22:42:08 +0000
    "Prasad, Ramit" <> wrote:

    > Nick Sabalausky wrote:
    > > On Mon, 22 Oct 2012 14:35:23 -0700 (PDT)
    > > darnold <> wrote:
    > > >
    > > > i'm not brave enough to dig too deeply into SQLAlchemy, but maybe
    > > > this will help? :
    > > >
    > > > http://kashififtikhar.blogspot.com/2010/07/using-sqlalchemy-reflection-with-pylons.html
    > > >
    > > > that came up from googling "sqlalchemy table reflection tutorial".

    > >
    > > Thanks, your view of Google seems to be far better tailored for
    > > Python than mine is, that doesn't come up for me anywhere on the
    > > first five pages of results for that query.
    > >
    > > Unfortunately the info on that page doesn't seem to work for me:
    > >
    > > ----------------------------------
    > > from sqlalchemy import *
    > > from sqlalchemy.orm import sessionmaker
    > >
    > > engine = create_engine(my connection string)
    > > meta = MetaData()
    > > meta.bind = engine
    > > meta.reflect()
    > >
    > > Session = sessionmaker(bind=engine)
    > > session = Session()
    > >
    > > res = session.query(user).filter(user.name=="bert").first()
    > > print res.name
    > > ----------------------------------
    > >
    > > That just gives me:
    > >
    > > NameError: name 'user' is not defined
    > >
    > > (And yes, the code given on that page to print out the table info
    > > *does* indicate a table named 'user' was found.)

    >
    > This does not seem to be a SQLAlchemy problem. Instead it seems
    > there is not a variable called `name`.


    Oops, yea, it's supposed to be:

    meta.tables["user"].columns["name"]

    Not:

    meta.tables["user"].name

    Works now, thanks all.
     
    Nick Sabalausky, Oct 24, 2012
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    253
    Bruno Desthuilliers
    Oct 9, 2006
  2. Karlo Lozovina

    SQLAlchemy and py2exe

    Karlo Lozovina, Oct 21, 2006, in forum: Python
    Replies:
    1
    Views:
    459
    Steve Holden
    Oct 21, 2006
  3. Karlo Lozovina

    SQLAlchemy, py2exe and Python 2.5 problem?

    Karlo Lozovina, Dec 19, 2006, in forum: Python
    Replies:
    0
    Views:
    448
    Karlo Lozovina
    Dec 19, 2006
  4. Massi
    Replies:
    1
    Views:
    926
    Rebelo
    Jul 9, 2010
  5. Sam
    Replies:
    4
    Views:
    77
    Mark Lawrence
    Jan 17, 2014
Loading...

Share This Page