[sqlalchemy] Re: Q: fetch value of autoincrement column
Don't you want that non-null column to be a foreign key ? 2009/5/21 Adrian von Bidder avbid...@fortytwo.ch: Hi, Is it possible to fetch the values of an autoincrement field without flushing the object to the DB? (In postgres, I obviously can manually fetch nextval of the automatically generated sequence, but I lose the portability that way ...) Why? Because I need the id to generate data that will be filled into some (non- null) columns of the table row. So I can't flush since I'll get an IntegrityError about non-null columns, and I can't fill those columns without knowing the id that's going to be assigned. (Yes, I can use dummy values, then flush(), and then update the row before committing. But that's not exactly elegant...) cheers -- vbi -- Fnord. --~--~-~--~~~---~--~~ 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: Small note on reading SA docs
Oh, I was not aware. The 05 docs is, as you say, addressing some of my points. Thanks On May 22, 1:14 am, semafor jo...@drange.net wrote: Hi. Currently, the documentation for SA is not easy for the eyes. Usinghttp://www.sqlalchemy.org/docs/04/sqlalchemy_engine.htmlas an example, there are ways to improve this: * Quasi syntax highlighting the function and it's arguments. Having def create(self, entity, **kwargs) all in bold is making it hard to browse through the functions by name as, at least, I often do. I also takes some effort separating the arguments from each other. For instance, close_with_result=False is also all in bold. False should undoubtedly be of a different format than the argument name. * Differentiate class documentation from function documentation. Today, the class header is just ~2px larger than the function header, i.e., not a big enough deviation. Using 30%+ larger header or wrapping it in a styled box should do it. * De-emphasizing back to section top. The link is almost the same size as the class ancestor (constructor argument), which requires effort to separate. * The darkcell div is indeed superfluous. One could create the same effect of division with white space and/or horizontal bordering. * Emphasizing class properties and module includes. The default indentation of definition data is not sufficient. * Generally using white space more efficiently. I could happily assist someone in charge of the site, or do it myself. For the latter, I probably need some approval, branches of both sqlalchemy and sqlalchemyorg repos, or both? Anyway, SA is an impressive project. Kind regards, Jonas. --~--~-~--~~~---~--~~ 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] TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30
Hello everyone, After some time of operation (a day or so), from time to time I'm getting this error: TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 I have to stress that not much activity is taking place on that OS as it is development installation. Does anybody know what could be causing this? Restarting apache or postgres eliminates that problem, but then it reappears. I'm getting sick of this. Does anybody know what could be the root cause? How to fix this? My app uses mod_python / SQLA 5.3. The backend is Postgres 8.1 on RH 5.3. Regards, mk --~--~-~--~~~---~--~~ 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] Filtering a polymorphic with 'any'
Hi,
I am trying the following:
import sqlalchemy as sqa
from sqlalchemy import orm
engine = sqa.create_engine(sqlite://, echo=True)
meta = sqa.MetaData(bind=engine)
Session = orm.scoped_session(orm.sessionmaker(bind=engine,
autocommit=True,autoflush=False))
tab = sqa.Table(test, meta,
sqa.Column(poly, sqa.Integer),
sqa.Column(id, sqa.Integer, primary_key=True),
sqa.Column(parent, sqa.Integer, sqa.ForeignKey(test.id)),
sqa.Column(other, sqa.Integer, sqa.ForeignKey(test.id)),
)
meta.create_all()
class C1(object):
pass
class C2(C1):
pass
Session.mapper(C1, tab, polymorphic_on=tab.c.poly, polymorphic_identity=0,
properties={
children: orm.relation(C2, primaryjoin=tab.c.parent==tab.c.id),
})
Session.mapper(C2, inherits=C1, polymorphic_identity=1)
print C1.query.filter(C1.children.any()).all()
the print statement gives me
AttributeError: 'ClauseList' object has no attribute 'proxy_set'
Where am I wrong?
--
Jazz is not dead, it just smells funny (FZ)
--~--~-~--~~~---~--~~
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] Filtering a polymorphic with 'any'
Hi,
I am trying the following:
import sqlalchemy as sqa
from sqlalchemy import orm
engine = sqa.create_engine(sqlite://, echo=True)
meta = sqa.MetaData(bind=engine)
Session = orm.scoped_session(orm.sessionmaker(bind=engine,
autocommit=True,autoflush=False))
tab = sqa.Table(test, meta,
sqa.Column(poly, sqa.Integer),
sqa.Column(id, sqa.Integer, primary_key=True),
sqa.Column(parent, sqa.Integer, sqa.ForeignKey(test.id)),
sqa.Column(other, sqa.Integer, sqa.ForeignKey(test.id)),
)
meta.create_all()
class C1(object):
pass
class C2(C1):
pass
Session.mapper(C1, tab, polymorphic_on=tab.c.poly, polymorphic_identity=0,
properties={
children: orm.relation(C2, primaryjoin=tab.c.parent==tab.c.id),
})
Session.mapper(C2, inherits=C1, polymorphic_identity=1)
print C1.query.filter(C1.children.any()).all()
the print statement gives me
AttributeError: 'ClauseList' object has no attribute 'proxy_set'
Where am I wrong?
--
Jazz is not dead, it just smells funny (FZ)
--~--~-~--~~~---~--~~
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: Small note on reading SA docs
On Friday 22 May 2009 01.59:13 Michael Bayer wrote: otherwise if you have any advice on how to get 0.4/0.3 delisted from such a prominent place on Google, that would be appreciated. Since removing them entirely is an option for you, perhaps just completely remove them from search engines via robots.txt? (And have a page linking to and describing the 0.3/0.4 docs in a way so that people searching for them explicitly still find them.) cheers -- vbi -- The most interesting [DNS weirdness] is that when I visit the Asus website two Asus IPs (one in the US, one in Taiwan) will query my nameserver for the . record for an entire week. -- Koos van den Hout signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Q: fetch value of autoincrement column
On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote: Don't you want that non-null column to be a foreign key ? Would that make a difference? cheers -- vbi 2009/5/21 Adrian von Bidder avbid...@fortytwo.ch: Hi, Is it possible to fetch the values of an autoincrement field without flushing the object to the DB? (In postgres, I obviously can manually fetch nextval of the automatically generated sequence, but I lose the portability that way ...) Why? Because I need the id to generate data that will be filled into some (non- null) columns of the table row. So I can't flush since I'll get an IntegrityError about non-null columns, and I can't fill those columns without knowing the id that's going to be assigned. (Yes, I can use dummy values, then flush(), and then update the row before committing. But that's not exactly elegant...) cheers -- vbi -- Fnord. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- -- Der Glaube versetzt Berge, der Zweifel erklettert sie. -- Friedrich Georg Jünger signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Q: fetch value of autoincrement column
Adrian, Adrian von Bidder wrote: Hi, Is it possible to fetch the values of an autoincrement field without flushing the object to the DB? (In postgres, I obviously can manually fetch nextval of the automatically generated sequence, but I lose the portability that way ...) Why? Because I need the id to generate data that will be filled into some (non- null) columns of the table row. So I can't flush since I'll get an IntegrityError about non-null columns, and I can't fill those columns without knowing the id that's going to be assigned. (Yes, I can use dummy values, then flush(), and then update the row before committing. But that's not exactly elegant...) This is one of the beauties of SQLA which it took me a while to catch on to. I use SQLA ORM (declarative) and you can just do: lang = session.query(db.Language).get(1) ca = db.Country_Ls() ca.language = lang ca.name = 'some country' reg = db.Region_Ls() reg.language = lang reg.name = 'some region' reg.country_ls = ca session.add(ca) session.add(reg) print ca print reg session.flush() print '===' print 'flushed' print '===' print ca print ca.countryid print '' print 'region' print reg print reg.fk_countryid Which gives me this output and as you can see the actual primary key and foreign key are only known after I do a flush, but it is not needed to add etc. Country_Ls(language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some country') Region_Ls(country_ls=Country_Ls(language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some country'), language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some region') === flushed === Country_Ls(centralkey=None, countryid=241, created=datetime.datetime(2009, 5, 22, 13, 22, 48, 826000), fk_langid=1, id=None, language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some country', shortname=None, updated=datetime.datetime(2009, 5, 22, 13, 22, 48, 826000)) 241 region Region_Ls(centralkey=None, country_ls=Country_Ls(centralkey=None, countryid=241, created=datetime.datetime(2009, 5, 22, 13, 22, 48, 826000), fk_langid=1, id=None, language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some country', shortname=None, updated=datetime.datetime(2009, 5, 22, 13, 22, 48, 826000)), created=datetime.datetime(2009, 5, 22, 13, 22, 48, 829000), fk_countryid=241, fk_langid=1, id=None, language=Language(created=datetime.date(2009, 5, 22), langid=1, locales=u'en ', name=u'English', updated=datetime.date(2009, 5, 22)), name='some region', regionid=214, shortname=None, updated=datetime.datetime(2009, 5, 22, 13, 22, 48, 829000)) 241 Hope this helps Werner --~--~-~--~~~---~--~~ 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: Q: fetch value of autoincrement column
Hello Adrian, 2009/5/22 Adrian von Bidder avbid...@fortytwo.ch: On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote: Don't you want that non-null column to be a foreign key ? Would that make a difference? That's what a foreign key is used for: http://en.wikipedia.org/wiki/Foreign_key I think you should know what a foreign key is before using a tool like SQLAlchemy. SQLAlchemy is an abstraction layer to deal with cells, records, columns, tables, relations of databases. An abstraction layer hides some details of lower level concepts. Abstraction layers like SQLA are meant to ease your everyday work. But you still have to understand lower level concepts in order to correctly use abstraction tools like SQLAlchemy. What you are trying to achieve is already a feature that the database itself can handle for you. It's called a constraint. Read a few papers about databases in general and how constraints and relations between tables work. There's a lot of documentation about that out there. Have fun! :) Alex --~--~-~--~~~---~--~~ 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] Can't determine join between 'engines' and 'versions'. Please specify the 'onclause' of this join explicitly ?
Hi :)
I have three tables:
1)
t_version = sa.Table(versions, meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(version, mysql.MSChar(length=100,
collation='utf8_polish_ci'), nullable=False, unique=True)
)
class Version(object):
pass
orm.mapper(Version, t_version)
2)
t_type = sa.Table(types,
meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(name, mysql.MSChar(length=100,
collation='utf8_polish_ci'), nullable=False, unique=True),
)
class Type(object):
pass
orm.mapper(Type, t_type, properties = {
'engine' : orm.relation(Engine, uselist=False,
backref='Type_Engine')
})
3)
t_engine = sa.Table(engines, meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(min_version_id, sa.types.Integer(), sa.ForeignKey
(versions.id), nullable=False),
sa.Column(max_version_id, sa.types.Integer(), sa.ForeignKey
(versions.id), nullable=False),
sa.Column(type_id, sa.types.Integer(), sa.ForeignKey
(types.id), nullable=False),
)
class Engine(object):
pass
orm.mapper(Engine, t_engine, properties = {
'type' : orm.relation(Type, uselist=False, backref='Engine_Type'),
'min_version' : orm.relation(Version,
primaryjoin=t_engine.c.min_version_id==t_version.c.id),
'max_version' : orm.relation(Version,
primaryjoin=t_engine.c.max_version_id==t_version.c.id)
})
My problem is how to make query equal this SQL instruction select
e.id, e.type_id, tv.version as min_version, tv2.version as max_version
from engines e join versions tv on(e.min_version_id=tv.id) join
versions tv2 on(e.max_version_id=tv2.id) where '7.0.1.32' between
tv.version and tv2.version;
I've tried to do something like that:
engine = meta.Session.query(Engine).outerjoin(Version).filter(between
(request.POST['version'], Engine.min_version.version,
Engine.max_version.version)).all()
but I've got:
ArgumentError: Can't determine join between 'engines' and 'versions';
tables have more than one foreign key constraint relationship between
them. Please specify the 'onclause' of this join explicitly.
Thanks in advance for help.
--~--~-~--~~~---~--~~
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] Creating something with a unique, random key
Hi there, I am working with SqlAlchemy for the first time (coming from SqlObject), and I fear I may not understand it as well as I thought I did... I have a class (X), persisted with SA which contains a key (X.key) which is a randomly generated string of fixed length. This field is supposed to be unique as well. When you create a X, though, there is the chance that the newly generated key may clash with an existing one in the DB. In which case I'd like to regenerate the random key and try again. But, you cannot use a sequence in the DB for generating the key, since it should be random, but unique. Naïvely, I thought you'd create an X, flush it, and then catch any IntegrityError's thrown. Something like: session.begin() #create some other objects unique = False #session.begin() while not unique: try: newx = X() # It generates its own random key in the __init__ session.save(newx) session.flush() unique = True except IntegrityError: pass #session.commit() session.commit() But, this does not work: It seems that the whole transaction is rolled back when the IntegrityError happens, so objects created before this code is then lost here. Ive tried putting a nested transaction around this bit of code (using session.begin/commit as indicated in comments), but that did not work. This is done with SqlAlchemy 0.4 (and Elixir 0.5), on postgresql 8.3. --~--~-~--~~~---~--~~ 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: Small note on reading SA docs
I've had quite the opposite experience with SQLAlchemy's documentation, and I just started using it for a couple of small projects about two weeks ago. Once I was accustomed to the site layout, it became reasonably easy to find what I was looking for. Specifically, I've been using the integrated search, and while it isn't the best for somewhat more advanced multi-keyword matches, it seems that mixing keywords found from Google (even if it does turn up things from 0.3 and up :) with the site search works pretty well. The greatest problem I can see with the docs is that they're quite voluminous. Frankly, that's a *good* problem to have. :) Keep up the great work! Regards, Benjamin --~--~-~--~~~---~--~~ 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: Small note on reading SA docs
Bobby Impollonia wrote: otherwise if you have any advice on how to get 0.4/0.3 delisted from such a prominent place on Google, that would be appreciated. The simplest thing to do is to append: Disallow: /docs/04/ Disallow: /docs/03/ agreed, this is done. --~--~-~--~~~---~--~~ 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: Small note on reading SA docs
Bobby Impollonia wrote: I also noticed that your current robots.txt file disallows indexing of anything under /trac/. It would nice to let google index bugs in trac so that someone who searches google for sqlalchemy help can come across an extant bug describing their problem. In addition, you have links on the front page (changelog and what's new) that go to urls under /trac/ , so google will not follow those links due to your robots.txt. I was probably concerned about load at some point but I've allowed /trac now. --~--~-~--~~~---~--~~ 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: Can't determine join between 'engines' and 'versions'. Please specify the 'onclause' of this join explicitly ?
sniipe wrote:
Hi :)
I have three tables:
1)
t_version = sa.Table(versions, meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(version, mysql.MSChar(length=100,
collation='utf8_polish_ci'), nullable=False, unique=True)
)
class Version(object):
pass
orm.mapper(Version, t_version)
2)
t_type = sa.Table(types,
meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(name, mysql.MSChar(length=100,
collation='utf8_polish_ci'), nullable=False, unique=True),
)
class Type(object):
pass
orm.mapper(Type, t_type, properties = {
'engine' : orm.relation(Engine, uselist=False,
backref='Type_Engine')
})
3)
t_engine = sa.Table(engines, meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(min_version_id, sa.types.Integer(), sa.ForeignKey
(versions.id), nullable=False),
sa.Column(max_version_id, sa.types.Integer(), sa.ForeignKey
(versions.id), nullable=False),
sa.Column(type_id, sa.types.Integer(), sa.ForeignKey
(types.id), nullable=False),
)
class Engine(object):
pass
orm.mapper(Engine, t_engine, properties = {
'type' : orm.relation(Type, uselist=False, backref='Engine_Type'),
'min_version' : orm.relation(Version,
primaryjoin=t_engine.c.min_version_id==t_version.c.id),
'max_version' : orm.relation(Version,
primaryjoin=t_engine.c.max_version_id==t_version.c.id)
})
My problem is how to make query equal this SQL instruction select
e.id, e.type_id, tv.version as min_version, tv2.version as max_version
from engines e join versions tv on(e.min_version_id=tv.id) join
versions tv2 on(e.max_version_id=tv2.id) where '7.0.1.32' between
tv.version and tv2.version;
I've tried to do something like that:
engine = meta.Session.query(Engine).outerjoin(Version).filter(between
(request.POST['version'], Engine.min_version.version,
Engine.max_version.version)).all()
when you do the outerjoin, pass it the relation you're joining on or an
onclause, such as
query.outerjoin(Version.engine)
or
query.outerjoin((Version, Engine.some_col==Version.some_other_col))
--~--~-~--~~~---~--~~
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: Creating something with a unique, random key
Iwan wrote: Hi there, I am working with SqlAlchemy for the first time (coming from SqlObject), and I fear I may not understand it as well as I thought I did... I have a class (X), persisted with SA which contains a key (X.key) which is a randomly generated string of fixed length. This field is supposed to be unique as well. When you create a X, though, there is the chance that the newly generated key may clash with an existing one in the DB. In which case I'd like to regenerate the random key and try again. But, you cannot use a sequence in the DB for generating the key, since it should be random, but unique. have you considered using some more industrial strength randomness, like GUIDs generated from the current timestamp or similar ?the python uuid module works very well for this. Naïvely, I thought you'd create an X, flush it, and then catch any IntegrityError's thrown. Something like: session.begin() #create some other objects unique = False #session.begin() while not unique: try: newx = X() # It generates its own random key in the __init__ session.save(newx) session.flush() unique = True except IntegrityError: pass #session.commit() session.commit() But, this does not work: It seems that the whole transaction is rolled back when the IntegrityError happens, so objects created before this code is then lost here. Ive tried putting a nested transaction around this bit of code (using session.begin/commit as indicated in comments), but that did not work. if your database supports SAVEPOINT, this usage pattern can be achieved using begin_nested() to start a SAVEPOINT (but not with 0.4...). Otherwise, you have to query the database first for your value - and you might need to use table locks to prevent concurrent inserts. This is done with SqlAlchemy 0.4 (and Elixir 0.5), on postgresql 8.3. if you just started with SQLAlchemy, why 0.4 ? 0.5 has been out in final release for six months. --~--~-~--~~~---~--~~ 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: Creating something with a unique, random key
On Friday 22 May 2009 12.01:05 Iwan wrote: Naïvely, I thought you'd create an X, flush it, and then catch any IntegrityError's thrown. [...] I know that PostgreSQL can't continue in a transaction after an error, you have to roll back the transaction. I don't know what the SQL standard says on this, but that's how postgres has always behaved. I guess you just have to query for your string to see if it's unique. Performance-wise it shouldn't make a difference, and in Python, I usually find a simple if even nicer than a try-except block. cheers -- vbi -- Jeder Mensch ist einzigartig. -- Benedetto Croce signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Q: fetch value of autoincrement column
On Friday 22 May 2009 13.58:34 Alexandre Conrad wrote: Hello Adrian, 2009/5/22 Adrian von Bidder avbid...@fortytwo.ch: On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote: Don't you want that non-null column to be a foreign key ? Would that make a difference? That's what a foreign key is used for: http://en.wikipedia.org/wiki/Foreign_key Oh, thanks a lot. Not really helpful, though. I was quite simply asking if it's possibly to fetch the value of an autoincrement column (in a portable way, if possible) without causing a flush, because at the time when I want to use the value, the row is not complete yet. Oh, well... cheers -- vbi -- featured link: http://www.pool.ntp.org signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Create a stored procedure using SQLAlchemy
Thanks. This works great.
On May 20, 4:54 pm, Clovis Fabricio nos...@gmail.com wrote:
2009/5/19 Daniel daniel.watr...@gmail.com:
Hello,
I have a stored procedure for SQL Server and I would like to be able
to execute the code to create the stored procedure using SA. Here's
the basic idea.
The problem is that I'm getting an error complaining about invalid
syntax ProgrammingError: (ProgrammingError) ('42000', [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
the keyword 'CREATE'
I know the stored procedure call works since I can run it against the
database. What do I need to do to execute my script from SA?
Execute each command separately. I've never seen a DBAPI driver that
allows execution of more than one full command in the same query.
myStoredProcedure = [
IF object_id('mySP') IS NOT NULL
BEGIN
DROP PROCEDURE mySP
END,
CREATE PROCEDURE mySP
AS
DECLARE @aVar VARCHAR(48)
BEGIN
SELECT TOP 1
�...@avar = aVar
FROM [dbo].[someTable] (UPDLOCK)
WHERE
priority 0
ORDER BY
priority DESC
SELECT
@aVar AS aVar
END
]
for command in myStoredProcedure: engine.execute(command)
--~--~-~--~~~---~--~~
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: Can't determine join between 'engines' and 'versions'. Please specify the 'onclause' of this join explicitly ?
Ok It's working but I can't use 'between':
engine = meta.Session.query(Engine).outerjoin((Version,
Engine.min_version_id==Version.id)).filter(between(request.POST
['version'], Engine.min_version.version,
Engine.max_version.version)).all()
and I've got error:
AttributeError: Neither 'InstrumentedAttribute' object nor
'Comparator' object has an attribute 'version'
What am I doing wrong?
On 22 Maj, 17:59, Michael Bayer mike...@zzzcomputing.com wrote:
sniipe wrote:
Hi :)
I have three tables:
1)
t_version = sa.Table(versions, meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(version, mysql.MSChar(length=100,
collation='utf8_polish_ci'), nullable=False, unique=True)
)
class Version(object):
pass
orm.mapper(Version, t_version)
2)
t_type = sa.Table(types,
meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(name, mysql.MSChar(length=100,
collation='utf8_polish_ci'), nullable=False, unique=True),
)
class Type(object):
pass
orm.mapper(Type, t_type, properties = {
'engine' : orm.relation(Engine, uselist=False,
backref='Type_Engine')
})
3)
t_engine = sa.Table(engines, meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(min_version_id, sa.types.Integer(), sa.ForeignKey
(versions.id), nullable=False),
sa.Column(max_version_id, sa.types.Integer(), sa.ForeignKey
(versions.id), nullable=False),
sa.Column(type_id, sa.types.Integer(), sa.ForeignKey
(types.id), nullable=False),
)
class Engine(object):
pass
orm.mapper(Engine, t_engine, properties = {
'type' : orm.relation(Type, uselist=False, backref='Engine_Type'),
'min_version' : orm.relation(Version,
primaryjoin=t_engine.c.min_version_id==t_version.c.id),
'max_version' : orm.relation(Version,
primaryjoin=t_engine.c.max_version_id==t_version.c.id)
})
My problem is how to make query equal this SQL instruction select
e.id, e.type_id, tv.version as min_version, tv2.version as max_version
from engines e join versions tv on(e.min_version_id=tv.id) join
versions tv2 on(e.max_version_id=tv2.id) where '7.0.1.32' between
tv.version and tv2.version;
I've tried to do something like that:
engine = meta.Session.query(Engine).outerjoin(Version).filter(between
(request.POST['version'], Engine.min_version.version,
Engine.max_version.version)).all()
when you do the outerjoin, pass it the relation you're joining on or an
onclause, such as
query.outerjoin(Version.engine)
or
query.outerjoin((Version, Engine.some_col==Version.some_other_col))
--~--~-~--~~~---~--~~
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: Q: fetch value of autoincrement column
Could you please send your SQLAlchemy tables you are working with to have a better idea of what's you want to achieve ? 2009/5/22 Adrian von Bidder avbid...@fortytwo.ch: On Friday 22 May 2009 13.58:34 Alexandre Conrad wrote: Hello Adrian, 2009/5/22 Adrian von Bidder avbid...@fortytwo.ch: On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote: Don't you want that non-null column to be a foreign key ? Would that make a difference? That's what a foreign key is used for: http://en.wikipedia.org/wiki/Foreign_key Oh, thanks a lot. Not really helpful, though. I was quite simply asking if it's possibly to fetch the value of an autoincrement column (in a portable way, if possible) without causing a flush, because at the time when I want to use the value, the row is not complete yet. Oh, well... cheers -- vbi -- featured link: http://www.pool.ntp.org --~--~-~--~~~---~--~~ 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: Can't determine join between 'engines' and 'versions'. Please specify the 'onclause' of this join explicitly ?
Engine.min_version and max_version are instrumented column attributes.
they don't have an attribute called version. i think you want
between(x, Engine.min_version, Engine.max_version).
sniipe wrote:
Ok It's working but I can't use 'between':
engine = meta.Session.query(Engine).outerjoin((Version,
Engine.min_version_id==Version.id)).filter(between(request.POST
['version'], Engine.min_version.version,
Engine.max_version.version)).all()
and I've got error:
AttributeError: Neither 'InstrumentedAttribute' object nor
'Comparator' object has an attribute 'version'
What am I doing wrong?
On 22 Maj, 17:59, Michael Bayer mike...@zzzcomputing.com wrote:
sniipe wrote:
Hi :)
I have three tables:
1)
t_version = sa.Table(versions, meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(version, mysql.MSChar(length=100,
collation='utf8_polish_ci'), nullable=False, unique=True)
)
class Version(object):
pass
orm.mapper(Version, t_version)
2)
t_type = sa.Table(types,
meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(name, mysql.MSChar(length=100,
collation='utf8_polish_ci'), nullable=False, unique=True),
)
class Type(object):
pass
orm.mapper(Type, t_type, properties = {
'engine' : orm.relation(Engine, uselist=False,
backref='Type_Engine')
})
3)
t_engine = sa.Table(engines, meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(min_version_id, sa.types.Integer(), sa.ForeignKey
(versions.id), nullable=False),
sa.Column(max_version_id, sa.types.Integer(), sa.ForeignKey
(versions.id), nullable=False),
sa.Column(type_id, sa.types.Integer(), sa.ForeignKey
(types.id), nullable=False),
)
class Engine(object):
pass
orm.mapper(Engine, t_engine, properties = {
'type' : orm.relation(Type, uselist=False, backref='Engine_Type'),
'min_version' : orm.relation(Version,
primaryjoin=t_engine.c.min_version_id==t_version.c.id),
'max_version' : orm.relation(Version,
primaryjoin=t_engine.c.max_version_id==t_version.c.id)
})
My problem is how to make query equal this SQL instruction select
e.id, e.type_id, tv.version as min_version, tv2.version as max_version
from engines e join versions tv on(e.min_version_id=tv.id) join
versions tv2 on(e.max_version_id=tv2.id) where '7.0.1.32' between
tv.version and tv2.version;
I've tried to do something like that:
engine = meta.Session.query(Engine).outerjoin(Version).filter(between
(request.POST['version'], Engine.min_version.version,
Engine.max_version.version)).all()
when you do the outerjoin, pass it the relation you're joining on or an
onclause, such as
query.outerjoin(Version.engine)
or
query.outerjoin((Version, Engine.some_col==Version.some_other_col))
--~--~-~--~~~---~--~~
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: Can't determine join between 'engines' and 'versions'. Please specify the 'onclause' of this join explicitly ?
that means min_version and max_version aren't columns.I guess you're
looking for Version.version, in which case you probably need to JOIN to
that table twice on both the min_version and max_version. write (and
test) the query you want in SQL first to get an idea for what you're
doing.
sniipe wrote:
After change (delete 'version' attribute):
ProgrammingError: (ProgrammingError) (1064, You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'AND' at line 3) u'SELECT
engines.id AS engines_id, engines.min_version_id AS
engines_min_version_id, engines.max_version_id AS
engines_max_version_id, engines.type_id AS engines_type_id \nFROM
engines LEFT OUTER JOIN versions ON engines.min_version_id =
versions.id \nWHERE %s BETWEEN AND ' ['7.0.1.4']
On 22 Maj, 21:40, Michael Bayer mike...@zzzcomputing.com wrote:
Engine.min_version and max_version are instrumented column attributes.
they don't have an attribute called version. i think you want
between(x, Engine.min_version, Engine.max_version).
sniipe wrote:
Ok It's working but I can't use 'between':
engine = meta.Session.query(Engine).outerjoin((Version,
Engine.min_version_id==Version.id)).filter(between(request.POST
['version'], Engine.min_version.version,
Engine.max_version.version)).all()
and I've got error:
AttributeError: Neither 'InstrumentedAttribute' object nor
'Comparator' object has an attribute 'version'
What am I doing wrong?
On 22 Maj, 17:59, Michael Bayer mike...@zzzcomputing.com wrote:
sniipe wrote:
Hi :)
I have three tables:
1)
t_version = sa.Table(versions, meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(version, mysql.MSChar(length=100,
collation='utf8_polish_ci'), nullable=False, unique=True)
)
class Version(object):
pass
orm.mapper(Version, t_version)
2)
t_type = sa.Table(types,
meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(name, mysql.MSChar(length=100,
collation='utf8_polish_ci'), nullable=False, unique=True),
)
class Type(object):
pass
orm.mapper(Type, t_type, properties = {
'engine' : orm.relation(Engine, uselist=False,
backref='Type_Engine')
})
3)
t_engine = sa.Table(engines, meta.metadata,
sa.Column(id, sa.types.Integer(), primary_key=True,
autoincrement=True),
sa.Column(min_version_id, sa.types.Integer(), sa.ForeignKey
(versions.id), nullable=False),
sa.Column(max_version_id, sa.types.Integer(), sa.ForeignKey
(versions.id), nullable=False),
sa.Column(type_id, sa.types.Integer(), sa.ForeignKey
(types.id), nullable=False),
)
class Engine(object):
pass
orm.mapper(Engine, t_engine, properties = {
'type' : orm.relation(Type, uselist=False,
backref='Engine_Type'),
'min_version' : orm.relation(Version,
primaryjoin=t_engine.c.min_version_id==t_version.c.id),
'max_version' : orm.relation(Version,
primaryjoin=t_engine.c.max_version_id==t_version.c.id)
})
My problem is how to make query equal this SQL instruction select
e.id, e.type_id, tv.version as min_version, tv2.version as
max_version
from engines e join versions tv on(e.min_version_id=tv.id) join
versions tv2 on(e.max_version_id=tv2.id) where '7.0.1.32' between
tv.version and tv2.version;
I've tried to do something like that:
engine =
meta.Session.query(Engine).outerjoin(Version).filter(between
(request.POST['version'], Engine.min_version.version,
Engine.max_version.version)).all()
when you do the outerjoin, pass it the relation you're joining on or
an
onclause, such as
query.outerjoin(Version.engine)
or
query.outerjoin((Version, Engine.some_col==Version.some_other_col))
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
