On 05/20/2016 08:53 AM, John Omernik wrote:
Awesome, I am making some progress, I realized that the visit_join I had
was specific to Microsoft access, so I commented it out to see what
happened... that function was somehow not using the ?. When I commented
it out I got "better" sql:
OK so the example of the "access" dialect is referred to in order to
illustrate the layout of files for a third-party dialect, such that it
plugs into SQLAlchemy smoothly.
However, the "access" dialect from a "works really well with MS Access"
standpoint is not very good at all, as MS Access is not really well
supported by the odbc driver, or PyODBC, or as far as providing a robust
SQL dialect that we can make good use of. So it's not great as an
example of how to use the internal dialect APIs.
To learn about the hooks and flags that Dialect subclasses really make
use of, you should look in the dialects that are *included* with
SQLAlchemy since they really cover the span of the kinds of things that
are needed. Look in the source under lib/sqlalchemy/dialects and
compare and contrast Postgresql, MySQL, SQLite, Oracle, SQL server. I
think looking at these is how most people learn the basics of getting a
dialect to work.
Access visit_join:
def visit_join(self, join, asfrom=False, **kwargs):
return ( self.process(join.left, asfrom=True) + \
(join.isouter and " LEFT OUTER JOIN " or " INNER JOIN ") + \
self.process(join.right, asfrom=True) + " ON " + \
self.process(join.onclause) )
SELECT field1 AS field1, COUNT(*) AS cnt
FROM table INNER JOIN (SELECT field1 AS __field1
FROM table GROUP BY field1 ORDER BY COUNT(*) DESC
LIMIT ?) AS anon_1 ON field1 = __field1 GROUP BY field1 ORDER BY cnt DESC
LIMIT 5000
No visit_join
SELECT field1 AS field1, COUNT(*) AS cnt
FROM table JOIN (SELECT field1 AS __field1
FROM table GROUP BY field1 ORDER BY COUNT(*) DESC
LIMIT 50) AS anon_1 ON field1 = __field1 GROUP BY field1 ORDER BY cnt DESC
LIMIT 5000
This is better! Now, the only question I have now is how I can create a
visit_join (or perhaps this goes elsewhere) where the "ON field1 =
__field1" can actually be ON field1 = anon_1.__field1 Drill doesn't
like field1 = __field1, says its ambiguous. Is there an easy way to do
this rather than trying to manually rewrite the statement. (For example,
anon_1 is being put in by something, and it's non-obvious to me, but I
don't want hard code anon_1 in a dialect...
Thanks for your help thus far in pointing me in the right direction.
John
On Thu, May 19, 2016 at 7:50 PM, John Omernik <[email protected]
<mailto:[email protected]>> wrote:
Hey Mike, thanks for the reply. I feel really bad in that I am
struggling on this, a little background, I am trying to get Caravel,
which uses SQL Alchemy to play nice with Apache Drill, this means I
am learning about dialects through a crash course of feeling really
dumb, I've had some success (with help) and have some aspects
working great. One of the things I am struggling with is the "How"
to trouble shoot when, for example Caravel is issuing a Query that
should have a limit, but instead comes through like this:
SELECT myfield AS myfield, COUNT(*) AS cnt
FROM mytable INNER JOIN (SELECT myfield AS __myfield
FROM mytable GROUP BY myfield ORDER BY COUNT(*) DESC
LIMIT ?) AS anon_1 ON myfield = __myfield GROUP BY myfield ORDER BY cnt
DESC
LIMIT 5000
So this hos Drill receives the query and it fails. There are two
things wrong here, first is the limits. Now, why the query is build
this way is beyond me (I can't tell if its Caravel building it that
way or SQL Alchemy, but the the self join makes no sense to me... it
seems redudant, but I digress.) There are two LIMITS, the first is
the ? which I am assuming should be the parameter from Caravel, and
then LIMIT 5000. I am guessing that the limit 5000 is put on by
CARAVEL as that is the max row count... but why this is failing is
beyond me, it doesn't work on my drill/pyodbc setup, but when it's
the sqllite dialect on a sqllite test database, that works fine...
The second issue is the join. Drill finds the myfield =
__myfield ambiguous. I just need to find how I could tell
SQLAlchemy to qualify the __myfield with the anon_1. I can't tell if
that is SQL Alchemy thing or a caravel thing. I am still digging,
and will continue to, but if you or anyone here could help me debug
and/or point me in the right direction, I figured it would be good
to articulate my thoughts.
On Thu, May 19, 2016 at 6:31 PM, Mike Bayer
<[email protected] <mailto:[email protected]>> wrote:
a SQL query that uses parameters will use ? if the DBAPI uses
"qmark" paramstyle which is very common. The actual value that
lines up with the ? is part of the "parameters" sequence. The
specification for this is at
https://www.python.org/dev/peps/pep-0249/#id15 which also links
out to paramstyle.
On 05/19/2016 05:27 PM, John Omernik wrote:
I am writing my own dialect, and for some reason, when my
application
sets a limit via parameter, the query submitted to the back
end just has
? in it. Is this an issue with the dialect, the pyodbc
(doubt it's
that), or the ODBC driver itself?
Thanks, John
--
You received this message because you are subscribed to the
Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails
from it, send
an email to [email protected]
<mailto:sqlalchemy%[email protected]>
<mailto:[email protected]
<mailto:sqlalchemy%[email protected]>>.
To post to this group, send email to
[email protected] <mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the
Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from
it, send an email to [email protected]
<mailto:sqlalchemy%[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
