[sqlalchemy] Re: execute() is not returning multiple result sets from stored procedure call
Ah, thank you so much. Yes MySQLDB does support multiple result sets. So off to the raw cursors i go. Michael Bayer wrote: I dont know that MySQLdb (the adapter we use) supports multiple result sets ?you'd have to check its documentation. but in general SQLAlchemy's API doesnt support multiple result sets in one execution, you'd have to use a raw cursor. On May 9, 2009, at 4:17 PM, Rodney Haynie wrote: Hi everyone. SQLAlchemy 0.5.3 TurboGears 2.0 MySQL Windows I am trying to execute a stored procedure call from my controller using: result = conn.execute(CALL test_2resultsets).fetchall() However, result will only hold the first result set. I was anticipating result holding all of the result sets. In this case, the test stored procedure is returning 2 separate result sets. Is there a separate call I have to make to pull the second result set? I have included the stored procedure code below. delimiter // CREATE PROCEDURE test_2resultsets () BEGIN SELECT 1 AS my_id, 'Rodney' AS my_name; SELECT 2 AS her_id, 'Angela' AS her_name; END; // delimiter ; Thanks for any help. -Rodney --~--~-~--~~~---~--~~ 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] Basic Search Engine
Greetings,
I have been using SQLA for a few months.
For admin CRUD index pages I have been using a naive search_keywords
function as
seen at end of message.
Instead of using a primitive shlex.split, which incidentally is not unicode
friendly, and one crude search_type (AND|OR) I'd like to use something
that will
lex/parse a search string and build the queries.
eg.
((x or z or y) and q) or not h m
I imagine this would be a fairly common requirement however I can't seem
to find
any implementation anywhere.
I used google code search with the query pyparsing sqlalchemy lang:python
however found no useful results.
Before I set off attempting to learn about lexing/[py]parsing I was
wondering if
any one has some code like this laying about underneath their bed.
Cheers.
=
=
ensure_list = lambda l: l if isinstance(l, list) else [l]
def shlex_split(line):
shlex.split does not handle unicode properly so must be codecd
if isinstance(line, unicode):
line = line.encode('utf-8')
return [ w.strip().decode('utf-8') for w in shlex.split(line) ]
def like_escape(s):
return ( s.replace('\\', '')
.replace('%', '\\%')
.replace('_', '\\_') )
def search_keywords(q, model, key_words='', fields=[], search_type='and',
default_fields=[], **kw):
if not key_words:
return q
# Escape the search string
# TODO: this should be done by formencode validators
if isinstance(key_words, basestring):
key_words = shlex_split(key_words)
# Space delimited keyword search
key_words = ['%'+ like_escape(w) +'%' for w in key_words]
# Make sure fields is a list and if none specified use default
fields = ensure_list(fields) if fields else default_fields # TODO
# WHERE ($X OR $Y) AND|OR ($Z OR $Q) ...
search_type = and_ if search_type == 'and' else or_
if key_words:
q = q.filter (
search_type ( * (
or_( *( getattr(model, field).like(key_word, escape=r'\\')
for field in fields ) )
for key_word in key_words
)
)
)
return q
=
=
--~--~-~--~~~---~--~~
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
It may not be what you want but have you explored our full text search
support through the use of the match operator?
On May 10, 2009, at 6:13 AM, Nicholas Dudfield ndudfi...@gmail.com
wrote:
Greetings,
I have been using SQLA for a few months.
For admin CRUD index pages I have been using a naive search_keywords
function as
seen at end of message.
Instead of using a primitive shlex.split, which incidentally is not
unicode
friendly, and one crude search_type (AND|OR) I'd like to use something
that will
lex/parse a search string and build the queries.
eg.
((x or z or y) and q) or not h m
I imagine this would be a fairly common requirement however I can't
seem
to find
any implementation anywhere.
I used google code search with the query pyparsing sqlalchemy
lang:python
however found no useful results.
Before I set off attempting to learn about lexing/[py]parsing I was
wondering if
any one has some code like this laying about underneath their bed.
Cheers.
===
===
===
===
=
=
===
===
===
===
ensure_list = lambda l: l if isinstance(l, list) else [l]
def shlex_split(line):
shlex.split does not handle unicode properly so must be
codecd
if isinstance(line, unicode):
line = line.encode('utf-8')
return [ w.strip().decode('utf-8') for w in shlex.split(line) ]
def like_escape(s):
return ( s.replace('\\', '')
.replace('%', '\\%')
.replace('_', '\\_') )
def search_keywords(q, model, key_words='', fields=[],
search_type='and',
default_fields=[], **kw):
if not key_words:
return q
# Escape the search string
# TODO: this should be done by formencode validators
if isinstance(key_words, basestring):
key_words = shlex_split(key_words)
# Space delimited keyword search
key_words = ['%'+ like_escape(w) +'%' for w in key_words]
# Make sure fields is a list and if none specified use default
fields = ensure_list(fields) if fields else default_fields # TODO
# WHERE ($X OR $Y) AND|OR ($Z OR $Q) ...
search_type = and_ if search_type == 'and' else or_
if key_words:
q = q.filter (
search_type ( * (
or_( *( getattr(model, field).like(key_word,
escape=r'\\')
for field in fields ) )
for key_word in key_words
)
)
)
return q
===
===
===
===
=
=
===
===
===
===
--~--~-~--~~~---~--~~
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: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
I love that we're putting this level of thought into the issue. I
also hate that the state of MSSQL requires us to put this level of
thought into the issue.that said, carry on !
On May 9, 2009, at 4:21 PM, Rick Morrison wrote:
There are some differences on what happens then the MS Windows ODBC
driver connects to an MSSQL database, and what happens when FreeTDS
connects, and I believe that the three most common problems reported
for pyodbc on FreeTDS:
1) string encoding issues (attempting to execute unicode
statements)
2) transaction coordination issues (the MSSQL autocommit mode
problem)
3) the multiple result set issue we see here
are all likely artifacts of these differences. Here I'll focus only
on the third problem, the topic of this thread, but I think there
are similar solutions to the other two problems.
Issuing the following query to MSSQL:
insert into tablea values('foo'); select scope_identity()
Can return either one or two distinct result sets. In default mode,
MSSQL returns TWO result sets: the first is the number of rows
performed in the INSERT (this happens with UPDATE and DELETE as
well), and the second result set is the result of the second SELECT,
and contains the just-inserted identity value (if any).
It's possible to suppress the first result set by issuing a SET
NOCOUNT OFF statement, which sets the MSSQL connection to not
return the first result set (more info here).
Now it appears that the MS Windows ODBC driver might magically omit
the first result set and only return the results of the second
result set (which is all the current SQLA tests cover, I believe),
but that's all that's happening is that the MS Windows ODBC driver
issues the SET NOCOUNT OFF statement upon the connection
instantiation, and that FreeTDS does not.
That means that if no other action is taken, that issuing that
insert pair above is going to return ONE result set for a MS Windows
ODBC connection, and TWO result sets for a FreeTDS connection. So if
SQLA assumes that the first result set is going to contain the
identity values, it will work on Windows and bork on Unix, and that
pretty much matches the reported behavior so far.
So if that's the case, there's two possible fixes:
a) Issue the SET NOCOUNT OFF at connection-establishment time,
so both flavors of connection will behave the same.
or,
b) Process the list of returned result sets and determine which
set contains the identity value (more into on that here on MSDN)
I think that multi-result set handling was recently added to pyodbc,
so (b) just recently became possible, but all things being equal,
the (a) option may be easier, especially because there's other magic
words that the MS Windows ODBC driver utters when establishing a
connection as well, issuing spells to turn off autocommit mode for
example, and other things that can subtlety (and not so subtlety)
affect the outcomes of queries issued over that connection.
So assuming going with plan (a), a full fix for the FreeTDS + pyodbc
problem is going to involve diagnosing what the list of those
incantations are, and coming up with what amounts to an
initialization script of SQL statements that should be sent over a
newly established pyodbc connection to make sure it's in a known
state before returning it for user operations.
As far as the content of that init script goes, it STM that it
should be a matter of running a query trace tool on the server side
to see what SQL is sent when a Windows ODBC connection is made, and
basically duplicating that stream of statements for FreeTDS
connections.
But the other piece of the puzzle is then which init script gets
sent upon connection establishment? You'll need to know if it's a
Windows connection or a FreeTDS connection. Is there a
straightforward way to determine if the connection being made is
over a Windows driver, a commercial UNIX driver like EasySoft, or
over FreeTDS, or should SQLA just punt, and leave that sort of thing
up to the user to figure out?
I'm travelling this weekend, but I can maybe spare some time next
week to look at this, or Mike/Micheal: if you think there's enough
here to work with, feel free to run with it.
Rick
On Sat, May 9, 2009 at 12:41 AM, mtrier mtr...@gmail.com wrote:
If you change the test case to indicate that supports_unicode and
supports_unicode_statements = False, then it runs just fine with the
fix. Without the fix it fails as well, which indicates to me the
issue is in FreeTDS.
I'd like to commit this but I want to have a discussion with Mike
Bayer first to be sure he's okay with it.
I had a discussion with Mike Bayer and he expressed that he was
uncomfortable committing a hack that just hides the problem instead of
figuring out and fixing the problem properly. As we got into the code
we
[sqlalchemy] Re: Basic Search Engine
On May 10, 5:13 am, Nicholas Dudfield ndudfi...@gmail.com wrote: Greetings, I have been using SQLA for a few months. For admin CRUD index pages I have been using a naive search_keywords function as seen at end of message. Instead of using a primitive shlex.split, which incidentally is not unicode friendly, and one crude search_type (AND|OR) I'd like to use something that will lex/parse a search string and build the queries. eg. ((x or z or y) and q) or not h m I imagine this would be a fairly common requirement however I can't seem to find any implementation anywhere. I used google code search with the query pyparsing sqlalchemy lang:python however found no useful results. Google for pyparsing query parser and you'll find some helpful links: http://pyparsing.wikispaces.com/file/view/searchparser.py http://rephrase.net/days/07/04/pyparsing The O'Reilly shortcut Getting Started with Pyparsing ends with the development of a search query parser to search for recipes by querying for matching ingredients. -- Paul (author of Getting Started with Pyparsing) --~--~-~--~~~---~--~~ 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
@Michael No, I'm about as new to sql as I am to sqlalchemy. I'll have to look into it. Thanks @Paul Thanks, I'll look into that. I had found searchparser.py but was just wondering if anyone had already adapted it to work with sqlalchemy querys. On May 11, 8:21 am, Paul McGuire pt...@austin.rr.com wrote: On May 10, 5:13 am, Nicholas Dudfield ndudfi...@gmail.com wrote: Greetings, I have been using SQLA for a few months. For admin CRUD index pages I have been using a naive search_keywords function as seen at end of message. Instead of using a primitive shlex.split, which incidentally is not unicode friendly, and one crude search_type (AND|OR) I'd like to use something that will lex/parse a search string and build the queries. eg. ((x or z or y) and q) or not h m I imagine this would be a fairly common requirement however I can't seem to find any implementation anywhere. I used google code search with the query pyparsing sqlalchemy lang:python however found no useful results. Google for pyparsing query parser and you'll find some helpful links:http://pyparsing.wikispaces.com/file/view/searchparser.pyhttp://rephrase.net/days/07/04/pyparsing The O'Reilly shortcut Getting Started with Pyparsing ends with the development of a search query parser to search for recipes by querying for matching ingredients. -- Paul (author of Getting Started with Pyparsing) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
