strattonbrazil wrote: > > From what I've read of sqlalchemy, I originally wanted to have a main > table with one attribute foreign keyed to another table's > autoincremented integer primary key userId and a userName. I thought > I could join the tables together and set that as the mapper. The save > every object in my session. I thought I had it all figured out until > I found out that specifically for sqlite, autoincrement (or > autogenerated primary keys) don't work with "composite" tables/using a > join.
as long as the primary key of table #1 is a single-column primary key, SQLite autoincrements it. To get the pk into your joined table, set the two columns to the same attribute. e.g. as in http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables . > > I've tried to do it a number of different ways and was hoping to get a > better way to do it. > > Option #1 > Insert it into the tables separately. Without using a session, insert > every user name into the users table catching every uniqueness error > and ignoring it. Run a select to get the primary keys back, and when > lookup the userId before inserting an entry into the first table. > I've tried something like this, but it was pretty slow, but I think I > was doing a selection every time instead of one big one at the end, so > I assume this was why it was so slow. Plus catching unique errors > seems like a hack, but I don't see any support for an insert-ignore. > I've tried using a bulk insert, but it seems if there are any > duplicates and throws an error, nothing is inserted. I could filter > before hand out all the duplicates, but I just assume sqlalchemy/ > sqlite would be able to handle this for me. It might get hairy, too, > with other instances of the program running on the same database. If > read back the names from the database, another instance could add > another name that might kill my filtered bulk insert. > > Option #2 > Somehow get it to work using a session. Since I can't do the join > like I wanted (including the sqlite autoincrement feature), add > everything like option #1 per table creating a mapper for each table. > This seems like a hack having to map and unmap. And I still have to > catch duplicates errors. > > Is there a better way to go about doing this? > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
