You need to use the class when querying the database. SQLAlchemy will then return an instance of that class for each matching row:
customer = session.query(Customer).filter_by(name=u'Powersports Etc').first() customer.name = u'New Name' session.flush() The getAllClasses function I showed was meant to be a direct analog to your getAllTables function, which was returning the tables themselves. You were then passing those to session.query() to get a whole load of rows back, but those rows were not update-able. I was trying to suggest that if you replaced those Table objects with mapped classes (such as Customer), then when those classes were passed to session.query(), what you get back will be Customer *instances*. session.query(someTable) returns glorified tuples that can't be updated session.query(someMappedClass) returns instances of someMappedClass, that *can* be updated. Hope that makes sense, Simon On Thu, Feb 4, 2016 at 2:07 PM, Alex Hall <ah...@autodist.com> wrote: > This is where sqlalchemy gets murky for me. If I return all classes, > I'm not returning all *instances* of those classes. How, then, would I > update a given customer's record? The objects (c1 and c2, for > instance) were instantiated elsewhere, and my GUIManager module has no > knowledge of them. More generally, when I switch this over to our > AS400 and start querying, I won't have created any records at all. > Every record will be pulled from a table on the 400, and while I will > have a schema, I won't have any instances of that schema. > > As I think about this, it occurs to me that I should *create* the > record objects from the records. That is: > > for record in recordsList: #an array of session.query(someTable).all() > myTempRecord = mySchemaClass(record) > myTempRecord.property = newValue > #update the database > > Assuming GUIManager knows about mySchemaClass, would that approach > work? How I'd pass in a record and get back an instance of > mySchemaClass I'm not yet sure, but is this worth looking into more, > or am I on the wrong track? > > On 2/4/16, Simon King <si...@simonking.org.uk> wrote: > > getAllTables is returning Core Table objects. I suppose you could have a > > similar function which returns all mapped classes, something like this: > > > > def getAllClasses(): > > return base.__subclasses__() > > > > (If your class hierarchy is more complicated you'd need a more > > sophisticated function there) > > > > Simon > > > > On Thu, Feb 4, 2016 at 12:32 PM, Alex Hall <ah...@autodist.com> wrote: > > > >> Yes, I'm using Declarative. I was following a great tutorial, then > >> realized it was way out of date. The one recommended on the forum I > >> found said to use Declarative, so I did. > >> > >> In DBInterface, I have this little function: > >> > >> def getAllTables(): > >> return base.metadata.tables > >> #end def getAllTables > >> > >> I then loop over that array, grabbing the value only and ignoring the > >> key. I just tried printing the type, as in: > >> > >> def getAllTables(): > >> for table in base.metadata.tables.values(): > >> print type(table) > >> return base.metadata.tables > >> #end def getAllTables > >> > >> I got <class 'sqlalchemy.sql.schema.Table'> Here's the loop that > >> generates the table list passed to my GUI (remember that this must be > >> an array of arrays): > >> > >> tables = DBInterface.getAllTables() > >> tablesList = [] > >> for table in tables.values(): > >> tablesList.append([table]) > >> #end for > >> > >> Just to be sure, I stuck a "print type(table)" statement in that for > >> loop, and the objects are still sqlalchemy.sql.schema.Table objects. > >> > >> On 2/4/16, Simon King <si...@simonking.org.uk> wrote: > >> > SQLAlchemy has 2 main layers, the "Core" layer which deals with mostly > >> > database-level constructs, such as Tables, and the ORM layer, which is > >> > built on top of Core. With the ORM, you map your own classes onto the > >> > lower-level Tables, then work with instances of those classes. You > >> > appear > >> > to be using the declarative system to define your classes, so > >> > SQLAlchemy > >> > will be creating the associated Table instances for you under the > hood. > >> > > >> > In your example, Customer is an ORM-level class. Somewhere, there will > >> be a > >> > construct representing the Core-level Table that the Customer class is > >> > mapped to. (It's probably available at Customer.__table__, but also in > >> > other places). > >> > > >> > When you say that "self.choices" contains table objects, I suspect > that > >> > those are the Core-level Table instances. When you query using those, > >> > you > >> > don't get Customer objects back. You need to query using the Customer > >> class > >> > instead. Can you get the Customer class into your self.choices array, > >> > either instead of the table, or as well as? Where are you getting the > >> > object that you are putting in the first element of each of the > >> > self.choices list? > >> > > >> > Simon > >> > > >> > On Thu, Feb 4, 2016 at 11:34 AM, Alex Hall <ah...@autodist.com> > wrote: > >> > > >> >> It's all from a GUI, so it's something like this (my code isn't in > >> >> front > >> >> of me): > >> >> DBInterface.session"query(self.choices[self.selectedIndex][0]).all() > >> >> Choices is a 2D array where the first (0th) element of each sub-array > >> >> is > >> >> a > >> >> table object. The query works, because I get the records as expected > >> >> and > >> >> can display them or inspect them. I just can't modify them for some > >> >> reason. > >> >> As I said, though, I'm new to this package so am likely missing an > >> >> obvious > >> >> step, or have something set up very wrong. > >> >> > >> >> Sent from my iPhone > >> >> > >> >> > On Feb 3, 2016, at 16:18, Simon King <si...@simonking.org.uk> > wrote: > >> >> > > >> >> > OK, so you’re not actually getting Customer objects back from your > >> >> query. What does your call to session.query() look like? For this to > >> >> work, > >> >> it really ought to be something like “session.query(Customer)”. I > >> suspect > >> >> you are doing something like “session.query(Customer.id, > >> >> Customer.name, > >> >> …)” > >> >> instead. > >> >> > > >> >> > Simon > >> >> > > >> >> >> On 3 Feb 2016, at 17:43, Alex Hall <ah...@autodist.com> wrote: > >> >> >> > >> >> >> I'm on the Gmail site, so am not sure I can reply in-line. Sorry. > >> >> >> > >> >> >> This is a basic table class, like > >> >> >> class Customer(base): > >> >> >> __tablename__ = "customers" > >> >> >> name = Column(String(50)), > >> >> >> ... > >> >> >> > >> >> >> When I print the type: > >> >> >> <class 'sqlalchemy.util._collections.result'> > >> >> >> And repr(): > >> >> >> (2, u'Powersports Etc', 5554443210L, u'ahall+dbte...@autodist.com > ', > >> >> True) > >> >> >> > >> >> >> > >> >> >>> On 2/3/16, Simon King <si...@simonking.org.uk> wrote: > >> >> >>>> On Wed, Feb 3, 2016 at 3:54 PM, Alex Hall <ah...@autodist.com> > >> >> >>>> wrote: > >> >> >>>> > >> >> >>>> Hello list, > >> >> >>>> I'm new to SQLAlchemy, but not to Python. I have an application > >> >> >>>> that's > >> >> >>>> coming together, and relies on SQLAlchemy to talk to a database > >> >> >>>> for > >> >> >>>> many of the app's functions. Listing tables, listing records, > >> >> >>>> updating > >> >> >>>> records, pulling records for internal use, and so on. > >> >> >>>> > >> >> >>>> My app is working, but so far I've been writing the framework > and > >> >> >>>> GUI > >> >> >>>> with a bit of SQLite just to check that things are working how I > >> >> >>>> want. > >> >> >>>> Now, though, I'm getting into my first "real" user-facing > >> >> >>>> database > >> >> >>>> task: taking the values from a dialog and updating a record > >> >> >>>> according > >> >> >>>> to those values. Thus far, I'm having no luck. > >> >> >>>> > >> >> >>>> My organization for now is DBInterface.py, which holds all my > >> >> >>>> table > >> >> >>>> definitions, database details, and my base, session, and engine > >> >> >>>> objects. I can hear the groans from here; I do plan to move the > >> >> >>>> table > >> >> >>>> definitions into a module of their own at some point, there > >> >> >>>> simply > >> >> >>>> hasn't been a need yet. GUIManager.py imports DBInterface, and > >> >> >>>> handles > >> >> >>>> all the GUI stuff, as the name suggests. It's where, eventually, > >> >> >>>> I'll > >> >> >>>> take user input and use it to update records by calling > functions > >> >> >>>> from > >> >> >>>> DBInterface. That's the problem, though. In GUIManager, I have a > >> >> >>>> simple test: > >> >> >>>> > >> >> >>>> self.records[self.selectedRecordIndex].name="test name" #records > >> >> >>>> is > >> >> >>>> the list of objects returned by querying the current table > >> >> >>>> > >> >> >>>> Which errors out every time: > >> >> >>>> AttributeError: can't set attribute > >> >> >>>> > >> >> >>>> (Yes, "name" is an attribute name of my Customer class.) From > >> >> >>>> what > >> >> >>>> I've read thus far, updating records is as easy as modifying > >> >> >>>> their > >> >> >>>> properties and calling session.commit(). That isn't working, > >> though. > >> >> >>>> I > >> >> >>>> imagine the problem is that the records in a query aren't the > >> >> >>>> same > >> >> >>>> as > >> >> >>>> the records originally created, and modify/commit only works on > >> >> >>>> those > >> >> >>>> originals. I'm not sure if that's right, though. If it is, how > >> could > >> >> >>>> I > >> >> >>>> modify the originals, given that I run a new query each time the > >> >> >>>> user > >> >> >>>> selects a table name in my GUI's list of names? If I'm wrong, > how > >> >> >>>> would I update the record attributes and save the changes back > to > >> >> >>>> the > >> >> >>>> database? I think I'm picturing this whole thing wrong, to be > >> >> >>>> honest. > >> >> >>>> Thanks for any help, and please let me know if I need to provide > >> >> >>>> more > >> >> >>>> code or context. > >> >> >>> Is your query against a single mapped class, or is it against > some > >> >> >>> set > >> >> of > >> >> >>> columns? What do you get if you write: > >> >> >>> > >> >> >>> print type(self.records[self.selectedRecordIndex]) > >> >> >>> print repr(self.records[self.selectedRecordIndex]) > >> >> >>> > >> >> >>> ...at the point where you are trying to set the name? > >> >> >>> > >> >> >>> Simon > >> >> >>> > >> >> >>> -- > >> >> >>> 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. > -- 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.
