That worked! Thank you so much for your patience. Part of it was the
code, and part of it turned out to be that I was still using
vendorTable = base.classes.VENDR
It didn't occur to me that my VENDR class had taken over that part, so
base.classes would no longer contain VENDR. When I saw your asserts,
it struck me that the last piece of the puzzle might be to set
ventorDable = VENDR, and that seems to be doing the job beautifully.
Thanks again!
On 3/14/16, Mike Bayer <clas...@zzzcomputing.com> wrote:
> metadata.reflect(..., extend_existing=True), here's a complete example
>
> from sqlalchemy import create_engine
>
> e = create_engine("mssql+pyodbc://scott:tiger@ms_2008", echo=True)
>
> with e.begin() as conn:
>
> conn.execute("""
> if not exists (select * from sysobjects where name='sometable'
> and xtype='U')
> create table sometable (
> id integer,
> data varchar(20),
> primary key (id)
> )
> """)
>
> conn.execute("""
> if not exists (select * from sysobjects where
> name='someothertable' and xtype='U')
> create table someothertable (
> id integer,
> data varchar(20),
> primary key (id)
> )
> """)
>
> conn.execute("""
> if not exists (select * from sysobjects where name='VENDR' and
> xtype='U')
> create table [VENDR] (
> [PVVNNO] integer,
> [DATA] varchar(20)
> )
> """)
>
> from sqlalchemy.ext.automap import automap_base
> from sqlalchemy import MetaData, Column, String
> from sqlalchemy.orm import Session
>
> metadata = MetaData()
>
> desiredTables = ["sometable", "someothertable", "VENDR"]
> base = automap_base(metadata=metadata)
>
>
> class VENDR(base):
> __tablename__ = "VENDR"
> PVVNNO = Column(String, primary_key=True)
>
> metadata.reflect(e, only=desiredTables, extend_existing=True)
> assert 'VENDR' in metadata.tables
>
> base.prepare()
>
> assert VENDR.DATA
>
> sess = Session(e)
> print sess.query(VENDR).all()
>
>
>
>
> On 03/14/2016 10:21 AM, Alex Hall wrote:
>> I hate to say it, but... AttributeError: VENDR. I've moved different
>> lines all around, above and below the class definition, but nothing
>> I've tried works. The only change was when I put my declaration of
>> base below the class, and Python naturally said it didn't know what my
>> table class was inheriting from. I don't know why this is being such a
>> problem.
>>
>> On 3/14/16, Mike Bayer <clas...@zzzcomputing.com> wrote:
>>> oh. try it like this:
>>>
>>> class VENDR(base):
>>> __tablename__ = "VENDR"
>>> PVVNNO = sqlalchemy.Column(sqlalchemy.String, primary_key=True)
>>>
>>> __table_args__ = {"extend_existing": True}
>>>
>>> that tells reflection to add new data to this Table object even though
>>> it already exists.
>>>
>>>
>>> On 03/14/2016 09:24 AM, Alex Hall wrote:
>>>> Thanks for that. Somehow, I'm getting the same error as before--the
>>>> VENDR table isn't being reflected. Here's the entire snippet, from
>>>> engine to trying to get the table.
>>>>
>>>> engine = sqlalchemy.create_engine("mssql+pyodbc://%s:%s@%s"
>>>> %(username, password, dsn))
>>>> session = Session(engine)
>>>> metadata = sqlalchemy.MetaData()
>>>> desiredTables = ["item", "assignment", "attachment", "attach_text",
>>>> "attribute", "attributevalue", "VENDR", "attributevalueassign"]
>>>> base = automap_base(metadata=metadata)
>>>> #pause here to make a table, since VENDR lacks a PK
>>>> class VENDR(base):
>>>> __tablename__ = "VENDR"
>>>> PVVNNO = sqlalchemy.Column(sqlalchemy.String, primary_key=True)
>>>> #done. Anyway...
>>>> metadata.reflect(engine, only=desiredTables)
>>>> base.prepare()
>>>>
>>>> itemTable = base.classes.item
>>>> assignmentTable = base.classes.assignment
>>>> attachmentTable = base.classes.attachment
>>>> attachmentTextTable = base.classes.attach_text
>>>> attributeTable = base.classes.attribute
>>>> attributeValueTable = base.classes.attributevalue
>>>> attributeValueAssignmentTable = base.classes.attributevalueassign
>>>> vendorTable = base.classes.VENDR #AttributeError: VENDR
>>>>
>>>> I still don't quite see how base, metadata, and session all interact
>>>> to do what SA does, or I'd have a much easier time troubleshooting
>>>> this. I'm sure I just have something out of order, or some other
>>>> simple mistake.
>>>>
>>>> On 3/11/16, Mike Bayer <clas...@zzzcomputing.com> wrote:
>>>>> like this:
>>>>>
>>>>> class VENDR(MyAutomapBase):
>>>>> __tablename__ = 'VENDR'
>>>>>
>>>>> id = Column(Integer, primary_key=True)
>>>>>
>>>>> Above, the 'id' column name should match the column in the table that
>>>>> you'd like to consider as the primary key (and so should the type) -
>>>>> the
>>>>> "id" / "Integer" combination above is just an example.
>>>>>
>>>>> Then do the automap as you've done. At the end, if it worked,
>>>>> Base.classes.VENDR should be the same class as the VENDR class above.
>>>>>
>>>>>
>>>>> On 03/11/2016 05:09 PM, Alex Hall wrote:
>>>>>> Sorry, do you mean the base subclass, or a new table class? In either
>>>>>> case, I'm not sure I see how this will fit into my automapping code.
>>>>>> I
>>>>>> know this is all fairly basic, I just can't quite picture what goes
>>>>>> where and what inherits from/gets passed to what to make it automap
>>>>>> this VENDR table. If I could, I'd just add a PK column to the table
>>>>>> itself. Sadly, I can't change that kind of thing, only query it.
>>>>>>
>>>>>> On 3/11/16, Mike Bayer <clas...@zzzcomputing.com> wrote:
>>>>>>> just make the class and include the PK column, then automap. the
>>>>>>> rest
>>>>>>> of the columns should be filled in.
>>>>>>>
>>>>>>>
>>>>>>> On 03/11/2016 04:14 PM, Alex Hall wrote:
>>>>>>>> Ah, you're right. Every other table I've used in this database has
>>>>>>>> had
>>>>>>>> a key, and I didn't even notice that this VENDR table lacks one.
>>>>>>>> That
>>>>>>>> explains the mystery! Thanks.
>>>>>>>>
>>>>>>>> Now to map this table. I've read the section of the docs on doing
>>>>>>>> this, and I get that I subclass base, set __table__ to be my VENDR
>>>>>>>> table, then set the key in my subclass. My question is how I access
>>>>>>>> the table, given that I can't automap it first. That is, if I can't
>>>>>>>> map the table because it has no PK, to what do I set __table__ in
>>>>>>>> the
>>>>>>>> subclass that will let me map the table?
>>>>>>>>
>>>>>>>> One post I found suggested something like this:
>>>>>>>>
>>>>>>>> vendorTable = Table("VENDR", metadata, column("PVVNNO",
>>>>>>>> primary_key=True))
>>>>>>>>
>>>>>>>> I'm guessing I'd have to add the column definitions for the other
>>>>>>>> columns if I did that. I'm further guessing that this replaces the
>>>>>>>> docs' method of subclassing, since the PK is now set. However, I
>>>>>>>> don't
>>>>>>>> know if this would still work with automapping.
>>>>>>>>
>>>>>>>> On 3/11/16, Mike Bayer <clas...@zzzcomputing.com> wrote:
>>>>>>>>> ah. does VENDR have a primary key? it won't be mapped if not.
>>>>>>>>>
>>>>>>>>> what's in base.classes.keys() ? base.classes['VENDR'] ?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On 03/11/2016 12:47 PM, Alex Hall wrote:
>>>>>>>>>> VENDR is right there, in base.classes and metadata.tables. Yet,
>>>>>>>>>> vendorTable = base.classes.VENDR
>>>>>>>>>> raises an AttributeError. Odd! There's nothing cap-sensitive
>>>>>>>>>> about
>>>>>>>>>> __hasattr__ that I'm forgetting, is there? Or, could I somehow
>>>>>>>>>> alias
>>>>>>>>>> the name before I try to access it, if that would help at all?
>>>>>>>>>> This
>>>>>>>>>> is
>>>>>>>>>> the only table in the CMS to have a name in all caps, but I need
>>>>>>>>>> to
>>>>>>>>>> access it to look up manufacturer details for items.
>>>>>>>>>>
>>>>>>>>>> On 3/11/16, Mike Bayer <clas...@zzzcomputing.com> wrote:
>>>>>>>>>>>
>>>>>>>>>>> can you look in metadata.tables to see what it actually
>>>>>>>>>>> reflected
>>>>>>>>>>> ?
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> On 03/11/2016 12:09 PM, Alex Hall wrote:
>>>>>>>>>>>> That's weird: the name I see is exactly what I've been using,
>>>>>>>>>>>> "VENDR".
>>>>>>>>>>>> All caps and everything. I tried using lowercase, just to see
>>>>>>>>>>>> what
>>>>>>>>>>>> it
>>>>>>>>>>>> would do, but it failed.
>>>>>>>>>>>>
>>>>>>>>>>>> On 3/11/16, Mike Bayer <clas...@zzzcomputing.com> wrote:
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> On 03/11/2016 09:39 AM, Alex Hall wrote:
>>>>>>>>>>>>>> Hello list,
>>>>>>>>>>>>>> Finally, a pure SA question from me. I'm using Automap and
>>>>>>>>>>>>>> the
>>>>>>>>>>>>>> "only"
>>>>>>>>>>>>>> keyword to automap a subset of the tables in our CMS
>>>>>>>>>>>>>> database.
>>>>>>>>>>>>>> This
>>>>>>>>>>>>>> has worked perfectly thus far. Now, though, it's failing on a
>>>>>>>>>>>>>> specific
>>>>>>>>>>>>>> table, and the only difference I can see is that this table's
>>>>>>>>>>>>>> name
>>>>>>>>>>>>>> is
>>>>>>>>>>>>>> in all caps, whereas the rest are all lowercase.
>>>>>>>>>>>>>> Capitalization
>>>>>>>>>>>>>> shouldn't matter, right?
>>>>>>>>>>>>>
>>>>>>>>>>>>> it does, as ALLCAPS is case sensitive and indicates quoting
>>>>>>>>>>>>> will
>>>>>>>>>>>>> be
>>>>>>>>>>>>> used. How to handle this depends on the exact name that's in
>>>>>>>>>>>>> the
>>>>>>>>>>>>> database and if it truly does not match case-insensitively.
>>>>>>>>>>>>>
>>>>>>>>>>>>> Examine the output of:
>>>>>>>>>>>>>
>>>>>>>>>>>>> inspect(engine).get_table_names()
>>>>>>>>>>>>>
>>>>>>>>>>>>> find your table, and that's the name you should use.
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> Stranger still, the actual reflection doesn't
>>>>>>>>>>>>>> error out. Later, where I try to assign base.classes.MYTABLE
>>>>>>>>>>>>>> to
>>>>>>>>>>>>>> a
>>>>>>>>>>>>>> variable, is where I get an AttributeError. Here's my code:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> engine = sqlalchemy.create_engine("mssql+pyodbc://%s:%s@%s"
>>>>>>>>>>>>>> %(username, password, dsn))
>>>>>>>>>>>>>> base = automap_base()
>>>>>>>>>>>>>> session = Session(engine)
>>>>>>>>>>>>>> metadata = sqlalchemy.MetaData()
>>>>>>>>>>>>>> desiredTables = ["table", "othertable", "VENDR"]
>>>>>>>>>>>>>> metadata.reflect(engine, only=desiredTables) #works fine
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> table = base.classes.table #fine
>>>>>>>>>>>>>> table2 = base.classes.othertable #fine
>>>>>>>>>>>>>> vendorTable = base.classes.VENDR #AttributeError
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I've added and removed tables as I adjust this script, and
>>>>>>>>>>>>>> all
>>>>>>>>>>>>>> of
>>>>>>>>>>>>>> them
>>>>>>>>>>>>>> work perfectly. This VENDR table is the first one in two days
>>>>>>>>>>>>>> to
>>>>>>>>>>>>>> cause
>>>>>>>>>>>>>> problems. If I iterate over all the classes in base.classes
>>>>>>>>>>>>>> and
>>>>>>>>>>>>>> print
>>>>>>>>>>>>>> each one, I don't even see it in that list, so SA isn't
>>>>>>>>>>>>>> simply
>>>>>>>>>>>>>> transforming the name. This is probably a simple thing, but I
>>>>>>>>>>>>>> don't
>>>>>>>>>>>>>> see the problem. Thanks for any suggestions.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> --
>>>>>>>>>>>>> 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+unsubscr...@googlegroups.com.
>>>>>>>>>>>>> To post to this group, send email to
>>>>>>>>>>>>> sqlalchemy@googlegroups.com.
>>>>>>>>>>>>> Visit this group at
>>>>>>>>>>>>> https://groups.google.com/group/sqlalchemy.
>>>>>>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> 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+unsubscr...@googlegroups.com.
>>>>>>>>>>> To post to this group, send email to
>>>>>>>>>>> sqlalchemy@googlegroups.com.
>>>>>>>>>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>>>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> 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+unsubscr...@googlegroups.com.
>>>>>>>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>>>>>>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> 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+unsubscr...@googlegroups.com.
>>>>>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>>>>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>
>>>>>>
>>>>>
>>>>> --
>>>>> 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+unsubscr...@googlegroups.com.
>>>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>
>>>>
>>>
>>> --
>>> 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+unsubscr...@googlegroups.com.
>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
