[sqlalchemy] in_ Subselect
Hi, This might be a noob question, but I am trying to reproduce the following sql query in SA select user_id, friend_id from follows where friend_id not (in select id from users); First, I do this: subquery = Session.query(User.id).subquery() Then q = follows_table.select().where(not_(follows_table.c.friend_id.in_ (subquery))) q turns into: SELECT follows.user_id, follows.friend_id FROM follows, (SELECT users.id AS id FROM users) AS anon_1 WHERE follows.friend_id NOT IN SELECT users.id FROM users I'm not really sure where the first subselect comes from. Also, it isn't a valid query in postgres because the second SELECT users.id FROM users needs to be in parens. Am I missing something? Thanks, 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Overriding reflected columns in SqlSoup?
Hi Neil
I managed to make queries on those tables by creating a new table
definition and getting a class mapping from that explicitly, for
example:
from sqlalchemy.ext.sqlsoup import SqlSoup
from sqlalchemy import *
engine = create_engine('sqlite:///:memory:')
metadata = MetaData(bind=engine)
retailer_table = Table('retailer', metadata, Column('retailer_id',
primary_key=True), autoload=True)
db = SqlSoup(metadata)
Retailer = db.map(retailer_table)
Retailer.first()
MappedRetailer(retailer_id=33199, ...)
So then the Retailer class returns MappedRetailer instances even
though the underlying schema defines no primary key. That part seemed
fine, but I then had to work with relations on those tables that I was
mapping explicitly and I found it easier at that point just to skip
SqlSoup and define the table metadata and mapping myself.
I hope that helps.
Stephen Emslie
On Sun, Apr 26, 2009 at 11:48 PM, NeilK neilku...@gmail.com wrote:
Hi Stephen, did you find a way to access those tables without a
primary key using SqlSoup?
Thanks,
-neil
On Apr 25, 3:42 am, Stephen Emslie stephenems...@gmail.com wrote:
I am using SqlSoup to do a little maintenance on a database whose
schema I have no control over. Unfortunately some tables are without a
primary key, and thus SqlSoup complains when accessing them:
sqlalchemy.ext.sqlsoup.PKNotFoundError: table 'category' does not have
a primary key defined
When reflecting tables directly with sqlalchemy, using Table(name,
meta, autoload=True), one can override the reflected columns to
compensate for the lack of a primary key. Is this possible in SqlSoup?
Stephen Emslie
--~--~-~--~~~---~--~~
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: in_ Subselect
cant reproduce.
test case:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
metadata = MetaData()
Base = declarative_base(metadata=metadata)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
follows_table = Table('follows', metadata, Column('friend_id'))
Session = sessionmaker()()
subquery = Session.query(User.id).subquery()
q =
follows_table.select().where(not_(follows_table.c.friend_id.in_(subquery)))
print q
output:
SELECT follows.friend_id
FROM follows
WHERE follows.friend_id NOT IN (SELECT users.id
FROM users)
Mike Lewis wrote:
Hi,
This might be a noob question, but I am trying to reproduce the
following sql query in SA
select user_id, friend_id from follows where friend_id not (in select
id from users);
First, I do this:
subquery = Session.query(User.id).subquery()
Then
q = follows_table.select().where(not_(follows_table.c.friend_id.in_
(subquery)))
q turns into:
SELECT follows.user_id, follows.friend_id
FROM follows, (SELECT users.id AS id
FROM users) AS anon_1
WHERE follows.friend_id NOT IN SELECT users.id
FROM users
I'm not really sure where the first subselect comes from. Also, it
isn't a valid query in postgres because the second SELECT users.id
FROM users needs to be in parens.
Am I missing something?
Thanks,
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: in_ Subselect
That's incredibly strange, because I ran your exact code and got this
for output
SELECT follows.friend_id
FROM follows, (SELECT users.id AS id
FROM users) AS anon_1
WHERE follows.friend_id NOT IN SELECT users.id
FROM users
I checked which version of SA I was running and it was 0.5.2
Upgraded it 0.5.3, and now I get the correct output. I had assumed I
was running the latest because I did a clean install a couple weeks
ago.
I am guessing it was something fixed in the latest version. Sorry for
the bother.
Thanks,
Mike
On Apr 28, 7:47 am, Michael Bayer mike...@zzzcomputing.com wrote:
cant reproduce.
test case:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
metadata = MetaData()
Base = declarative_base(metadata=metadata)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
follows_table = Table('follows', metadata, Column('friend_id'))
Session = sessionmaker()()
subquery = Session.query(User.id).subquery()
q =
follows_table.select().where(not_(follows_table.c.friend_id.in_(subquery)))
print q
output:
SELECT follows.friend_id
FROM follows
WHERE follows.friend_id NOT IN (SELECT users.id
FROM users)
Mike Lewis wrote:
Hi,
This might be a noob question, but I am trying to reproduce the
following sql query in SA
select user_id, friend_id from follows where friend_id not (in select
id from users);
First, I do this:
subquery = Session.query(User.id).subquery()
Then
q = follows_table.select().where(not_(follows_table.c.friend_id.in_
(subquery)))
q turns into:
SELECT follows.user_id, follows.friend_id
FROM follows, (SELECT users.id AS id
FROM users) AS anon_1
WHERE follows.friend_id NOT IN SELECT users.id
FROM users
I'm not really sure where the first subselect comes from. Also, it
isn't a valid query in postgres because the second SELECT users.id
FROM users needs to be in parens.
Am I missing something?
Thanks,
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
-~--~~~~--~~--~--~---
[sqlalchemy] secondary table order by count help
posts_table = sa.Table('posts', metadata,
sa.Column(id,sa.Integer, sa.Sequence
('post_id_seq'), primary_key=True),
sa.Column('userid', sa.types.Integer,
sa.ForeignKey('users.id'), nullable = False),
sa.Column('title', sa.types.String(255),
nullable = False, unique=False),
sa.Column('summary',sa.types.String(4098),
nullable = False),
)
location_tags_table = sa.Table('location_tag', metadata,
sa.Column('postid', sa.types.Integer(),
sa.ForeignKey('posts.id')),
sa.Column('locationid', sa.types.Integer(),
sa.ForeignKey('locations.id')),
)
locations_table = sa.Table('locations', metadata,
sa.Column('id', sa.types.Integer(), sa.Sequence
('location_uid_seq'), primary_key=True),
sa.Column('name', sa.types.String
()),
)
class mappers
class Post(object): -- for posts_table
def __init__(self):
pass
class Location(object): -- locations_table
def __init__(self):
pass
post_table_mapper = orm.mapper(Post, posts_table, properties={
'comments':orm.relation(Comment, backref='post'),
'locations':orm.relation(Location, secondary=location_tags_table,
backref='posts'),
})
I want to get the all the locations which are popular by descending
order. How to do that?
Thanks a lot
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] reflection with app server
hello, i am looking for help and explanation to reflect tables in context of app server like cherrypy. i can't find a 'place' to auto-load and map tables. if you do it as part of cp start thread (e.g. http://cherrypy.org/wiki/CustomPlugins, http://tools.cherrypy.org/wiki/Databases), we load/map often; i sure don't want to load/map for every app user. with reflection, i need an engine, which i don't get with app server until app server starts ... and then it looks to late. anybody can explain? thx, paul --~--~-~--~~~---~--~~ 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] InvalidRequestError: The transaction is inactive due to a rollback... using sqlite with multiple commits.
Hello, In my application I have a function that looks more or less like this def run(self): # process first object in sequence for firstObject in firstObjects: self.session.add(firstObject) self.session.commit() # process second object in sequence # lots of these, so break along the way count = 0 for secondObject in secondObjects: self.session.add(secondObject) count += 1 if (count 100): #** self.session.commit() #** count = 0 time.sleep(1) # pause to let other process access the db self.session.commit() # process third objects for thirdObject in thirdObjects: self.session.add(thirdObject) self.session.commit() The commit nested inside the second loop (highlighted by asterisks) is potentially called many times (occasionally there are thousands of objects to deal with). intermittently that commit will produce the following error: Traceback (most recent call last): File C:\Aptina\pop\tester\AptinaStagingService.py, line 106, in __init__ self.run(self.pushPath,self.stagingPath) File C:\Aptina\pop\tester\AptinaStagingService.py, line 231, in run self.session.commit() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 673, in commit self.transaction.commit() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 378, in commit self._prepare_impl() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 351, in _prepare_impl self._assert_is_active() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 247, in _assert_is_active The transaction is inactive due to a rollback in a InvalidRequestError: The transaction is inactive due to a rollback in a subtransaction. Issue rollback() to cancel the transaction. I've read elsewhere in this group (http://groups.google.com/group/ sqlalchemy/browse_thread/thread/b87af73232998fe4) about this error message, but I'm not sure what they mean by squelching the original exception somewhere. Can someone please help me understand why I'm getting this error and ideas on how to fix it. Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] InvalidRequestError: The transaction is inactive... using sqlite and multiple commits
Hello, In my application I have a function that looks more or less like this def run(self): # process first object in sequence for firstObject in firstObjects: self.session.add(firstObject) self.session.commit() # process second object in sequence # lots of these, so break along the way count = 0 for secondObject in secondObjects: self.session.add(secondObject) count += 1 if (count 100): #** self.session.commit() #** count = 0 time.sleep(1) # pause to let other process access the db self.session.commit() # process third objects for thirdObject in thirdObjects: self.session.add(thirdObject) self.session.commit() The commit nested inside the second loop (highlighted by asterisks) is potentially called many times (occasionally there are thousands of objects to deal with). intermittently that commit will produce the following error: Traceback (most recent call last): File C:\StagingService.py, line 106, in __init__ self.run(self.pushPath,self.stagingPath) File C:\StagingService.py, line 231, in run self.session.commit() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 673, in commit self.transaction.commit() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 378, in commit self._prepare_impl() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 351, in _prepare_impl self._assert_is_active() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 247, in _assert_is_active The transaction is inactive due to a rollback in a InvalidRequestError: The transaction is inactive due to a rollback in a subtransaction. Issue rollback() to cancel the transaction. I've read elsewhere in this group (http://groups.google.com/group/ sqlalchemy/browse_thread/thread/b87af73232998fe4) about this error message, but I'm not sure what they mean by squelching the original exception somewhere. Can someone please help me understand why I'm getting this error and ideas on how to fix it. Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: InvalidRequestError: The transaction is inactive due to a rollback... using sqlite with multiple commits.
squelching typically means one of two things. either you're doing this: try: # do stuff with session except: print error ! # .. keep going or, you are allowing concurrent access to a single session with multiple threads, one of your threads is throwing an exception (usually due to the corrupted state of the session, since the session is not mutexed) and the other thread gets this error. On Apr 28, 2009, at 4:53 PM, Daniel wrote: Hello, In my application I have a function that looks more or less like this def run(self): # process first object in sequence for firstObject in firstObjects: self.session.add(firstObject) self.session.commit() # process second object in sequence # lots of these, so break along the way count = 0 for secondObject in secondObjects: self.session.add(secondObject) count += 1 if (count 100): #** self.session.commit() #** count = 0 time.sleep(1) # pause to let other process access the db self.session.commit() # process third objects for thirdObject in thirdObjects: self.session.add(thirdObject) self.session.commit() The commit nested inside the second loop (highlighted by asterisks) is potentially called many times (occasionally there are thousands of objects to deal with). intermittently that commit will produce the following error: Traceback (most recent call last): File C:\Aptina\pop\tester\AptinaStagingService.py, line 106, in __init__ self.run(self.pushPath,self.stagingPath) File C:\Aptina\pop\tester\AptinaStagingService.py, line 231, in run self.session.commit() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 673, in commit self.transaction.commit() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 378, in commit self._prepare_impl() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 351, in _prepare_impl self._assert_is_active() File c:\python25\Lib\site-packages\sqlalchemy\orm\session.py, line 247, in _assert_is_active The transaction is inactive due to a rollback in a InvalidRequestError: The transaction is inactive due to a rollback in a subtransaction. Issue rollback() to cancel the transaction. I've read elsewhere in this group (http://groups.google.com/group/ sqlalchemy/browse_thread/thread/b87af73232998fe4) about this error message, but I'm not sure what they mean by squelching the original exception somewhere. Can someone please help me understand why I'm getting this error and ideas on how to fix it. Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] obtaining a table schema
Hi gang, I've recently started using sqlalchemy, so hopefully this isn't a stupid question... I was wondering whether there was an easy way to obtain a particular table's schema if one is using just bare connection (ie, not using any special orm's). Specifically, is there a utility method somewhere which allows one to obtain the primary key of a table? Thanks!! paul -- Paul Rigor Graduate Student Institute for Genomics and Bioinformatics Donald Bren School of Information and Computer Sciences University of California in Irvine 248 ICS2 Bldg. +1 (760) 536 - 6767 (skype) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
