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.