[sqlalchemy] Re: Q: fetch value of autoincrement column
Adrian von Bidder wrote: On Friday 22 May 2009 23.00:05 Werner F. Bruhin wrote: What do you want to do with the autoincrement column? Often these are used for primary keys, which in turn get used as foreign keys. I want to use the id as filename; the table will cache some info that comes from the file. Using it as a filename directly has several advantages: * I don't have to care about a unique filename, since the db already takes care of that. * I don't have to create an additional column with the filename. Obviously, I can just first create the file with a temp filename, but I'd like to avoid this additional step if it's possible. Less code == fewer bugs. And I hoped that using the id from the beginning would be easily possible. I have a similar use case (for images and xml files), I use the wine name and vintage (easier for the user if ever they look for a particular image in the file system and want to use it outside my app) and primary key (just to make sure that it is unique), but in my case I can do it after having flush'ed. Right now, I'm undecided if I should just drop portability and fetch the next value from the mytable_id_seq that postgres has generated for me with the autoincrement columns (from what I understand, even if I declare the column autoincrement, if I set the id manually it will Just Work(tm) like it should Based on Michael's post you can keep partial portability by manually getting it using the Sequence. I was intriged and wanted to figure out how to get at the sequence defined in the model. Is the following really the only way to get at it? I am using ORM/declarative. seq = sa.Sequence(l.__table__.c.langid.default.name) nextid = engine.execute(seq) Werner --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Q: fetch value of autoincrement column
Hello Adrian, In my case, I actually build up my SA model, flush it, and save the file on disk using the id that's been populated on my object after flush. Sorry, your original question didn't really give enough details on what you wanted to do. What you were trying to achieve is not common practice and is database specific (thus not standard) and probably there would be a better normalized solution for you. That's why I pointed you foreign keys. I thought you'd be going the wrong way, that's all. :) Alex 2009/5/23 Adrian von Bidder avbid...@fortytwo.ch: On Friday 22 May 2009 23.00:05 Werner F. Bruhin wrote: What do you want to do with the autoincrement column? Often these are used for primary keys, which in turn get used as foreign keys. I want to use the id as filename; the table will cache some info that comes from the file. Using it as a filename directly has several advantages: * I don't have to care about a unique filename, since the db already takes care of that. * I don't have to create an additional column with the filename. Obviously, I can just first create the file with a temp filename, but I'd like to avoid this additional step if it's possible. Less code == fewer bugs. And I hoped that using the id from the beginning would be easily possible. Right now, I'm undecided if I should just drop portability and fetch the next value from the mytable_id_seq that postgres has generated for me with the autoincrement columns (from what I understand, even if I declare the column autoincrement, if I set the id manually it will Just Work(tm) like it should or if I should go the temporary filename route. I'm leaning towards the former because I probably will drop portability at some point in any case, since PostgreSQL just has too many cool features waiting to be used :-) (And no, I'm not going to use blobs and store the file as a large object in the db. Relational databases are not made for this, and besides it makes the db harder to maintain because db dumps get huge or the blobs have to be backed up separately, and debugging is harder because I can't use shell tools to get at the file data.) (So you see, I'm not trying to build foreign keys by hand or any such thing. I actually know relational databases quite well, it's just my knowledge of SQLAlchemy and Python which is lacking.) thanks -- vbi -- Alle Wirksamkeit ist stärker am Mittelpunkt als gegen die Peripherie zu. Raum zwischen Mars und Jupiter. -- Goethe, Maximen und Reflektionen, Nr. 1336 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Q: fetch value of autoincrement column
On Friday 22 May 2009 23.00:05 Werner F. Bruhin wrote: What do you want to do with the autoincrement column? Often these are used for primary keys, which in turn get used as foreign keys. I want to use the id as filename; the table will cache some info that comes from the file. Using it as a filename directly has several advantages: * I don't have to care about a unique filename, since the db already takes care of that. * I don't have to create an additional column with the filename. Obviously, I can just first create the file with a temp filename, but I'd like to avoid this additional step if it's possible. Less code == fewer bugs. And I hoped that using the id from the beginning would be easily possible. Right now, I'm undecided if I should just drop portability and fetch the next value from the mytable_id_seq that postgres has generated for me with the autoincrement columns (from what I understand, even if I declare the column autoincrement, if I set the id manually it will Just Work(tm) like it should or if I should go the temporary filename route. I'm leaning towards the former because I probably will drop portability at some point in any case, since PostgreSQL just has too many cool features waiting to be used :-) (And no, I'm not going to use blobs and store the file as a large object in the db. Relational databases are not made for this, and besides it makes the db harder to maintain because db dumps get huge or the blobs have to be backed up separately, and debugging is harder because I can't use shell tools to get at the file data.) (So you see, I'm not trying to build foreign keys by hand or any such thing. I actually know relational databases quite well, it's just my knowledge of SQLAlchemy and Python which is lacking.) thanks -- vbi -- Alle Wirksamkeit ist stärker am Mittelpunkt als gegen die Peripherie zu. Raum zwischen Mars und Jupiter. -- Goethe, Maximen und Reflektionen, Nr. 1336 signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Q: fetch value of autoincrement column
if its of any value, databases like MySQL and SQLIte do not give you any way to get at an auto-generated ID without actually INSERTing a row. Only databases that support sequences, i.e. postgres, firebird, and oracle, give you a built in way to get IDs without using INSERT. On May 23, 2009, at 8:25 AM, Adrian von Bidder wrote: On Friday 22 May 2009 23.00:05 Werner F. Bruhin wrote: What do you want to do with the autoincrement column? Often these are used for primary keys, which in turn get used as foreign keys. I want to use the id as filename; the table will cache some info that comes from the file. Using it as a filename directly has several advantages: * I don't have to care about a unique filename, since the db already takes care of that. * I don't have to create an additional column with the filename. Obviously, I can just first create the file with a temp filename, but I'd like to avoid this additional step if it's possible. Less code == fewer bugs. And I hoped that using the id from the beginning would be easily possible. Right now, I'm undecided if I should just drop portability and fetch the next value from the mytable_id_seq that postgres has generated for me with the autoincrement columns (from what I understand, even if I declare the column autoincrement, if I set the id manually it will Just Work(tm) like it should or if I should go the temporary filename route. I'm leaning towards the former because I probably will drop portability at some point in any case, since PostgreSQL just has too many cool features waiting to be used :-) (And no, I'm not going to use blobs and store the file as a large object in the db. Relational databases are not made for this, and besides it makes the db harder to maintain because db dumps get huge or the blobs have to be backed up separately, and debugging is harder because I can't use shell tools to get at the file data.) (So you see, I'm not trying to build foreign keys by hand or any such thing. I actually know relational databases quite well, it's just my knowledge of SQLAlchemy and Python which is lacking.) thanks -- vbi -- Alle Wirksamkeit ist stärker am Mittelpunkt als gegen die Peripherie zu. Raum zwischen Mars und Jupiter. -- Goethe, Maximen und Reflektionen, Nr. 1336 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Q: fetch value of autoincrement column
Don't you want that non-null column to be a foreign key ? 2009/5/21 Adrian von Bidder avbid...@fortytwo.ch: Hi, Is it possible to fetch the values of an autoincrement field without flushing the object to the DB? (In postgres, I obviously can manually fetch nextval of the automatically generated sequence, but I lose the portability that way ...) Why? Because I need the id to generate data that will be filled into some (non- null) columns of the table row. So I can't flush since I'll get an IntegrityError about non-null columns, and I can't fill those columns without knowing the id that's going to be assigned. (Yes, I can use dummy values, then flush(), and then update the row before committing. But that's not exactly elegant...) cheers -- vbi -- Fnord. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Q: fetch value of autoincrement column
On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote: Don't you want that non-null column to be a foreign key ? Would that make a difference? cheers -- vbi 2009/5/21 Adrian von Bidder avbid...@fortytwo.ch: Hi, Is it possible to fetch the values of an autoincrement field without flushing the object to the DB? (In postgres, I obviously can manually fetch nextval of the automatically generated sequence, but I lose the portability that way ...) Why? Because I need the id to generate data that will be filled into some (non- null) columns of the table row. So I can't flush since I'll get an IntegrityError about non-null columns, and I can't fill those columns without knowing the id that's going to be assigned. (Yes, I can use dummy values, then flush(), and then update the row before committing. But that's not exactly elegant...) cheers -- vbi -- Fnord. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- -- Der Glaube versetzt Berge, der Zweifel erklettert sie. -- Friedrich Georg Jünger signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Q: fetch value of autoincrement column
Adrian, Adrian von Bidder wrote: Hi, Is it possible to fetch the values of an autoincrement field without flushing the object to the DB? (In postgres, I obviously can manually fetch nextval of the automatically generated sequence, but I lose the portability that way ...) Why? Because I need the id to generate data that will be filled into some (non- null) columns of the table row. So I can't flush since I'll get an IntegrityError about non-null columns, and I can't fill those columns without knowing the id that's going to be assigned. (Yes, I can use dummy values, then flush(), and then update the row before committing. But that's not exactly elegant...) This is one of the beauties of SQLA which it took me a while to catch on to. I use SQLA ORM (declarative) and you can just do: lang = session.query(db.Language).get(1) ca = db.Country_Ls() ca.language = lang ca.name = 'some country' reg = db.Region_Ls() reg.language = lang reg.name = 'some region' reg.country_ls = ca session.add(ca) session.add(reg) print ca print reg session.flush() print '===' print 'flushed' print '===' print ca print ca.countryid print '' print 'region' print reg print reg.fk_countryid Which gives me this output and as you can see the actual primary key and foreign key are only known after I do a flush, but it is not needed to add etc. Country_Ls(language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some country') Region_Ls(country_ls=Country_Ls(language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some country'), language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some region') === flushed === Country_Ls(centralkey=None, countryid=241, created=datetime.datetime(2009, 5, 22, 13, 22, 48, 826000), fk_langid=1, id=None, language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some country', shortname=None, updated=datetime.datetime(2009, 5, 22, 13, 22, 48, 826000)) 241 region Region_Ls(centralkey=None, country_ls=Country_Ls(centralkey=None, countryid=241, created=datetime.datetime(2009, 5, 22, 13, 22, 48, 826000), fk_langid=1, id=None, language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some country', shortname=None, updated=datetime.datetime(2009, 5, 22, 13, 22, 48, 826000)), created=datetime.datetime(2009, 5, 22, 13, 22, 48, 829000), fk_countryid=241, fk_langid=1, id=None, language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some region', regionid=214, shortname=None, updated=datetime.datetime(2009, 5, 22, 13, 22, 48, 829000)) 241 Hope this helps Werner --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Q: fetch value of autoincrement column
Hello Adrian, 2009/5/22 Adrian von Bidder avbid...@fortytwo.ch: On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote: Don't you want that non-null column to be a foreign key ? Would that make a difference? That's what a foreign key is used for: http://en.wikipedia.org/wiki/Foreign_key I think you should know what a foreign key is before using a tool like SQLAlchemy. SQLAlchemy is an abstraction layer to deal with cells, records, columns, tables, relations of databases. An abstraction layer hides some details of lower level concepts. Abstraction layers like SQLA are meant to ease your everyday work. But you still have to understand lower level concepts in order to correctly use abstraction tools like SQLAlchemy. What you are trying to achieve is already a feature that the database itself can handle for you. It's called a constraint. Read a few papers about databases in general and how constraints and relations between tables work. There's a lot of documentation about that out there. Have fun! :) Alex --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Q: fetch value of autoincrement column
On Friday 22 May 2009 13.58:34 Alexandre Conrad wrote: Hello Adrian, 2009/5/22 Adrian von Bidder avbid...@fortytwo.ch: On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote: Don't you want that non-null column to be a foreign key ? Would that make a difference? That's what a foreign key is used for: http://en.wikipedia.org/wiki/Foreign_key Oh, thanks a lot. Not really helpful, though. I was quite simply asking if it's possibly to fetch the value of an autoincrement column (in a portable way, if possible) without causing a flush, because at the time when I want to use the value, the row is not complete yet. Oh, well... cheers -- vbi -- featured link: http://www.pool.ntp.org signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Q: fetch value of autoincrement column
Could you please send your SQLAlchemy tables you are working with to have a better idea of what's you want to achieve ? 2009/5/22 Adrian von Bidder avbid...@fortytwo.ch: On Friday 22 May 2009 13.58:34 Alexandre Conrad wrote: Hello Adrian, 2009/5/22 Adrian von Bidder avbid...@fortytwo.ch: On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote: Don't you want that non-null column to be a foreign key ? Would that make a difference? That's what a foreign key is used for: http://en.wikipedia.org/wiki/Foreign_key Oh, thanks a lot. Not really helpful, though. I was quite simply asking if it's possibly to fetch the value of an autoincrement column (in a portable way, if possible) without causing a flush, because at the time when I want to use the value, the row is not complete yet. Oh, well... cheers -- vbi -- featured link: http://www.pool.ntp.org --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
