Re: [sqlalchemy] [psql] string escaping quirk in like clauses
You shouldn't need to write special code for this, have you tried changing
the escape character?
e.execute(t.select(t.c.a.like('\\', escape=~))
where ~ could be any substitute escape character.
I don't have Postgres currently available, but their docs also state that
and empty string will disable escaping; don't know if the Python modules
support that feature.
http://docs.sqlalchemy.org/en/rel_0_8/core/expression_api.html?highlight=.like#sqlalchemy.sql.operators.ColumnOperators.like
--
Mike Conley
On Fri, Jun 28, 2013 at 6:19 AM, Simon King si...@simonking.org.uk wrote:
On Fri, Jun 28, 2013 at 11:05 AM, Burak Arslan
burak.ars...@arskom.com.tr wrote:
On 06/28/13 11:55, Simon King wrote:
When you write this:
e.execute(t.select(t.c.a.like('\\')))
...the pattern that you are sending to SA is a single backslash, and
SA is forwarding that directly to PG. What do you think the behaviour
should be in this case?
Well, I'd prefer sqlalchemy did not leak such quirks and escape strings
sent to .like() accordingly.
In other words, I want the two to be equivalent:
e.execute(t.select(t.c.a.like('\\')))
e.execute(t.select(t.c.a == '\\'))
Otherwise, I'll have to implement a psql_escape_for_like function and
make it run like so:
e.execute(t.select(t.c.a.like(psql_escape_for_like(whatever
I wouldn't really prefer to go down this route -- it's ugly!..
Ah, OK, I see what you mean now. I'm not sure this could be changed
without breaking backwards compatibility. I wonder if you could do
something with the compiler module
(http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html).
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] datetime and engine
The Oracle database only has one date type, and it includes time. If you
require date only it is up to you to extract the date. Alternatively, if
you really care about date only for all usage of that column, store it with
a time of 00:00:00.
On Mar 30, 2012 8:08 AM, jo jose.soa...@sferacarta.com wrote:
shinriyo wrote:
hi jo
Oracle and PostgreSQL are different.
Oracle also have hour and minutes and second.
If you want minutes and second on PostgresQL, you should use datetime.
Hi shinryo,
I don't want hours and minutes.
My problem is that I have a comparison in my code like this:
if data_inizio data_fine:
...
TypeError: can't compare datetime.datetime to datetime.date
--
data_inizio is a value returned by:
Frazione.get(31).tariffa_data_**inizio # returns an object
datetime.date (both oracle and pg)
data_fine is a value returned by engine: #oracle returns a datetime while
pg returns a date
engine.connect().execute('**select data_fine from tariffa where
id_frazione=31').fetchone()
I would like to know why Oracle engine returns dates as datetime.datetime
instead of datetime.date
and if there is a way to have the same behavior between oracle and pg in
such case.
j
--
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+unsubscribe@**
googlegroups.com sqlalchemy%2bunsubscr...@googlegroups.com.
For more options, visit this group at http://groups.google.com/**
group/sqlalchemy?hl=en 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] Deletion
How are you doing the delete?
This should delete both.
a = sess.query(Peripheral).filter(Peripheral.label=='some label').one()
sess.delete(a)
sess.commit()
This will not work.
a = sess.query(Peripheral).filter(Peripheral.label=='some
label').delete()
I think the explanation here is that in this case we are creating an
explicit SQL delete statement without adding Peripheral instances to the
session and so there is no knowledge that a related Actuator exists. Someone
else might be able to give a better explanation.
--
Mike Conley
On Tue, Oct 18, 2011 at 9:03 AM, fribes fri...@gmail.com wrote:
Hi all,
Despite some doc and web digging, I didn't find how to tell sqa to behave
the way I want :
on deletion on Peripheral, also delete in Actuator.
with the following code, the record in Actuator remains after a deletion,
and a subsequent creation fails with IntegrityError.
class Peripheral(Base):
__tablename__ = 'peripheral'
id = Column(Integer, primary_key=True)
label = Column(String(20), nullable=False)
__mapper_args__ = {'polymorphic_on': peripheral_type,
'polymorphic_identity': 'peripheral'}
class Actuator(Peripheral):
__tablename__ = 'actuator'
__mapper_args__ = {'polymorphic_identity': 'actuator'}
id = Column(None, ForeignKey('peripheral.id'), primary_key=True)
duration = Column(Integer)
Any suggestion ?
Regards,
--
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.
Re: [sqlalchemy] DropTable if exists
On Wed, Sep 28, 2011 at 8:56 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On Sep 28, 2011, at 9:47 AM, Chris Withers wrote:
On 28/09/2011 14:09, Michael Bayer wrote:
I'm doing engine.execute('drop table if exists %s' + table.name) in the
meantime, which just feels icky...
oh probably it doesn't like table name as a bound parameter.
Don't you mean 'drop table if exists %s' % table.name not + table.name
if table.name is mytable wouldn't using + generate drop table if exists
%smytable?
--
Mike
--
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] obtaining * field when more than 1 table
Suppose I wish to do something like: Session.query(Files.original_name, MSPResponse.* because MSPResponse table has so many fields, and I want to get them all. How do I do this given that I am also picking field(s) from other tables ? Thanks RVince Session.query(Files.original_name, MSPResponse) should work. You get result rows with 2 items: a scalar for original_name and a MSPResponse object. You can then process the result like this: for row in result: ### access Files.original_name as row.original_name ### access MSPResponse as row[1] -- Mike Conley -- 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: Error while using CAST
Don't know what database you are using, but this looks like you are trying
to cast the string 'testing' to an integer and the database engine says you
can't do that.
--
Mike Conley
On Wed, Sep 14, 2011 at 9:51 AM, pravin battula pravin.batt...@gmail.comwrote:
Sorry for the spelling mistake.It shows an error as below.
OperationalError: (OperationalError) (1292, Truncated incorrect
INTEGER value: 'testing') 'UPDATE test.mytable SET
`newColumn`=CAST(test.mytable.`empno` AS SIGNED INTEGER)' ()
On Sep 14, 6:48 pm, pravin battula pravin.batt...@gmail.com wrote:
Hi,
I'm using cast to update values in a table by issuing following
command.
table.update().values(empno = cast(empno,Integer)).execute().
Where as empno is an string field,i'm trying to convert the data from
empno column from string to integer and then issuing the below command
to alter the data type of the column by issuing following command.
alter_column(table.c.empno,type=Integer).
It shows an error as
OperationalError: (OperationalError) (1292, Truncated incorrect
INTEGER value: '1d') 'UPDATE test.mytable SET
`newColumn`=CAST(CAST(%s AS CHAR) AS SIGNED INTEGER)' ('1d',)
Please do the needful
--
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] Unregister an event listener
Is there a method available to unregister an event listener? Can't think of a specific use case right now, but it would go something like this. define a listener with some complex logic do stuff that uses that listener unregister the listener because the complex stuff is done continue on without the listener -- Mike Conley -- 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] Calculating percentage using subquery
This should work
subq = sess.query(func.count('*').label('countall')).\
select_from(FilmParticipation).subquery()
qry = sess.query(FilmParticipation.PartType,
func.count(1).label('Amount'),
((100*func.count(1)) / subq.c.countall)).\
group_by(FilmParticipation.PartType)
--
Mike Conley
On Mon, Aug 29, 2011 at 3:05 PM, nospam lhfied...@gmail.com wrote:
I'm trying to construct a query in sqlalchemy similiar to this:
SELECT FilmParticipation.PartType, COUNT(*) AS Amount,
100*COUNT(*) /(SELECT count(*) FROM FilmParticipation) AS
Percentage_of_Total
FROM FilmParticipation
GROUP BY FilmParticipation.PartType;
I create a subquery for the nested select, but, I get a
NoSuchColmnError on the percentage part.
Any ideas how this could be constructed?
Cheers,
Lars
--
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.
Re: [sqlalchemy] Handling optional relationship
The default relationship cascade settings will do it for you. Here I made
them explicit.
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 Conley
On Fri, Aug 19, 2011 at 12:10 PM, Mark Erbaugh m...@microenh.com wrote:
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] Re: SQL Delete in session does not work
On Tue, Aug 16, 2011 at 1:16 PM, Dirk Makowski dirk.makow...@googlemail.com wrote: P.S. In my first attempt, I used the ORM like this sess = DbSession() entity = sess.query(Role).filter(Role.id==id).one() sess.delete(entity) sess.flush() This had worked well. But this way the record first is selected before it is deleted, which I'd liked to eliminate. Look at the delete() method of query. http://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Query.delete sess.query(Role).filter(Role.id==id).delete() read the description of synchronize_session parameter to determine what is needed in your application -- Mike Conley -- 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: SQL Delete in session does not work
On Tue, Aug 16, 2011 at 3:59 PM, Dirk Makowski dirk.makow...@googlemail.com wrote: Excellent, Mike, thanks. This solves my 2nd post, and there's also an equivalent for update. So I can use sess.query(Role).filter(Role.**id==id).update(values, synchronize_session=False) where I previously had loaded the entity and then updated it. Fine, because I am using the ORM again, the problem in post 1 is alleviated. Nonetheless I am still curious about that phenomenon. Dirk Looking at the limited snippet in the first post, it seems that the commit should be sess.commit() where does transaction come from? -- Mike Conley -- 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
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 The column attribute you need to check for could also be server_default if that is what you specified in the metadata. Be careful, if the defaults are specified as server side defaults in an existing database where the DDL is not generated from SQLAlchemy, the SQLAlchemy metadata doesn't necessarily know about them unless you reflected the table or you coded the server side default in your code by hand. -- Mike Conley -- 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] writing a (sub)query
0.7 has support for window functions. I haven't tried it in a subquery. http://www.sqlalchemy.org/docs/core/tutorial.html?highlight=window#window-functions -- Mike Conley On Wed, Jul 27, 2011 at 9:16 AM, Eduardo ruche...@googlemail.com wrote: I have the following statement : SELECT name, id, division, value, FROM ( SELECT name, id, division,value, max(value) over (partition by division) as max_val FROM tab1 ) WHERE value = max_val I try to turn this sql statement into a Query object I tried this sqlquery=session.query(sometab) sqlquery.statement= SQL QUERY GIVEN ABOVE but it does not work because statement cannot be set directly. What is get-around for this? Can this statement be written as a subquery? If yes, how? I saw several example of subqueries on the internet but none of these seem to be suitable for this statement. Thanking in advance Eddie -- 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.
Re: [sqlalchemy] Question on the precedence of insert and delete in session flush
And the recipe I have used is to issue a flush() after the deletes and before the inserts. In most cases this is sufficient to get things to work in the right order. I can imagine that there are some complex data management use cases where that is not sufficient. It works for your sample as the comment in your code indicates. This should not change the performance of the app since we are only changing the sequence of statements, not introducing additional transaction overhead. When I run into scenarios where the sequence of issuing SQL is significant, I'm not sure how much control I want to give up to the UOW code. After all if sequence is important this could very well be application dependent and the UOW might have to get really sophisticated to guess right. That increases the risk of taking a performance hit in the 90+% of cases where it doesn't matter. -- Mike Conley -- 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] engine.echo not working as expected
Under 0.5 I was able to turn echo on and off as desired to support
debugging; it doesn't seem to work now.
Python version: 2.7.1
SQLAlchemy version: 0.7.1
Here's the code:
from sqlalchemy import *
eng1 = create_engine('sqlite:///')
meta1 = MetaData(bind=eng1)
tab_a = Table('x', meta1,
Column('id',Integer, primary_key=True))
meta1.create_all()
conn = eng1.connect()
conn.execute(tab_a.insert())
x=conn.execute(select([tab_a])).fetchone()
eng1.echo=True
conn.execute(tab_a.delete().where(tab_a.c.id==x.id))
Under 0.5.8 The SQL for the delete is echoed, under 0.7 (and I think 0.6) it
is not.
If I move the echo=True before the select, both the select and delete are
echoed.
It looks like there might be a subtle difference since 0.5 that keeps the
logging from taking effect immediately when echo is changed.
P.S. Now as I try to reverify it, I have to move the echo=True all the way
before the connect() to get it to echo.
--
Mike Conley
--
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] engine.echo not working as expected
I saw that, but unless setting echo actually changes the Python logger
configuration I don't see how it applies here.
--
Mike Conley
On Wed, Jul 27, 2011 at 12:31 PM, King Simon-NFHD78
simon.k...@motorolasolutions.com wrote:
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Mike Conley
Sent: 27 July 2011 17:43
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] engine.echo not working as expected
Under 0.5 I was able to turn echo on and off as desired to support
debugging; it doesn't seem to work now.
Python version: 2.7.1
SQLAlchemy version: 0.7.1
Here's the code:
from sqlalchemy import *
eng1 = create_engine('sqlite:///')
meta1 = MetaData(bind=eng1)
tab_a = Table('x', meta1,
Column('id',Integer, primary_key=True))
meta1.create_all()
conn = eng1.connect()
conn.execute(tab_a.insert())
x=conn.execute(select([tab_a])).fetchone()
eng1.echo=True
conn.execute(tab_a.delete().where(tab_a.c.id==x.id))
Under 0.5.8 The SQL for the delete is echoed, under 0.7 (and I think
0.6) it is not.
If I move the echo=True before the select, both the select and delete
are echoed.
It looks like there might be a subtle difference since 0.5 that keeps
the logging from taking effect immediately when echo is changed.
P.S. Now as I try to reverify it, I have to move the echo=True all
the way before the connect() to get it to echo.
This is explained in the note at the bottom of
http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging.
(not that that necessarily helps you, but it does at least say that it
is expected behaviour)
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.
--
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: default value from column
given
Column('status', String(1), default='A')
Column('num', String(10), nullable=False, server_default='000')
try
table.status.c.default.arg
for sqlalchemy managed arguments
or
table.num.c.server_default.arg
for database managed defaults
Remember to test that the .default property actually exists; it won;t be
there if the column has no default.
For the server_default to work you need to either specify it in your
sqlalchemy table definition or reflect the table from the database so
sqlalchemy picks it up.
You might also look at the inspector interface for schema in
http://www.sqlalchemy.org/docs/core/schema.html
--
Mike Conley
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Problem with DeclarativeMeta
We have a database of about 100 tables with timestamp audit columns on most,
but not all tables, and use declarative to describe the database. I am
attempting to use a metaclass to create a base class that defines the audit
columns so we can stop defining them on every class. This seems to work OK
in most cases, but when I mix classes derived from my metaclass with classes
derived from the out-of-the-box base class I get an UnmappedClassError.
I am using version 0.5.8 (we will be able to upgrade soon, but not yet), but
get the same error using a metaclass customized for 0.6.
The stripped down example below should gives the error. the interesting
thing I have seen is that when running with code for our full database, the
error does not always point at the same table and occasionally gives an
Attribute error instead of the UnmappedClassError. In this example, if you
remove the product relationship property on SubSystemModule; the error
disappears. Also, if both classes are derived from AuditBase, there is no
error.
Since we generate most of the SQLAlchemy classes directly from our data
model, we can go back to adding audit columns to every class, but we don't
really want to do that.
import datetime
from sqlalchemy import __version__ as sa_ver
from sqlalchemy import (Column, ForeignKeyConstraint, PrimaryKeyConstraint,
create_engine, MetaData, DateTime, Integer, String)
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
Base = declarative_base()
metadata = Base.metadata
class AuditMeta(DeclarativeMeta):
def __init__(cls, classname, bases, dict_):
dict_['ModificationTS'] = Column(ModificationDate,DateTime
,default=datetime.datetime.now
,onupdate=datetime.datetime.now)
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
AuditBase = declarative_base(metaclass=AuditMeta, metadata=metadata)
class Product(AuditBase):
__tablename__ = 'Product'
PID = Column(Integer)
Name = Column(String)
__table_args__ = (
PrimaryKeyConstraint('PID'),
{})
class SubSystemModule(Base):
__tablename__ = 'SubSystemModule'
SSMID = Column(Integer)
PID = Column(Integer)
Name = Column(String)
__table_args__ = (
PrimaryKeyConstraint('SSMID'),
ForeignKeyConstraint(['PID'],['Product.PID']),
{})
product = relation('Product',
backref=backref('subsystemmodule', cascade='all'),
primaryjoin='SubSystemModule.PID==Product.PID')
if __name__ == '__main__':
print 'SQLAlchemy version:',sa_ver
from sqlalchemy.orm import compile_mappers
compile_mappers()
--
Mike Conley
--
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] intersect_all vs chaining of filter
On Tue, Jun 21, 2011 at 6:05 AM, Eduardo ruche...@googlemail.com wrote: What is the best practice: to chain filters or to collect queries in a list and then apply intersect_all()? Overall efficiency will depend on the underlying database engine, but I can't help but expect that most databases will be more efficient with the chained filters query. It would take a really smart optimizer to make the intersect method as efficient as the chained filter. Using an unrealistic set of queries, but it shows the principle. Using intersect_all will generate SQL like this: q1 = sess.query(Book).filter(Book.title=='A') q2 = sess.query(Book).filter(Book.title=='B') q3 = sess.query(Book).filter(Book.title=='C') q4 = sess.query(Book).filter(Book.title=='D') q5 = q1.intersect_all(q2,q3,q4) SELECT anon_1.book_bookid AS anon_1_book_bookid, anon_1.book_title AS anon_1_book_title, anon_1.book_authorid AS anon_1_book_authorid FROM (SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ?) AS anon_1 Chaining filters generates this SQL: q7 = sess.query(Book).filter(Book.title=='A') q7 = q7.filter(Book.title=='B') q7 = q7.filter(Book.title=='C') q7 = q7.filter(Book.title=='D') SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? AND book.title = ? AND book.title = ? AND book.title = ? -- Mike Conley -- 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] updates using declarative extention
see http://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Query.update We use session.query(..).update(..,synchronize_session=False) for updates where we are sure about the criteria and session state. This generates an update statement without any selects. -- Mike Conley -- 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] sqlalchemy filter by count problem
Not sure how you get 2 queries, but this seems like it gives right answer.
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
items = relationship('Item')
class Item(Base):
__tablename__ = 'items'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey(User.id))
q1 = session.query(User.id, func.count(Item.id)).\
join('items').\
group_by(User.id).having(func.count(Item.id)5)
Check out the documentation for Query.join for alternatives on the join
parameter
http://www.sqlalchemy.org/docs/06/orm/query.html#sqlalchemy.orm.query.Query.join
for example: if the items relationship isn't declared
.join((Item,User.id==Item.user_id))
generates the same SQL
--
Mike Conley
--
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] update table row immediately after writing
2011/5/30 Cornelius Kölbel cornelius.koel...@lsexperts.de OK, after some more reading and thinking, I think i managed it this way: self.session.add(at) self.session.flush() # At this point at contains the primary key id at.signature = self._sign( at ) self.session.merge(at) This merge should not be needed, In this case with the session configured as you have it the update is flushed to the database prior to the commit. Try a little test with echo=True on the engine and you will see it. self.session.commit() Kind regards Cornelius -- 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] insert record into dependency table
session is designed to handle this. Add a property that leverages the foreign key you defined. Then let session do the work. http://www.sqlalchemy.org/docs/orm/tutorial.html#building-a-relationship class ModelAttribute(Base): __tablename__ ='model_attribute' id = Column(Integer,primary_key=True) confirmed = Column(Boolean,default=False, nullable=False) values = relationship(ModelAttributeValue) attr = ModelAttribute(confirmed=True) attr.values.append(ModelAttributeValue(value=1)) session.add(attr) session.commit() -- Mike Conley -- 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: Side by side versions on one machine.
If you only want to change the SQLAlchemy version and use easy install tools, I have done it by altering the sqlalchemy path in site_packages/easy-install.pth I find that easier than setting up virtual environments. If you need to change a lot of things including maybe the Python version, then virtualenv might be the way to go. -- Mike Conley -- 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 NULL
Use people.married_status == None to check for NULL -- Mike Conley -- 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] Execute a function on orphan
Not sure I understand the use case example. With these tables, doesn't the act of an administrator unassigning a user from its UserGroup set the group id column to NULL? If so, doesn't that effectively remove all the permissions because there is no longer a connection between the user and the group. -- Mike Conley -- 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] Automatically retrieving the row index
Take a look at using ordering_list for the collection class on your relation. You add a position in season and SQLAlchemy will maintain the value. http://www.sqlalchemy.org/docs/orm/extensions/orderinglist.html -- Mike Conley -- 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] MultipleResultsFound
You issued a query with a .one() qualifier and there is more than one row in the database satisfying the condition. Example: 2 names in a table firstname=pete, lastname=smith firstname=john, lastname=smith query for rows lastname=smith with .one() will fail because there are 2 smith in database -- Mike Conley -- 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] Defining a relationship without a foreign key constraint?
The foreign key and join condition can be specified as part of the relation
definition without having the foreign key existing in the database
class User(Base):
__tablename__ = 'users'
logon = Column(String(10), primary_key=True)
group_id = Column(Integer)
class Group(Base):
__tablename__ = 'groups'
group_id = Column(Integer, primary_key=True)
group_nm = Column(String(10))
users = relation('User', backref='grp',
primaryjoin='User.group_id==Group.group_id',
foreign_keys='User.group_id')
--
Mike Conley
--
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] Multi-get?
On Wed, Jan 26, 2011 at 8:17 PM, Yang Zhang yanghates...@gmail.com wrote: Is there something similar to the .get() method in SqlSoup and Session but which allows me to fetch more than one object by ID, so as to save on round trips to the DB? (This could be done by composing using the IN operator in SQL.) Thanks in advance. Did you try something like session.query(MyClass).filter(MyClass.id.in_([...list of ids...])).all() -- Mike Conley -- 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: Odd many-to-one problem
For cases like this I have found something like this to be useful http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relationships-against-the-same-parent-child using lazy loading and viewonly=True as needed I found this to be clearer than column property because it fits cleanly with the rest of the relationship configuration. -- Mike Conley -- 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] Re: Odd many-to-one problem
If it's simply a matter of sequence of how code is organized:
1. Define Merchants table and mappers
2. Define Deals table and mappers
3. Add relations to Merchant
All of this can be in separate files if needed; just import right
definitions where needed.
metadata = MetaData()
merchants = Table('merchants', metadata,
Column('id', Integer, primary_key=True),
Column('name', String)
)
class Merchant(object):
pass
mapper(Merchant, merchants)
deals = Table('deals', metadata,
Column('id', Integer, primary_key=True),
Column('merch_id', Integer, ForeignKey('merchants.id')),
Column('deal_status', String(10))
)
class Deal(object):
pass
mapper(Deal, deals)
Merchant.all_deals = relation(Deal, backref='merchant')
Merchant.active_deals = relation(Deal, primaryjoin=
and_(merchants.c.id==deals.c.merch_id,
deals.c.deal_status=='active'))
This is one advantage of using declarative because the primaryjoin can be
defined as a string that will not be compiled until later. That can be
deferred until after everything is defined.
--
Mike Conley
On Thu, Nov 11, 2010 at 1:33 PM, Jonathan Gardner
jgard...@jonathangardner.net wrote:
This is what I need to do, except the Merchant object is defined
before the Deal object. In the example in the documentation, I have
mapped User before I have mapped Address.
--
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] Create_all() - Create_JustThese(engine, [Table1,Table2,....])?
Check the docs, create_all has an optional tables= parameter. On Oct 27, 2010 8:18 AM, Martijn Moeling mart...@xs4us.nu wrote: Hi, I have a huge definition module where I create Python objects and use declarative. Since not all databases (Multiple for different customers) need all tables I do not like to use create_all is there any way to create just the tables I really need (according to some config list or so) say: class C1(Base): __tablename__ = C1 .. class C2(Base): __tablename__ = C2 .. class C3(Base): __tablename__ = C3 .. create_JustThese(engine, [C1,C3]) - table C2 is NOT created Please do not reply with why I would want this, I just want to know if it is possible and it would help me out big time if it is Martijn -- 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] Change echo at will
On Thu, Aug 26, 2010 at 4:21 PM, Michael Hipp mich...@hipp.com wrote: Is there a way to set 'echo' at any time? Everything I can find sets it when the engine is created and doesn't seem to change it afterward. Thanks, Michael You can assign the engine.echo property to True or False any time after creating the engine. -- 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 do housekeeping jobs before using sqlalchemy and coworking with python sqlite3 and sqlalchemy ?
On Wed, Aug 25, 2010 at 1:30 PM, keekychen.shared
keekychen.sha...@gmail.com wrote:
How to test if an existing database file is a valid sqlite3 format
file before using sqlalchemy?
Here is function we use
import os, os.path as osp
try:
from pysqlite2 import dbapi2 as sqlite
except:
import sqlite3 as sqlite
def isSQLite(filename):
True if filename is a SQLite database
File is database if: (1) file exists, (2) length is non-zero,
(3) can connect, (4) has sqlite_master table
# validate file exists
if not osp.isfile(filename):
return False
# is not an empty file
if not os.stat(filename).st_size:
return False
# can open a connection
try:
conn = sqlite.connect(filename)
except:
return False
# has sqlite_master
try:
result = conn.execute('pragma table_info(sqlite_master)').fetchall()
if len(result) == 0:
conn.close()
return False
except:
conn.close()
return False
# looks like a good database
conn.close()
return True
--
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] create_all() fails silently
On Thu, Aug 5, 2010 at 6:29 AM, Michael Hipp mich...@hipp.com wrote: On 8/4/2010 10:03 PM, Mike Conley wrote: Thanks. But by the time I'm done there will be at least a dozen of those otherfiles. Which one do I get Base from? You can put the declaration of Base in a common file that is imported by all the other files and your main program. Reference the Base from that file whenever needed and all code will share the same instance. -- 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] create_all() fails silently
On Wed, Aug 4, 2010 at 9:39 PM, Michael Hipp mich...@hipp.com wrote: Can someone tell me why this code won't create any tables? The tables are defined in another file that calls declarative_base(). I presume the problem is that it doesn't know which tables to create. If so, how do I tell it what tables to create? Base = declarative_base() database = 'sqlite:///convert/db.sqlite' engine = create_engine(database, echo=True) metadata = Base.metadata metadata.create_all(engine) # Does nothing, says nothing Session = sessionmaker() Session.configure(bind=engine) Thanks, Michael Well, metadata here doesn't refer to the metadata that holds table definitions. What about something like import otherfile Base = otherfile.Base# assuming you use Base = declarative_base() in otherfile then continue as your in sample from database =. This should give you access to metadata from the other file. -- 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] distinct query
Did you try qry = session.query(AssetCategory).join(Asset).join(Shot).filter(Shot.id==1).distinct() qry = qry.filter(Shot.id==shot_id_of_interest) that generates SELECT DISTINCT AssetCategory.id AS AssetCategory_id FROM AssetCategory JOIN Asset ON AssetCategory.id = Asset.category_id JOIN Shot ON Shot.id = Asset.shot_id WHERE Shot.id = :id_1 -- Mike Conley -- 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] ArgumentError: Only '='/'!=' operators can be used with NULL
On Tue, Dec 22, 2009 at 2:34 AM, jo jose.soa...@sferacarta.com wrote:
Hi all,
I'm trying to solve this error...
File
/home/sfera/release/sicer/BASE/controller/controlli/sopralluogo.py,
line 645, in verifiche
Piano.c.data_inizio =data.get('data_sop') ,
File /usr/lib/python2.4/site-packages/sqlalchemy/sql.py, line 1294, in
__le__
return self._compare('=', other)
File /usr/lib/python2.4/site-packages/sqlalchemy/sql.py, line 1423, in
_compare
raise exceptions.ArgumentError(Only '='/'!=' operators can be used with
NULL)
ArgumentError: Only '='/'!=' operators can be used with NULL
-
here the code that originates the above error:
sr = sa.and_(Piano.c.data_inizio =data.get('data_sop') )
I suppose data.get('data_sop') returns a None value, thus, sa compiles
the query like so:
where piano.data_inizio = NULL
Could someone give me, some suggest how to solve this problem?
Thank you
This is correct behavior for a SQL query since the only legal behavior for
NULL is IS NULL or IS NOT NULL.
First question to answer is what should your application do when data_sop is
NULL?
If you want to treat NULL same as zero, use a case() function to force null
to zero in the comparison. If NULL should not participate at all, put a
!=None check first in the and_condition to skip rows with NULL values.
There are probably other alternatives, but it all comes down to desired
behavior when the column is NULL.
--
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] Getting relation type from mapper
On Wed, Dec 9, 2009 at 10:11 AM, Sergey Koval serge.ko...@gmail.com wrote: Hi, I'm trying to get relation type from the mapper, but having problems detecting 'one to one' relation. Pseudo code: for p in mapper.iterate_properties: if isinstance(p, RelationProperty): ... p.uselist is False, p.backref is available, but does not have any information on it's type (should it use list or not). p.direction is set to 'MANYTOONE'. How it can be accomplished? Thanks, Serge. Interesting question. It looks like you can use the backref property to discover the other side of the relation via the backref.prop attribute. From there you should be able to find the the property on the other side by examining the mapper on that class and view it's uselist property also. I'm not sure how you would find the right relation if there were 2 relations between the classes. There might be a simpler approach, but it looks like this one should work. -- Mike Conley -- 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] Functions on results of subquery question
On Fri, Nov 27, 2009 at 10:55 AM, Oliver Beattie oli...@obeattie.comwrote:
Hey there,
I'm probably missing something here, but no matter what I try, I can't
seem to find a way to translate this query into SQLAlchemy code:
SELECT AVG(sub.average)
FROM (
SELECT AVG(feedback.overall_rating) AS average
FROM feedback
INNER JOIN listings ON feedback.listing_id = listings.id
WHERE feedback.is_for_driver = false
GROUP BY feedback.listing_id
) AS sub;
So, is there any way someone could possibly point me in the right
direction? All of the tables have SA mappers defined for them (named
Feedback and Listing), if that helps. I've tried to do things like:
sa.select([sa.func.avg('sub.average'), sa.select([sa.func.avg
(Feedback.overall_rating).label('average')]).alias('sub')])
but no avail (and I know that doesn't include the grouping or the
where :)
Anyway, if someone could possibly help me out, I'd be most grateful.
Thanks,
Oliver Beattie
How about this?
sub = session.query(func.avg(Feedback.overall_rating).label('average')).\
join(Listings).filter(Feedback.is_for_driver==False).\
group_by(Feedback.listing_id).subquery()
qry = session.query(func.avg(sub.c.average))
print qry
In general:
construct a query that gives desired result for subquery, labeling columns
as needed
make it a subquery()
refer to columns of the subquery using subq.c.column in final query
It took me a while to get used to this because using the .c. with ORM
didn't feel natural.
--
Mike Conley
--
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] Weird error with update
Why not use update({ESMagicNumber.last_access: datetime.datetime.now()}) ?
one advantage of this syntax is that Python will raise an exception that
last_access does not exist.
--
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] Getting a join in one object
query() will return a tuple of the items listed in the query. In your query you are asking for a and b objects, so that is what you get. If you want the result to be individual columns directly, you need to list them individually. db.query(a.a, a.acol1, b.b, b.bcol1).\ select_from(orm.join(a, b, a.a== b.a)).all() if there are duplicate names in a and b you need to apply .label() to distinguish the columns. Unless your select_from() is especially complicated this can be simplified and eliminate the subselect in the generated code db.query(a.a, a.acol1, b.b, b.bcol1).join((b, a.a== b.a)).all() or if you have foreign keys defined between a and b db.query(a.a, a.acol1, b.b, b.bcol1).join(b).all() -- 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=.
Re: [sqlalchemy] more than one one to many relation
this should work OK if you fix the ForeignKey definitions
Column('parent_id', Integer, ForeignKey('parent.parent_id'))
or
Column('parent_id', Integer,ForeignKey(parent_table.c.parent_id))
--
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=.
Re: [sqlalchemy] Weird error with update
On Fri, Nov 20, 2009 at 5:14 PM, Mariano Mara mariano.m...@gmail.comwrote:
... or, at least, is weird for me :)
Hi everyone. I'm running a pylons controller
with the following instruction:
meta.Session.query(ESMagicNumber).filter(
ESMagicNumber.uuid==request.params['uuid_']).\
update({'last_access':datetime.datetime.now()})
but I'm getting the following error:
(ProgrammingError) syntax error at or near WHERE
LINE 1: UPDATE es_magicnumber SET WHERE es_magicnumber.uuid = E'705...
^ 'UPDATE es_magicnumber SET WHERE es_magicnumber.uuid = %(uuid_1)s'
{'uuid_1': u'705fa1e6-977f-416a-9847-fe8715cc920a'}
I am able to create the same bad SQL, but only if the key in the update
dictionary is not a column in the table being updated. Are you sure
'last_access' is a valid column in ESMagicNumber?
This is because the SET clause is generated from the update dictionary, and
if there are no valid columns, there is nothing to generate and the SET
clause becomes nothing.
--
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=.
[sqlalchemy] Re: column label and order by
And you do need to quote the column name in order_by also.
session.query(func.sum(X.amount).label('tot_amount')).group_by(X.date).order_by('tot_amount').limit(10)
generates code
SELECT sum(x.amount) AS tot_amount
FROM x GROUP BY x.date ORDER BY tot_amount
LIMIT 10 OFFSET 0
--~--~-~--~~~---~--~~
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: Query on a related object's field
I haven't seen how to do this using the relation directly. I do: session.query(Someclass).join(OtherClass).filter_by(OtherClass.field==somevalue) I did do some experimenting with a more abstract approach, but did not find any need in my application. The only advantage is that it takes away the need to know up front what is the name of the other class. That might be useful if you are building a framework based on SA, but not in most applications. otherclas = SomeClass.relname.property.mapper session.query(Someclass).join(otherclas).filter_by(otherclas.c.field==somevalue) --~--~-~--~~~---~--~~ 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] ConcurrentModificationError exception
I am getting this error on a session.flush(). sqlalchemy.orm.exc.ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 and can't figure out where to start looking. The database is sqlite, there are no other users connected to the database, and only one process running at the time. This is happening in the middle of a fairly large routine that is copying a data structure within my database. The routine starts at a high level parent object and copies the parent and many related children through several intertwined relationships resulting in a copy of the parent and all related rows in the database. Think of it as copying object X and everything about X to a new object Y that will have a copy of all X ' s information. There are about 20 tables involved in the process. Periodically in the process I need to do a flush() to get the id's of some new objects and it is in one of these flush() operations that I get this error. I'm looking for any help I can get about what could cause the error or what I should look at to debug it and track down the cause. I am pretty sure it will be in my code someplace because this is all new code and I am just in the unit testing stage. -- Mike Conley --~--~-~--~~~---~--~~ 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] Infinite loop in SA
I ran across this when writing some new code. I incorrectly appended
something to a relation and sent SA 0.5.6 off into an infinite loop until
max recursion depth was reached.
Obviously this is a coding bug on my part, but is it also a bug in SA that
should be fixed? I don't have access to 0.6 right now to see if the problem
is there also.
class A(Base):
__tablename__ = 'a'
a_id = Column(Integer, primary_key=True)
class B(Base):
__tablename__ = 'b'
b_id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.a_id'))
a = relation('A', backref='b')
a=A()
b=B()
x={22:33} # does not loop if dictionary is empty
a.b.append(b, x) # -- goes into infinite loop, should be a.b.append(b)
which works OK
--
Mike Conley
--~--~-~--~~~---~--~~
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: ConcurrentModificationError exception
Thanks, that helps. It looks like the problem is around a M:N relationship. There is some complex logic about whether or not the row on the :N side of the relation should be copied or the original referenced. The related maintenance of the supporting association table causes the update problem. --~--~-~--~~~---~--~~ 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: Infinite loop in SA
That is pretty much what I expected. Considering it only occurs if the developer writes incorrect code, I wouldn't want to make the performance tradeoff. --~--~-~--~~~---~--~~ 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: Subquery questions
On Tue, Nov 10, 2009 at 9:55 PM, jcm jonmast...@gmail.com wrote: Folks, I could do with some decent docs on subqueries. I've tried to play with what's on the website, but it's not helping me to convert the following into an SQLAlchemy subquery: select * from symbollistentries WHERE symbollistentries.symbollistentry_id NOT IN (select symbollistentries.symbollistentry_id from symbollistentries join votes ON (votes.symbollistentry_id=symbollistentries.symbollistentry_id) join states ON (states.state_id=votes.state_id) join users ON (users.user_id=states.owner_id) WHERE users.user_name='admin'); It doesn't really matter what I'm doing here, this is just an example, and I've all kinds of mappers in place that I use for queries. What I really need is a pointer to some good subquery examples, not the solution to encoding the above. I need to understand how aliases work and how I can, for example, perform the nested select, alias it and then test for NOT IN using another query. I can't see how you implement NOT IN either :) Easy part is the NOT IN question. the not_() function or ~ operator will negate an expression. tbl.c.colum.in_([1,2,3]) - tbl.c IN (1,2,3) not_( tbl.c.colum.in_([1,2,3]) ) - tbl.c NOT IN (1,2,3) ~tbl.c.colum.in_([1,2,3]) - tbl.c NOT IN (1,2,3) I can describe you how I deal with subqueries. Since you say you have mappers, I assume you are using ORM syntax like I do. Step 1. Create and test a regular query that gives the result you are looking for: subq = session.query(X.id).join(Y).join(Z).filter(some condition) If the query is complex, you might have to iterate on this several times Step 2. Add subquery() subq = subq.subquery() Step 3. use the subquery in final query (using a NOT IN example here) qry = session.query(X).filter(~X.id.in_(subq)) Maybe use the subquery in a join subq = session.query(X.id, Y.data, Z.colum).join(Y).join(Z).filter(some condition) qry = session.query(X.data, subq.c.colum).join((subq, subq.c.id==X.id)) I'm still learning this stuff, all I can suggest is practice a lot of examples. Given that, your example might look something like this, assuming you have mappers describing all the relationships for the joins. subq = session.query(SLE.id).join(Votes).\ join(States).join(Users).\ filter(Users.name=='admin').subquery() qry = session.query(SLE).filter(not_(SLE.id.in_(subq))) --~--~-~--~~~---~--~~ 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: insertion of an object with a user defined field value
The id is generate by the database engine, not SQLAlchemy, so session.add()
does nothing to push your object to the database and generate the id. You
need to execute session.flush() after session.add() to write the book to the
database and generate the id. After the flush() operation, the book id is
available to save in your dictionary.
something like this:
bk_ids = {}
for title in ('Tom Sawyer', 'Huck Finn'):
book = Book(title=title)
session.add(book)
session.flush()
bk_ids[title] = book.id
session.commit()
Without the flush(), the id will be NULL.
--~--~-~--~~~---~--~~
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: insertion of an object with a user defined field value
Using your class definitions, it seems to work. What is different?
Base.metadata.bind=create_engine('sqlite:///')
Base.metadata.create_all()
session=sessionmaker()()
bk_ids = {}
for title in ('Tom Sawyer', 'Huck Finn'):
book = Book(title=title)
session.add(book)
session.flush()
bk_ids[title] = book.id
session.commit()
print bk_ids
for i, content in enumerate((
'Once upon a time there was a little fellow called Tom.',
'His surname was Sawyer.')):
page = Page(i, content, bk_ids['Tom Sawyer'])
session.add(page)
session.commit()
for page in session.query(Page):
print 'page:',page.id,' book:',page.book_id
--~--~-~--~~~---~--~~
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: M:N self-reference
Something like this? The association table is declared in the relationships,
but never referenced when creating or accessing objects.
class Assoc(Base):
__tablename__ = 'assoc'
parent = Column(Integer, ForeignKey('m_to_n.id'), primary_key=True)
child = Column(Integer, ForeignKey('m_to_n.id'), primary_key=True)
class MToN(Base):
__tablename__ = 'm_to_n'
id = Column(Integer, primary_key=True)
name = Column(String)
children = relation('MToN', secondary=Assoc.__table__,
primaryjoin='MToN.id==Assoc.parent',
secondaryjoin='MToN.id==Assoc.child',
backref=backref('parents')
)
def __repr__(self):
return M:N %s %s % (self.id, self.name)
metadata.create_all()
compile_mappers()
p1 = MToN(name='P1')
p2 = MToN(name='P2')
p3 = MToN(name='P3')
c1 = MToN(name='C1')
c1a = MToN(name='C1A')
c2 = MToN(name='C2')
c3 = MToN(name='C3')
p1.children.append(c1)
p1.children.append(c1a)
c1.children.append(c2)
p2.children.append(c1)
c3.parents.append(p1)
c3.parents.append(p3)
session.add_all([p1, p2, p3])
session.commit()
engine.echo=False
qry_p = session.query(MToN).filter(MToN.name.like('P%'))
for p in qry_p:
print '=='
print p
for ch1 in p.children:
print ' ', ch1
for ch2 in ch1.children:
print ' ',ch2
--~--~-~--~~~---~--~~
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: M:N self-reference
Short answer, no. Think about it this way. In a simple 1:n relationship, each child has a pointer to the parent. In a m:n relationship, each child must have a pointers to many parents and those pointers must live somewhere. In a relational database, there is no way to store an arbitrary number of parent pointers in a child record; that leads to the requirement for an association table. No other way to do it. SQLAlchemy cannot change the data modeling needed here, but constructs available in SA can make the coding easier for managing the association table. Mike Conley --~--~-~--~~~---~--~~ 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: Renaming or copying tables
there's nothing built in that does that.you might build your own using the way that tometadata() works to provide clues. So, something like this? (Ignoring schema for now) def copyinmetadata(table, to_name): Make copy of table in own metadata metadata = table.metadata assert metadata.tables.get(to_name, None) is None, \ 'Copy/Rename target %s already exists in metadata' % to_name args = [] for c in table.columns: args.append(c.copy()) for c in table.constraints: args.append(c.copy()) return Table(to_name, metadata, *args) def renameinmetadata(table, to_name): Rename table in own metadata metadata = table.metadata new_table = copyinmetadata(table, to_name) metadata.remove(table) return new_table --~--~-~--~~~---~--~~ 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] Renaming or copying tables
Is it possible to rename and/or copy a table object in metadata?
That is I have a table
t1 = Table('t1', metadata, ...)
I want a copy of that table as t2 in the same metadata, or rename it to t2.
For right now, this is metadata manipulation only ignoring the underlying
database.
--
Mike Conley
--~--~-~--~~~---~--~~
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: How can i use insert/update with transactions
Looks like you are trying to mix ORM and SQL expression constructs. Also, Insert() objects should be constructed via the insert() function. Try this conn = session.connection() # get handle to the session's connection t = conn.begin() res = conn.execute(insert(t_table).values(id=None,pv=6)) print res.last_inserted_ids() t.rollback() --~--~-~--~~~---~--~~ 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: Multi table select?
On Mon, Oct 26, 2009 at 10:09 PM, Bobby Impollonia bob...@gmail.com wrote:
You can also create a view mapped to that union and use that as a
virtual table so that you don't have to repeat the union specification
for every query:
http://www.w3schools.com/Sql/sql_view.asp
I don't know if that helps for SQLAlchemy though.
On Mon, Oct 26, 2009 at 5:59 PM, AF allen.fow...@yahoo.com wrote:
On Oct 26, 8:48 pm, AF allen.fow...@yahoo.com wrote:
Hello,
I don't know if this is even possible is SQL, so please bear with
me :)
There are a couple a tables (say, a b) that are used as logs for
two different processes. They both have the same simple structure.
(id, time_stamp, user_id, message)
I would like to create a query that merges the data and returns
following results:
time_stamp, user_id, a_or_b, message
(where a_or_b is a value that indicates which table the data row came
from)
Can this be done in SQL/SQLAlchemy.
Thank you,
:)
p.s.
Alternatively, the message columns do not need to be merged though
I guess time_stamp / user would still need to be.
That is: time_stamp, user_id, message_a, message_b
I don't know if that makes any easier...
OK:
http://www.w3schools.com/Sql/sql_union.asp
Doh. OK, so now I have an SQL statement I wrote by hand that works
fine, but I still have two questions:
1) Can this be done via the SQA ORM?
2) If not, how should I I build this using non-ORM SQA?
Thank you,
:)
Let's say you have 2 mapped classes
class Stuff1(Base):
--- etc.
class Stuff2(Base)
--- etc.
You can do something like this
q1 = session.query(Stuff1.columnA,
Stuff1.columnB, literal_column('S1').label('source'))
q2 = session.query(Stuff2.columnA,
Stuff2.columnB, literal_column('S2').label('source'))
subq = session.query().from_statement(union_all(q1, q2)).subquery()
qry = session.query(subq)
Note: just in case your font makes the quotes hard to read,
'S1' is double-quote,single-quote,S,1,single-quote,double-quote
--~--~-~--~~~---~--~~
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: How to label text columns in a query
Only a couple of months late, but here is the final working recipe:
class A(Base):
__tablename__ = 'tbl_a'
id = Column(Integer, primary_key=True)
data = Column(String)
class B(Base):
__tablename__ = 'tbl_b'
id = Column(Integer, primary_key=True)
data = Column(String)
meta.create_all()
session.add(A(data='a1'))
session.add(B(data='b1'))
session.commit()
q1 = session.query(A.data.label('somedata'),
literal_column('A').label('source'))
q2 = session.query(B.data.label('somedata'),
literal_column('B').label('source'))
subq = session.query().from_statement(union_all(q1,q2)).subquery()
query = session.query(subq)
for row in query:
print row.source, row.somedata
generated SQL is:
SELECT anon_1.somedata AS anon_1_somedata, anon_1.source AS anon_1_source
FROM (SELECT tbl_a.data AS somedata, 'A' AS source
FROM tbl_a UNION ALL SELECT tbl_b.data AS somedata, 'B' AS source
FROM tbl_b) AS anon_1
Not sure if using the subquery will cause inefficient SQL, but that would
take some research looking at query plans and might vary by database engine.
--~--~-~--~~~---~--~~
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: Shallow copying
I see, but I need a proper shallow copy instead, with every attribute.. Is there no way? :/ What is missed? I am not sure what you mean by shallow copy. The fact that there are relations to be lazy loaded is present in the new object when instantiated. Obviously the objects pointed to by the relation are not copied, but I would call that a deep copy, and to do that you would need to iterate over them also. --~--~-~--~~~---~--~~ 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: Shallow copying
On Mon, Oct 26, 2009 at 9:13 AM, Joril jor...@gmail.com wrote: On 26 Ott, 13:41, Mike Conley mconl...@gmail.com wrote: I see, but I need a proper shallow copy instead, with every attribute.. Is there no way? :/ What is missed? I am not sure what you mean by shallow copy. The fact that there are relations to be lazy loaded is present in the new object when instantiated. Obviously the objects pointed to by the relation are not copied, but I would call that a deep copy, and to do that you would need to iterate over them also. I'm sorry, by shallow copy I mean that only the parent object should be a copy (=different instance), while the related ones should be kept as-is... For example, given an object A that references B and C, a shallow copy of A would be made up of a copy of A that references the original B and C (not copies of B or C). So, in Python a non-automatic shallow copy would be: A = Some_class() A.refs = [B, C] Acopy = Some_class() Acopy.refs = A.refs My problem is that if A.refs has to be lazy loaded, when I try to copy it over to Acopy, it triggers the query, and I'd like to avoid that, while still copying the fact that Acopy.refs should be the same as A.refs. Thanks for your attention again! So, let's understand the underlying data model. We say, A is a parent object related to B and C children. (1) If this is a one-to-many relationship, then B and C will contain foreign keys pointing at A. Copying of A to Acopy cannot have references point at [B, C] because the children cannot point at two parents at the same time; there is only one foreign key column. (2) If this is a many-to-many relationship, then there will be another table between A and [B,C] managing the many-to-many connection and something needs to be done during the copy to preserve the relationships. Which do we have here? --~--~-~--~~~---~--~~ 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: ordering_list vs compound primary keys
On Mon, Oct 26, 2009 at 11:18 AM, Michael Bayer mike...@zzzcomputing.comwrote:
I haven't used ordering list much but perhaps you need to flush() before
such an operation, so that the renumbering performed by ordering list is
persisted and the previous object's identity key is updated to the new
value, so that the new one coming in does not conflict.
The issue here is not really ordering_list. It is can we update a compound
primary key in such a way that a new primary key appears to conflict with an
old primary key, even if only temporarily.
When the update to position is being performed on for primary key
[user_id:1,position:1] to [user_id:1,position:2] the new record conflicts
with the existing [user_id:1,position:2] that is going to be updated
to [user_id:1,position:3]. In theory, the conflict will not exist IF we are
able to process the position updates highest number first, but since the
error occurs in flush() processing I'm not sure SA can identify that fact.
Maybe Michael Baker can enlighten us on that.
I was able to reproduce the problem without using ordering_list.
http://pastebin.com/m5de2cfe
The important differences from original post:
# Blurb __init__() supplies position
class Blurb(object):
def __init__(self, blurb, position):
self.blurb = blurb
self.position = position
# mapper does not use ordering_list
mapper(User, users, properties={
'topten': relation(Blurb)})
# simulate what ordering_list needs to accomplish
u = session.query(User).get(uid)
new_blurb = Blurb('I am the new Number Two.',1)
for blurb in u.topten:
if blurb.position = new_blurb.position:
blurb.position = blurb.position + 1
u.topten.append(new_blurb)
session.commit()
Gives same exception.
sqlalchemy.orm.exc.FlushError: New instance Blurb at 0xeb9f50 with
identity key (class '__main__.Blurb', (99, 1)) conflicts with persistent
instance Blurb at 0xecd2d0
--
Mike Conley
--~--~-~--~~~---~--~~
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: Saved Queries (or text representation of the SQL)
On Fri, Oct 23, 2009 at 12:45 PM, jeff jeff.sie...@seagate.com wrote:
I would like to save a number of these in a database so size is
important (the serialized select() was somewhat large...) so I would
like to get the string representation of the raw SQL directly useable
by sqlalchemy if possible. As I have in my examples, the str(select)
doesn't seem directly useable as it is missing the parameters -
upper(host_status.site) = %(upper_1)s instead of upper
(host_status.site) = 'LCO' for example. Is there a way to get the
raw SQL text just as it is sent to the database and ready for reuse by
sqlalchemy (execute(text(SQLtext) )? Or do I have to construct my
own by doing string replaces on the parameters with the parameters as
found in .context? Seems like the raw SQL has to be available at some
point but don't know if the user has access to it.
This might be part of your answer:
Here is an arbitrary query in my database (I use ORM, but I'm sure you can
do equivalent with SQL expression language):
qry = sess.query(L.ListName,L.Description,LI.Item).\
join(LI).order_by(L.ListName,LI.Item).\
filter(L.ListName.startswith('SP'))
I can get the SQL as:
sql=qry.statement.compile()
string_sql = str(sql)
print string_sql
SELECT Lists.ListName, Lists.Description, ListItems.Item
FROM Lists JOIN ListItems ON Lists.ListName = ListItems.ListName
WHERE Lists.ListName LIKE :ListName_1 || '%%' ORDER BY
Lists.ListName, ListItems.Item
parameters are available as:
params = sql.params
print params
{u'ListName_1': 'SP'}
Now, save string_sql and params (you might need to get creative about
saving the dictionary) in your database. Later you can retrieve them and:
conn = whatever to get a good connection to database
results = conn.execute(text(string_sql), params).fetchall()
This approach has all the limitations of using text() as described in the
documentation. To me, the most important is that I have lost any knowledge
about the nature of each column. I do not know that the first column is
Lists.ListName. Maybe there is an attribute on the result set that allows
me to discover that information, but I don't know what it is.
Hope this helps a little
--~--~-~--~~~---~--~~
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: Saved Queries (or text representation of the SQL)
On Fri, Oct 23, 2009 at 1:47 PM, Michael Bayer mike...@zzzcomputing.comwrote: Much easier to use serializer. I agree with 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] Re: Shallow copying
On Fri, Oct 23, 2009 at 12:40 PM, Joril jor...@gmail.com wrote: Hi everyone! I'm trying to automatically build a shallow copy of a SA-mapped object.. At the moment my function is just: newobj = src.__class__() for prop in class_mapper(src.__class__).iterate_properties: setattr(newobj, prop.key, getattr(src, prop.key)) but I'm having troubles with lazy relations... Obviously getattr triggers the lazy loading, but since I don't need their values right away, I'd like to just copy the this should be lazy loaded-state of the attribute... Is this possible? Many thanks for your time! I did something similar. I iterated on class_mapper().columns to get the attributes to populate. That approach skipped all the relations, and in my case was exactly what I wanted Something like this (untested): newobj = src.__class__() for col in object_mapper(src).columns: setattr(newobj, col.name, getattr(src, col.name)) --~--~-~--~~~---~--~~ 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: bad result when querying for null values (in pk at least)
add allow_null_pks to your mapper arguments See http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html --~--~-~--~~~---~--~~ 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: bad result when querying for null values (in pk at least)
On Thu, Oct 15, 2009 at 9:21 AM, alexbodn.gro...@gmail.com wrote: thanks a lot mike, it's working great now. but this flag should be implied if one of the primary key fields is nullable (especially since it's not nullable by default). what would you think? You can argue just as easily that the null primary key means the record is incomplete and should not be available. It's an application code issue. --~--~-~--~~~---~--~~ 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: Simple join
Now I want to build a query to get all Workstations which are related to server 'foo'. This works: ws = DBSession.query(WorkStation).select_from(join(WorkStation, DHCPServer)).filter(DHCPServer.label == 'foo').all() but It's too complex. Is there an easier way? Something like: DBSession.query(WorkStation).filter(Workstation.server.label == 'foo').all() Thanks! ws = DBSession.query(WorkStation,DHCPServer).join(DHCPServer).filter(DHCPServer.label == 'foo') Should give the same result. You shouldn't need a select_from() for a simple query like this when all the join conditions are obvious. --~--~-~--~~~---~--~~ 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: order by a field of related object
Assuming you have the foreign keys defined, it should be fairly easy. session.query(Project).join(Country).order_by(Country.name) if you don't have the keys defined, you will need to add the join condition to the .join() -- Mike Conley --~--~-~--~~~---~--~~ 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: Number of row updated or deleted
Use rowcount property of the ResultProxy returned from delete/update result = conn.execute(tbl.delete()) count = result.rowcount Note that the quality of the number will depend on the underlying database and Python dbapi. --~--~-~--~~~---~--~~ 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: session.query object instead rowtuple
The column is available as e.Namefile, no need to subscript with numbers. On Mon, Sep 28, 2009 at 6:07 AM, Christian Démolis christiandemo...@gmail.com wrote: Hi everybody, I have a little problem with session.query. I try to optimize my queries with only attributes that i need. When we impose attribute, sqlalchemy return a rowtuple s = session.query(IdFile, NameFile) When we don't impose attribute, the return is the object s = session.query(File) Is it possible to obtain an sql alchemy object instead of rowtuple when we impose attributes??? I need it to avoid write this in my code for e in s: print e[0] it's more difficult to read than for e in s: print e.NameFile --~--~-~--~~~---~--~~ 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: Circular Dependancy Hell
You need to use argumentpost_update=True
on your thumbnails relation
http://www.sqlalchemy.org/docs/05/mappers.html#rows-that-point-to-themselves-mutually-dependent-rows
http://www.sqlalchemy.org/docs/05/mappers.html#rows-that-point-to-themselves-mutually-dependent-rowsHere
is a sample I used that seems to work. Interesting is that you cannot create
the tables with meta.create_all() because of te circular dependency. I
created the table in 2 separate calls.
class Image(Base):
__tablename__='image'
id = Column(Integer, primary_key=True)
project_id = Column(Integer, ForeignKey('project.id'))
def __repr__(self):
return I id:%s % self.id
class Project(Base):
__tablename__='project'
id = Column(Integer, primary_key=True)
thumbnail_id = Column(Integer, ForeignKey('image.id') )
images = relation('Image', backref=backref('project'),
primaryjoin=Project.id==Image.project_id,
foreign_keys=[Image.project_id]
)
thumbnail = relation(Image,
primaryjoin=Project.thumbnail_id==Image.id,
foreign_keys=[thumbnail_id],
uselist=False, post_update=True)
def __repr__(self):
return P id:%s thumb:%s % (self.id, self.thumbnail_id)
Image.__table__.create()
Project.__table__.create()
P1 = Project()
I1 = Image()
I2 = Image()
I3 = Image()
P1.images.extend([I1,I2,I3])
P1.thumbnail=I2
session.add(P1)
session.flush()
--~--~-~--~~~---~--~~
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: Tables reflected in Postgresql + MySQL but not Oracle.
Did you verify that the full query gives results?
SELECT table_name FROM all_tables
WHERE nvl(tablespace_name, 'no tablespace') NOT IN ('SYSTEM', 'SYSAUX')
AND owner = 'SCHEM';
I've been away from Oracle for a while, but I do remember it is unusual, but
still possible for user's tables to be in the SYSTEM or SYSAUX tablespaces.
Double check with
SELECT table_name, tablespace_name FROM all_tables WHERE owner = 'SCHEM';
--~--~-~--~~~---~--~~
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: Tables reflected in Postgresql + MySQL but not Oracle.
What does the second query report for tablespace_name? SYSTEM?
If so, you need to talk to your DBA about why the default tablespace for
your owner is SYSTEM. Normally the system is configured to have user tables
go somewhere else.
From the SQLAlchemy side, the maintainers of databases/oracle.py might
consider removing SYSTEM, SYSAUX condition from the table_names query when
schema is provided. I'm not sure of side effects, but should be OK, because
table list will still be filtered by schema name.
You could experiment with this yourself to see if that is the issue and
submit a ticket
A crude experiment (no guarantees, this might break everything) is to do
this early in your script, some time before creating engine and reflecting
the tables.
import sqlalchemy.databases.oracle
def my_table_names(self, connection, schema):# a modified version of
OracleDialect.table_names()
# note that table_names() isnt loading DBLINKed or synonym'ed tables
if schema is None:
s = select table_name from all_tables where
nvl(tablespace_name, 'no tablespace') NOT IN ('SYSTEM', 'SYSAUX')
cursor = connection.execute(s)
else:
# remove SYSTEM, SYSAUX tablespace filter from original query
s = select table_name from all_tables where OWNER = :owner #
removed SYSTEM tablespace filter
cursor = connection.execute(s, {'owner':
self._denormalize_name(schema)})
return [self._normalize_name(row[0]) for row in cursor]
sqlalchemy.databases.oracle.OracleDialect.table_name = my_table_names
--~--~-~--~~~---~--~~
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] Mapping arbitrary selectables
When mapping an arbitrary selectable, does mapper's primary_key argument
need to be a primary key in the base table?
Using 0.5.6, but I seem to remember same behavior in earlier versions.
This works and does not generate any errors:
t1 = Table('t1', meta, Column('foo', Integer, primary_key=True))
s1 = select([t1.c.foo])
class One(object): pass
mapper(One, s1, primary_key=[s1.c.foo])## Note: also OK without pk
argument
This raises an exception complaining about the primary key
t2 = Table('t2', meta, Column('bar', Integer))
s2 = select([t2.c.bar])
class Two(object): pass
mapper(Two, s2, primary_key=[s2.c.bar]) # same error using [t2.c.bar]
ArgumentError: Mapper Mapper|Two|%(31476816 anon)s could not assemble any
primary key columns for mapped table '%(31476816 anon)s'
--
Mike Conley
--~--~-~--~~~---~--~~
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: Mapping arbitrary selectables
Submitted ticket #1542 --~--~-~--~~~---~--~~ 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: getting data from primary keys
If I understand this, you want to construct a query that returns the primary keys in an arbitrary table? Try this: key_cols = [c for c in table.primary_key.columns] session.query(*key_cols).all() --~--~-~--~~~---~--~~ 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] Correlated subqueries and ORM
I am trying to figure out how to implement a correlated subquery with ORM. One of the complexities is that the query has a table that correlates to itself and I can't figure out how to do that aliasing within ORM queries. In a recent thread, http://groups.google.com/group/sqlalchemy/browse_thread/thread/c010ac1f326dbb2b Richie Ward asked about using subquery to find max item within a group of items Given this class class Content(Base): __tablename__ = 'content' revision_id = Column(Integer, primary_key=True) modulename = Column(Unicode(256)) content = Column(Unicode(102400), default='') summary = Column(Unicode(256)) created = Column(DateTime, default=datetime.now) for each modulename, select the the object with highest revision_id and non-blank content and the solution was something like this revision_ids = session.query(func.max(Content.revision_id)).\ filter(Content.content != '').group_by(Content.modulename).subquery() pages = session.query(Content.modulename, Content.revision_id, Content.content).\ filter(Content.revision_id.in_(revision_ids)).order_by(Content.modulename) The generated SQL (somewhat cleaned up for readability) is SELECT c1.modulename, c1.revision_id, c1.content FROM content c1 WHEREc1.revision_id IN (SELECT MAX(c2.revision_id) FROM content c2 WHERE c2.content != '' GROUP BY c2.modulename) ORDER BY c1.modulename In a very large database with a more complex query and depending on the underlying database engine, a correlated subquery will likely perform better, so I want to build this SQL using ORM syntax. SELECT c1.modulename, c1.revision_id, c1.content FROM content c1 WHEREc1.revision_id = (SELECT MAX(c2.revision_id) FROM content c2 WHERE c2.modulename = c1.modulename AND c2.content != '') ORDER BY c1.modulename How do we do that? None of my attempts manage to get the subquery where clause to include the correlation c2.modulename = c1.modulename. Maybe need to use alias somehow? -- Mike Conley --~--~-~--~~~---~--~~ 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: Update session extension after creation
Well, it looks like configure is a class method on Session, so when you do session.configure() you are configuring future sessions, not the current one. The extensions for a session instance are are a property named extensions. You could try setting that list yourself. session.extensions = [MySessionExtension()] to replace the existing extension or session.extensions.append(MySessionExtension()) to add yours to the list But understand the risk that this is modifying internals and might not work, and no guarantee it will work in future versions. -- Mike Conley On Thu, Sep 10, 2009 at 4:29 AM, asrenzo laurent.rah...@gmail.com wrote: I also tried session.configure(extension=MySessionExtension()) with no success On 10 sep, 10:13, Laurent Rahuel laurent.rah...@gmail.com wrote: Hi, I'm currently using a webframework which uses sqlalchemy. Each time a request hits the server, the framework creates a session object I can use during the page creation. I wish you could update this session with one of my SessionExtension but I'm facing a small problem: I tested my code with a standalone session I had created with this syntax : session = create_session(extension=MySessionExtension()) and everything works. When I try the same code in my web context and I do: from framework.db import session session.extension = MySessionExtension() . . . None of my extension session methods are called. Any idea ? --~--~-~--~~~---~--~~ 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: Update session extension after creation
As expected, the simple test works. Something else is happening.
In [7]: cpaste
Pasting code; enter '--' alone on the line to stop.
:Base=declarative_base()
:Base.metadata.bind = create_engine('sqlite:///', echo=True)
:class Foo(Base):
:__tablename__ = 'foo'
:id = Column(Integer, primary_key=True)
:Base.metadata.create_all()
:class MyExtension(SessionExtension):
:def after_flush(self, session, context):
:print '*** MyExtension.after_flush()'
:Session = scoped_session(sessionmaker())
:session = Session()
:print 'extensions before append', session.extensions
:session.extensions.append(MyExtension())
:print 'extensions after append', session.extensions
:session.add(Foo())
:session.flush()
:--
2009-09-10 09:52:09,328 INFO sqlalchemy.engine.base.Engine.0x...43b0 PRAGMA
table_info(foo)
2009-09-10 09:52:09,328 INFO sqlalchemy.engine.base.Engine.0x...43b0 ()
2009-09-10 09:52:09,342 INFO sqlalchemy.engine.base.Engine.0x...43b0
CREATE TABLE foo (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
2009-09-10 09:52:09,375 INFO sqlalchemy.engine.base.Engine.0x...43b0 ()
2009-09-10 09:52:09,375 INFO sqlalchemy.engine.base.Engine.0x...43b0 COMMIT
extensions before append []
extensions after append [__main__.MyExtension object at 0x025C48D0]
2009-09-10 09:52:09,405 INFO sqlalchemy.engine.base.Engine.0x...43b0 BEGIN
2009-09-10 09:52:09,405 INFO sqlalchemy.engine.base.Engine.0x...43b0 INSERT
INTO foo DEFAULT VALUES
2009-09-10 09:52:09,405 INFO sqlalchemy.engine.base.Engine.0x...43b0 []
*** MyExtension.after_flush()
--~--~-~--~~~---~--~~
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: Somewhat complex union_all() question
Nothing hackish about it. The SQL is doing exactly what you want; union the posting tables and join the result to users. Simple enough that the database engine should construct a reasonable plan. -- Mike Conley On Wed, Sep 9, 2009 at 8:41 PM, Seth seedifferen...@gmail.com wrote: Ok Mike, Tell me what you think about this: q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body, P1.created, P1.updated) q2 = DBSession.query(P2.id, P2.user_id, 'P2', P2.title, P2.body, P2.created, P2.updated) q3 = DBSession.query(P3.id, P3.user_id, 'P3', P3.title, P3.body, P3.created, P3.updated) subquery = DBSession.query().from_statement(union_all(q1, q2, q3)).subquery() posts = DBSession.query(subquery, User.name).filter (User.user_id==subquery.c.user_id) Kind of hackish, but... it seems to work? Seth --~--~-~--~~~---~--~~ 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: Somewhat complex union_all() question
On Wed, Sep 9, 2009 at 10:46 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Sep 9, 2009, at 8:41 PM, Seth wrote: Ok Mike, Tell me what you think about this: q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body, P1.created, P1.updated) q2 = DBSession.query(P2.id, P2.user_id, 'P2', P2.title, P2.body, P2.created, P2.updated) q3 = DBSession.query(P3.id, P3.user_id, 'P3', P3.title, P3.body, P3.created, P3.updated) subquery = DBSession.query().from_statement(union_all(q1, q2, q3)).subquery() posts = DBSession.query(subquery, User.name).filter (User.user_id==subquery.c.user_id) Kind of hackish, but... it seems to work? you should be able to call select() directly on the union_all() and send that as your subquery. What would that look like? I don't get it the syntax. This is also really close to answering the question I posted earlier about labeling literals in the first query of a union. I'll reply to that thread again when I have the answer completed. -- Mike Conley --~--~-~--~~~---~--~~ 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: Somewhat complex union_all() question
here is the sample code I am using http://pastebin.com/m6cd9c5dd -- Mike Conley On Wed, Sep 9, 2009 at 11:10 PM, Mike Conley mconl...@gmail.com wrote: On Wed, Sep 9, 2009 at 10:46 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Sep 9, 2009, at 8:41 PM, Seth wrote: Ok Mike, Tell me what you think about this: q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body, P1.created, P1.updated) q2 = DBSession.query(P2.id, P2.user_id, 'P2', P2.title, P2.body, P2.created, P2.updated) q3 = DBSession.query(P3.id, P3.user_id, 'P3', P3.title, P3.body, P3.created, P3.updated) subquery = DBSession.query().from_statement(union_all(q1, q2, q3)).subquery() posts = DBSession.query(subquery, User.name).filter (User.user_id==subquery.c.user_id) Kind of hackish, but... it seems to work? you should be able to call select() directly on the union_all() and send that as your subquery. What would that look like? I don't get it the syntax. This is also really close to answering the question I posted earlier about labeling literals in the first query of a union. I'll reply to that thread again when I have the answer completed. -- Mike Conley --~--~-~--~~~---~--~~ 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: Cascade option, what does all mean?
Your assumption should be correct. http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html#sqlalchemy.orm.relation On Tue, Sep 8, 2009 at 6:40 AM, Eloff dan.el...@gmail.com wrote: Hi, I see cascade='all, delete, delete-orphan' in the tutorial, but I thought I read in the docs elsewhere (can't seem to find the place atm) that all includes merge, delete, and others so that cascade='all, delete-orphan' should be equivalent? Is this correct? Thanks, -Dan --~--~-~--~~~---~--~~ 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: distinct (or group by) with max in sqlalchemy - how to?
See the documentation at http://www.sqlalchemy.org/docs/05/sqlexpression.html#functions Something like this Using ORM mapped classes session.query(Tabl.name, func.max(Tabl.cnt)).group_by(Tabl.name).all() or SQL expression language select([tabl.c.name,func.max(tabl.c.cnt)]).group_by(tabl.c.name).fetchall() --~--~-~--~~~---~--~~ 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: Single row of a One to Many query
On Thu, Sep 3, 2009 at 8:05 AM, Noufal nou...@gmail.com wrote:
stmt = session.query(Order.table.c.client_id,func.max
(Order.table.c.date).label('latest_order')).group_by
(Order.table.c.date).subquery()
I think your group_by needs to be Order.table.c.client_id to get latest
order per client
--
Mike Conley
--~--~-~--~~~---~--~~
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: http://www.sqlalchemy.org/docs/05/ormtutorial.html
Any chance this is the second iteration of declaring the User class in this session, and the first time was missing the primary_key? I run into this in interactive sessions and need to call clear_mappers() before redoing the class. It seems that the old mapper is still hanging around and causes the compilation error. -- Mike Conley --~--~-~--~~~---~--~~ 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: extended inserts
people = [Person('Mick Jagger'), Person('Keith Richards')]
s.add_all(people)
s.commit()
INSERT INTO person (name) VALUES (('Mick Jagger'), ('Keith Richards'))
I could be wrong for some database engine, but INSERT generally does not
support a bulk insert mechanism like this. The only bulk insert capability
is the INSERT ... SELECT FROM syntax.
DB API's, such as the Python DBAPI executemany() or SQLAlchemy, accept
something like a bulk insert, but actually generate multiple insert
statements.
--
Mike Conley
--~--~-~--~~~---~--~~
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: How to label text columns in a query
Either of these works for the individual queries, but when combined in the
union() or union_all() the result is still that the literal from the first
query is used on all result rows
q1 = session.query(A.data.label('somedata'), literal('A').label('source'))
q2 = session.query(B.data.label('somedata'), literal('B').label('source'))
qry = q1.union(q2)
generates the SQL
SELECT anon_1.somestuff AS somestuff, ? AS source
FROM (SELECT a.data AS somestuff, ? AS source
FROM a UNION ALL SELECT b.data AS somestuff, ? AS source
FROM b) AS anon_1
with bind parameters
['A', 'A', 'B']
q1 = session.query(A.data.label('somedata'),
literal_column('\'A\'').label('source'))
q2 = session.query(B.data.label('somedata'),
literal_column('\'B\'').label('source'))
qry = q1.union(q2)
generates the SQL
SELECT anon_1.somestuff AS somestuff, 'A' AS source
FROM (SELECT a.data AS somestuff, 'A' AS source
FROM a UNION SELECT b.data AS somestuff, 'B' AS source
FROM b) AS anon_1
The correct code would be
SELECT anon_1.somestuff AS somestuff, anon_1.source AS source
etc.
--
Mike Conley
--~--~-~--~~~---~--~~
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] Joining subqueries
What is correct way to join two subqueries? The example is somewhat
contrived, but illustrates the problem.
SQL might look like this
SELECT x.blah, y.blah
FROM (SELECT id, data AS blah FROM a) AS x
JOIN (SELECT id, data AS blah FROM b) AS y
ON x.id = y.id
Mapped classes are:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String)
def __repr__(s):
return 'A id:%s data:%s' % (s.id, s.data)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
data = Column(String)
def __repr__(s):
return 'B id:%s data:%s' % (s.id, s.data)
with some data:
session.add_all([A(data='a1'),A(data='a2'),B(data='b1'),B(data='b2'),])
session.commit()
and subqueries:
subqa = session.query(A.id, A.data.label('blah')).subquery()
subqb = session.query(B.id, B.data.label('blah')).subquery()
First attempt:
session.query(subqa.c.blah, subqb.c.blah)
as expected this give a cross join of all A's and B's
Second attempt:
session.query(subqa.c.blah, subqb.c.blah).join((subqb,subqb.c.id==subqa.c.id
))
gives and error:
AttributeError: 'NoneType' object has no attribute 'base_mapper'
probably because subqa is not a mapped entity
Third attempt (this one works):
class SubA(object): pass
mapper(SubA,subqa)
compile_mappers()
s.query(SubA.blah, subqb.c.blah).join((subqb,subqb.c.id==SubA.id))
Is there a more direct way without needing to create the temporary mapped
entity?
It appears that when using Session.query.join(), the first parameter to
query() must be a mapped entity or an attribute of a mapped entity. Is that
true?
--
Mike Conley
--~--~-~--~~~---~--~~
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: Somewhat complex union_all() question
It works and will probably be OK. Using this style (query for user multiple times) in a really big database could lead to a performance problem depending on how the underlying database engine constructs a query plan. Try it and see how it goes. -- Mike Conley On Fri, Aug 28, 2009 at 2:22 PM, Seth seedifferen...@gmail.com wrote: Mike, Thanks again for your posts. What about something like: q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body, P1.created, P1.updated, User.name).filter(P1.user_id==User.id) q2 = DBSession.query(P2.id, P2.user_id, 'P2', P2.title, P2.body, P2.created, P2.updated, User.name).filter(P2.user_id==User.id) q3 = DBSession.query(P3.id, P3.user_id, 'P3', P3.title, P3.body, P3.created, P3.updated, User.name).filter(P3.user_id==User.id) posts = q1.union_all(q2, q3) ? Seth On Aug 27, 2:45 pm, Mike Conley mconl...@gmail.com wrote: OK, I can mostly answer my own question q1=session.query(P1.userid,P1.extra,P1.title,P1.body) q2=session.query(P2.userid,'X',P2.title,P2.body) q3=session.query(P3.userid,'X',P3.title,P3.body) subq=q1.union_all(q2,q3).subquery() q = session.query(USER.email, subq).join((subq, USER.userid==subq.c.userid)) gives the desired SQL But what if I have a real requirement to retrieve the email address last in the row? q = session.query(subq,USER.email).join((USER, USER.userid==subq.c.userid)) and q = session.query(subq,USER.email).join((subq, USER.userid==subq.c.userid)) both complain AttributeError: 'NoneType' object has no attribute 'base_mapper' Probably because the subq is first in the list and is not an entity -- Mike Conley On Thu, Aug 27, 2009 at 5:05 PM, Mike Conley mconl...@gmail.com wrote: Assuming a declarative based class USER exists, then you can join each of the queries q1, q2, q3 to USER like this: q1 = session.query(P1.userid,P1.extra,P1.title,P1.body,USER.email) q1 = q1.join((USER,USER.userid==P1.userid)) q2 = session.query(P2.userid,'X',P2.title,P2.body,USER.email) q2 = q2.join((USER,USER.userid==P2.userid)) q3 = session.query(P3.userid,'X',P3.title,P3.body,USER.email) q3 = q3.join((USER,USER.userid==P3.userid)) q=q1.union_all(q2,q3) Not a very elegant solution, and probably leads to an inefficient query plan in many databases. Can anyone tell us how to join the result of union_all with another table? Probably a subquery()? Effectively: - create q1, q2, q3 as selects from P1, P2, P# as in original solution - combine q1, q2, q3 with a union_all() - add column USER.email to the query - join resulting query to USER based on userid column in the union_all statement SQL would look something like this: SELECT qry.a, qry.b, qry.c, user.x FROM (SELECT a,b,c FROM p1 UNION ALL SELECT a,b,c FROM p2 UNION ALL SELECT a,b,c FROM p3) as qry JOIN USER on qry.a = USER.a but I can't seem to get this result in SQLAlchemy -- Mike Conley --~--~-~--~~~---~--~~ 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: how to make unique constrain within ORM
Constraints are defined in __table_args__ try:
__table_args__ = (
UniqueConstraint('api_id', 'daskey_id', name='uix_1'),
{'mysql_engine':'InnoDB'})
--~--~-~--~~~---~--~~
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: Somewhat complex union_all() question
OK, I can mostly answer my own question q1=session.query(P1.userid,P1.extra,P1.title,P1.body) q2=session.query(P2.userid,'X',P2.title,P2.body) q3=session.query(P3.userid,'X',P3.title,P3.body) subq=q1.union_all(q2,q3).subquery() q = session.query(USER.email, subq).join((subq, USER.userid==subq.c.userid)) gives the desired SQL But what if I have a real requirement to retrieve the email address last in the row? q = session.query(subq,USER.email).join((USER, USER.userid==subq.c.userid)) and q = session.query(subq,USER.email).join((subq, USER.userid==subq.c.userid)) both complain AttributeError: 'NoneType' object has no attribute 'base_mapper' Probably because the subq is first in the list and is not an entity -- Mike Conley On Thu, Aug 27, 2009 at 5:05 PM, Mike Conley mconl...@gmail.com wrote: Assuming a declarative based class USER exists, then you can join each of the queries q1, q2, q3 to USER like this: q1 = session.query(P1.userid,P1.extra,P1.title,P1.body,USER.email) q1 = q1.join((USER,USER.userid==P1.userid)) q2 = session.query(P2.userid,'X',P2.title,P2.body,USER.email) q2 = q2.join((USER,USER.userid==P2.userid)) q3 = session.query(P3.userid,'X',P3.title,P3.body,USER.email) q3 = q3.join((USER,USER.userid==P3.userid)) q=q1.union_all(q2,q3) Not a very elegant solution, and probably leads to an inefficient query plan in many databases. Can anyone tell us how to join the result of union_all with another table? Probably a subquery()? Effectively: - create q1, q2, q3 as selects from P1, P2, P# as in original solution - combine q1, q2, q3 with a union_all() - add column USER.email to the query - join resulting query to USER based on userid column in the union_all statement SQL would look something like this: SELECT qry.a, qry.b, qry.c, user.x FROM (SELECT a,b,c FROM p1 UNION ALL SELECT a,b,c FROM p2 UNION ALL SELECT a,b,c FROM p3) as qry JOIN USER on qry.a = USER.a but I can't seem to get this result in SQLAlchemy -- Mike Conley --~--~-~--~~~---~--~~ 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: Somewhat complex union_all() question
Assuming a declarative based class USER exists, then you can join each of the queries q1, q2, q3 to USER like this: q1 = session.query(P1.userid,P1.extra,P1.title,P1.body,USER.email) q1 = q1.join((USER,USER.userid==P1.userid)) q2 = session.query(P2.userid,'X',P2.title,P2.body,USER.email) q2 = q2.join((USER,USER.userid==P2.userid)) q3 = session.query(P3.userid,'X',P3.title,P3.body,USER.email) q3 = q3.join((USER,USER.userid==P3.userid)) q=q1.union_all(q2,q3) Not a very elegant solution, and probably leads to an inefficient query plan in many databases. Can anyone tell us how to join the result of union_all with another table? Probably a subquery()? Effectively: - create q1, q2, q3 as selects from P1, P2, P# as in original solution - combine q1, q2, q3 with a union_all() - add column USER.email to the query - join resulting query to USER based on userid column in the union_all statement SQL would look something like this: SELECT qry.a, qry.b, qry.c, user.x FROM (SELECT a,b,c FROM p1 UNION ALL SELECT a,b,c FROM p2 UNION ALL SELECT a,b,c FROM p3) as qry JOIN USER on qry.a = USER.a but I can't seem to get this result in SQLAlchemy -- Mike Conley --~--~-~--~~~---~--~~ 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] How to label text columns in a query
Here is the problem
I have two mapped classes (A and B) and want to create a union_all query.
q1 = session.query(A.data, '\'A\'') # includes a literal 'A' in the result
q2 = session.query(B.data, '\'B\'')
qry = q1.union_all(q2)
generates this SQL:
SELECT anon_1.data, 'A'
FROM (SELECT a.data AS data, 'A'
FROM a UNION ALL SELECT b.data AS data, 'B'
FROM b) AS anon_1
and plugs a 'A' into each row of the result
I would want to get 'A' or 'B' in the result depending on which query
produced the row and it would seem to work if I could apply a label to the
literal column 'A' similar to what I can do to the columns. Get the effect
of
q1 = session.query(A.data.label('somestuff'), '\'A\''.label('somelabel'))
# of course str objects do not have a method label, so that doesn't work
q2 = session.query(B.data, '\'B\'')
qry = q1.union_all(q2)
and generate SQL like
SELECT anon_1.somestuff AS somestuff, anon_1.somelabel as somelabel
FROM (SELECT a.data AS somestuff, 'A' as somelabel
FROM a UNION ALL SELECT b.data AS data, 'B'
FROM b) AS anon_1
which will give the desired result
--
Mike Conley
--~--~-~--~~~---~--~~
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: multiple insert with default values
Look at the generated SQL. The Python DBAPI uses one INSERT statement for
all rows inserted when using executemany(). In this case,
INSERT INTO test (col1, col2) VALUES (?, ?)
SA created the column list from the first set of values provided to
i.insert(), and so supplied a NULL value for col2 in the second row. SA had
no choice, the code must provide values for each column and since your code
did not supply a value, SA used None.
i.execute([{col1 : 2, col2 : 5}, {col1 : 1}])
x...94f0 INSERT INTO test (col1, col2) VALUES (?, ?)
x...94f0 [[2, 5], [1, None]]
x...94f0 COMMIT
Implied rule is: when inserting many records, provide same value list for
each row.
--
Mike Conley
On Wed, Aug 26, 2009 at 5:36 AM, menuge men...@gmail.com wrote:
Hi all,
I d like to insert a list of dictionary in a simple MySQL table but, I
have a problem, in my case, the MySQL default values are not
supported...
The table is very simple; 2 columns and a default value on the col2:
##
CREATE TABLE `test` (
`col1` int(11) default NULL,
`col2` int(11) default '3'
)
##
Here is the python code:
##
from sqlalchemy import *
db = create_engine(mysql://r...@localhost/test)
meta = MetaData(db)
meta.echo = True
table = Table(test, meta, autoload=True)
i = table.insert()
i.execute([{col1 : 2, col2 : 5}, {col1 : 1}])
print list(db.execute(SELECT * FROM test))
##
The result is:
[(2L, 5L), (1L, None)]
I don't understand... In my opinion, the result should be: [(2L, 5L),
(1L, 3L)]
I use Python 2.5.2 and sqlalchemy 0.4.7
Can someone help me please?
thks
--~--~-~--~~~---~--~~
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: Declarative way of delete-orphan
Add cascade='delete-orphan' to the relation definition for children.
cascade='all,delete-orphan' is also a fairly common option.
See the documentation for other options in cascade.
http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html#sqlalchemy.orm.relation
--
Mike Conley
On Wed, Aug 26, 2009 at 11:20 AM, rajasekhar911 rajasekhar...@gmail.comwrote:
Hi
How do i define a delete-orphan using declarative base?
I am using sqlite and SA0.5.5
I have defined a one to one relation.
class Child(DeclarativeBase):
__tablename__='children'
id=Column(String(50),primary_key=True)
parent_id=Column(String(50),ForeignKey
('parent.id',onupdate=CASCADE,ondelete=CASCADE))
name=Column(String(50))
class Parent(DeclarativeBase):
__tablename__='parent'
id=Column(String(50),primary_key=True)
name=Column(String(50))
children=relation('Child', uselist=False)
when i delete the parent it makes the parent_id None in Child.
I tried giving ondelete=DELETE according to
http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/schema.html#sqlalchemy.schema.ForeignKey
ondelete – Optional string. If set, emit ON DELETE value when
issuing DDL for this constraint. Typical values include CASCADE,
DELETE and RESTRICT.
But gave syntax error while trying to create the child table near
DELETE
I tried making parent_id as primarykey for Child.But that gave the
error Constraint tried to blank out the
PrimaryKey for instance
what am i doing wrong?
thnx in advance.
--~--~-~--~~~---~--~~
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: Somewhat complex union_all() question
Did you try something like this? q1=session.query(P1.userid, P1.extra, P1.title, P1.body) q2=session.query(P2.userid, 'X', P2.title, P2.body) q3=session.query(P3.userid, 'X', P3.title, P3.body) q=q1.union_all(q2).union_all(q3) -- Mike Conley On Wed, Aug 26, 2009 at 10:45 PM, Seth seedifferen...@gmail.com wrote: I have three different types of post tables with all of the same columns except that the first table has an extra column named type (placed in-between the 'user_id' and 'title' columns of the other tables). I want to do a UNION ALL that will combine the data from all these tables into a single list (with a blank value for the tables missing the type column), and then I want to sort the posts by creation date. However, try as I might, I cannot seem to get this to work without throwing me an ArgumentError of All selectables passed to CompoundSelect must have identical numbers of columns; select #1 has 7 columns, select #2 has 6. I suspect this has something to do with SQLAlchemy auto-magically reading the DeclarativeBase table classes. The SQL version of this query would look something like: SELECT 'cat1' as category, * FROM posts_1 UNION ALL SELECT 'cat2', id, user_id, 'NONE', title, body, created, updated FROM posts_2 UNION ALL SELECT 'cat3', id, user_id, 'NONE', title, body, created, updated FROM posts3 ORDER BY created DESC LIMIT 10 How can I get this to translate into SQLAlchemy with the extra 'NONE' for the tables that don't have a type column without giving me the ArgumentError? Thanks, Seth --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
