[sqlalchemy] DateTime format
Hello, I am using Sqlalchemy under Turbogears and I'd like to know which is the best way to customize column to string conversion of a DateTime column. I need to do it in an automatic way instead having to format it before showing. Regards -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/cU712dWA_kMJ. 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] Create tables from class declaration
The question is probably very simple, but I can't find an answer anywhere...
Suppose I already have some tables declarad in a declarative way, as
below, how do I create the database schema from them?
I usually always did with the
meta.create_all() after defining the various Table('name', meta...)
in this way it should be even easier but I can't find the magic instruction..
class TestStatus(Base):
__tablename__ = 'TestStatus'
id = Column(String, primary_key=True)
done = Column(Boolean, default=False)
def __init__(self, testid):
self.testid = testid
class AreaTests(Base):
__tablename__ = 'AreaTests'
area = Column(String, primary_key=True)
test = Column(String, ForeignKey('TestStatus.id'))
def __init__(self, area, test):
self.area = area
self.test = test
class Results:
Keep track of the results of the test that have been run, using
an temporary sqlite database to store
def __init__(self):
db_path = 'sqlite:///%s' % SQLITE_TEMP
eng = create_engine(db_path)
meta = MetaData(bind=eng)
# must first create the schema if not present already
self.session = sessionmaker(bind=eng)()
def add_area(self, area, testid):
Add an area and a testid to this simple database
test_obj = TestStatus(testid)
if test_obj not in self.session:
self.session.add(test_obj)
self.session.add(AreaTests(area, testid))
self.session.commit()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Create tables from class declaration
On Tue, Aug 21, 2012 at 5:25 PM, andrea crotti
andrea.crott...@gmail.com wrote:
The question is probably very simple, but I can't find an answer anywhere...
Suppose I already have some tables declarad in a declarative way, as
below, how do I create the database schema from them?
I usually always did with the
meta.create_all() after defining the various Table('name', meta...)
in this way it should be even easier but I can't find the magic instruction..
class TestStatus(Base):
__tablename__ = 'TestStatus'
id = Column(String, primary_key=True)
done = Column(Boolean, default=False)
def __init__(self, testid):
self.testid = testid
class AreaTests(Base):
__tablename__ = 'AreaTests'
area = Column(String, primary_key=True)
test = Column(String, ForeignKey('TestStatus.id'))
def __init__(self, area, test):
self.area = area
self.test = test
class Results:
Keep track of the results of the test that have been run, using
an temporary sqlite database to store
def __init__(self):
db_path = 'sqlite:///%s' % SQLITE_TEMP
eng = create_engine(db_path)
meta = MetaData(bind=eng)
# must first create the schema if not present already
self.session = sessionmaker(bind=eng)()
def add_area(self, area, testid):
Add an area and a testid to this simple database
test_obj = TestStatus(testid)
if test_obj not in self.session:
self.session.add(test_obj)
self.session.add(AreaTests(area, testid))
self.session.commit()
The MetaData instance is available via the declarative base class, so
you should be able to do something like:
Base.metadata.create_all()
http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/declarative.html#accessing-the-metadata
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 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] Create tables from class declaration
2012/8/21 Simon King si...@simonking.org.uk: The MetaData instance is available via the declarative base class, so you should be able to do something like: Base.metadata.create_all() http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/declarative.html#accessing-the-metadata 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 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. Very good thanks, I also had to bind it first to my engine but then it worked perfectly: meta = Base.metadata meta.bind = eng -- 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] Create tables from class declaration
On Aug 21, 2012, at 12:50 PM, andrea crotti wrote: 2012/8/21 Simon King si...@simonking.org.uk: The MetaData instance is available via the declarative base class, so you should be able to do something like: Base.metadata.create_all() http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/declarative.html#accessing-the-metadata 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 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. Very good thanks, I also had to bind it first to my engine but then it worked perfectly: meta = Base.metadata meta.bind = eng Base.metadata.create_all(eng) will get you there more succinctly and without a messy fixed association set up. -- 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] Having trouble using classes in different modules with the same name with declarative setup.
To give an example I have I have two classes that have the same name, but belong to different modules. there is an accounts.py that has a class Account(Base), and a testing.py that has a class Account(Base). When I try to set this up I get warnings: The classname 'Account' is already in the registry of this declarative base Is there any way I can get around this without having to change my class names? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/wGUh_akx25IJ. 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] How do I get an object from a relationship by object's PK?
Hi all, I've asked this question on stackoverflow: http://stackoverflow.com/questions/12031861/sqlalchemy-how-do-i-get-an-object-from-a-relationship-by-objects-pk/12032405 Basically, I'm looking for a dict-like access to a relationship to be able to quickly retrieve items by some key (item's PK). To illustrate, a normal relationship is list-like: for book in library.books: print book.id # prints 10, 20, 30 *In addition,* I'd like to be able to access books from an already-loaded relationship by their id: book1 = library.books.by_id(10) book2 = library.books.by_id(20) book3 = library.books.by_id(23) # raises KeyError while still being able to iterate over library.books in a list-like manner. I'm looking at MappedCollection (http://docs.sqlalchemy.org/en/latest/orm/collections.html?highlight=collection#custom-dictionary-based-collections) - would building a custom collection class be the right solution to the problem? Or is there something pre-built for this purpose in the depths of SQLAlchemy? Thanks, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/mUIFlVguh34J. 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] Having trouble using classes in different modules with the same name with declarative setup.
Good to hear, thanks! On Tuesday, August 21, 2012 5:19:49 PM UTC-4, Michael Bayer wrote: On Aug 21, 2012, at 4:36 PM, jers wrote: To give an example I have I have two classes that have the same name, but belong to different modules. there is an accounts.py that has a class Account(Base), and a testing.py that has a class Account(Base). When I try to set this up I get warnings: The classname 'Account' is already in the registry of this declarative base Is there any way I can get around this without having to change my class names? In 0.8, when using string-based configuration, you'll be able to refer to classes of the same name in different modules using a module-qualified pathname. However, this warning is harmless and only means you can't refer to those classes via string name when you use relationship(). You need to either use the class directly: relationship(Account) or a lambda in conjunction with making sure the calling module has Account available when all the mappings are complete: relationship(lambda: Account) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/wGUh_akx25IJ. To post to this group, send email to sqlal...@googlegroups.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. 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 view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/8jPpgq5NacAJ. 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] PG error I don't understand.
Hi
I have created a simple update like this on a PG database via PG8000;
table=Table('invoice_line_items', meta, autoload=True)
query = table.update()
query = query.where(and_(eq(table.c.InvBook, 'SC'), eq(table.c.InvNum,
12862), eq(table.c.InvLine, 1)))
query = query.values(**data)
query.execute()
I'm getting this error, and looking at the SQL (from the error message), I
can't see what is wrong or why PG is complaining.
('ERROR', '42712', 'table name invoice_line_items specified more than
once') u'UPDATE invoice_line_items SET DocType=%s, LineType=%s,
Complete=%s, Group=%s, Product=%s, SerialNumber=%s,
Description=%s, Warehouse=%s, UnitCode=%s, UnitQty=%s,
Supplier=%s, Active=%s, Customer=%s, BillTo=%s, Date=%s,
ConsignmentFlag=%s, TaxFlag=%s, HiddenFlag=%s, JoinFlag=%s,
OrderQty=%s, ReserveQty=%s, BackorderQty=%s, InvoiceQty=%s,
ShippedQty=%s, Currency=%s, CostEach=%s, SellEach=%s, TotalEx=%s,
Total=%s, InputTax=%s, OutputTax=%s, TaxTotal=%s,
BudgetCostEx=%s, BudgetCostInc=%s, Weight=%s, GLRevenue=%s,
GLCost=%s, GLCostFrom=%s, PriceLevel=%s, OrderType=%s,
StatusCode=%s, ReasonCode=%s, LotNumber=%s, GST=%s, BookingID=%s,
StampDuty=%s, Insurance=%s, PriceSource=%s, SalesRep=%s,
ETADate=%s, CostCentreRevenue=%s, CostCentreCost=%s,
CostCentreCostFrom=%s, RebateRule=%s, CustOrderUnitCode=%s,
CustOrderQty=%s, FreightMode=%s, FreightExEach=%s,
FreightExApplied=%s, GLFreight=%s, BundleGroup=%s,
BundleOrderQty=%s, BundleComponentQty=%s, MinSell=%s,
DiscTaxApplyFlag=%s, yearWeek=%s, yearMonth=%s, yearQuarter=%s,
colour=%s, feeCode=%s, feeTotalEX=%s FROM invoice_line_items WHERE
invoice_line_items.InvBook = %s AND invoice_line_items.InvNum = %s AND
invoice_line_items.InvLine = %s' (u'C', u'P', u'Y', u'FIL', u'OBS3E',
u'', u'ORGANIC SUPREME SKIN ON', u'S01', u'KG', 1.0, u'MANDJ', u'Y',
u'S2550', u'S2550', datetime.date(2007, 6, 5), u'N', u'N', u'N', u'N',
-2.4, 0.0, 0.0, 0.0, 0.0, u'AUD', 0.0, 19.5, -46.8, -46.8, u'', u'N', 0.0,
0.0, 0.0, -2.4, u'101-30010-000', u'101-10530-000', u'101-10530-000', u'L',
u'', u'', u'', u'', 0.0, 0, 0.0, 0.0, u'L', u'SAM', None, u'', u'', u'',
u'', u'KG', -2.4, u'', 0.0, 0.0, u'', u'', 0.0, 0.0, 0.0, u'', u'2007-22',
u'2007-06', u'2007-02', None, None, None, u'SC', 12862, 1)
Many thanks
Warwick
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Self-Referential Many-to-Many Relationship: related nodes
Hi Michael and all
I successfully built a all nodes relationship following your guidelines.
As:
subq1=select([caso_vinculo.c.caso_1_id.label('id1'),caso_vinculo.c.caso_2_id.label('id2')]).union(select([caso_vinculo.c.caso_2_id,
caso_vinculo.c.caso_1_id]))
subq2=aliased(subq1)
CasoMapper = mapper(Caso, caso, properties=
{
'LinkedNodes':relation(Caso, secondary=subq2,
primaryjoin=caso.c.id ==
subq2.c.id1,
secondaryjoin=subq2.c.id2==caso.c.id),
So with this property I can get both left and right linked nodes.
with:
CasoAlias=aliased(Caso)
q1=session.query(Caso.id).outerjoin(CasoAlias.LinkedNodes)
q1.all()
works fine
and once I add a new column from the aliased entity (CasoAlias)
q2=q1.add_column(CasoAlias.id)
it compiles fine
SELECT caso.id AS caso_id, caso_1.id AS caso_1_id
FROM caso AS caso_1 LEFT OUTER JOIN (SELECT caso_vinculo.caso_1_id AS id1,
caso_vinculo.caso_2_id AS id2
FROM caso_vinculo UNION SELECT caso_vinculo.caso_2_id AS caso_2_id,
caso_vinculo.caso_1_id AS caso_1_id
FROM caso_vinculo) AS anon_1 ON caso_1.id = anon_1.id1 LEFT OUTER JOIN caso
ON anon_1.id2 = caso.id
BUT
q2.all()
breaks with
Traceback (most recent call last):
File pyshell#6, line 1, in module
q2.all()
File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 2115, in all
return list(self)
File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 2351, in
instances
labels) for row in fetch]
File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 3342, in proc
return row[column]
File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 2616, in
__getitem__
processor, obj, index = self._parent._key_fallback(key)
File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 2835, in
_key_fallback
expression._string_or_unprintable(key))
NoSuchColumnError: Could not locate column in row for column '%(34374288
caso)s.id'
I am using sa '0.7.8'
Any lead on this error?
Thanks in advance for any help!!
Adolfo
On Monday, August 20, 2012 5:07:39 PM UTC-5, adolfo wrote:
I have a Self-Referential Many-to-Many Relationship situation where the
right_nodes = relationship(Node,
secondary=node_to_node,
primaryjoin=id==node_to_node.c.left_node_id,
secondaryjoin=id==node_to_node.c.right_node_id,
backref=left_nodes
works fine.
The problem:
I need a related nodes relationship, which, in one expression, returns
all related nodes, both left nodes and right nodes, excluding the given
node itself.
Is that possible using the RELATIONSHIP construct?
Adolfo
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/uGFItXQmwU4J.
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] Self-Referential Many-to-Many Relationship: related nodes
Thanks. The subquery approach works fine.
My class is named 'Caso' (Case, in english)
I included at the mapper:
subq2=aliased(subq1)
CasoMapper = mapper(Caso, caso, properties=
{
'LinkedNodes':relation(Caso, secondary=subq2,
primaryjoin=caso.c.id ==
subq2.c.id1,
secondaryjoin=subq2.c.id2==caso.c.id),
.
where id1 and id2 works like the left and right links
Given an instance of caso, lets say
c96=session.query(Caso).filter(Caso.id==96)[0]
I obtain
c96.LinkedNodes
== [Caso:25, Caso:26, Caso:28, Caso:65]
which is great. That is what is expected.
but when trying
Caso2=aliased(Caso)
q=session.query(Caso.id).join(Caso2,Caso.LinkedNodes).add_column(Caso2.id)
and compiles nicely:
print q
SELECT caso.id AS caso_id, caso_1.id AS caso_1_id
FROM caso JOIN (SELECT caso_vinculo.caso_1_id AS id1,
caso_vinculo.caso_2_id AS id2
FROM caso_vinculo UNION SELECT caso_vinculo.caso_2_id AS caso_2_id,
caso_vinculo.caso_1_id AS caso_1_id
FROM caso_vinculo) AS anon_1 ON caso.id = anon_1.id1 JOIN caso AS caso_1 ON
anon_1.id2 = caso_1.id
BUT
q.all()
and I get:
Traceback (most recent call last):
File pyshell#37, line 1, in module
q3.all()
File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 2115, in all
return list(self)
File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 2351, in
instances
labels) for row in fetch]
File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 3342, in proc
return row[column]
File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 2616, in
__getitem__
processor, obj, index = self._parent._key_fallback(key)
File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 2835, in
_key_fallback
expression._string_or_unprintable(key))
NoSuchColumnError: Could not locate column in row for column '%(67064688
caso)s.id'
strange
any idea?
thanks for all this great effort.
Adolfo
On Monday, August 20, 2012 10:04:59 PM UTC-5, Michael Bayer wrote:
On Aug 20, 2012, at 7:08 PM, adolfo wrote:
Thanks Michael.
The question is: how can I use that trick as a relation?
I can do:
session.query(Node).outerjoin(Node.right_nodes)
and
session.query(Node).outerjoin(Node.left_nodes)
but not
session.query(Node).outerjoin(Node.all_nodes)
and that is what I'm looking for. I mean the relation() functionality.
Is there some way to accomplish that?
the outerjoin is like this:
subq = select([node_to_node.c.left_node_id.label('parent'),
node_to_node.c.right_node_id.label('child')]).union(select([node_to_node.c.right_node_id,
node_to_node.c.left_node_id]))
nalias = aliased(Node)
session.query(Node).outerjoin(subq,
Node.id==subq.c.parent).outerjoin(nalias, nalias.c.id==subq.c.child)
that is, a UNION in the middle.
you can make a relationship() where you take that subq above and make it
the secondary part of the relationship, if you wanted to have more of the
relationship mechanics available.
Thanks again!
Adolfo
On Monday, August 20, 2012 5:35:51 PM UTC-5, Michael Bayer wrote:
On Aug 20, 2012, at 6:07 PM, adolfo wrote:
I have a Self-Referential Many-to-Many Relationship situation where the
right_nodes = relationship(Node,
secondary=node_to_node,
primaryjoin=id==node_to_node.c.left_node_id,
secondaryjoin=id==node_to_node.c.right_node_id,
backref=left_nodes
works fine.
The problem:
I need a related nodes relationship, which, in one expression, returns
all related nodes, both left nodes and right nodes, excluding the given
node itself.
Is that possible using the RELATIONSHIP construct?
this is the my friends and people who I'm friends with query and the
recipe iswell I guess I didn't put it up anywhere yet, the idea is to
use a @property:
class MyClass(Base):
# ...
@property
def all_nodes(self):
return self.left_nodes + self.right_nodes
to do this in SQL only requires a UNION in a subquery. If you really
wanted that I can work it out, it's more burdensome but if you have a
specific query style in mind it could be useful.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/W5_3pjXf2V4J.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
To unsubscribe from this group, send email to
sqlalchemy+...@googlegroups.com javascript:.
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 view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/n2qirzTZY60J.
To post to this group, send email to
Re: [sqlalchemy] How do I get an object from a relationship by object's PK?
On Aug 21, 2012, at 5:47 PM, Sergey V. wrote: Hi all, I've asked this question on stackoverflow: http://stackoverflow.com/questions/12031861/sqlalchemy-how-do-i-get-an-object-from-a-relationship-by-objects-pk/12032405 Basically, I'm looking for a dict-like access to a relationship to be able to quickly retrieve items by some key (item's PK). To illustrate, a normal relationship is list-like: for book in library.books: print book.id # prints 10, 20, 30 In addition, I'd like to be able to access books from an already-loaded relationship by their id: book1 = library.books.by_id(10) book2 = library.books.by_id(20) book3 = library.books.by_id(23) # raises KeyError while still being able to iterate over library.books in a list-like manner. I'm looking at MappedCollection (http://docs.sqlalchemy.org/en/latest/orm/collections.html?highlight=collection#custom-dictionary-based-collections) - would building a custom collection class be the right solution to the problem? Or is there something pre-built for this purpose in the depths of SQLAlchemy? there's two variations here. One, which is definitely the quickest, is just to use a @property that re-evaulates library.books as a dict: @property def books_by_id(self): return dict((book.id, book) for book in self.books) the other, the more integrated approach, is to use a collection_class that's mostly a dict but just yields the values instead of the keys when iterated. It might be nice if attribute_mapped_collection provided a simple argument to pass in a dict subclass but apparently it doesn't have this yet, so just subclass MappedCollection: class IterateValuesDict(MappedCollection): def __iter__(self): return iter(self.values()) collection_cls = lambda: IterateValuesDict(keyfunc=lambda obj: obj.id) class Library(Base): # ... books = relationship(Book, collection_cls=collection_cls) Thanks, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/mUIFlVguh34J. 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.
Re: [sqlalchemy] PG error I don't understand.
On Aug 21, 2012, at 6:32 PM, Warwick Prince wrote:
Hi
I have created a simple update like this on a PG database via PG8000;
table=Table('invoice_line_items', meta, autoload=True)
query = table.update()
query = query.where(and_(eq(table.c.InvBook, 'SC'), eq(table.c.InvNum,
12862), eq(table.c.InvLine, 1)))
query = query.values(**data)
query.execute()
I'm getting this error, and looking at the SQL (from the error message), I
can't see what is wrong or why PG is complaining.
('ERROR', '42712', 'table name invoice_line_items specified more than
once') u'UPDATE invoice_line_items SET DocType=%s, LineType=%s,
Complete=%s, Group=%s, Product=%s, SerialNumber=%s, Description=%s,
Warehouse=%s, UnitCode=%s, UnitQty=%s, Supplier=%s, Active=%s,
Customer=%s, BillTo=%s, Date=%s, ConsignmentFlag=%s, TaxFlag=%s,
HiddenFlag=%s, JoinFlag=%s, OrderQty=%s, ReserveQty=%s,
BackorderQty=%s, InvoiceQty=%s, ShippedQty=%s, Currency=%s,
CostEach=%s, SellEach=%s, TotalEx=%s, Total=%s, InputTax=%s,
OutputTax=%s, TaxTotal=%s, BudgetCostEx=%s, BudgetCostInc=%s,
Weight=%s, GLRevenue=%s, GLCost=%s, GLCostFrom=%s, PriceLevel=%s,
OrderType=%s, StatusCode=%s, ReasonCode=%s, LotNumber=%s, GST=%s,
BookingID=%s, StampDuty=%s, Insurance=%s, PriceSource=%s,
SalesRep=%s, ETADate=%s, CostCentreRevenue=%s, CostCentreCost=%s,
CostCentreCostFrom=%s, RebateRule=%s, CustOrderUnitCode=%s,
CustOrderQty=%s, FreightMode=%s, FreightExEach=%s,
FreightExApplied=%s, GLFreight=%s, BundleGroup=%s, BundleOrderQty=%s,
BundleComponentQty=%s, MinSell=%s, DiscTaxApplyFlag=%s, yearWeek=%s,
yearMonth=%s, yearQuarter=%s, colour=%s, feeCode=%s, feeTotalEX=%s
FROM invoice_line_items WHERE invoice_line_items.InvBook = %s AND
invoice_line_items.InvNum = %s AND invoice_line_items.InvLine = %s'
(u'C', u'P', u'Y', u'FIL', u'OBS3E', u'', u'ORGANIC SUPREME SKIN ON', u'S01',
u'KG', 1.0, u'MANDJ', u'Y', u'S2550', u'S2550', datetime.date(2007, 6, 5),
u'N', u'N', u'N', u'N', -2.4, 0.0, 0.0, 0.0, 0.0, u'AUD', 0.0, 19.5, -46.8,
-46.8, u'', u'N', 0.0, 0.0, 0.0, -2.4, u'101-30010-000', u'101-10530-000',
u'101-10530-000', u'L', u'', u'', u'', u'', 0.0, 0, 0.0, 0.0, u'L', u'SAM',
None, u'', u'', u'', u'', u'KG', -2.4, u'', 0.0, 0.0, u'', u'', 0.0, 0.0,
0.0, u'', u'2007-22', u'2007-06', u'2007-02', None, None, None, u'SC', 12862,
1)
so there's a FROM in there, which is a PG syntax we've recently started
supporting known as UPDATE..FROM. It's supposed to be used when there are
additional tables in the UPDATE statement other than the target table. But
here we have UPDATE invoice_line_items SET ... FROM invoice_line_items WHERE
... which is wrong.What would cause that here would be if you have two
separate Table objects both called invoice_line_items - one is mentioned as
the subject via table.update(), and the other would be embedded in the WHERE
clause somehow. The code you've illustrated doesn't show any way this might
be happening. Something is different in the actual code.
--
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.
