[sqlalchemy] SQLAlchemy GUI
Hi All I'm planning on making a GUI database configurator based on SQLAlchemy. It will be roughly a GUI interface to the SQLAlchemy API. Allowing the user to view/edit table data as well as database objects. Eventually i'd like it to support most of the database access and db object manipulation that SQLAlchemy + SQLMigrate.changeset supports. Anyway, Thats my plan : I'm posting this to ask if anyone else is working on something similar? Regards, Jar --~--~-~--~~~---~--~~ 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: Basic Search Engine
@Paul
I have been having a play with [py]parsing. What a nifty little
library!
I read those 2 free tutes and liked what I saw so bought a
subscription to safari just so I could read your short cut.
For my purposes (a few k objects at most, generally a few hundred) a
non indexed and inefficient like search will do fine until I work out
the match operator.
Here is what I have so far. Any tips welcome.
IMPORTS
###
# PyParsing
from pyparsing import ( CaselessLiteral, Literal, Word, alphas,
quotedString,
removeQuotes, operatorPrecedence,
ParseException,
stringEnd, opAssoc )
# SqlAlchemy
from sqlalchemy import and_, not_, or_
## LIKE ESCAPE
#
LIKE_ESCAPE = r'\\'
def like_escape(s):
return '%' + ( s.replace('\\', '')
.replace('%', '\\%')
.replace('_', '\\_') ) + '%'
### REUSABLE ACTIONS
###
class UnaryOperation(object):
def __init__(self, t):
self.op, self.a = t[0]
def __repr__(self):
return %s:(%s) % (self.name, str(self.a))
def express(self):
return self.operator[0](self.a.express())
class BinaryOperation(object):
def __init__(self, t):
self.op = t[0][1]
self.operands = t[0][0::2]
def __repr__(self):
return %s:(%s) % ( self.name,
,.join(str(oper) for oper in
self.operands) )
def express(self):
return self.operator[0](*( oper.express() for oper in
self.operands ))
class SearchAnd(BinaryOperation):
name = 'AND'
operator = [and_]
class SearchOr(BinaryOperation):
name = 'OR'
operator = [or_]
class SearchNot(UnaryOperation):
name = 'NOT'
operator = [not_]
### REUSABLE GRAMMARS
##
AND_ = CaselessLiteral(and) | Literal('+')
OR_ = CaselessLiteral(or) | Literal('|')
NOT_ = CaselessLiteral(not) | Literal('!')
searchTermMaster = (
Word(alphas) | quotedString.copy().setParseAction
( removeQuotes ) )
## THREAD SAFE PARSER FACTORY
##
def like_parser(model, fields=[]):
class SearchTerm(object):
def __init__(self, tokens):
self.term = tokens[0]
def express(self):
return or_ (
*( getattr(model, field).like( like_escape(self.term),
escape = LIKE_ESCAPE)
for field in fields )
)
def __repr__(self):
return self.term
searchTerm = searchTermMaster.copy().setParseAction(SearchTerm)
searchExpr = operatorPrecedence( searchTerm,
[ (NOT_, 1, opAssoc.RIGHT, SearchNot),
(AND_, 2, opAssoc.LEFT, SearchAnd),
(OR_, 2, opAssoc.LEFT, SearchOr) ] )
return searchExpr + stringEnd
### SEARCH FIELDS LIKE HELPER
##
def search_fields_like(s, model, fields):
if isinstance(fields, basestring): fields = [fields]
parser = like_parser(model, fields)
return parser.parseString(s)[0].express()
--~--~-~--~~~---~--~~
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: Basic Search Engine
I apologise for the formatting. How does one go about posting snippets inline properly? In the future I think I'll just post links to pastes. Paste of the above code: http://pastie.org/474342 --~--~-~--~~~---~--~~ 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] bindparams not resolved?
I am wondering if this is a bug in my reasoning, or in SQLAlchemy (0.5.3). I have a model which is pretty simple: class ClothingArticle(BaseObject): __tablename__ = clothing_article id = schema.Column(types.Integer(), primary_key=True, autoincrement=True) currency = schema.Column(types.String(3), nullable=False, default=EUR, index=True) price = schema.Column(types.Numeric(precision=6, scale=2), nullable=False) price_euro = schema.Column(types.Numeric(precision=6, scale=2), nullable=False, index=True) When a currerency rate changes I am trying to update it, using a simple prepared statement: update=ClothingArticle.__table__.update().\ where(ClothingArticle.currency==sql.bindparam(currency)).\ values(dict(price_euro=ClothingArticle.price*sql.bindparam(newrate))) The statement is processed correctly: (Pdb) print update UPDATE clothing_article SET price_euro=(clothing_article.price * :newrate) WHERE clothing_article.currency = :currency But when I try to use it: session.execute(update, currency=currency[code], newrate=newrate) I get an error: TypeError: get_bind() got an unexpected keyword argument 'currency' I find it hard to believe parameter binding does not work, since SA uses that internally, but I also can't spot my mistake unfortunately. Wichert. -- Wichert Akkerman wich...@wiggy.netIt is simple to make things. http://www.wiggy.net/ It is hard to make things simple. --~--~-~--~~~---~--~~ 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: Declarative and self-referencing tables
Adrian von Bidder wrote:
Heyho!
How can I use Declarative to create self-referencing stuff? I'm trying
without success to create the tree example (Node with Node.parent and
Node.children) in declarative.
The basic table is:
class Node(Base):
__tablename__ = 'nodes'
id = Column(Integer, primary_key = True)
parent_id = Column('parent', Integer, ForeignKey('nodes.id'))
Now I'm struggling with how to add a relation so that I can use
mynode.parent and mynode.children.
I've tried
parent = relation(
'Node', backref=backref('children', remote_side=[Node.id]))
and various variants but always ended up with an exception (different ones
for various cases.)
I guess I have to use remote_side somehow but I couldn't figure how.
class Node(Base):
__tablename__ = 'nodes'
id = Column(Integer, primary_key = True)
parent_id = Column('parent', Integer, ForeignKey('nodes.id'))
parent = relation(Node, remote_side=id, backref=children)
--~--~-~--~~~---~--~~
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: bindparams not resolved?
the signature for Session.execute is not the same as that of Connection or
Engine:
Session.execute(stmt, params={'foo':'bar'})
Wichert Akkerman wrote:
I am wondering if this is a bug in my reasoning, or in SQLAlchemy
(0.5.3). I have a model which is pretty simple:
class ClothingArticle(BaseObject):
__tablename__ = clothing_article
id = schema.Column(types.Integer(), primary_key=True,
autoincrement=True)
currency = schema.Column(types.String(3),
nullable=False, default=EUR, index=True)
price = schema.Column(types.Numeric(precision=6, scale=2),
nullable=False)
price_euro = schema.Column(types.Numeric(precision=6, scale=2),
nullable=False, index=True)
When a currerency rate changes I am trying to update it, using a simple
prepared statement:
update=ClothingArticle.__table__.update().\
where(ClothingArticle.currency==sql.bindparam(currency)).\
values(dict(price_euro=ClothingArticle.price*sql.bindparam(newrate)))
The statement is processed correctly:
(Pdb) print update
UPDATE clothing_article SET price_euro=(clothing_article.price * :newrate)
WHERE clothing_article.currency = :currency
But when I try to use it:
session.execute(update, currency=currency[code], newrate=newrate)
I get an error:
TypeError: get_bind() got an unexpected keyword argument 'currency'
I find it hard to believe parameter binding does not work, since SA uses
that internally, but I also can't spot my mistake unfortunately.
Wichert.
--
Wichert Akkerman wich...@wiggy.netIt is simple to make things.
http://www.wiggy.net/ It is hard to make things simple.
--~--~-~--~~~---~--~~
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: Sequence start value not working with PostgreSQL
the start functionality is unimplemented at the moment. instead, issue:
t = Table(mytable, )
DDL(CREATE SEQUENCE ).execute_at('before-create', t)
Chris Miles wrote:
I need to create an explicit Sequence with a specified start value.
Looks simple, I tried Sequence('test_seq', start=5000) however the
actual sequence created is left with the default start value. The
start parameter appears to have no effect.
This is using PostgreSQL 8.2.4 and SQLAlchemy 0.5.3.
Test code below demonstrates the problem.
Am I misunderstanding the start parameter or is this a bug?
import sqlalchemy as sa
engine = sa.create_engine('postgres://localhost/test1', echo=True)
meta = sa.MetaData()
meta.bind = engine
test_seq = sa.Sequence('test_seq', start=5000, metadata=meta)
test_seq.create()
nextval = engine.execute(test_seq)
assert nextval == 5000, nextval
Cheers,
Chris Miles
--~--~-~--~~~---~--~~
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 run a stored procedure?
Any reply on this? Should I submit a new bug report?
On May 8, 11:49 am, Daniel daniel.watr...@gmail.com wrote:
I've just been looking through the code in mssql.py and the change
mentioned in the changeset I mentioned isn't there anymore. I also
can't see that's it's been abstracted to a parent class. Is there a
possibility that this bug has crept back in?
If so, let me know where the sensible place would be to include the
EXEC keyword in order to return result sets for MSSQL stored
procedures, or if there would be a better approach.
Thanks.
On May 8, 11:24 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I just found this
thread:http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08048.html
which corresponds to this
changeset:http://www.sqlalchemy.org/trac/changeset/4159
It seems that this issue has come up in the past. I've tried the
following modified query:
result = conn.execute('EXEC claim_highest_priority_work')
which should satisfy the regexp, but it still produces the closed
cursor error.
Not sure if this helps you help me...
On May 8, 11:09 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I'm not sure if this helps, but I've noticed that if my stored
procedure returns all null values, then I can fetch them. If they are
non-null values I get an error:
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
Traceback (most recent call last):
File interactive input, line 1, in module
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
1668, in fetchone
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
(None, None, None, None, None)
Any idea why this would be?
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases,
running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use
it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as
follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm
not
sure how to access the return values? They exactly match one of
the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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: FOR UPDATE or UPDLOCK
Can someone give me an idea about this? Should this be submitted as a bug or feature request? Thanks. On May 7, 3:50 pm, Daniel daniel.watr...@gmail.com wrote: Hello, I have a transaction that involves a SELECT and subsequent UPDATE. It is operating against MSSQL. I need to make sure that the row locks so that other processes may not access it until I have completed my update, or that they at least fail when trying to UPDATE after the first transaction commits. I think that either FOR UPDATE or UPDLOCK would work, but I can't find a way to make either of them work. In the mmsql.py file I find this code: def for_update_clause(self, select): # FOR UPDATE is only allowed on DECLARE CURSOR which SQLAlchemy doesn't use return '' This leads me to believe that FOR UPDATE will not work. I've also tried this s = select(table.c, table.c.field0, [text((UPDLOCK))]) conn.execute(s) Rather than producing SELECT * FROM table (UPDLOCK) where field 0 it instead produces SELECT * FROM table, (UPDLOCK) where field 0 That little comman throws the whole thing off. Can anyone suggest a way for me to accomplish what I'm trying to do in sqlalchemy. Thanks in advance, Daniel --~--~-~--~~~---~--~~ 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: FOR UPDATE or UPDLOCK
I mentioned this originally, but maybe it wasn't clear. In order to have MSSQL perform a select for update the FROM clause must be modified. This is comparable to appending FOR UPDATE to the entire query in other DBMSs. For example, the following two queries have the same effect and show the difference between MySQL and MSSQL MySQL: SELECT id, name FROM employee WHERE id = 1 FOR UPDATE MSSQL: SELECT id, name FROM employee (UPDLOCK) WHERE id=1 In SQLAlchemy 0.5.3, I've noticed that the 'for_update_clause(self, select)' function appears to simply append something for the languages where it is supported. How/Where would I add the ability in MSSQL to modify the FROM clause to have '(UPDLOCK)'? At the moment the only way I've found to accomplish this is to hand write my queries and execute them all directly, as in session.execute (query). I'd prefer not to do this since it's functionality that's available in MSSQL and appears to have a sensible insertion point in SQLAlchemy. Thanks. On May 11, 8:40 am, Daniel daniel.watr...@gmail.com wrote: Can someone give me an idea about this? Should this be submitted as a bug or feature request? Thanks. On May 7, 3:50 pm, Daniel daniel.watr...@gmail.com wrote: Hello, I have a transaction that involves a SELECT and subsequent UPDATE. It is operating against MSSQL. I need to make sure that the row locks so that other processes may not access it until I have completed my update, or that they at least fail when trying to UPDATE after the first transaction commits. I think that either FOR UPDATE or UPDLOCK would work, but I can't find a way to make either of them work. In the mmsql.py file I find this code: def for_update_clause(self, select): # FOR UPDATE is only allowed on DECLARE CURSOR which SQLAlchemy doesn't use return '' This leads me to believe that FOR UPDATE will not work. I've also tried this s = select(table.c, table.c.field0, [text((UPDLOCK))]) conn.execute(s) Rather than producing SELECT * FROM table (UPDLOCK) where field 0 it instead produces SELECT * FROM table, (UPDLOCK) where field 0 That little comman throws the whole thing off. Can anyone suggest a way for me to accomplish what I'm trying to do in sqlalchemy. Thanks in advance, Daniel --~--~-~--~~~---~--~~ 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 run a stored procedure?
Email on the pyodbc mailing list for instructions on how to execute a
stored procedure.The information there will guide how this is done
with SQLAlchemy.
Daniel wrote:
Any reply on this? Should I submit a new bug report?
On May 8, 11:49 am, Daniel daniel.watr...@gmail.com wrote:
I've just been looking through the code in mssql.py and the change
mentioned in the changeset I mentioned isn't there anymore. I also
can't see that's it's been abstracted to a parent class. Is there a
possibility that this bug has crept back in?
If so, let me know where the sensible place would be to include the
EXEC keyword in order to return result sets for MSSQL stored
procedures, or if there would be a better approach.
Thanks.
On May 8, 11:24 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I just found this
thread:http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08048.html
which corresponds to this
changeset:http://www.sqlalchemy.org/trac/changeset/4159
It seems that this issue has come up in the past. I've tried the
following modified query:
result = conn.execute('EXEC claim_highest_priority_work')
which should satisfy the regexp, but it still produces the closed
cursor error.
Not sure if this helps you help me...
On May 8, 11:09 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I'm not sure if this helps, but I've noticed that if my stored
procedure returns all null values, then I can fetch them. If they
are
non-null values I get an error:
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
Traceback (most recent call last):
File interactive input, line 1, in module
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py,
line
1668, in fetchone
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py,
line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
(None, None, None, None, None)
Any idea why this would be?
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com
wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS
claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in
function
name.
this is more of an MSSQL how to issue then. on most databases,
running
a function and returning results looks like SELECT function().
On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com
wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how
to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as
follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but
I'm not
sure how to access the return values? They exactly match one
of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct
which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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: FOR UPDATE or UPDLOCK
but is the comment in the code correct ? is DECLARE CURSOR required ? Daniel wrote: I mentioned this originally, but maybe it wasn't clear. In order to have MSSQL perform a select for update the FROM clause must be modified. This is comparable to appending FOR UPDATE to the entire query in other DBMSs. For example, the following two queries have the same effect and show the difference between MySQL and MSSQL MySQL: SELECT id, name FROM employee WHERE id = 1 FOR UPDATE MSSQL: SELECT id, name FROM employee (UPDLOCK) WHERE id=1 In SQLAlchemy 0.5.3, I've noticed that the 'for_update_clause(self, select)' function appears to simply append something for the languages where it is supported. How/Where would I add the ability in MSSQL to modify the FROM clause to have '(UPDLOCK)'? At the moment the only way I've found to accomplish this is to hand write my queries and execute them all directly, as in session.execute (query). I'd prefer not to do this since it's functionality that's available in MSSQL and appears to have a sensible insertion point in SQLAlchemy. Thanks. On May 11, 8:40 am, Daniel daniel.watr...@gmail.com wrote: Can someone give me an idea about this? Should this be submitted as a bug or feature request? Thanks. On May 7, 3:50 pm, Daniel daniel.watr...@gmail.com wrote: Hello, I have a transaction that involves a SELECT and subsequent UPDATE. It is operating against MSSQL. I need to make sure that the row locks so that other processes may not access it until I have completed my update, or that they at least fail when trying to UPDATE after the first transaction commits. I think that either FOR UPDATE or UPDLOCK would work, but I can't find a way to make either of them work. In the mmsql.py file I find this code: def for_update_clause(self, select): # FOR UPDATE is only allowed on DECLARE CURSOR which SQLAlchemy doesn't use return '' This leads me to believe that FOR UPDATE will not work. I've also tried this s = select(table.c, table.c.field0, [text((UPDLOCK))]) conn.execute(s) Rather than producing SELECT * FROM table (UPDLOCK) where field 0 it instead produces SELECT * FROM table, (UPDLOCK) where field 0 That little comman throws the whole thing off. Can anyone suggest a way for me to accomplish what I'm trying to do in sqlalchemy. Thanks in advance, Daniel --~--~-~--~~~---~--~~ 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: FOR UPDATE or UPDLOCK
A DECLARE CURSOR is required for the keywords 'FOR UPDATE, but I'm not sure that they do the same thing as FOR UPDATE in MySQL. On the other hand, the (UPDLOCK) keyword attached to the FROM clause doesn't require a DECLARE CURSOR. On May 11, 9:34 am, Michael Bayer mike...@zzzcomputing.com wrote: but is the comment in the code correct ? is DECLARE CURSOR required ? Daniel wrote: I mentioned this originally, but maybe it wasn't clear. In order to have MSSQL perform a select for update the FROM clause must be modified. This is comparable to appending FOR UPDATE to the entire query in other DBMSs. For example, the following two queries have the same effect and show the difference between MySQL and MSSQL MySQL: SELECT id, name FROM employee WHERE id = 1 FOR UPDATE MSSQL: SELECT id, name FROM employee (UPDLOCK) WHERE id=1 In SQLAlchemy 0.5.3, I've noticed that the 'for_update_clause(self, select)' function appears to simply append something for the languages where it is supported. How/Where would I add the ability in MSSQL to modify the FROM clause to have '(UPDLOCK)'? At the moment the only way I've found to accomplish this is to hand write my queries and execute them all directly, as in session.execute (query). I'd prefer not to do this since it's functionality that's available in MSSQL and appears to have a sensible insertion point in SQLAlchemy. Thanks. On May 11, 8:40 am, Daniel daniel.watr...@gmail.com wrote: Can someone give me an idea about this? Should this be submitted as a bug or feature request? Thanks. On May 7, 3:50 pm, Daniel daniel.watr...@gmail.com wrote: Hello, I have a transaction that involves a SELECT and subsequent UPDATE. It is operating against MSSQL. I need to make sure that the row locks so that other processes may not access it until I have completed my update, or that they at least fail when trying to UPDATE after the first transaction commits. I think that either FOR UPDATE or UPDLOCK would work, but I can't find a way to make either of them work. In the mmsql.py file I find this code: def for_update_clause(self, select): # FOR UPDATE is only allowed on DECLARE CURSOR which SQLAlchemy doesn't use return '' This leads me to believe that FOR UPDATE will not work. I've also tried this s = select(table.c, table.c.field0, [text((UPDLOCK))]) conn.execute(s) Rather than producing SELECT * FROM table (UPDLOCK) where field 0 it instead produces SELECT * FROM table, (UPDLOCK) where field 0 That little comman throws the whole thing off. Can anyone suggest a way for me to accomplish what I'm trying to do in sqlalchemy. Thanks in advance, Daniel --~--~-~--~~~---~--~~ 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: Building an or_ filter in loop
this would even be easier (and correcter if the terms contain %)
written as:
cond = or_(*[Fruit.name.contains(term) for term in terms])
On 8 mei, 21:59, Kyle Schaffrick k...@raidi.us wrote:
On Fri, 8 May 2009 12:52:09 -0700 (PDT)
Bryan bryanv...@gmail.com wrote:
I can't figure out a clean way of adding a bunch of filter terms to a
query in a loop joined by an OR clause. Successive calls to filter
join the expressions by AND. I would like to do something like the
following, but have the expressions joined by OR
terms = ['apple', 'orange', 'peach']
q = Session.query(Fruit)
for term in terms:
q = q.filter(Fruit.name.like('%' + term + '%')
Desired pseudo-sql:
SELECT * FROM fruit WHERE name like '%apple%' OR name like '%orange%'
OR name like '%peach%'
I think this might do what you want:
cond = or_(*[ Fruit.name.like('%' + term + '%') for term in terms ])
q = Session.query(Fruit).filter(cond)
-Kyle
--~--~-~--~~~---~--~~
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] Possible documentation glitch?
The last example in the reference for Query.join (http:// www.sqlalchemy.org/docs/05/reference/orm/query.html#the-query-object) seems to contain a few typos: Articles.id should be Article.id article_keywords.c (2x) does not exist anymore. (The .c should simply be left out, I think.) Cheers Klaus --~--~-~--~~~---~--~~ 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 run a stored procedure?
Michael,
I can execute a stored procedure from SQLAlchemy, but I can't get a
result set back out of SQLAlchemy. I've verified that the SP executes
as expected and I know that it's returning a result set. I'm
following what's been suggested on this forum
http://groups.google.com/group/sqlalchemy/browse_thread/thread/12e3360ee7efe15d
and http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08048.html.
What type of question do you think I should ask on the pyodbc forum?
It seems the issue is that a bug that was fixed in a previous version
of SQLAlchemy has crept back in. I might be able to fix it with a
little help, but things have changed quite a bit since the changeset
that fixed it last time(see previous post on this thread) and I'm not
sure where to start.
On May 11, 9:32 am, Michael Bayer mike...@zzzcomputing.com wrote:
Email on the pyodbc mailing list for instructions on how to execute a
stored procedure. The information there will guide how this is done
with SQLAlchemy.
Daniel wrote:
Any reply on this? Should I submit a new bug report?
On May 8, 11:49 am, Daniel daniel.watr...@gmail.com wrote:
I've just been looking through the code in mssql.py and the change
mentioned in the changeset I mentioned isn't there anymore. I also
can't see that's it's been abstracted to a parent class. Is there a
possibility that this bug has crept back in?
If so, let me know where the sensible place would be to include the
EXEC keyword in order to return result sets for MSSQL stored
procedures, or if there would be a better approach.
Thanks.
On May 8, 11:24 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I just found this
thread:http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08048.html
which corresponds to this
changeset:http://www.sqlalchemy.org/trac/changeset/4159
It seems that this issue has come up in the past. I've tried the
following modified query:
result = conn.execute('EXEC claim_highest_priority_work')
which should satisfy the regexp, but it still produces the closed
cursor error.
Not sure if this helps you help me...
On May 8, 11:09 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I'm not sure if this helps, but I've noticed that if my stored
procedure returns all null values, then I can fetch them. If they
are
non-null values I get an error:
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
Traceback (most recent call last):
File interactive input, line 1, in module
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py,
line
1668, in fetchone
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py,
line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
(None, None, None, None, None)
Any idea why this would be?
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com
wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS
claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in
function
name.
this is more of an MSSQL how to issue then. on most databases,
running
a function and returning results looks like SELECT function().
On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com
wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how
to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as
follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but
I'm not
sure how to access the return values? They exactly match one
of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct
which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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
[sqlalchemy] Re: Building an or_ filter in loop
On Mon, 11 May 2009 09:01:06 -0700 (PDT) polaar steven.vereec...@gmail.com wrote: this would even be easier (and correcter if the terms contain %) written as: cond = or_(*[Fruit.name.contains(term) for term in terms]) Indeed, good catch. I was so interested in the apply or_ logic that I missed the escaping bug :) -Kyle --~--~-~--~~~---~--~~ 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: Basic Search Engine
On May 11, 6:20 am, fluence ndudfi...@gmail.com wrote:
@Paul
I have been having a play with [py]parsing. What a nifty little
library!
I read those 2 free tutes and liked what I saw so bought a
subscription to safari just so I could read your short cut.
Glad to hear that pyparsing is giving you a jump start! I downloaded
sqlalchemy, and eventually got the parameters straight to call your
code (passing the search string, the c attribute of a Table,
followed by a list of column names). Here's what I got:
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey
metadata = MetaData()
prod_table = Table('product', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('color', String),
Column('size', String),
Column('material', String),
)
print search_fields_like(RED OR GREEN AND NOT BLUE, prod_table.c,
color size.split())
Gives:
product.color LIKE :color_1 ESCAPE '\\' OR product.size LIKE :size_1
ESCAPE '\\' OR (product.color LIKE :color_2 ESCAPE '\\' OR
product.size LIKE :size_2 ESCAPE '\\') AND NOT (product.color
LIKE :color_3 ESCAPE '\\' OR product.size LIKE :size_3 ESCAPE '\\')
(Where do the parsed values, like RED, GREEN, and BLUE go?
You may at some point need to go beyond just Word(alphas) for search
terms, such as Word(alphanums) (words made up of alphas or letters),
or Word(alphas, alphanums) (words made up of alphas or letters, but
must start with an alpha).
Since your search string just takes search values, this is what makes
it necessary for you to qualify the call with a list of potential
search columns. This is okay if you are searching fields of an
article (like say, title, subject, abstract, and/or body). But if the
columns are dissimilar, such as fields of the products in a catalog,
then you'll be searching fields like size and color with impossible or
even misleading options (size like '%RED%' is a wasted search, but
color like '%L%' will return YELLOW items, whether they are Large or
not). For an application like the product catalog, then you could
recast your query grammar to search for boolean combinations of
conditional expressions like field like value. Then you wouldn't
need the additional parameter listing the fields to search, you can
parse them out of the query string itself. This would also support
conditional tests other than like, such as price 200.
In any event, I hope pyparsing will help support your experimenting,
and let you try some different application ideas while pyparsing
offloads some of the dull parsing stuff.
-- 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] Re: SQLAlchemy and unit tests
clear out the session (or make a new one) between tests. while the
session attempts to weak reference its contents, it usually ends up
holding onto a lot of stuff due to a particular reference cycle
created by backrefs (I'm thinking of ways to eliminate that behavior).
On May 11, 2009, at 10:54 PM, James wrote:
Hi all,
I'm trying to track down an error where running a full TurboGears unit
test suite fails with a SQLAlchemy error, while running the single
(failing) unit test passes OK.
The SA error is of the form:
FlushError: New instance terms...@0x2b15590 with identity key (class
'yel.model.select_source.TermSite', (1, 1), None) conflicts with
persistent instance terms...@0x2b8ad50
TermSite is an association table for a many-to-many relationship,
which has a multi-column primary key:
term_site_table = Table('term_site', metadata,
Column('term_id', Integer, ForeignKey(term.id,
ondelete=CASCADE), primary_key=True),
Column('site_id', Integer, ForeignKey(site.id,
ondelete=CASCADE), primary_key=True),
Column('weight', Integer, default=1, nullable=False),
)
The error seems to signify that SA thinks there is already an object
waiting to be flushed with the same term_id and site_id. The fact that
the test only fails when a full test suite is run implies that there
is some state left over in between unit test executions.
Abbreviated setUp and tearDown methods:
def setUp(self):
cherrypy.root = root.Root()
turbogears.startup.startTurboGears()
metadata.create_all()
def tearDown(self):
metadata.drop_all()
turbogears.startup.stopTurboGears()
TurboGears provides a sqlalchemy_cleanup method (here:
http://svn.turbogears.org/branches/1.0/turbogears/testutil.py), but
that seemed to be too aggressive, producing errors of the form:
InvalidRequestError: Class 'Visit' entity name 'None' has no mapper
associated with it
So:
- what is the recommended database initialisation / cleanup strategy
for unit tests involving SA?
- can anyone suggest how ORM state could be hanging around between
unit tests (I'm using an in-memory DB)?
- is there a convenient way to check on objects in the ORM, waiting to
be flushed?
This is SA 0.4.3 and TG 1.0.8 running against an in-memory sqlite DB
Thanks!
James
(Cross-posted to TG list)
--~--~-~--~~~---~--~~
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: Sequence start value not working with PostgreSQL
Thanks Michael.
On 12/05/2009, at 12:34 AM, Michael Bayer wrote:
the start functionality is unimplemented at the moment. instead,
issue:
t = Table(mytable, )
DDL(CREATE SEQUENCE ).execute_at('before-create', t)
Chris Miles wrote:
I need to create an explicit Sequence with a specified start value.
Looks simple, I tried Sequence('test_seq', start=5000) however the
actual sequence created is left with the default start value. The
start parameter appears to have no effect.
This is using PostgreSQL 8.2.4 and SQLAlchemy 0.5.3.
Test code below demonstrates the problem.
Am I misunderstanding the start parameter or is this a bug?
import sqlalchemy as sa
engine = sa.create_engine('postgres://localhost/test1', echo=True)
meta = sa.MetaData()
meta.bind = engine
test_seq = sa.Sequence('test_seq', start=5000, metadata=meta)
test_seq.create()
nextval = engine.execute(test_seq)
assert nextval == 5000, nextval
Cheers,
Chris Miles
--~--~-~--~~~---~--~~
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: SQLAlchemy and unit tests
Perfect, thanks Michael - I'll pass this on to the TG list
2009/5/11 Michael Bayer mike...@zzzcomputing.com
clear out the session (or make a new one) between tests. while the
session attempts to weak reference its contents, it usually ends up
holding onto a lot of stuff due to a particular reference cycle
created by backrefs (I'm thinking of ways to eliminate that behavior).
On May 11, 2009, at 10:54 PM, James wrote:
Hi all,
I'm trying to track down an error where running a full TurboGears unit
test suite fails with a SQLAlchemy error, while running the single
(failing) unit test passes OK.
The SA error is of the form:
FlushError: New instance terms...@0x2b15590 with identity key (class
'yel.model.select_source.TermSite', (1, 1), None) conflicts with
persistent instance terms...@0x2b8ad50
TermSite is an association table for a many-to-many relationship,
which has a multi-column primary key:
term_site_table = Table('term_site', metadata,
Column('term_id', Integer, ForeignKey(term.id,
ondelete=CASCADE), primary_key=True),
Column('site_id', Integer, ForeignKey(site.id,
ondelete=CASCADE), primary_key=True),
Column('weight', Integer, default=1, nullable=False),
)
The error seems to signify that SA thinks there is already an object
waiting to be flushed with the same term_id and site_id. The fact that
the test only fails when a full test suite is run implies that there
is some state left over in between unit test executions.
Abbreviated setUp and tearDown methods:
def setUp(self):
cherrypy.root = root.Root()
turbogears.startup.startTurboGears()
metadata.create_all()
def tearDown(self):
metadata.drop_all()
turbogears.startup.stopTurboGears()
TurboGears provides a sqlalchemy_cleanup method (here:
http://svn.turbogears.org/branches/1.0/turbogears/testutil.py), but
that seemed to be too aggressive, producing errors of the form:
InvalidRequestError: Class 'Visit' entity name 'None' has no mapper
associated with it
So:
- what is the recommended database initialisation / cleanup strategy
for unit tests involving SA?
- can anyone suggest how ORM state could be hanging around between
unit tests (I'm using an in-memory DB)?
- is there a convenient way to check on objects in the ORM, waiting to
be flushed?
This is SA 0.4.3 and TG 1.0.8 running against an in-memory sqlite DB
Thanks!
James
(Cross-posted to TG list)
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
