Re: [sqlalchemy] Column Mixin
Am 26.11.2011 15:26 schrieb Mark Erbaugh m...@microenh.com:
I'm using a ColumnMixin to have a subset of columns common in two tables. Is
there an easy way to populate the common columns in one descendent table with
the corresponding columns in a row of the other descendent tables, and can
this be a method of the ColumnMixin class? Ideally, I'd like the copy method
to dynamically respond to changes in the ColumnMixin class (i.e. if I add a
column defiinition to the mixin, I don't want to have to modify the copy
method).
Thanks,
Mark
On Nov 27, 2011, at 4:06 AM, Robert Forkel wrote:
Hi,
I'm doing something similar and ended up giving all columns contributed by a
mixin a common prefix, and have the copy method loop over all columns of an
object, picking out the right ones.
Regards
Robert
Robert,
Thanks for the reply. I ended up with a different approach, illustrated by the
code snippet:
_sizing_pump_columns = (
('pump', String(6)),
('mod_date', String(19)),
('curve', String(15)),
('eq_gpm', Float),
('eq_psi', Float),
)
current_pump_table = Table('current_pump', Base.metadata,
Column('id', Integer, primary_key=True),
Column('user', String, ForeignKey('user._user')),
*[Column(*i) for i in _sizing_pump_columns]
)
pump_table = Table('pump', Base.metadata,
Column('id', Integer, primary_key=True),
Column('sizing_id', Integer, ForeignKey('sizing.id')),
*[Column(*i) for i in _sizing_pump_columns]
)
class _SizedPumpBase(object):
def copy(self, other):
copy data from other into self
for i in _sizing_pump_columns:
self.__dict__[i[0]] = other.__dict[i[0]]
class SizedPump(_SizedPumpBase):
pass
class SizedPumpCurrent(_SizedPumpBase):
pass
mapper(SizedPumpCurrent, current_pump_table)
mapper(SizedPump, pump_table)
I switched from SA declarative to the separate table and mapper, so I could use
the _sizing_pump_columns tuple for the definition of the current_pump_table and
pump_table objects and use field names in the copy method. As of now, it's
passing my unit tests, but I'd appreciate any comments specifically on
something I might have missed.
Mark
--
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] Column Mixin
I'm using a ColumnMixin to have a subset of columns common in two tables. Is there an easy way to populate the common columns in one descendent table with the corresponding columns in a row of the other descendent tables, and can this be a method of the ColumnMixin class? Ideally, I'd like the copy method to dynamically respond to changes in the ColumnMixin class (i.e. if I add a column defiinition to the mixin, I don't want to have to modify the copy method). Thanks, Mark -- 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] Dynamic data member instrumentation with declarative
I'm trying to use data from a sequence to add columns to a SQLAlchemy table
created declaratively. Here's what I'm doing:
class Sizing(Base):
__tablename__ = 'sizing'
id = Column(Integer, primary_key=True)
[...]
for name in ('column1', 'column2', 'column3', ...):
x = Column(type_=Integer)
x.name = name
Sizing.__table__.append_column(x)
This works as far as creating the table in the database, i.e. viewing the
database shows columns named column1, column2, column3, etc, but code like
getattr(sizing, 'column1') (sizing is an instance of Sizing) is failinging
with a message 'Sizing' object has no attribute 'column1'
While code like:
Sizing.colum1 = Column(Integer)
works, but
Sizing.__dict__['column1'] = Column(Integer)
or
Sizing.__setattr__(Sizing, 'column1', Column(Integer))
fails
The reason I'm trying to use the sequence to create the colums is that data
from the sequence containing the column names is used in other parts of the
application and I'd like to maintain that data in just one place. It's okay if
I have to rebuild the database when the columns in the sequence change.
Thanks,
Mark
--
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] Dynamic data member instrumentation with declarative
On Nov 10, 2011, at 12:57 PM, Michael Bayer wrote:
On Nov 10, 2011, at 9:34 AM, Mark Erbaugh wrote:
I'm trying to use data from a sequence to add columns to a SQLAlchemy table
created declaratively. Here's what I'm doing:
class Sizing(Base):
__tablename__ = 'sizing'
id = Column(Integer, primary_key=True)
[...]
for name in ('column1', 'column2', 'column3', ...):
x = Column(type_=Integer)
x.name = name
Sizing.__table__.append_column(x)
that will just add the column to the table but won't map it. the mapper
isn't aware of this operation.
This works as far as creating the table in the database, i.e. viewing the
database shows columns named column1, column2, column3, etc, but code like
getattr(sizing, 'column1') (sizing is an instance of Sizing) is failinging
with a message 'Sizing' object has no attribute 'column1'
While code like:
Sizing.colum1 = Column(Integer)
works, but
right so that hits the __setattr__ of the DeclarativeMeta class which
receives the Column object, checks it out, and assigns it correctly to the
mapper and table.
Sizing.__dict__['column1'] = Column(Integer)
In general, you should never set attributes this way from the outside, that's
just a Python thing, as you're bypassing whatever attribute set mechanics may
be present on the target object.
or
Sizing.__setattr__(Sizing, 'column1', Column(Integer))
this is not much different as again you're bypassing instrumentation that may
be available on the class. Use the Python setattr() function instead:
setattr(Sizing, name, object).
Thanks so much!
Mark
--
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] select count(*)
On Nov 4, 2011, at 4:54 AM, Stefano Fontanelli wrote:
Il 04/11/11 03.08, Mark Erbaugh ha scritto:
On Nov 3, 2011, at 2:54 PM, Stefano Fontanelli wrote:
Il 03/11/11 19.18, Mark Erbaugh ha scritto:
Using the query object count() method generates a sub-query (as per the
docs). The docs say to use func.count to avoid the subquery. func.count
seems to require a field object. Is there a way to generate a query that
essentially becomes 'select count(*) from table' using the ORM, not the
SQL generator?
Thanks,
Mark
Did you try func.count('*')?
How would you specify the table you want counted? I trued
func.count('table.*') and that didn't work.
Check the manual: http://www.sqlalchemy.org/docs/orm/tutorial.html#counting
To achieve our simple SELECT count(*) FROM table, we can apply it as:
SQL session.query(func.count('*')).select_from(User).scal
ar()
Is that right for you?
Stefano,
Thanks. I missed that in the documentation and it does indeed generate the
expected SQL (at least with SQLite).
Mark
--
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] select count(*)
Using the query object count() method generates a sub-query (as per the docs). The docs say to use func.count to avoid the subquery. func.count seems to require a field object. Is there a way to generate a query that essentially becomes 'select count(*) from table' using the ORM, not the SQL generator? Thanks, Mark -- 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] select count(*)
On Nov 3, 2011, at 2:54 PM, Stefano Fontanelli wrote:
Il 03/11/11 19.18, Mark Erbaugh ha scritto:
Using the query object count() method generates a sub-query (as per the
docs). The docs say to use func.count to avoid the subquery. func.count
seems to require a field object. Is there a way to generate a query that
essentially becomes 'select count(*) from table' using the ORM, not the SQL
generator?
Thanks,
Mark
Did you try func.count('*')?
How would you specify the table you want counted? I trued
func.count('table.*') and that didn't work.
Mark
--
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] select count(*)
On Nov 3, 2011, at 3:31 PM, werner wrote: Mark, On 11/03/2011 07:18 PM, Mark Erbaugh wrote: Using the query object count() method generates a sub-query (as per the docs). The docs say to use func.count to avoid the subquery. func.count seems to require a field object. Is there a way to generate a query that essentially becomes 'select count(*) from table' using the ORM, not the SQL generator? Just the other day I thought I needed the same, initially I just used the id column which all my tables had, but as count(anything) is pretty expensive (using Firebird SQL - so might be different for other dbs) I wanted to find a way without using count(). In my case I needed at some point to get all the id values of that table (to build a virtual listctrl in wxPython), so instead of doing the count and starting feeling the list I got the id and did a len(onresult) to get my count. Point I am trying to make with a lot of words, maybe there is a solution which doesn't need count() at all:-) . I never considered that a count(*) was that expensive especially if there is no where clause. I would think that it would be less expensive than actually retrieving all the rows and counting them. What if there are millions of rows? The result set could fill up memory. In my case, I just need to know how many rows. I don't care about any other details. In one case, I'm checking to see if there are zero rows, in which case, I populate the table with initial rows. In another case, I'm just unittesting some code and I want to make sure that there are the proper number of rows in the table as one of the test conditions. Mark -- 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] count rows in a table
What's the recommended way to count the rows in a table. In SQL, I would
typically use select count(*) from table;
The statement session.query(table).count() issues a count(*) on a sub-query.
The docs say for finer control to use func.count
i.e. session.query(func.count(table.column)). That works, but you do have to
specify a table column.
session.query(func.count('table.*')) also appears to work, but issues a
parameterized query.
Is getting the count (however it's done) and checking for 0 the best way to
check for an empty table?
Mark
--
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] Degenerate relationship?
On Oct 15, 2011, at 10:17 AM, Michael Bayer wrote: On Oct 14, 2011, at 9:45 PM, Mark Erbaugh wrote: There are two tables pump and curve. The curve table has three fields, curve_pn, head and gpm. The design is that the rows with the same curve_pn value represent x,y points (head,gpm) on a pump performance curve. Each row in the pump table has a curve_pn column that links to the performance curve for that pump. The same performance curve can apply to multiple pumps. To me it seems that there is a many-many relationship, yet there is no association table. This design works fine in straight SQL. To model it in SQLAlchemy, do I need to add an association table? For the purposes of this application, the data is read-only, but if it were not, if the data for a curve were to change, I would want it to change for all the pumps that use that curve_pn. SQLAlchemy's rules are more relaxed than relational database rules here, which would definitely require that you use proper foreign keys. In SQLA's case it populates local to remote from A-B as the configuration tells it to, does a join on lookup, and primaryjoin/foreign_keys does what you need: 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] Degenerate relationship?
There are two tables pump and curve. The curve table has three fields, curve_pn, head and gpm. The design is that the rows with the same curve_pn value represent x,y points (head,gpm) on a pump performance curve. Each row in the pump table has a curve_pn column that links to the performance curve for that pump. The same performance curve can apply to multiple pumps. To me it seems that there is a many-many relationship, yet there is no association table. This design works fine in straight SQL. To model it in SQLAlchemy, do I need to add an association table? For the purposes of this application, the data is read-only, but if it were not, if the data for a curve were to change, I would want it to change for all the pumps that use that curve_pn. TIA, Mark -- 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] Get value from field class and instance
Let's say there is a mapped (declaratively, but that shouldn't matter) class, Data, that has fields Data.value1, ... Data.value10. There is also an instance of this class, data that is populated from the data table. Obviously, you can get the values using data.value1, ... But is there a simple way to get a data value using the instance object (data) and a class field (Data.value1). What's the easiest way given data and Data.value1 to get / set that value of data.value1? So far I've come up with: Data.__getattribute__(data, Data.value1.property.columns[0].name) but is there a more direct way? == If you're curious, here's what I'm trying to do. I have an calculation that sums a calculation on all of a particular type of field. If I add a new field of this type to the table, it would be nice if it were automatically included in the calculation. I've created a custom descendent of Column for this type of column. When the constructor of this custom class is called during the table construction, it adds the created field to a list. The calculation should then step through the columns in this list when calculating the value. Thanks, Mark -- 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] Get value from field class and instance
On Sep 6, 2011, at 2:48 PM, Michael Bayer wrote: On Sep 6, 2011, at 2:38 PM, Mark Erbaugh wrote: Let's say there is a mapped (declaratively, but that shouldn't matter) class, Data, that has fields Data.value1, ... Data.value10. There is also an instance of this class, data that is populated from the data table. Obviously, you can get the values using data.value1, ... But is there a simple way to get a data value using the instance object (data) and a class field (Data.value1). What's the easiest way given data and Data.value1 to get / set that value of data.value1? Data.value1 is a Python descriptor, so Data.value1.__get__(data, Data) would do it.Or getattr(data, Data.value1.key) as key is present on the SQLA instrumented attribute. 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.
Re: [sqlalchemy] Repetitive Fields in declarative
On Aug 18, 2011, at 7:01 PM, Mark Erbaugh wrote:
On Aug 18, 2011, at 6:06 PM, Mark Erbaugh wrote:
want to create a table that has several similar fields. For example, assume
the fields are field1, field2, ...
Is there a way in the declarative class that I can do something like:
for i in range(10):
'field%d' % i = Column( ... )
Thanks,
Mark
Figured it out:
after the class definition:
for i in range(10):
class.__table__.append_column(Column('field%d' % i, ...))
I guess not: while the above code adds the fields to the database table, it
doesn't add them as named data members of the class. Here's my latest effort:
class Preferences:
...
for i in range(10):
setattr(Preferences, 'field%d' % i, Column(...
This also answers my question about relationships
setattr(Preferences 'relationship%d' % i, relationship(...
Mark
--
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] Repetitive Fields in declarative
On Aug 19, 2011, at 10:41 AM, Michael Bayer wrote: Id use a mixin so that a superclass can be generated in a data driven manner: MyCols = type(MyCols, (object, ), dict((field%d % i, Column(Integer)) for i in xrange(1, 10))) class MyClass(MyCols, Base): ... otherwise if you want to go the append_column() route, you can just tack them on the class, declarative will call append_column() as well as mapper.add_property(): for name, col in (field%d % i, Column(Integer)) for i in xrange(1, 10)): setattr(MyClass, name, col) Michael, Thanks for the info. Mark -- 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] Handling optional relationship
I have a table that has a foreign key field that is optional. IOW, the current row may be linked to at most one row in the foreign table. If the foreign key field is not NULL, it must point to a valid row in the foreign table, but if it is NULL that means that it it not linked. Is there an automatic way to have the value of the foreign key field set to NULL if the linked row in the foreign table is deleted? Thanks, Mark -- 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] Handling optional relationship
On Aug 19, 2011, at 2:10 PM, Mike Conley wrote:
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
p_id = Column(Integer, ForeignKey(Parent.id))
parent = relation(Parent, backref=backref('children',
cascade=save-update,merge))
sess.add(Parent(children=[Child(),Child()]))
sess.commit()
p = sess.query(Parent).first()
sess.delete(p)
sess.commit()
Mike,
Thanks. that does indeed work. For some reason, it didn't seem to be work in
my schema. I'll have to do some more testing.
Mark
--
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] Repetitive Fields in declarative
I want to create a table that has several similar fields. For example, assume the fields are field1, field2, ... Is there a way in the declarative class that I can do something like: for i in range(10): 'field%d' % i = Column( ... ) Thanks, Mark -- 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] Repetitive Fields in declarative
On Aug 18, 2011, at 6:06 PM, Mark Erbaugh wrote:
want to create a table that has several similar fields. For example, assume
the fields are field1, field2, ...
Is there a way in the declarative class that I can do something like:
for i in range(10):
'field%d' % i = Column( ... )
Thanks,
Mark
Figured it out:
after the class definition:
for i in range(10):
class.__table__.append_column(Column('field%d' % i, ...))
Mark
--
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] Repetitive Fields in declarative
Me again (see below):
On Aug 18, 2011, at 7:01 PM, Mark Erbaugh wrote:
On Aug 18, 2011, at 6:06 PM, Mark Erbaugh wrote:
want to create a table that has several similar fields. For example, assume
the fields are field1, field2, ...
Is there a way in the declarative class that I can do something like:
for i in range(10):
'field%d' % i = Column( ... )
Thanks,
Mark
Figured it out:
after the class definition:
for i in range(10):
class.__table__.append_column(Column('field%d' % i, ...))
Some of the fields that I am adding this way are foreign keys to another table.
Is there a way to specify a relationship based on these foreign key fields?
Mark
--
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] Group / Order by field in relationship?
On Aug 17, 2011, at 10:15 AM, Conor wrote: On 08/17/2011 12:01 AM, Mark Erbaugh wrote: Is it possible to group or order by a field in a many to one related table? class Rental(Base): __tablename__ = 'rental' rental_id = Column(Integer, autoincrement=True, primary_key=True) inventory_id = Column(Integer, ForeignKey(Inventory.inventory_id), nullable=False) inventory = relation(Inventory, uselist=False, backref='rentals', ) class Inventory(Base): __tablename__ = 'inventory' inventory_id = Column(Integer, autoincrement=True, primary_key=True) film_id = Column(Integer, ForeignKey(Film.film_id), nullable=False) film = relation(Film, uselist=False, backref='inventory', ) session.query(Rental).order_by(Rental.inventory.film_id) generates the error: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'film_id' You have to explicitly join to the related table, e.g.: session.query(Rental).join(Rental.inventory).order_by(Inventory.film_id) For bonus points, you can tell SQLAlchemy that Rental.inventory has been eagerloaded. This may reduce the number of lazy loads when you access a Rental instance's inventory: q = session.query(Rental) q = q.join(Rental.inventory) q = q.options(sqlalchemy.orm.contains_eager(Rental.inventory)) q = q.order_by(Inventory.film_id) -Conor Conor, Thanks for the information / confirmation. I had found that the explicit union worked, but I know there's a lot of SA that I don't understand and was concerned I was missing something. Mark -- 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] Group / Order by field in relationship?
Is it possible to group or order by a field in a many to one related table? class Rental(Base): __tablename__ = 'rental' rental_id = Column(Integer, autoincrement=True, primary_key=True) inventory_id = Column(Integer, ForeignKey(Inventory.inventory_id), nullable=False) inventory = relation(Inventory, uselist=False, backref='rentals', ) class Inventory(Base): __tablename__ = 'inventory' inventory_id = Column(Integer, autoincrement=True, primary_key=True) film_id = Column(Integer, ForeignKey(Film.film_id), nullable=False) film = relation(Film, uselist=False, backref='inventory', ) session.query(Rental).order_by(Rental.inventory.film_id) generates the error: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'film_id' Thanks, Mark -- 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] Softcoding .filter(...)
On Aug 12, 2011, at 10:21 AM, RVince wrote: I'm trying to discern a means of creating a .filter(A rel B) where the values for A, rel and B come from an parameters passed in to the web page. I already have an SQLAlchemy statement, say query = Session.query(table).filter(A==B) and I want to be able to allow for a drilldown of sorts by the, such that from the web page they can pick a value from a dropdown, a relation (from a dropdown) and a textbox to compare to. But my problem is once I have these three values, how do I get them into the .filter() function? That's not going to merely accept string values -- is there a way to do this? Thanks, RVince You can build your SQLAlchemy queries dynamically, i.e. q1 = query.Session.query(table).filter(A == B) q2 = q1.filter(C == D) q3 = q2.filter(E == F) you could apply different relationships using conditional Python statements: if rel == 'eq': q4 = q3.filter(G == H) elif rel == 'neq': q4 = q3.filter(G != H) is this what you're looking for? Mark -- 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: Softcoding .filter(...)
On Aug 12, 2011, at 11:52 AM, NiL wrote: say you want to filter on the 'field' (field would be a string representing the name of the field) on objects of class == Klass field_attr = getattr(Klass, field) would give you the instrumented attribute then Session.query(Klass).filter(field_attr == searchString) or Session.query(Klass).filter(field_attr.endswith(searchString)) would run HTH NiL You can also use the class's __dict__ member: field_attr = Klass.__dict__['field'] It really amazes me how Pythonic SQLAlchemy makes database access. Mark -- 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] declarative __table__ columns
Is there a way to access the parameters to the Column() call used to set up a
database table when given either an instance field or class field?
For example:
class MyClass(Base):
...
f1 = Column(Integer, nullable=False, info={'min':0})
...
If I have MyClass.f1 or my_class.f1 (where my_class is an instance of MyClass)
is there a way to get nullable or info?
The only way I've come up with so far is to match the __table__.columns
elements on the name parameter.
Thanks,
Mark
--
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] declarative __table__ columns
On Aug 12, 2011, at 5:26 PM, Michael Bayer wrote:
Is there a way to access the parameters to the Column() call used to set up
a database table when given either an instance field or class field?
For example:
class MyClass(Base):
...
f1 = Column(Integer, nullable=False, info={'min':0})
...
If I have MyClass.f1 or my_class.f1 (where my_class is an instance of
MyClass) is there a way to get nullable or info?
The only way I've come up with so far is to match the __table__.columns
elements on the name parameter.
if you have MyClass.fi, column is MyClass.f1.property.columns[0].
Thanks - just what I was looking for!
Mark
--
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] Default values
On Aug 6, 2011, at 7:18 AM, Mike Conley wrote: You can get to the column default value. class MyTable(Base): __tablename__ = 'table' id = Column(Integer, primary_key=True) name = Column(String, default='new name') def __init__(self, name=None): if name is not None: self.name = name else: self.name = getDefault(self, 'name') def getDefault(instance, colName): col = instance.__table__.c[colName] if col.default is not None: dflt = col.default.arg else: dflt = None return dflt Mike, Thanks. I adapted your code: def __init__(self): for col in self.__table__.c: if col.default is not None: self.__setattr__(col.key, col.default.arg) Mark -- 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] Default values
In a declaratively created table, is there an automatic way to get a new instance of the class object to be populated with values specified in a 'default' clause? i.e. class MyTable(Base): __tablename__ = 'table' name = Column(String, default='new name') ... newRow = MyTable() is there a way to have newRow.name automatically have the value 'new name' before it is committed to the database? The best I've been able to come up with so far is to use a 'CONSTANT' in the default clause and use that same CONSTANT to initialize the field in the class' __init__, but this doesn't seem very DRY. Or, maybe is this the wrong question? Maybe I'm trying to do things the wrong way. I'm trying to use mostly the same code add a new row or edit an existing row. If the user is adding a record, I create a new instance of the class and use the add/edit screen to edit the data. If the user is editing an existing row, I retrieve the row, then use the add/edit screen with it. Thanks, Mark -- 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] Declarative Field Type 'Alias'
In my application, some tables have several fields that need to have the same type and default value, i.e.: field1 = Column(Integer, default=2) field2 = Column(Integer, default=2) ... Is there some way to refactor the Common(Integer, default=2), short of creating a custom column type? I could see the possibility that in a future version of the application, I would want to globally change the column type or default value for all these fields at once. So far, I've come up with creating a function that returns the column. def common_field(): return Column(Integer, default=2) field1 = common_field() field2 = common_field() Is there a better way? Mark -- 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] Default values
On Aug 5, 2011, at 2:00 PM, Stefano Fontanelli wrote: Il 05/08/11 19.29, Mark Erbaugh ha scritto: In a declaratively created table, is there an automatic way to get a new instance of the class object to be populated with values specified in a 'default' clause? i.e. class MyTable(Base): __tablename__ = 'table' name = Column(String, default='new name') ... newRow = MyTable() is there a way to have newRow.name automatically have the value 'new name' before it is committed to the database? The best I've been able to come up with so far is to use a 'CONSTANT' in the default clause and use that same CONSTANT to initialize the field in the class' __init__, but this doesn't seem very DRY. Or, maybe is this the wrong question? Maybe I'm trying to do things the wrong way. I'm trying to use mostly the same code add a new row or edit an existing row. If the user is adding a record, I create a new instance of the class and use the add/edit screen to edit the data. If the user is editing an existing row, I retrieve the row, then use the add/edit screen with it. Thanks, Mark Hi Mark, to fill with defaults you can do: newRow = MyTable() session.add(newRow) session.flush() print newRow.name 'print newRow.name' will display 'new name' To use the same code for create/update I suggest you to use session.merge: http://www.sqlalchemy.org/docs/orm/session.html#merging Stefano, Thanks for the reply. The problem I see with this approach is that I think it actually commits the new row to the database. In the app, it's possible that the user could decide to cancel before inserting the new row. Of course, I could back out the addition, but it seems like it would be better to not insert in the first place. Mark -- 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] Declarative Field Type 'Alias'
On Aug 5, 2011, at 3:51 PM, Michael Bayer wrote: On Aug 5, 2011, at 1:36 PM, Mark Erbaugh wrote: In my application, some tables have several fields that need to have the same type and default value, i.e.: field1 = Column(Integer, default=2) field2 = Column(Integer, default=2) ... Is there some way to refactor the Common(Integer, default=2), short of creating a custom column type? I could see the possibility that in a future version of the application, I would want to globally change the column type or default value for all these fields at once. So far, I've come up with creating a function that returns the column. def common_field(): return Column(Integer, default=2) field1 = common_field() field2 = common_field() Is there a better way? What's the issue with using a function to generate a Column of a certain pre-determined configuration (what are functions in a procedural language for if not this) ? No issue at all. I just wanted to make sure I was doing it 'the right way'. I just noticed that in several places, SA will let you pass in a class or an instance of a class and figures out what to do with it. I thought that something like that might be working here. Actually, there is a small issue with using a function: Where should the function live? Obviously for some schema, this field type is used in multiple tables and belongs in a global namespace, but for others (as in my application), the field type is unique to an individual table. It would be nice if the function could live in the class's namespace. This is more of a Python issue than a SA issue, but I had trouble getting this to work. I did, but the code seems a little awkard to me sigh. In addition to the requirements already, I also wanted toe default value to be a class level 'constant'. The problem, as I see it, is that since the class definition isn't complete, it's namespace isn't avaialble. Since the default value 'constant' is a class data member, it would make sense if the function were a @classmethod, but I couldn't get python to accept: class Table(Base): ... DEFAULT = 2 @classmethod def CustomColumn(cls): return Column(Integer, default=DEFAULT) ... field1 = CustomColumn() Python complained 'classmethod object is not callable' on the last line above. Next I tried changing that line to: field1 = Table.CustomColumn() Now Python complained 'Table' is not defined If I leave the @classmethod decroator off, I couldn't figure out how to reference the class level data DEFAULT. Python complained on the return Column(... line ' global name DEFAULT is not defined. What I finally ended up with that works is: class Table(Base): ... DEFAULT = 2 def CustomColumn(default=DEFAULT): return Column(Integer, default=default) ... field1 = CustomColumn() Mark -- 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] Populate sample data
On Aug 4, 2011, at 9:22 AM, Michael Bayer wrote: The range of speedups here would be between 30% and 80%, with direct usage of connection/session .execute() with Table metadata giving you the 80%. Thanks. I'll look into your suggestions I'm not sure what transaction is in transaction.begin() , if you're using a regular SQLAlchemy Session in it is always in a transaction in that it uses a single connection until rollback() or commit() is called. Originally, I thought transaction was from the standard Python library, but upon research, it looks like it's from the transaction package that is part of Zope. It's included in the Pyramid installation. Mark -- 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] Populate sample data
On Aug 4, 2011, at 9:22 AM, Michael Bayer wrote: On Aug 3, 2011, at 8:38 PM, Mark Erbaugh wrote: I'm using SA (with SQLite) with a schema like: A - B - C - D where - means that the tables have a one to many relationship I'm populating a sample data set where there are 25 rows in A, 25 rows in B for each row in A, 25 rows in C for each row in B and 25 rows in D for each row in C. This results in about 390k rows in D. The database itself is only about 12 MB, but it takes a long time (several minutes) to write the data to the file. I'm taking the approach of appending items to the table's relationship column. for i in range(25): x = A() session.add(A) for j in range(25): y = B() x.b.append(y) for k in range(25): z = C() y.c.append(z) for l in range(25): xx = D() z.d.append(xx) session.flush() Thanks again for the help. I decided to time the various approaches. My original approach took 4:23 (minutes: seconds). Note: all my times included data generation and insertion into a SQLite on-disk database. The biggest speed variable in a mass INSERT operation is whether or not individual cursor.execute() calls, or a small handful of cursor.executemany() calls each with thousands of rows, are used. With the ORM, a mass executemany() is used for INSERT in the case that primary key values are already present in the given objects. If not, the ORM has to INSERT each A() row one at a time, get the new primary key value, and then later populate 25*25 B() object's foreign key with the A.id value; then this goes down to the B()-C() area, etc. So if A(), B(), C(), D() can be created with id=5, id=6, etc., assuming id is the primary key, the ORM should be able to bunch lots of rows together into one cursor.executemany() call and you'll see an immediate, dramatic speedup. This took 3:36 The next level would be if you populated the a_id, b_id, foreign-key-to-parent columns directly instead of using append().If you did a profile on your script you'd see lots of time taken in many places, but all those append() operations would be one of them, as well as lots of event firing and bookkeeping that SQLAlchemy has to do when they occur, both at append() time as well as within the flush() (populating the primary key values to foreign key attributes). This took 2:28 By far the fastest way to do this would be to use session.execute(a_table.insert(), [rows]), session.execute(b_table.insert(), [rows]), etc. That is, assemble the whole set of A, B, C, D, directly in terms of the mapped table, or better yet do it in chunks, perhaps drill down through B, C, D for a single A then insert everything, etc.That way you optimize how these rows are constructed in Python exactly to the pattern that corresponds directly to the database structure, instead of having SQLAlchemy decode the database structure from an object hierarchy. An insertmany is documented at http://www.sqlalchemy.org/docs/core/tutorial.html#executing-multiple-statements. The ORM Session has an execute() method just like Connection does. I did this with one session.execute for each table, rather than chunks. It took 0:46 The range of speedups here would be between 30% and 80%, with direct usage of connection/session .execute() with Table metadata giving you the 80%. Mark -- 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] One to many, but only load one
Table A has a one to many relationship with Table B. There may be zero or more rows in B for each row in A. I would like to have a query that retrieves all the rows in table A joined with the first related row in table B (if one exists). In this case, each row in table B has a DATE field and I want to retrieve the row with the latest date. Is this possible using joinedload? Thanks, Mark -- 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] One to many, but only load one
Thanks,
Could you explain how to do contains_eager with an explicit query(). I tried
putting a query inside a call to contains_eager, but get an error:
ArgumentError: mapper option expects string key or list of attributes
Mark
On Aug 4, 2011, at 6:39 PM, Michael Bayer wrote:
awkardly and inefficiently from a SQL perspective. contains_eager() with an
explicit query() would produce better result
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
import datetime
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
date = Column(Date)
A.latest_b = relationship(B,
primaryjoin=and_(
A.id==B.a_id,
B.date==select([func.max(B.date)]).where(B.a_id==A.id).correlate(A.__table__)
)
)
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
A(bs=[
B(date=datetime.date(2011, 10, 5)),
B(date=datetime.date(2011, 8, 4)),
B(date=datetime.date(2011, 9, 17)),
]),
A(bs=[
B(date=datetime.date(2011, 10, 5)),
B(date=datetime.date(2011, 8, 4)),
B(date=datetime.date(2011, 9, 17)),
]),
])
s.commit()
for obj in s.query(A).options(joinedload(A.latest_b)):
print obj.latest_b
On Aug 4, 2011, at 5:55 PM, Mark Erbaugh wrote:
Table A has a one to many relationship with Table B. There may be zero or
more rows in B for each row in A.
I would like to have a query that retrieves all the rows in table A joined
with the first related row in table B (if one exists). In this case, each
row in table B has a DATE field and I want to retrieve the row with the
latest date. Is this possible using joinedload?
Thanks,
Mark
--
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.
--
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.
--
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] Populate sample data
I'm using SA (with SQLite) with a schema like: A - B - C - D where - means that the tables have a one to many relationship I'm populating a sample data set where there are 25 rows in A, 25 rows in B for each row in A, 25 rows in C for each row in B and 25 rows in D for each row in C. This results in about 390k rows in D. The database itself is only about 12 MB, but it takes a long time (several minutes) to write the data to the file. I'm taking the approach of appending items to the table's relationship column. for i in range(25): x = A() session.add(A) for j in range(25): y = B() x.b.append(y) for k in range(25): z = C() y.c.append(z) for l in range(25): xx = D() z.d.append(xx) session.flush() The bulk of the delay seems to be the session.flush call. I'm using the Pyramid framework which used Python's transaction module. I call transaction.begin() prior to adding the rows. According to the SQLite FAQ, this should speed things up. Are there any suggestions on how to speed things up? Thanks, Mark -- 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] SA Unicode and SQLite
I'm trying to follow the instructions in the SA docs regarding Unicode and SQLite. I've declared all my character fields as either Unicode or UnicodeText. When populating the data, I specify strings as unicode strings (u'string'), but I'm still getting an warning: SAWarning Unicode type received non-unicode bind parameter, when I initially populate the database. On the next line, it reports param.append(processors[key](compiled_params[key])). Is this supposed to be telling me what the errant bind parameter is? I've turned on echo and looking at the queries and parameters, all the character parameters are specified as unicode strings, except for the dates which are given like '2011-08-02'. Are the dates what's causing the non-unicode bind parameter warning? I'm using SQLAlchemy 0.7.1, with Python 2.7.2 Thanks, Mark -- 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] SA Unicode and SQLite
On Aug 2, 2011, at 6:50 PM, Michael Bayer wrote: On Aug 2, 2011, at 6:35 PM, Mark Erbaugh wrote: I'm trying to follow the instructions in the SA docs regarding Unicode and SQLite. I've declared all my character fields as either Unicode or UnicodeText. When populating the data, I specify strings as unicode strings (u'string'), but I'm still getting an warning: SAWarning Unicode type received non-unicode bind parameter, when I initially populate the database. On the next line, it reports param.append(processors[key](compiled_params[key])). Is this supposed to be telling me what the errant bind parameter is? I've turned on echo and looking at the queries and parameters, all the character parameters are specified as unicode strings, except for the dates which are given like '2011-08-02'. Are the dates what's causing the non-unicode bind parameter warning? I'm using SQLAlchemy 0.7.1, with Python 2.7.2 Set the warnings filter to error and send off a stack trace, that will show exactly where the offending statement is (its not impossible that its within SQLA too). import warnings warnings.simplefilter(error) http://docs.python.org/library/warnings.html Thanks, that did the trick. Mark -- 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] Read-Only Database
My program accesses a sqlite database. It only extracts data from the database, it never writes anything to it. It can also be assumed that the database is not updated by other processes. In reality, the database is completely replaced periodically by a new version, but the program can be shut down and re-started whenever that happens. Is there a way to tell SQLAlchemy that the database is read-only, and would that simplify the work that SA does behind the scenes? Thanks, Mark -- 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] Sqlite date field
On Jun 19, 2011, at 10:06 AM, Michael Bayer wrote: Look into using a TypeDecorator around String. process_bind_param() and process_result_value() would coerce the data between string / Python date. http://www.sqlalchemy.org/docs/core/types.html#augmenting-existing-types some examples: http://www.sqlalchemy.org/docs/core/types.html#typedecorator-recipes Michael, Thanks for the tip and the examples. I got a TypeDecorator working. My initial attempts failed as I was trying to descent from a Date class or use impl=types.Date. When I switched to String, things worked better. Mark -- 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] Reflection overhead
On Jun 18, 2011, at 6:37 AM, Tomasz Jezierski - Tefnet wrote: How about: http://code.google.com/p/sqlautocode/ ? Thanks for the pointer. That's just the kind of module I was looking for. -- 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] Reflection overhead
On Jun 18, 2011, at 10:37 AM, Michael Bayer wrote: reflection is not a fast process at all since it aims to be comprehensive. The MetaData, Table and everything related is pickleable for the purpose of apps that want to cache the results of reflection in a file, to be pulled out later. Thanks, I'll look into that. -- 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] Sqlite date field
I have a legacy database where dates are stored in the format mm/dd/ (i.e. 06/18/2011). Is it possible to adapte the Sqlalchemy DATE() type to use this format? If not, is is possible to create a new class to handle this format? Thanks, Mark -- 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] Reflection overhead
Is there overhead associated with reflection? When SA is used in a stateless web server, I think it would have to do the reflection every time a new page is served. Is there a way to create and reuse a snapshot of the reflection results. I guess what I'm asking is if I write manual code that specifies the db layout is setting that up significantly quicker than using reflection, and if so, is there some way to generate manual code from the reflection process? I'm working with an existing database rather than one specifically designed for the application. Thanks, Mark -- 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] Advantage ov SQLAlchemy over plain SQL
On Nov 22, 2010, at 5:38 AM, A. S. wrote: Hi! at the company I work for I'd like to propose introducing Python to replace the hopelessly outdated SAS scripts. SQLAlchemy seems like a good option for SQL handling and I can imagine modules specifically tailored to our needs. However, the other guys are slightly conservative and might object they are fine using simple SQL and SAS. Actually they are not, because even simple tasks take hours of work. And our project work requires solving many ad hoc tasks which our system isn't really able to handle. Now I'd like to point out advantages of the SQLAlchemy approach, but I only vaguely know them. Could you pin point the advantages of using (python classes)/(the sqlalchemy system) for database toolboxes? Something simple, maybe with example ideas that I could present? [I know there is a small paragraph in the documentation, but it only says an ORM couldn't be written otherwise. Unfortunately I don't know well enough why to use an ORM. Actually I was thinking about the expression language.] I don't have any example code, but I have written several Python applications (and one C++ app) using plain SQL and have started work on a new app using SQLAlchemy, so I'll share my experience. Let me add that I spent several years maintaining a moderately sized SQL database and wrote lots of pure SQL, though not necessarily using Python. As my programs accessed SQL, I found myself writing a lot of SQL code to access the data. A lot of this code while not identical, was very similar and seemed redundant. For example, take a simple single table lookup. If you want to do simple CRUD (create, update delete), you have to write at least three separate SQL statements for each table. While the skeleton of these SQL statements are similar, the specific column names and the table name are different. I ended up writing some Python routines that would build the SQL statements if I supplied a list of columns and the table name. But this is what SQLAlchemy does (and much more) so why reinvent the wheel? In the case of my C++ app (I hadn't found a suitable ORM), I ended up writing a Python script to generate SQL statements and C++ code to access the tables. Another advantage is the relative ease with which you can handle changes to the database structure. As I was developing my SA app, I realized that I needed a new column in (at least) one of my tables. I'm using SA's declarative approach and I only had to add the column to my declarative. I didn't have to change any SQL or Python code. One problem I have had with SQLAlchemy is unlearning the way I did things with pure SQL. At it's simplest level, SQLAlchemy can generate the SQL and Python code to access single tables and you might be tempted to write Python code to merge this kind of single table access into larger data graphs, but the true power (IMHO) of SQLAlchemy is that it can handle complex data graphs automatically. Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Table Inheritance
Please refer to the Joined Table Inheritance Section under declarative.ext (I'm using SA 0.5.8). Is is possible to create a Person who is both an Engineer and a Manager using joined table inheritance? IOW, both Manager and Engineer would link to the same row in Person. Thanks, Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] How to separate models into own modules?
On Oct 31, 2010, at 3:08 PM, Daniel Meier wrote:
Hi list
I have tried the examples in the ORM tutorial and I'm wondering how I
can separate different models into own modules.
Let's say I have a BankAccount model and a BankCustomer model. My
idea would be to create two modules, bankaccount.py and
bankcustomer.py, and a database handler that imports the necessary
stuff from that modules.
This is what I came up with: http://pastie.org/1262474
Actually, it doesn't work because the database was not yet initiated
(metadata.create_all). Where should I put that? Or is my idea of
splitting up a bad one?
Daniel
I also like to keep my models separated in separate Python modules, although if
there are closely related tables (such as a header and a detail table), I keep
them in the same module. The trick is to have them all inherit from the same
declarative_base class. I do this by simply having a separate module that
creates that class:
base.py
==
from sqlalchemy.ext.declarative import declarative_base
BASE = declarative_base()
==
I then import BASE into the individual modules that create the models.
I have discovered that when calling create_all(), it's only necessary to import
the model module and the declarative_base class files before doing the import
and create_all() will create all the tables that are imported. This is
convenient for creating temporary databases (I use SQLite in-memory databases
for testing).
create_test_database.py
===
from name import Name
from system import System
from base import BASE
engine = create_engine('sqlite://')
BASE.metadata.create_all(bind=engine)
# populate with test data
In my actual application there are many more tables, but for this instance of
testing, I only need the tables modeled by Name and System.
I hope this helps.
Mark
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Secialists question: how to do implement stock-management
On Oct 29, 2010, at 6:12 AM, Dan @ Austria wrote: Hi, i have a question to database/design specialist. How can (should!) i implement a stock management system in sql-alchemy and python? I get the following data from another system via files - movements: bills from a scanner at a cash deck - movements: from goods-receipt Of course i have also master data on an per article basis. What i want do is keep charge of the stock available in our warehouse. Should i built a warehouse table with [ article / amount of article available ] and update the articles with an update statement like UPDATE warehouse_table SET amount = amount - (bill amount) where article = bill_article ? Would that be a good solution? Is there any literature or reference implementations around? Any hint is much apreciated. Although i have written a couple of database applications, i never had the probleme to change a field (amount field per article) so often. I guess there is a lot of data comming in ... I have a book that I have that gives data models, including those for inventory management is The Data Model Resource Book, Revised Edition, Volume 1 by Len Silverston. The models presented are quite complex though and intended to be refined by the designer by removing complexity that isn't needed for a given application. One beef I have with the book is that even though the book is priced higher than many computer books (about $60 US) at Amazon and comes with a CD-ROM, the schema for the data which is printed in the book is only available from the CD once you purchase a license (about $200, if I recall) to unlock it. The Revised Edition is copyright 2001. More information is at http://silverston.wiley.com/ While you can use SQL statements directly with SQLAlchemy, you should consider using SA methods. For an example of what you suggest with your SQL look for the Correlated Updates section in the SQL Expression Language Tutorial in the SA docs. Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] SQLite / Decimal
How do people handle Decimal data with SA / SQLite? Newer versions of SA give the following warning: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage. I assume that the gist of the message is similar to the reason that decimal.Decimal objects in Python can't be initialized with a float, just int's and string's. I'm working on an accounting application where I would like to use Decimal fields to store monetary amounts. If I understand the warning, I should convert the Python Decimal data and from ints to store in the database. I think I can safely get away with ints if I design things so that every data is stored with the same number of decimal digits. I could also use strings in the database, but that would eliminate the ability to do math operations, such as SUM in the SQL code. Also, is there an single place in the code to to the int / Decimal conversion. Ideally, I would like the application to take advantage of Decimal database objects in databases where they are supported, so it would be nice to have one place in the code that changes depending on which database is in use. Thanks, Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Many to One vs session
I have a table (T) that has a many-to-one relationship (via foreign key inclusion) to a category table (C). Let's name the relationship category. When I retrieve an existing T record, SA populates the category field with an instance of C. I can change the category to a different value by storing a different instance of C into category. My question is does it matter if the various instances of C are associated with the same session as the T instance? Can the C instances come from a different session, or can the be expunge'd from the session that retrieved them? Can I store an entirely new C instance, and if so, will SA do an insert into the C table? Thanks, Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] SQLite / Decimal
On Oct 29, 2010, at 6:18 PM, Michael Bayer wrote: How do people handle Decimal data with SA / SQLite? Newer versions of SA give the following warning: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage. I assume that the gist of the message is similar to the reason that decimal.Decimal objects in Python can't be initialized with a float, just int's and string's. I'm working on an accounting application where I would like to use Decimal fields to store monetary amounts. If I understand the warning, I should convert the Python Decimal data and from ints to store in the database. I think I can safely get away with ints if I design things so that every data is stored with the same number of decimal digits. I could also use strings in the database, but that would eliminate the ability to do math operations, such as SUM in the SQL code. Also, is there an single place in the code to to the int / Decimal conversion. Ideally, I would like the application to take advantage of Decimal database objects in databases where they are supported, so it would be nice to have one place in the code that changes depending on which database is in use. Decimals can actually be initialized with floats in Python 2.7. I looked at their code and i didn't immediately grok it - suffice to say it is significantly more elaborate than just '%f % num'. But anyway, a loss of precision is a given when dealing with native floating points, hence the warning. Storing monetary values as ints using a known exponent on the app side is the standard way to work around the issue. You'd use a TypeDecorator for this: http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorator#sqlalchemy.types.TypeDecorator . Thank you for the pointer on the type decorator. Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Many to One vs session
On Oct 29, 2010, at 9:39 PM, Conor wrote: I have a table (T) that has a many-to-one relationship (via foreign key inclusion) to a category table (C). Let's name the relationship category. When I retrieve an existing T record, SA populates the category field with an instance of C. I can change the category to a different value by storing a different instance of C into category. Technically, SQLAlchemy by default does not populate the category relation until you access it the first time (i.e. it is a lazy load). My question is does it matter if the various instances of C are associated with the same session as the T instance? I'm not sure what you mean here. There is no problem having multiple C instances in the same session as the T instance. SQLAlchemy will complain (hopefully; I haven't tried it) if multiple C instances in the same session share a primary key. Can the C instances come from a different session, or can the be expunge'd from the session that retrieved them? The only restriction is, if the T instance is part of session S, then the C instance that you assign to T.category must be part of S or not be part of any session. It is allowed if you first expunge the C instance from another session and merge it into S before assigning it to T.category. Thanks, that was what I was asking. I think I've tested it with C instances belonging to the same session and to C instances that have been expunged, but I didn't test C instances that were still an active part of a different session. Note that there are only a few use cases for moving instances from one session to another that I am aware of: Caching query results. You expunge the instances from the original session, keep them around in memory (or serialized to disk, memcache, etc.). Then you can merge them back into subsequent sessions to avoid repeated DB queries. Passing objects between threads. Sessions are not thread-safe, so if you want to pass objects from thread A to thread B, you have to merge session A's objects into session B before thread B can use them. Can I store an entirely new C instance, and if so, will SA do an insert into the C table? Yes, this is part of the save-update cascade[1] which is enabled by default on a relationship. Generally you only worry about cascade for one-to-many or many-to-many relationships. At least that's my experience. Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] 'Lookup' Tables
I have a data graph mapped as:
class BatchDetail(BASE):
__tablename__ = TABLE_BATCH_DET
id = Column(Integer, primary_key=True)
batch = Column(ForeignKey(TABLE_BATCH_HDR + '.id'))
account_id = Column(ForeignKey(TABLE_L3_ACCT + '.id'))
# other fields
# -
class BatchHeader(BASE):
__tablename__ = TABLE_BATCH_HDR
id = Column(Integer, primary_key=True)
# other fields
detail = relation('BatchDetail',
backref=backref('header', remote_side=id))
# ---
class L3Acct(BASE):
__tablename__ = TABLE_L3_ACCT
__table_args__ = (UniqueConstraint(l2_id, l3_acct), {})
id = Column(Integer, primary_key=True)
parent = Column(ForeignKey(TABLE_L3_ACCT + '.id'))
l2_id = Column(ForeignKey(TABLE_L2_ACCT + '.id'))
# other fields
Basically, a 'batch' consists of a number of detail lines, each of which has an
associated L3Account. In addition, each L3Acct is a child of a L2Acct (l2_id
foreign key) and may be a child of another L3Acct (parent foreign key).
The application considers the 'batch' (BatchHeader) as the main item. A batch
is created and detail (BatchDetail) rows are added. The L3Acct is used as a
lookup. In my mapping, I haven't set up a relationship between BatchDetail and
L3Acct and I'm maintaining that relationship with code external to the mapping.
Is it possible to let SA manage this relationship?
if so, Here's what I'm having trouble with. When the application needs to add
a new detail record, it can just create a new BatchDetail instance and append
it to the detail collection. I assume that similarly, to associate a L3Acct,
the BatchDetail instance needs to be added to a collection associated with
L3Acct, but where is that collection? And how would one change the association
from one L3Acct to another? Maybe a better way of asking this question is how
to you work with a one-many relationship from the 'many' side?
Thanks,
Mark
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] 'Lookup' Tables
On Oct 27, 2010, at 2:36 PM, Mark Erbaugh wrote:
I have a data graph mapped as:
class BatchDetail(BASE):
__tablename__ = TABLE_BATCH_DET
id = Column(Integer, primary_key=True)
batch = Column(ForeignKey(TABLE_BATCH_HDR + '.id'))
account_id = Column(ForeignKey(TABLE_L3_ACCT + '.id'))
# other fields
# -
class BatchHeader(BASE):
__tablename__ = TABLE_BATCH_HDR
id = Column(Integer, primary_key=True)
# other fields
detail = relation('BatchDetail',
backref=backref('header', remote_side=id))
# ---
class L3Acct(BASE):
__tablename__ = TABLE_L3_ACCT
__table_args__ = (UniqueConstraint(l2_id, l3_acct), {})
id = Column(Integer, primary_key=True)
parent = Column(ForeignKey(TABLE_L3_ACCT + '.id'))
l2_id = Column(ForeignKey(TABLE_L2_ACCT + '.id'))
# other fields
Basically, a 'batch' consists of a number of detail lines, each of which has
an associated L3Account. In addition, each L3Acct is a child of a L2Acct
(l2_id foreign key) and may be a child of another L3Acct (parent foreign key).
The application considers the 'batch' (BatchHeader) as the main item. A batch
is created and detail (BatchDetail) rows are added. The L3Acct is used as a
lookup. In my mapping, I haven't set up a relationship between BatchDetail
and L3Acct and I'm maintaining that relationship with code external to the
mapping. Is it possible to let SA manage this relationship?
if so, Here's what I'm having trouble with. When the application needs to
add a new detail record, it can just create a new BatchDetail instance and
append it to the detail collection. I assume that similarly, to associate a
L3Acct, the BatchDetail instance needs to be added to a collection associated
with L3Acct, but where is that collection? And how would one change the
association from one L3Acct to another? Maybe a better way of asking this
question is how to you work with a one-many relationship from the 'many'
side?
I've done a little reading in the docs and some playing with some unit tests,
and I think I have it figured out.
Mark
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Unique Identifier for newly added child records?
Does SA maintain a usable unique identifier for newly added child records before the data is committed? I have a mapping of a one-many relationship using a foreign key. The detail (many side) records are in an instrumented list. I need to relate the items in this list to rows in an user interface object (a ttk.Treeview object). The detail table has a primary key that is maintained by SA, but until the data graph has been committed to the database, the corresponding fields detail objects are None. I can't just use the position of the detail item in the instrumented list because it is possible that the user can add and delete rows. The Treeview object does not re-use row identifiers for rows that have been deleted, so after the user has added and deleted detail rows, the Treeview rows will not match the instrumented list rows. Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Unique Identifier for newly added child records?
On Oct 25, 2010, at 3:57 PM, Michael Bayer wrote: On Oct 25, 2010, at 2:19 PM, Mark Erbaugh wrote: Does SA maintain a usable unique identifier for newly added child records before the data is committed? I have a mapping of a one-many relationship using a foreign key. The detail (many side) records are in an instrumented list. I need to relate the items in this list to rows in an user interface object (a ttk.Treeview object). The detail table has a primary key that is maintained by SA, but until the data graph has been committed to the database, the corresponding fields detail objects are None. I can't just use the position of the detail item in the instrumented list because it is possible that the user can add and delete rows. The Treeview object does not re-use row identifiers for rows that have been deleted, so after the user has added and deleted detail rows, the Treeview rows will not match the instrumented list rows. the ORM uses Python object identity to maintain associations before foreign key / primary key identifiers are assigned.So you could either associate your user interface objects directly with the related objects, or with their identity via a dictionary, otherwise if you need something that is durable beyond the scope of a single Session you'd need to assign a unique identifier (uuid.uuid4() is a good choice for this) or issue flush() so that primary key ids are available. Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Newest records per category
On Oct 11, 2010, at 7:50 AM, Sebastian Elsner wrote: have one table called 'Assets' with a 'category' (String) and 'created' (DateTime) column. Now I would like to find the records created since a given datetime for each category: This is what I thought would work (with a self-join): session.query(Asset).join(Asset, and_(Asset.category == Asset.category, Asset.created specifiedDateTime) But it does error with 'Cant find any foreign key relationships...' How can I fix this? Or do you have a better idea how to accomplish the task? You probably don't need the self join? You can filter on multiple conditions. session.query(Asset).filter(Asset.category == spefiiedCateogory).filter(Asset.created specifiedDateTime) or the equivalent using the and_ function session.query(Asset).filter(and_(Asset.category == specifiedCategory, Asset.created specifiedDateTime)) Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Batch Delete with ORM
On Oct 5, 2010, at 4:48 AM, Chris Withers wrote: On 04/10/2010 13:16, Mark Erbaugh wrote: If I were doing this in SQL, I would to the first command as SELECT count(*) FROM period WHERE period.cycle = ? Why would you do this first? I wasn't sure why SA was issuing a select realperiod from period where period.cycle = ? before deleting the records. I incorrectly assumed that it was trying to get the count of records to be deleted. Michael Bayer pointed out that cursor.rowcount was used to get that value and that the code in question was used by the session to keep the local (in memory) copy of the data up to date. Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Batch Delete with ORM
I have a table described with: BASE = declarative_base(name=APP_ID) class Period(BASE): __tablename__ = 'period' realperiod = Column(Integer, primary_key=True) cycle = Column(Integer) [more columns] I want to delete all records with a given value for the cycle column. With the SQLAlchemy session in the variable session and the desired cycle in y, I execute q = session.query(Period).filter(Period.cycle==y).delete() This correctly deletes the desired rows and returns the number of rows deleted; however, if I turn echo on I see the following SQL commands: SELECT period.realperiod AS period_realperiod FROM period WHERE period.cycle = ? DELETE FROM period WHERE period.cycle = ? If I were doing this in SQL, I would to the first command as SELECT count(*) FROM period WHERE period.cycle = ? to get the count of rows to be deleted so I am wondering if I am doing things correctly. Thanks, Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] collection_class = set
I'm trying to use a relationship that uses a set as the collection_class by setting the collection_class parameter to set. I chose a set since there should only be one instance corresponding to a give table row since it is enforced by a foreign key on the many side. When initially populating the data, I found that I can add two objects that were instantiated with the same data. This makes sense because by default when Python compares two instances it looks at their object id and since these are two separate objects, there is no duplication. I added __hash__, __cmp__ and __eq__ methods to the class so that instances with the same data compare equal. Is this the correct way to use a set as a collection_class? Thanks, Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Batch Delete with ORM
On Oct 4, 2010, at 5:46 PM, Michael Bayer wrote: On Oct 4, 2010, at 8:16 AM, Mark Erbaugh wrote: I have a table described with: BASE = declarative_base(name=APP_ID) class Period(BASE): __tablename__ = 'period' realperiod = Column(Integer, primary_key=True) cycle = Column(Integer) [more columns] I want to delete all records with a given value for the cycle column. With the SQLAlchemy session in the variable session and the desired cycle in y, I execute q = session.query(Period).filter(Period.cycle==y).delete() This correctly deletes the desired rows and returns the number of rows deleted; however, if I turn echo on I see the following SQL commands: SELECT period.realperiod AS period_realperiod FROM period WHERE period.cycle = ? DELETE FROM period WHERE period.cycle = ? If I were doing this in SQL, I would to the first command as SELECT count(*) FROM period WHERE period.cycle = ? to get the count of rows to be deleted so I am wondering if I am doing things correctly. Most relational databases report the number of rows matched by any UPDATE or DELETE statement that just executed, and SQLA acquires this value via cursor.rowcount on any such statement, so a separate count() call is not required. 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. Thanks. What is the purpose of SELECT period.realperiod AS period_realperiod FROM period WHERE period.cycle = ? that appears to be generated by the session.query ... call? Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Batch Delete with ORM
On Oct 4, 2010, at 8:30 PM, Michael Bayer wrote: On Oct 4, 2010, at 8:06 PM, Mark Erbaugh wrote: Thanks. What is the purpose of SELECT period.realperiod AS period_realperiod FROM period WHERE period.cycle = ? that appears to be generated by the session.query ... call? the delete() and update() methods on Query want to invalidate all objects currently in the session which match the criterion. There are three options for this behavior, and in 0.5 the default is fetch which is what that appears to be doing. I would set it to evaluate so that it only scans through memory instead of hitting the DB, or None so that it doesn't invalidate anything, if you aren't concerned about coming across those objects in the same transaction. That makes sense. Thanks, Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Populating a self-referential table
I have a self-referential table:
class L3Acct(BASE):
__tablename__ = 'l3_acct'
id = Column(Integer, primary_key=True)
parent = Column(ForeignKey('l3_acct.id'))
[]
When adding new rows to the table, the id field is not assigned a value until
the data is actually written to the database. When adding several rows to a
session object is there a way for a new row to reference a row that has
previously been added in the same batch, but hasn't been assigned an id yet?
I'm using SQLAlchemy 0.5.8.
Thanks,
Mark
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Populating a self-referential table
On Sep 28, 2010, at 4:01 PM, Michael Bayer wrote:
On Sep 28, 2010, at 3:28 PM, Mark Erbaugh wrote:
I have a self-referential table:
class L3Acct(BASE):
__tablename__ = 'l3_acct'
id = Column(Integer, primary_key=True)
parent = Column(ForeignKey('l3_acct.id'))
[]
When adding new rows to the table, the id field is not assigned a value
until the data is actually written to the database. When adding several
rows to a session object is there a way for a new row to reference a row
that has previously been added in the same batch, but hasn't been assigned
an id yet?
sure, you use the relation() function at the class level to associate L3Acct
instances with their parent instance, and establish the linkage at the object
level, rather than the pk/fk level.
This would be many-to-one, make sure you follow whats at
http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships
carefully (including the remote_side flag). Those are the 0.6 docs but
configuration for 0.5.8 is the same (except relationship() is relation()).
Michael,
Thanks. I've already used a relation to add child records to a separate table,
I hadn't thought of doing that with the same table. I'm still trying to get my
head out of directly working with SQL and working with the ORM.
Mark
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Expunge
If I retrieve data strictly for reporting or other read-only use, e.g. the session will not be used to update data, should I expunge the objects returned by the query from the session? If I just let the session object go out of scope is that sufficient? Thanks, Mark -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
