Re: [sqlalchemy] milions of expires and types mutability.
2011/6/30 Michael Bayer mike...@zzzcomputing.com 2011/6/30 Michael Bayer mike...@zzzcomputing.com This looks like you have 49000 calls to session.commit(), so, depending on what you're doing, I'd reduce the number of commit calls down to one, after the entire series of insert operations is complete. Transactions should be written to enclose a full series of operations. Hmm i guess so but this is a particular concurrent context where i need those commits. that sounds curious. Do you have multiple processes communicating via information passed in the database ? It's not exactly a communication as they are not really exchanging informations, but it ensure that some operations are done only once even if a machine crashes. -- 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] MutableComposite has no attribute 'coerce'
Hi, I've been trying on the new futures of composite classes of SQLAlchemy 0.7, and I don't have any problems with the read-only default mapping. However, just when I inherit from MutableComposite I start to get an AttributeError exception when I set the value to None (AttributeError: type object 'MutableComposite' has no attribute 'coerce' in /sqlalchemy/ext/mutable.py, line 386, in set). I am running SQLAlchemy version 0.7.1. My mapping and classes are the following: class ContactInformation(object): # this gets changed to MutableComposite def __init__(self, *args, **kwargs): props = ['home_page', 'phone', 'fax', 'nextel'] if len(args) 0: for i in range(len(args)): kwargs[props[i]] = args[i] self.home_page = kwargs.get('home_page', None) self.phone = kwargs.get('phone', None) self.fax = kwargs.get('fax', None) self.nextel = kwargs.get('nextel', None) def __composite_values__(self): return self.home_page, self.phone, self.fax, self.nextel def __setattr__(self, name, value): object.__setattr__(self, name, value) # the following line gets uncommented when changed to MutableComposite #self.changed() def __eq__(self, other): return other is not None and \ str(self.home_page) == str(other.home_page) and \ self.phone == other.phone and \ self.fax == other.fax and \ self.nextel == other.nextel def __ne__(self, other): return not self.__eq__(other) user = Table( 'User', metadata, Column('ID', UUID(as_uuid=True), primary_key=True), Column('HomePage', String(300), nullable=True), Column('Telephone', String(30), nullable=True), Column('Fax', String(30), nullable=True), Column('Nextel', String(30), nullable=True) ) class User(object): def __init__(self, contact): self.id = uuid.uuid1() self.contact = contact # If I remove the properties and synonym the error is still there @property def contact(self): return self._contact @contact.setter def contact(self, value): # Right in this line the error is raised when is set to None self._contact = value orm.mapper(User, user, properties={ 'id': user.c.ID, '_contact': orm.composite(ContactInformation, user.c.HomePage, user.c.Telephone, user.c.Fax, user.c.Nextel), # I get the error with or without the synonym 'contact': orm.synonym('_contact') }) I've simplified the real classes, but the error remains. Anyone has an idea if I'm doing something wrong? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/gL7nOW3E6eYJ. 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: Using custom function expression throws 'TypeError: an integer is required' in orm query
I just tested it and session.execute(query.statement) returns the proper resultset. The 'similarity' functions returns REAL. --- In [13]: query.all() --- TypeError Traceback (most recent call last) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in all(self) 1675 1676 - 1677 return list(self) 1678 1679 @_generative(_no_clauseelement_condition) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in instances(self, cursor, _Query__context) 1916 1917 if filter: - 1918 rows = filter(rows) 1919 1920 if context.refresh_state and self._only_load_props \ /usr/local/lib/python2.7/dist-packages/sqlalchemy/util/ _collections.pyc in unique_list(seq, hashfunc) 594 if not hashfunc: 595 return [x for x in seq -- 596 if x not in seen 597 and not seen.__setitem__(x, True)] 598 else: TypeError: an integer is required # the last value in the row is the similarity value In [14]: session.execute(query).fetchall() Out[14]: [(10581, u'STI', u'STI', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [4-methyl-3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4ccc(cc4)C[NH +]5CC[NH+](CC5)C', 495.619, 37, 29, 8, 0, 0.275862, 2, 2, 0, 41, 8, 5, 4, 0, 0, 88.68, 2.588, 0.241379, True, False, False, False, False, False, False, False, False, False, 1.0), (8099, u'MPZ', u'MPZ', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'C[NH+]1CC[NH+](CC1)Cc2ccc(cc2)C(=O)Nc3(c3)Nc4nccc(n4)c5cccnc5', 481.592, 36, 28, 8, 0, 0.285714, 2, 2, 0, 40, 8, 5, 4, 0, 0, 88.68, 2.292, 0.214286, True, False, False, False, False, False, False, False, False, False, 0.811594202898551), (9593, u'PRC', u'PRC', None, u'N-[4-methyl-3-[[4-(3- pyridyl)pyrimidin-2-yl]amino]phenyl]pyridine-3-carboxamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4cccnc4', 382.418, 29, 22, 7, 0, 0.318182, 0, 0, 0, 32, 6, 4, 4, 0, 0, 92.69, 2.143, 0.0454545, True, False, False, False, False, False, False, False, False, False, 0.691176470588235), (5653, u'G6G', u'G6G', None, u'N-[3-[[3-[4-(4-methoxyanilino)-1,3,5- triazin-2-yl]-2-pyridyl]amino]-4-methyl-phenyl]-4-[(4- methylpiperazin-1-yl)methyl]benzamide', None, None, u'Cc1ccc(cc1Nc2c(cccn2)c3ncnc(n3)Nc4ccc(cc4)OC)NC(=O)c5ccc(cc5)C[NH +]6CC[NH+](CC6)C', 617.743, 46, 35, 11, 0, 0.314286, 2, 2, 0, 51, 11, 6, 5, 0, 0, 122.83, 3.668, 0.228571, True, False, False, False, False, False, False, False, False, False, 0.619047619047619), (1153, u'406', u'406', None, u'4-[[(1R,3R)-3- (dimethylamino)pyrrolidin-1-yl]methyl]-N-[4-methyl-3-[(4-pyrimidin-5- ylpyrimidin-2-yl)amino]phenyl]-3-(trifluoromethyl)benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cncnc3)NC(=O)c4ccc(c(c4)C(F)(F)F)C[N@@H +]5CC[C@@H](C5)[NH+](C)C', 578.631, 42, 30, 12, 3, 0.4, 2, 2, 0, 46, 10, 5, 4, 0, 0, 101.57, 3.187, 0.3, True, False, False, False, False, False, False, False, False, False, 0.526881720430108), (8552, u'NIL', u'NIL', None, u'4-methyl-N-[3-(4-methylimidazol-1- yl)-5-(trifluoromethyl)phenyl]-3-[[4-(3-pyridyl)pyrimidin-2- yl]amino]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)C(=O)Nc4cc(cc(c4)n5cc(nc5)C)C(F)(F)F', 529.516, 39, 28, 11, 3, 0.392857, 0, 0, 0, 43, 8, 5, 5, 0, 0, 97.62, 3.771, 0.107143, True, False, False, False, False, False, False, False, False, False, 0.50561797752809)] On Jun 30, 3:27 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 30, 2011, at 9:23 AM, Adrian wrote: SQAlchemy 0.7.1 / pyscopg 2.2.1 / PostgreSQL 9.0 --- I have a weird problem with orm queries that contain custom functions, in this case from postgres contrib modules. When I do a query like this session.query(Entity, func.similarity(Entity.string, 'querystring')).all() # postgres pg_trgm extension I will get the error below. However, when I specify one or all the columns of the Entity individually it works. It also works if the function is in the .order_by() clause. Any ideas where the problem could come from? this seems like it has to do with the type of object being returned from psycopg2, as the Query runs the rows through a uniquing function that uses sets, maybe a comparison is emitting that TypeError. The stack trace doesn't quite make it clear. It would be interesting to see what session.execute(myquery.statement) sends back in the result rows. (I haven't looked up the SIMILARITY function in the PG docs yet to see what it returns). - /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in all(self) 1675 1676 - 1677 return list(self) 1678 1679
[sqlalchemy] how to make obj copy with all relations?
Hello. How to make a copy of object with all relations? But with new PK value and save? Thanks. -- 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] Problem using ResultProxy
I have a query using join() where my tables have a few hundred thousand records in Postgres, that takes about 30-45 seconds regardless of how the tables are indexed: query = Session.query(SmartdataEligibilityRecord).join(Member).filter(Member.id==SmartdataEligibilityRecord.member_id).order_by(Member.last_name.asc()).all() If I rewrite this as: connection = engine.connect() trans = connection.begin() qs = select * from smartdata_eligibility_records,members where members.id=smartdata_eligibility_records.member_id order by members.last_name asc;; query =connection.execute(qs) connection.close() it flies, taking only a few seconds. My problem is that now the query object is a ResultProxy type. It looks like under the former way I was doing this, using join() it returned tuples. Now I am getting errors such as: TypeError: Sorry, your collection type is not supported by the paginate module. You can provide a list, a tuple, a SQLAlchemy select object or a SQLAlchemy ORM-query object. Is there a way I can casr this ResultProxy obect to a usable object here (like tuples?) Thanks, RVince -- 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] (Newbie)About loading a table only once
Greetings, First, many thanks for creating such an awesome library. Secondly, I'm very new to this and I've a basic question. I've a method that looks like following def test_table_load(self): doctor = Table('TestTable', META, autoload=True, autoload_with=DB) Now while the software runs, this method is called probably 40--50 times. I'm using MySQL and have following questions - So if I call test_table_load() 10 times, SQLAlchemy will load the database table with all records 10 times? - If I want to call test_table_load() 10 times but I want to make sure SQLAlchemy loads 'TestTable' only once in memory should I replace this doctor = Table('Doctor', META, autoload=True, autoload_with=DB) with doctor = Table('Doctor', META, autoload=True, autoload_with=DB, keep_existing=True) If this is not the way to do it then please let me know how can I make sure SQLAlchemy loads my table only once during the complete run of the program? Please enlighten me. I will really appreciate all the help. -- 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] (Newbie)About loading a table only once
Greetings, First, many thanks for creating such an awesome library. Secondly, I'm very new to this and I've a basic question. I've a method that looks like following def test_table_load(self): doctor = Table('TestTable', META, autoload=True, autoload_with=DB) Now while the software runs, this method is called probably 40--50 times. I'm using MySQL and have following questions - So if I call test_table_load() 10 times, SQLAlchemy will load the database table with all records 10 times? - If I want to call test_table_load() 10 times but I want to make sure SQLAlchemy loads 'TestTable' only once in memory should I replace this doctor = Table('Doctor', META, autoload=True, autoload_with=DB) with doctor = Table('Doctor', META, autoload=True, autoload_with=DB, keep_existing=True) If this is not the way to do it then please let me know how can I make sure SQLAlchemy loads my table only once during the complete run of the program? Please enlighten me. I will really appreciate all the help. -- 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] ondelete Cascade function not working in sqlalchemy
Hi, I have described my tables declaratively with foreign key being used at many places. I did set onupdate and ondelete as Cascade but it is not working properly. It does not delete the values in the foreign key column when i delete that entry in primary key column. Even the restrict is also not working. There are some posts regarding this on web with some solutions using relationship. I am new to sqlalchemy and i found those solutions not clear enough. Please suggest clearly a way to sort this out -- 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.
Re: [sqlalchemy] Problem using ResultProxy
On 07/01/2011 08:45 AM, RVince wrote: I have a query using join() where my tables have a few hundred thousand records in Postgres, that takes about 30-45 seconds regardless of how the tables are indexed: query = Session.query(SmartdataEligibilityRecord).join(Member).filter(Member.id==SmartdataEligibilityRecord.member_id).order_by(Member.last_name.asc()).all() If I rewrite this as: connection = engine.connect() trans = connection.begin() qs = select * from smartdata_eligibility_records,members where members.id=smartdata_eligibility_records.member_id order by members.last_name asc;; query =connection.execute(qs) connection.close() it flies, taking only a few seconds. My problem is that now the query object is a ResultProxy type. It looks like under the former way I was doing this, using join() it returned tuples. Now I am getting errors such as: TypeError: Sorry, your collection type is not supported by the paginate module. You can provide a list, a tuple, a SQLAlchemy select object or a SQLAlchemy ORM-query object. Is there a way I can casr this ResultProxy obect to a usable object here (like tuples?) Thanks, RVince In your second example, you have not yet retrieved any rows out of the DBAPI cursor. I believe psycopg2 collects all the rows for you anyway at the execute() step, but this is not guaranteed. A more realistic comparison would be to use .execute().fetchall() instead of just .execute(). Anyway, using fetchall() will give you the rows as a list, which you can then hand off to paginate. However, since you are using pagination, it does not make sense to fetch all the rows anyway: just let paginate modify the query to fetch the correct subset of rows. via ORM: remove the .all(): query = Session.query(SmartdataEligibilityRecord) query = query.join(Member) query = query.filter(Member.id==SmartdataEligibilityRecord.member_id) query = query.order_by(Member.last_name.asc()) via SQL layer (assuming you are using declarative): query = select([SmartdataEligibilityRecord.__table__, Member.__table__]) query = query.where(Member.id == SmartdataEligibilityRecord.member_id) query = query.order_by(Member.last_name.asc()) Note how the above does not use .all() or .execute(), because we will let paginate take care of that. When using paginate, the overhead of ORM should be negligible, so I recommend staying with the ORM in this case. -Conor -- 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.
Re: [sqlalchemy] Re: How to get setter-like behaviour universally across a custom type? (with UTC DateTime examples)
On Jul 1, 2011, at 1:19 AM, Russ wrote: The only thing I'm still unsure of in the code is why mapper.columns is a collection and it required checking columns[0], but I can either just live with that or look into it later. because an attribute can be mapped to multiple columns, i.e. http://www.sqlalchemy.org/docs/orm/mapper_config.html#mapping-a-class-against-multiple-tables . as I did this example and thought back upon how often people want to poke around the mapper configuration, i started trying to think of how to turn the mapper hierarchy into a more of a well described DOM-type of system.In this case one thing I thought of would be to add a first_column accessor to ColumnProperty. -- 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.
Re: [sqlalchemy] Re: Using custom function expression throws 'TypeError: an integer is required' in orm query
does your Entity class have some overridden __eq__(), __cmp__(), __hash__() on it ? I think there might be an issue here but I need a lot more specifics. On Jul 1, 2011, at 6:34 AM, Adrian wrote: I just tested it and session.execute(query.statement) returns the proper resultset. The 'similarity' functions returns REAL. --- In [13]: query.all() --- TypeError Traceback (most recent call last) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in all(self) 1675 1676 - 1677 return list(self) 1678 1679 @_generative(_no_clauseelement_condition) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in instances(self, cursor, _Query__context) 1916 1917 if filter: - 1918 rows = filter(rows) 1919 1920 if context.refresh_state and self._only_load_props \ /usr/local/lib/python2.7/dist-packages/sqlalchemy/util/ _collections.pyc in unique_list(seq, hashfunc) 594 if not hashfunc: 595return [x for x in seq -- 596 if x not in seen 597 and not seen.__setitem__(x, True)] 598 else: TypeError: an integer is required # the last value in the row is the similarity value In [14]: session.execute(query).fetchall() Out[14]: [(10581, u'STI', u'STI', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [4-methyl-3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4ccc(cc4)C[NH +]5CC[NH+](CC5)C', 495.619, 37, 29, 8, 0, 0.275862, 2, 2, 0, 41, 8, 5, 4, 0, 0, 88.68, 2.588, 0.241379, True, False, False, False, False, False, False, False, False, False, 1.0), (8099, u'MPZ', u'MPZ', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'C[NH+]1CC[NH+](CC1)Cc2ccc(cc2)C(=O)Nc3(c3)Nc4nccc(n4)c5cccnc5', 481.592, 36, 28, 8, 0, 0.285714, 2, 2, 0, 40, 8, 5, 4, 0, 0, 88.68, 2.292, 0.214286, True, False, False, False, False, False, False, False, False, False, 0.811594202898551), (9593, u'PRC', u'PRC', None, u'N-[4-methyl-3-[[4-(3- pyridyl)pyrimidin-2-yl]amino]phenyl]pyridine-3-carboxamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4cccnc4', 382.418, 29, 22, 7, 0, 0.318182, 0, 0, 0, 32, 6, 4, 4, 0, 0, 92.69, 2.143, 0.0454545, True, False, False, False, False, False, False, False, False, False, 0.691176470588235), (5653, u'G6G', u'G6G', None, u'N-[3-[[3-[4-(4-methoxyanilino)-1,3,5- triazin-2-yl]-2-pyridyl]amino]-4-methyl-phenyl]-4-[(4- methylpiperazin-1-yl)methyl]benzamide', None, None, u'Cc1ccc(cc1Nc2c(cccn2)c3ncnc(n3)Nc4ccc(cc4)OC)NC(=O)c5ccc(cc5)C[NH +]6CC[NH+](CC6)C', 617.743, 46, 35, 11, 0, 0.314286, 2, 2, 0, 51, 11, 6, 5, 0, 0, 122.83, 3.668, 0.228571, True, False, False, False, False, False, False, False, False, False, 0.619047619047619), (1153, u'406', u'406', None, u'4-[[(1R,3R)-3- (dimethylamino)pyrrolidin-1-yl]methyl]-N-[4-methyl-3-[(4-pyrimidin-5- ylpyrimidin-2-yl)amino]phenyl]-3-(trifluoromethyl)benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cncnc3)NC(=O)c4ccc(c(c4)C(F)(F)F)C[N@@H +]5CC[C@@H](C5)[NH+](C)C', 578.631, 42, 30, 12, 3, 0.4, 2, 2, 0, 46, 10, 5, 4, 0, 0, 101.57, 3.187, 0.3, True, False, False, False, False, False, False, False, False, False, 0.526881720430108), (8552, u'NIL', u'NIL', None, u'4-methyl-N-[3-(4-methylimidazol-1- yl)-5-(trifluoromethyl)phenyl]-3-[[4-(3-pyridyl)pyrimidin-2- yl]amino]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)C(=O)Nc4cc(cc(c4)n5cc(nc5)C)C(F)(F)F', 529.516, 39, 28, 11, 3, 0.392857, 0, 0, 0, 43, 8, 5, 5, 0, 0, 97.62, 3.771, 0.107143, True, False, False, False, False, False, False, False, False, False, 0.50561797752809)] On Jun 30, 3:27 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 30, 2011, at 9:23 AM, Adrian wrote: SQAlchemy 0.7.1 / pyscopg 2.2.1 / PostgreSQL 9.0 --- I have a weird problem with orm queries that contain custom functions, in this case from postgres contrib modules. When I do a query like this session.query(Entity, func.similarity(Entity.string, 'querystring')).all() # postgres pg_trgm extension I will get the error below. However, when I specify one or all the columns of the Entity individually it works. It also works if the function is in the .order_by() clause. Any ideas where the problem could come from? this seems like it has to do with the type of object being returned from psycopg2, as the Query runs the rows through a uniquing function that uses sets, maybe a comparison is emitting that TypeError. The stack trace doesn't quite make it clear. It would be interesting to see what session.execute(myquery.statement) sends back in the result rows. (I haven't looked
Re: [sqlalchemy] (Newbie)About loading a table only once
On Jul 1, 2011, at 8:11 AM, Oltmans wrote: Greetings, First, many thanks for creating such an awesome library. Secondly, I'm very new to this and I've a basic question. I've a method that looks like following def test_table_load(self): doctor = Table('TestTable', META, autoload=True, autoload_with=DB) Now while the software runs, this method is called probably 40--50 times. I'm using MySQL and have following questions - So if I call test_table_load() 10 times, SQLAlchemy will load the database table with all records 10 times? autoload only loads information about the table itself, i.e. the names and types of columns, and constraints. It doesn't load any rows. The above function will in fact emit the autoload operation only once - on subsequent calls, 'TestTable' will be present in META and it is returned as is. That said, you'd be better off declaring doctor just once, somewhere in the module level (or in a function that is called only once) once DB is established: def load_all_tables(DB): global doctor, patient, hospital doctor = Table('doctor', META, autoload=True, autoload_with=DB) patient = Table('patient', META, autoload=True, autoload_with=DB) hospital = Table('hospital', META, autoload=True, autoload_with=DB) # later... DB = create_engine('...') load_all_tables(DB) - If I want to call test_table_load() 10 times but I want to make sure SQLAlchemy loads 'TestTable' only once in memory should I replace this doctor = Table('Doctor', META, autoload=True, autoload_with=DB) with doctor = Table('Doctor', META, autoload=True, autoload_with=DB, keep_existing=True) keep_existing is a flag that indicates if you had other information in Table, i.e. more Column objects or similar, that they should be ignored. Its so that you can say: def _define_table(self): return Table('sometable', metadata, Column('x', Integer), keep_existing=True) and if your application runs in such a way that _define_table() gets called twice, the resulting Table, which remember is the same Table object both times, will only use Column('x') the first time, it wont replace Column('x') with another Column('x'), or in the case of a constraint add a redundant constraint, the second time it is called. -- 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.
Re: [sqlalchemy] MutableComposite has no attribute 'coerce'
On Jul 1, 2011, at 5:10 AM, Arturo Sevilla wrote: Hi, I've been trying on the new futures of composite classes of SQLAlchemy 0.7, and I don't have any problems with the read-only default mapping. However, just when I inherit from MutableComposite I start to get an AttributeError exception when I set the value to None (AttributeError: type object 'MutableComposite' has no attribute 'coerce' in /sqlalchemy/ext/mutable.py, line 386, in set). I am running SQLAlchemy version 0.7.1. My mapping and classes are the following: I've taken the extra steps to add imports plus an example usage to your mapping, and can only reproduce that condition if I assign something that is not a ContactInformation to contact.That error should be improved, but need to know if that is the problem on your end since those details were not sent along. from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.mutable import MutableComposite metadata = MetaData() class ContactInformation(MutableComposite): def __init__(self, *args, **kwargs): props = ['home_page', 'phone', 'fax', 'nextel'] if len(args) 0: for i in range(len(args)): kwargs[props[i]] = args[i] self.home_page = kwargs.get('home_page', None) self.phone = kwargs.get('phone', None) self.fax = kwargs.get('fax', None) self.nextel = kwargs.get('nextel', None) def __composite_values__(self): return self.home_page, self.phone, self.fax, self.nextel def __setattr__(self, name, value): object.__setattr__(self, name, value) # the following line gets uncommented when changed to MutableComposite #self.changed() def __eq__(self, other): return other is not None and \ str(self.home_page) == str(other.home_page) and \ self.phone == other.phone and \ self.fax == other.fax and \ self.nextel == other.nextel def __ne__(self, other): return not self.__eq__(other) user = Table( 'User', metadata, Column('ID', Integer, primary_key=True), Column('HomePage', String(300), nullable=True), Column('Telephone', String(30), nullable=True), Column('Fax', String(30), nullable=True), Column('Nextel', String(30), nullable=True) ) class User(object): def __init__(self, contact): self.contact = contact mapper(User, user, properties={ 'id': user.c.ID, 'contact': composite(ContactInformation, user.c.HomePage, user.c.Telephone, user.c.Fax, user.c.Nextel), }) e = create_engine('sqlite://') metadata.create_all(e) s = Session(e) u = User( ContactInformation(home_page='asdf', phone='asdf', fax='asfd', nextel='asdf') ) u.contact.phone = 'asdfas' s.add(u) s.commit() u = s.query(User).first() u.contact.home_page='asdfdasf' s.commit() -- 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.
Re: [sqlalchemy] MutableComposite has no attribute 'coerce'
Hi thanks, for answering. Yes I'm aware that setting it to a non-ContactInformation object will set off the coerce. However, if you put None to it also sets off the error, something which does not happen when you just have a non-mutable composite property. Should this be correct behavior? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/uJ3M5ClWIK4J. 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.
Re: [sqlalchemy] MutableComposite has no attribute 'coerce'
try it out, None is handled On Jul 1, 2011, at 11:40 AM, Arturo Sevilla wrote: Hi thanks, for answering. Yes I'm aware that setting it to a non-ContactInformation object will set off the coerce. However, if you put None to it also sets off the error, something which does not happen when you just have a non-mutable composite property. Should this be correct behavior? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/uJ3M5ClWIK4J. 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. -- 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: Problem using ResultProxy
Brilliant! Thank you Connor. -- 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.
Re: [sqlalchemy] MutableComposite has no attribute 'coerce'
Do you mean in 0.7.1 or in the snapshot you put here? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/cT683Za9q90J. 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.
Re: [sqlalchemy] MutableComposite has no attribute 'coerce'
the snapshot I linked.Latest rev moves the coerce() method up to the base type where None / incorrect type are distinctly handled. a value of None translates to MyComposite(x=None, y=None, ..) On Jul 1, 2011, at 12:40 PM, Arturo Sevilla wrote: Do you mean in 0.7.1 or in the snapshot you put here? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/cT683Za9q90J. 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. -- 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] problem with with_only_columns
I'm using sqlalchemy 0.7.1 on openSUSE 11.4 (which has python2.7) but the problem was also observed with older versions of python. Basically, with_only_columns seems to lose information about the columns: import sqlalchemy as sa e = sa.create_engine( 'sqlite:///' ) conn = e.connect() m = sa.MetaData() m.bind = conn t = sa.Table('a', m, sa.Column('a', sa.String()) ) t.create() s1 = sa.select( [t.c.a, sa.func.length(t.c.a), t.c.a * 2 ] ) cols = [ c for c in s1.c ] s2 = s1.with_only_columns( cols ) print s1 print s2 conn.execute(s1) conn.execute(s2) -- Jon -- 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.
Re: [sqlalchemy] problem with with_only_columns
ohwell it was intended to be used this way : s2 = s1.with_only_columns( [t.c.a, sa.func.length(t.c.a)] ) but I can see how you might think it works the way you're doing itbut i think that behavior would still be incorrect, i would think s1.with_only_columns([s1.c.foo, s1.c.bar]) would create a SELECT from itself, the way orm.query.from_self() does...but that's out of the scope of what with_only_columns() was going for. Its just going for, make the select() as though you said select([a, b, c]) instead of select([a, b, c, d, e, f]). On Jul 1, 2011, at 2:33 PM, Jon Nelson wrote: import sqlalchemy as sa e = sa.create_engine( 'sqlite:///' ) conn = e.connect() m = sa.MetaData() m.bind = conn t = sa.Table('a', m, sa.Column('a', sa.String()) ) t.create() s1 = sa.select( [t.c.a, sa.func.length(t.c.a), t.c.a * 2 ] ) cols = [ c for c in s1.c ] s2 = s1.with_only_columns( cols ) print s1 print s2 conn.execute(s1) conn.execute(s2) -- 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.
Re: [sqlalchemy] MutableComposite has no attribute 'coerce'
Great! It worked, I was going to suggest to impolemente coerce() in MutableComposite, but that's just what the snapshot has :) Do you know in which minor version should I expect to see this patch? Because while I can test it and make it work for my machine it would be better to have the dependency checked through setuptools for this project. Thanks again. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/0COZDkCM07oJ. 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.
Re: [sqlalchemy] MutableComposite has no attribute 'coerce'
Nevermind I just realized that it says 0.7.2 in the CHANGE file. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ri_r-8ywDlkJ. 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] Basic doubt regarding Foreign Key referenced columns
Hi all, I am new to sqlalchemy and i have a very basic doubt If i specify multiple columns as foreign key using the foreign key constraint, is it necessary that atleast one of the referenced columns must be unique. I tried by specifying the unique constraint on the referenced columns making the combination of those columns unique but it doesnt work. -- 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.
Re: [sqlalchemy] Basic doubt regarding Foreign Key referenced columns
On Jul 1, 2011, at 4:12 PM, Kartik Lakhotia wrote: Hi all, I am new to sqlalchemy and i have a very basic doubt If i specify multiple columns as foreign key using the foreign key constraint, is it necessary that atleast one of the referenced columns must be unique. I tried by specifying the unique constraint on the referenced columns making the combination of those columns unique but it doesnt work. This is a limitation of relational databases. Foreign keys are intended to reference the primary key of a table in most cases, and as an occasional fallback a column that has a UNIQUE constraint can be referenced, but this is not a common practice. From http://en.wikipedia.org/wiki/Foreign_key The foreign key identifies a column or set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must reference the columns of the primary key or other superkey in the referenced table. Note that if your table has a composite (multicolumn) primary key, the foreign key from another table must be created as a composite ForeignKeyConstraint(), as in the example at http://www.sqlalchemy.org/docs/core/schema.html#metadata-constraints . -- 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.
Re: [sqlalchemy] MutableComposite has no attribute 'coerce'
My only doubt now is whether you can query over the fields of the composite attribute. In the documentation an example is made of a query with a comparison of the whole data structure: session.query(Vertex).filter(Vertex.start == Point(3, 4)) But it also says: As of SQLAlchemy 0.7, composites have been simplified such that they no longer “conceal” the underlying column based attributes Does this mean that you can do a query like this: session.query(Vertex).filter(Vertex.start.x == 3) Does this work or is there a way to do this or do I have to query using the corresponding column definitions of the table object? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/TUTlybahglQJ. 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: Full-fledged objects as mapped attributes?
Awesome! That is exactly what I needed - thanks very much! On Jun 30, 8:06 am, Michael Bayer mike...@zzzcomputing.com wrote: your question has arrived at the same time almost the exact same question is coming from another user Russ, so I've added an example of how to use attribute events in conjunction with a TypeDecorator, so that the data is coerced both at the database level, as well as at the attribute setter level - this is athttp://www.sqlalchemy.org/trac/wiki/UsageRecipes/ValidateAllOccurrenc On Jun 29, 2011, at 9:40 PM, Jason Denning wrote: Hi All, I am building a Pyramid app using SQLAlchemy for the model, and I would like to be able to use the attributes as full-fledged objects (i.e., I would like to be able to define methods attached to the attributes), ideal example usage (although this is a somewhat contrived example): class Phone(Base): id = Column(Integer, primary_key=True) phone = Column(MyPhoneType) p1 = Phone(phone=9995551212) session.add(p1) p1.phone '9995551212' p1.phone.prettyPrint() (999) 555-1212 p1.phone.foo() Now doing foo... I have made some attempts towards this end using TypeDecorator and by trying to extend UserDefinedType, but it seems that the instantiated objects always have their mapped attributes converted into regular python types (string, unicode, etc..) losing whatever methods/class level stuff I try to define. I am not trying to affect any aspect of the descriptor protocol, object management, database interaction or any other ORM type stuff - I just want some helper methods, and maybe some instance-level attributes to be available for certain types of mapped attributes. Is this possible? Should I be doing this using TypeDecorator / UserDefinedType / other ? Is anyone else attempting this type of thing, or am I totally crazy? Thanks, Jason -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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.
Re: [sqlalchemy] MutableComposite has no attribute 'coerce'
On Jul 1, 2011, at 4:56 PM, Arturo Sevilla wrote: My only doubt now is whether you can query over the fields of the composite attribute. In the documentation an example is made of a query with a comparison of the whole data structure: session.query(Vertex).filter(Vertex.start == Point(3, 4)) But it also says: As of SQLAlchemy 0.7, composites have been simplified such that they no longer “conceal” the underlying column based attributes Does this mean that you can do a query like this: session.query(Vertex).filter(Vertex.start.x == 3) Does this work or is there a way to do this or do I have to query using the corresponding column definitions of the table object? Well, there is a way to do that which is to create descriptors on Vertex for each of start, end, there is no instrumentation for these right now and an instance of Vertex stores start and end using Python's default attribute mechanisms. The descriptors would then link up to the columns back on the parent at the class level to produce SQL expression behavior.It's definitely doable and could be a behavior built into SQLA. The change in 0.7 refers to the corresponding start and end columns of the parent table. So in your example you can query with User.HomePage and User.Telephone directly. Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/TUTlybahglQJ. 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. -- 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] onupdate not working with postgresql
I have specified a column in my table as -- 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: onupdate not working with postgresql
Sorry, i left a half finished message by mistake. I have described a column as : updated = Column(DateTime, onupdate = datetime.datetime.now()) But it leaves the field value null when any row is updated. I have also tried using func.current_Timestamp but that doesnt work either. -- 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.
Re: [sqlalchemy] Basic doubt regarding Foreign Key referenced columns
thanks a lot Michael -- 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: onupdate not working with postgresql
I too faced a similar problem. Spent a lot of time just to figure out that - *These update functions do not work when you try to update the table directly from database. *And actually they should not, as postgres does not provide such feature in its database When I tried updating from sqlalchemy session - things worked for me. SQLalchemy has already implemented the required triggers for auto updation. May be its the same problem for you. * * -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/LKHHqIgqTdQJ. 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.