[sqlalchemy] Create a list out of multiple columns
Hi,
I got no idea, whether this is possible with SA (version 0.6.4), but I
want to map multiple columns as one list into the resulting object.
I have the following code:
location_table = Table('loc_location', metadata,
Column('LOC_ID', Integer, primary_key=True),
Column('LOC_L1', Integer, key='layer1'),
Column('LOC_L2', Integer, key='layer2'),
Column('LOC_L3', Integer, key='layer3'),
Column('LOC_L4', Integer, key='layer4'),
Column('LOC_L5', Integer, key='layer5'),
Column('A_Name', String(100), key='name'),
)
class Location(object):
def __repr__(self):
return u%s % self.name
if i then try to do the following:
mapper(Location, location_table, properties={
'hirachy': column_property([
location_table.c.layer1,
location_table.c.layer2,
])
})
It tells me: AttributeError: 'list' object has no attribute 'label'
I also tried to use a select, but it then tells me:
sqlalchemy.exc.OperationalError: (OperationalError) 1241: Operand
should contain 1 column(s)
Is there any way to achieve that? It would be great, if SA then bushes
back the changes to the list to the database, too.
bye
--
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 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? Chris -- 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] Simple Join failing
Hi All
I have what I hope is a very simple question;
Just started experimenting with joins, so I tried a very basic test and got
a fail that I don't understand. It appears that SA is creating bad SQL, but
I'm sure it's something I'm missing.. Here's what I did;
I have two tables. products and product_prices. There is a one to many
relationship based on Foreign Keys of Group and Code Both tables have
columns Group and Code and they are also the primary of each.
I do this;
e = an Engine (MySQL connector)
m = MetaData(e)
prod = Table('products', m, autoload=True)
price = Table('product_prices, m, autoload=True
# These tables are both fine and load correctly
# I want to build up my query generatively, so..
# Note that I'm selecting specific columns, and both sets of Foreign Keys
are in the selected columns (not that I believe I should need to do that)
q = prod.select().with_only_columns(['products.Group', 'products.Code',
'product_prices.Group', 'product_prices.Code', 'product_prices.ListPriceEx',
'product_prices.ListPriceInc'])
q = q.join(price)
# I get this error;
ArgumentError: Can't find any foreign key relationships between 'Select
object' and 'product_prices'.(They do exists BTW)
So, I remove my .*with_only_columns* and try again
q = prod.select()
q = q.join(price)
# OK - no errors so far.. BUT...
print q1 gives me this;
*(*SELECT products.`Group` AS `Group`, products.`Code` AS `Code`,
products.`Description` AS `Description`,
...lots of other columns removed for your viewing pleasure...
FROM products*)* INNER JOIN product_prices ON `Group` =
product_prices.`Group` AND `Code` = product_prices.`Code`
Note the ( ) around the (SELECT . products)
When I execute that query it fails and gives me this;
Traceback (most recent call last):
File interactive input, line 1, in module
File string, line 1, in lambda
File C:\Python26\lib\site-packages\sqlalchemy\util.py, line 1780, in
warned
return fn(*args, **kwargs)
File C:\Python26\lib\site-packages\sqlalchemy\sql\expression.py, line
1290, in execute
return e._execute_clauseelement(self, multiparams, params)
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1798,
in _execute_clauseelement
return connection._execute_clauseelement(elem, multiparams, params)
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1272,
in _execute_clauseelement
parameters=params
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1380,
in __create_execution_context
connection=self, **kwargs)
File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line
342, in __init__
raise exc.*ArgumentError(Not an executable clause: %s % compiled)*
So, I have two basic questions;
1) Why did the first error occur with the FK's simply because I limited the
columns returned?
2) What's wrong with the join I'm doing and why is SA creating SQL that it
can't use?
I'm guessing I'm doing SOMETHING wrong, so the goal here is simple -This is
what I WANT to do;
Create a query that allows me to dictate the columns returned from a join of
two or more tables giving me a result set with access to all the columns
I've nominated.
I've looked hard in the docs and Google, and I guess everyone thinks this is
too basic to actually SHOW you how to do it! ;-)I took the ( ) out with
a cut and paste and dropped the SQL into an e.execute('SELECT.) and it
worked fine, so it's really close it just appears to be adding erroneous
brackets.
I'm running 0.6.4 BTW.
Thanking you in advance.
Cheers
Warwick
--
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] MySQL DATE_ADD function
On 04/10/2010 22:53, Bryan wrote: I'm having trouble converting this SQL into an ORM statement. DATE_ADD(datecol, INTERVAL(1 - DAYOFWEEK(datecol)) DAY) This is as far as I can get, which is basically nowhere. The second argument to date_add requires literal strings INTERVAL and DAY, but I also need to insert a function in the middle there. Any help is appreciated. func.date_add(Class.dateAttr, INTERVAL(1 - DAYOFWEEK(Class.dateAttr)) DAY) Are you looking for something database agnostic or something that just works for MySQL? If the latter, look at text: http://www.sqlalchemy.org/docs/core/tutorial.html#using-text If the former, then you'll want a database agnostic implementation. So, what's the above sql actually trying to achieve? Chris -- 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
Hi All Just my 2c; The original question was why is SA doing the select before it does the delete? and then the comment was added that he would have done a simple count instead.. It appears that he was not aware that the DELETE could return the count as well (indirectly) so in actual fact, NEITHER the SELECT count OR the SELECT that SA inserts in front of the DELETE appear to be required. So, back to the thread of the question - Why is SA doing the query that it does BEFORE it does the DELETE? It's purpose is not obvious. Hope that helps!? :-) On 5 October 2010 18:48, Chris Withers ch...@simplistix.co.uk 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? Chris -- 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.comsqlalchemy%2bunsubscr...@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 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] Simple Join failing
Warwick Prince wrote:
Hi All
I have what I hope is a very simple question;
Just started experimenting with joins, so I tried a very basic test
and got a fail that I don't understand. It appears that SA is
creating bad SQL, but I'm sure it's something I'm missing.. Here's
what I did;
I have two tables. products and product_prices. There is a one to
many relationship based on Foreign Keys of Group and Code Both
tables have columns Group and Code and they are also the primary of
each.
I do this;
e = an Engine (MySQL connector)
m = MetaData(e)
prod = Table('products', m, autoload=True)
price = Table('product_prices, m, autoload=True
# These tables are both fine and load correctly
# I want to build up my query generatively, so..
# Note that I'm selecting specific columns, and both sets of Foreign
Keys are in the selected columns (not that I believe I should need to
do that)
q = prod.select().with_only_columns(['products.Group',
'products.Code', 'product_prices.Group', 'product_prices.Code',
'product_prices.ListPriceEx', 'product_prices.ListPriceInc'])
q = q.join(price)
# I get this error;
ArgumentError: Can't find any foreign key relationships between
'Select object' and 'product_prices'.(They do exists BTW)
So, I remove my .with_only_columns and try again
q = prod.select()
Here you are creating a Select object (ie SELECT all columns FROM
products)
q = q.join(price)
Now you are joining that Select object with another table
ie. (SELECT all columns FROM products) JOIN price ON join condition
The extra parentheses are there because you are joining a SELECT with a
table.
Instead, you want to join the tables together:
prod.join(price)
To select from that, you can use the standalone select function:
http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.e
xpression.select
eg.
select([products.c.Group, products.c.Code, price.c.ListPriceEx],
from_obj=[prod.join(price)])
Hope that helps,
Simon
--
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] Simple Join failing
Hi Simon
Thanks for that - I knew it was something wrong with the approach but simply
could not pick it!Back to the test bench for another go :-)
Cheers
Warwick
P.S. OK - I have to ask - when and how (why?) do I do the .join on the
query? ;-)
On 5 October 2010 19:41, King Simon-NFHD78 simon.k...@motorola.com wrote:
Warwick Prince wrote:
Hi All
I have what I hope is a very simple question;
Just started experimenting with joins, so I tried a very basic test
and got a fail that I don't understand. It appears that SA is
creating bad SQL, but I'm sure it's something I'm missing.. Here's
what I did;
I have two tables. products and product_prices. There is a one to
many relationship based on Foreign Keys of Group and Code Both
tables have columns Group and Code and they are also the primary of
each.
I do this;
e = an Engine (MySQL connector)
m = MetaData(e)
prod = Table('products', m, autoload=True)
price = Table('product_prices, m, autoload=True
# These tables are both fine and load correctly
# I want to build up my query generatively, so..
# Note that I'm selecting specific columns, and both sets of Foreign
Keys are in the selected columns (not that I believe I should need to
do that)
q = prod.select().with_only_columns(['products.Group',
'products.Code', 'product_prices.Group', 'product_prices.Code',
'product_prices.ListPriceEx', 'product_prices.ListPriceInc'])
q = q.join(price)
# I get this error;
ArgumentError: Can't find any foreign key relationships between
'Select object' and 'product_prices'.(They do exists BTW)
So, I remove my .with_only_columns and try again
q = prod.select()
Here you are creating a Select object (ie SELECT all columns FROM
products)
q = q.join(price)
Now you are joining that Select object with another table
ie. (SELECT all columns FROM products) JOIN price ON join condition
The extra parentheses are there because you are joining a SELECT with a
table.
Instead, you want to join the tables together:
prod.join(price)
To select from that, you can use the standalone select function:
http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.e
xpression.select
eg.
select([products.c.Group, products.c.Code, price.c.ListPriceEx],
from_obj=[prod.join(price)])
Hope that helps,
Simon
--
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.comsqlalchemy%2bunsubscr...@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 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] Simple Join failing
Warwick Prince wrote: Hi Simon Thanks for that - I knew it was something wrong with the approach but simply could not pick it!Back to the test bench for another go :-) Cheers Warwick P.S. OK - I have to ask - when and how (why?) do I do the .join on the query? ;-) In SQL, you can treat a query just like a table, so you can join 2 queries together, or join a query to another table. For example: SELECT * FROM (SELECT a, b FROM table_1) as q1 INNER JOIN (SELECT c, d FROM table_2) as q2 ON q1.b = q2.c That example is not very helpful - it could easily be rewritten as a single SELECT, but I hope you see that the subqueries can be as complicated as you like. The object that you were originally producing with your 'q.join(price)' wasn't a Select object, but a Join - something that you can select from. You could write something like this: # JOIN the price table with a query on the products table: j = prod.select().join(price) # SELECT from that JOIN: q = select(some_columns, from_obj=[j]) This almost certainly isn't what you wanted in your situation, but there are plenty of cases where subqueries are very useful. Hope that helps, Simon -- 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] Simple Join failing
Hi Simon Thanks for your help. It's amazing what a tiny hint in the right direction can do.. Between these emails, I've made a proof on concept, and am now implementing the code in the real app. So easy when I'm not blocked by a warped vision of what I'm doing. Funny, looking back at the docs I can now clearly see TABLE.join T A B L E.join, not query.join...I'm SURE that wasn't there before ;-D Thanks again. Cheers Warwick On 5 October 2010 20:39, King Simon-NFHD78 simon.k...@motorola.com wrote: Warwick Prince wrote: Hi Simon Thanks for that - I knew it was something wrong with the approach but simply could not pick it!Back to the test bench for another go :-) Cheers Warwick P.S. OK - I have to ask - when and how (why?) do I do the .join on the query? ;-) In SQL, you can treat a query just like a table, so you can join 2 queries together, or join a query to another table. For example: SELECT * FROM (SELECT a, b FROM table_1) as q1 INNER JOIN (SELECT c, d FROM table_2) as q2 ON q1.b = q2.c That example is not very helpful - it could easily be rewritten as a single SELECT, but I hope you see that the subqueries can be as complicated as you like. The object that you were originally producing with your 'q.join(price)' wasn't a Select object, but a Join - something that you can select from. You could write something like this: # JOIN the price table with a query on the products table: j = prod.select().join(price) # SELECT from that JOIN: q = select(some_columns, from_obj=[j]) This almost certainly isn't what you wanted in your situation, but there are plenty of cases where subqueries are very useful. Hope that helps, Simon -- 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.comsqlalchemy%2bunsubscr...@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 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] eager/joined loading a whole graph
Hi All, I have also created a post for this question on StackOverflow: http://stackoverflow.com/questions/3863508/joinedload-eager-loading-whole-sub-graphs-in-sqlalchemy Let's say I have a Task object which can be dependent on other Tasks. Is there a way to sensibly eager/joinedload all of a given set of task's subtasks? Example code: class Task(DeclarativeBase): __tablename__ = 'task' task_id = Column(Integer, primary_key=True) name = Column(String, unique=True) def add_dependencies(self, *tasks): for task in tasks: TaskDependency(dependent=self, dependency=task) return self @property def dependencies(self): return [x.dependency for x in self.dependency_edges] @dependencies.setter def dependencies(self, what): Note: adds dependencies, doesn't remove them self.add_dependencies(*what) @property def dependents(self): return [x.dependent for x in self.dependent_edges] class TaskDependency(DeclarativeBase): __tablename__ = 'task_dependency' dependent_id = Column(Integer, ForeignKey(Task.task_id), primary_key=True) dependency_id = Column(Integer, ForeignKey(Task.task_id), primary_key=True) dependent = relationship(Task, primaryjoin=dependent_id == Task.task_id, backref='dependent_edges') dependency = relationship(Task, primaryjoin=dependency_id == Task.task_id, backref='dependency_edges') def example_task_maker(): make_cheese = Task( name=MAKE_CHEESE, dependencies=[ Task(name=MILK_COWS, dependencies=[ Task(name=BUY_COWS) ]), ] ) def load_task() # How to eagerly load the whole task tree here? DBSession.query(Task).filter(name=MAKE_CHEESE).all() Thanks in advance, - Peter -- 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] Re: eager/joined loading a whole graph
Ah. To answer my own question, what I was actually after was an adjacency list, of which there are examples to be found here, which I missed: http://www.sqlalchemy.org/docs/05/mappers.html#adjacency-list-relationships I believe this will do what I was after. Sorry for the noise, and thanks anyway. - Peter On 5 October 2010 14:39, Peter Waller peter.wal...@cern.ch wrote: Hi All, I have also created a post for this question on StackOverflow: http://stackoverflow.com/questions/3863508/joinedload-eager-loading-whole-sub-graphs-in-sqlalchemy Let's say I have a Task object which can be dependent on other Tasks. Is there a way to sensibly eager/joinedload all of a given set of task's subtasks? Example code: class Task(DeclarativeBase): __tablename__ = 'task' task_id = Column(Integer, primary_key=True) name = Column(String, unique=True) def add_dependencies(self, *tasks): for task in tasks: TaskDependency(dependent=self, dependency=task) return self @property def dependencies(self): return [x.dependency for x in self.dependency_edges] @dependencies.setter def dependencies(self, what): Note: adds dependencies, doesn't remove them self.add_dependencies(*what) @property def dependents(self): return [x.dependent for x in self.dependent_edges] class TaskDependency(DeclarativeBase): __tablename__ = 'task_dependency' dependent_id = Column(Integer, ForeignKey(Task.task_id), primary_key=True) dependency_id = Column(Integer, ForeignKey(Task.task_id), primary_key=True) dependent = relationship(Task, primaryjoin=dependent_id == Task.task_id, backref='dependent_edges') dependency = relationship(Task, primaryjoin=dependency_id == Task.task_id, backref='dependency_edges') def example_task_maker(): make_cheese = Task( name=MAKE_CHEESE, dependencies=[ Task(name=MILK_COWS, dependencies=[ Task(name=BUY_COWS) ]), ] ) def load_task() # How to eagerly load the whole task tree here? DBSession.query(Task).filter(name=MAKE_CHEESE).all() Thanks in advance, - Peter -- 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] MySQL DATE_ADD function
On Tue, Oct 5, 2010 at 1:58 AM, Chris Withers ch...@simplistix.co.uk wrote:
Are you looking for something database agnostic or something that just works
for MySQL?
If the latter, look at text:
http://www.sqlalchemy.org/docs/core/tutorial.html#using-text
If the former, then you'll want a database agnostic implementation. So,
what's the above sql actually trying to achieve?
Chris
I'm fine with a MySQL-only solution. The text construct is always the fallback,
but I'm wondering if there is a way that I can use the attributes of my class
for the column name, instead of just a string. My column names are going to
change soon, but my object model will stay the same, so I am trying not to
explicitly use the column names in my code.
Can I do something like this?
'DATE_ADD(' + Class.dateAttr + ', INTERVAL(1 - ' \
+ 'DAYOFWEEK(' + Class.dateAttr + ')) DAY)'
If I can't use my class's attributes, is there a way I can at least use the
table object's columns like this:
'DATE_ADD(' + table.c.date_col.name + ', INTERVAL(1 - ' \
+ 'DAYOFWEEK(' + table.c.date_col.name + ')) DAY)'
I prefer one of these to a string because I will get an error during testing
when the statement is encountered. With a string, I will only get an error if
the statement actually runs in the DB.
--
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] bug with declarative's _decl_class_registry
Hi All,
Start off with a base.py module:
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Now, say we have a module, a.py:
from sqlalchemy import *
from base import Base
class Something(Base):
__tablename__ = 'foo'
id = Column('id', Integer, primary_key=True)
...and another module, b.py:
from sqlalchemy import *
from base import Base
class Something(Base):
__tablename__ = 'bar'
id = Column('id', Integer, primary_key=True)
...and finally, a third module, c.py:
from sqlalchemy import *
from sqlalchemy.orm import relationship
from base import Base
import a,b
class AnotherThing(Base):
__tablename__ = 'baz'
id = Column('id', Integer, primary_key=True)
addresses = relationship(Something)
...what table will that relationship be to?
import c
c.AnotherThing._decl_class_registry['Something']
class 'b.Something'
I think an exception should be raised if a class name already exists in
_decl_class_registry when the assignment is made in _as_declarative.
Are there any cases where it would be legit to have one class override
another in _decl_class_registry in this way?
If the answer is no, I'll commit a test and patch asap...
cheers,
Chris
PS: Also, in the above, how come no foreign keys are created?
c.AnotherThing.__table__.foreign_keys
OrderedSet([])
import b
b.Something.__table__.foreign_keys
OrderedSet([])
import a
a.Something.__table__.foreign_keys
OrderedSet([])
--
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] bug with declarative's _decl_class_registry
On Oct 5, 2010, at 11:04 AM, Chris Withers wrote:
Hi All,
Start off with a base.py module:
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Now, say we have a module, a.py:
from sqlalchemy import *
from base import Base
class Something(Base):
__tablename__ = 'foo'
id = Column('id', Integer, primary_key=True)
...and another module, b.py:
from sqlalchemy import *
from base import Base
class Something(Base):
__tablename__ = 'bar'
id = Column('id', Integer, primary_key=True)
...and finally, a third module, c.py:
from sqlalchemy import *
from sqlalchemy.orm import relationship
from base import Base
import a,b
class AnotherThing(Base):
__tablename__ = 'baz'
id = Column('id', Integer, primary_key=True)
addresses = relationship(Something)
...what table will that relationship be to?
import c
c.AnotherThing._decl_class_registry['Something']
class 'b.Something'
I think an exception should be raised if a class name already exists in
_decl_class_registry when the assignment is made in _as_declarative.
yeah definitely, though in 0.6 it needs to be a warning to start since some
folks might be doing this semi-intentionally.
Are there any cases where it would be legit to have one class override
another in _decl_class_registry in this way?
If the answer is no, I'll commit a test and patch asap...
cheers,
Chris
PS: Also, in the above, how come no foreign keys are created?
theres no usage of ForeignKey() or ForeignKeyConstraint().
--
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] Re: MySQL DATE_ADD function
Hi Bryan,
the only tricky bit in your SQL is the dangling 'DAY', because there's
no operator to tie it to the rest. Otherwise you should be able to
write (schema.AppDcRpe2 is just a Table object I'm using as an
example):
q =
session.query(schema.AppDcRpe2).filter(func.date_add(schema.AppDcRpe2.asof,
func.interval(1 - func.dayofweek(schema.AppDcRpe2.asof))) func.sysdate)
print q
SELECT kdb_app_dc_rpe2.id AS kdb_app_dc_rpe2_id, kdb_app_dc_rpe2.asof
AS kdb_app_dc_rpe2_asof, kdb_app_dc_rpe2.instance_name AS
kdb_app_dc_rpe2_instance_name, kdb_app_dc_rpe2.dc AS
kdb_app_dc_rpe2_dc, kdb_app_dc_rpe2.rpe2_total AS
kdb_app_dc_rpe2_rpe2_total, kdb_app_dc_rpe2.rpe2_used AS
kdb_app_dc_rpe2_rpe2_used, kdb_app_dc_rpe2.rpe2_unused AS
kdb_app_dc_rpe2_rpe2_unused
FROM kdb_app_dc_rpe2
WHERE date_add(kdb_app_dc_rpe2.asof, interval(:dayofweek_1 -
dayofweek(kdb_app_dc_rpe2.asof))) :date_add_1
which is *almost* what you need. Can MySQL 'INTERVAL' perhaps be
written in 'function form', i.e. something like interval(x, 'DAY')? In
that case you should be able to translate it fully.
Or maybe there's a way to 'abuse' the alias method, like so:
q =
session.query(schema.AppDcRpe2).filter(func.date_add(schema.AppDcRpe2.asof,
func.interval(1 - func.dayofweek(schema.AppDcRpe2.asof)).alias('DAY'))
func.sysdate)
print q
SELECT kdb_app_dc_rpe2.id AS kdb_app_dc_rpe2_id,
kdb_app_dc_rpe2.asof AS kdb_app_dc_rpe2_asof, kdb_app_dc_rpe2.instance_name
AS kdb_app_dc_rpe2_instance_name, kdb_app_dc_rpe2.dc AS kdb_app_dc_rpe2_dc,
kdb_app_dc_rpe2.rpe2_total AS kdb_app_dc_rpe2_rpe2_total,
kdb_app_dc_rpe2.rpe2_used AS kdb_app_dc_rpe2_rpe2_used,
kdb_app_dc_rpe2.rpe2_unused AS kdb_app_dc_rpe2_rpe2_unused
FROM kdb_app_dc_rpe2, interval(:dayofweek_1 -
dayofweek(kdb_app_dc_rpe2.asof)) DAY
WHERE date_add(kdb_app_dc_rpe2.asof, interval(:dayofweek_1 -
dayofweek(kdb_app_dc_rpe2.asof))) :date_add_1
Except for the double quotes that looks to be pretty close to what you
want. But then again rather than massaging that into place you may as
well build a text SQL from your bits, I guess. The above would also be
MySQL specific, I believe. (BTW I have not tried to run any of this,
this is just the output of the parsed statements.)
-sas
On Oct 5, 4:45 pm, Bryan Vicknair bryanv...@gmail.com wrote:
On Tue, Oct 5, 2010 at 1:58 AM, Chris Withers ch...@simplistix.co.uk wrote:
Are you looking for something database agnostic or something that just works
for MySQL?
If the latter, look at text:
http://www.sqlalchemy.org/docs/core/tutorial.html#using-text
If the former, then you'll want a database agnostic implementation. So,
what's the above sql actually trying to achieve?
Chris
I'm fine with a MySQL-only solution. The text construct is always the
fallback,
but I'm wondering if there is a way that I can use the attributes of my class
for the column name, instead of just a string. My column names are going to
change soon, but my object model will stay the same, so I am trying not to
explicitly use the column names in my code.
Can I do something like this?
'DATE_ADD(' + Class.dateAttr + ', INTERVAL(1 - ' \
+ 'DAYOFWEEK(' + Class.dateAttr + ')) DAY)'
If I can't use my class's attributes, is there a way I can at least use the
table object's columns like this:
'DATE_ADD(' + table.c.date_col.name + ', INTERVAL(1 - ' \
+ 'DAYOFWEEK(' + table.c.date_col.name + ')) DAY)'
I prefer one of these to a string because I will get an error during testing
when the statement is encountered. With a string, I will only get an error if
the statement actually runs in the DB.
--
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] bug with declarative's _decl_class_registry
On 05/10/2010 16:10, Michael Bayer wrote: I think an exception should be raised if a class name already exists in _decl_class_registry when the assignment is made in _as_declarative. yeah definitely, though in 0.6 it needs to be a warning to start since some folks might be doing this semi-intentionally. What could the intention possibly be? Also, is there a 'trunk' or 'tip' anywhere now to put 0.7-targeted code? Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] Re: MySQL DATE_ADD function
On Oct 5, 4:45 pm, Bryan Vicknair bryanv...@gmail.com wrote:
I'm fine with a MySQL-only solution. The text construct is always the
fallback,
but I'm wondering if there is a way that I can use the attributes of my
class
for the column name, instead of just a string. My column names are going to
change soon, but my object model will stay the same, so I am trying not to
explicitly use the column names in my code.
This was my final solution. I cheated by using a different MYSQL
function, one
that actually accepts arguments seperated by commas.
func.timestampadd(text('day'),
func.if_(func.dayofweek(EmpTime.day) == 1,
0,
8 - func.dayofweek(EmpTime.day)),
EmpTime.day)
--
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] bug with declarative's _decl_class_registry
On Oct 5, 2010, at 12:37 PM, Chris Withers wrote: On 05/10/2010 16:10, Michael Bayer wrote: I think an exception should be raised if a class name already exists in _decl_class_registry when the assignment is made in _as_declarative. yeah definitely, though in 0.6 it needs to be a warning to start since some folks might be doing this semi-intentionally. What could the intention possibly be? they named two classes the same thing, they happened to have configured relationship() using the class object and not the string registry, and their app works. If 0.6 turns that into an exception they get sudden application failure upgrading on a minor point release. Also, is there a 'trunk' or 'tip' anywhere now to put 0.7-targeted code? there's not. You'd make a bitbucket repo and link to it on the trac ticket. -- 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] NUMERIC in sqlite
Hi, I'm just learning to use sqlalchemy now (although I've had some exposure to SQLObject and Django ORM, so not completely new to the game). I'd like to address a standard ORM issue - i.e. interfacing Python Decimal objects with SQLite backend. It seems that sqlalchemy follows the standard strategy of passing the buck to sqlite, which converts fixed-point to floating point. If I may be pardoned a diversion... I understand why sqlite does this, but it constantly boggles my mind that ORM layers allow fixed- floating conversions. I have yet to find a single application where this is desirable behavior, since fixed-point generally goes along with guarantees of strict equality (I'm speaking here as a finance geek, money-handling being perhaps the preeminent real-world use of Decimals). Anyway, religious matters notwithstanding... although I'm comforted by sqlite's assurance that it tests identity out to 15 decimal places, which exceeds my own needs for precision... I would like to follow the recommendation of sqlalchemy, and guarantee strict precision by use of Decimal-String-Decimal conversions round-tripping to the database. I am looking at the sqlalchemy.types source code, and I like what I see... it looks not difficult to define what I want with AbstractType, UserDefinedType, TypeDecorator, etc. However, as a new user, the docstrings aren't completely clear. Can y'all give a hint to a newbie looking not to reinvent the wheel? Can I use TypeDecorator with impl=types.Numeric? If so, what do I need to override - bind_processor(), result_processor(), and/or what? I can't be the first person to want to do this. TIA for any pointers. -- 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.
