NEEDED: SQL Parser callable from python?
MikeK
I've gotten to the point where I'm considering adding an SQL layer to the API.
Can anyone give me a pointer to an open source SQL parser callable from python?
I will need to use it to translate standard SQL into python code callbacks
to the functions/methods specific to the legacy DB API.
Thanks.
Gerhard Häring
processor too, which will probably quite hard. The only SQL parsers for
Python I know of are in Gadfly and Gordon McMillan's Metakit SQL layer.
You'd probably be better off by just putting your data into an RDBMS. If
it needs to be embedded, then PySQLite could be one option.
--
mail: gerhard <at> bigfoot <dot> de registered Linux user #64239
web: http://www.cs.fhm.edu/~ifw00065/ OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9 3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))
MikeK
{snip]
> You'd probably be better off by just putting your data into an RDBMS. If
> it needs to be embedded, then PySQLite could be one option.
I can't go that path, as the data can't be moved (at this time) to a different
database. It must continue to exist in the form that the legacy app expects
it in. I'm attempting to provide a new-and-improved interface to the data.
Perhaps in time it will remove the need for the legacy interface, but not in
the near future.
Yes, I know I would need to create my own query engine. In reality, that
engine would only need to map the SQL queries into the legacy system's queries.
Thanks for the reply. I'll look into the SQL parsers in the projects you
mentioned.
Anthony Baxter
Look into the 'introspect' module in the gadfly project, in particular
the 'RemoteView' class. You can subclass this to allow it to be hooked
into the gadfly core.
This comment is in my old snmp-tables-via-gadfly code - it looks like it's
still current.
"""A remote view must define self.column_names
to return a (fixed) list of string column names and
self.listing() to return a possibly varying
list of row values. If there is a single column
the listing() list must return a list of values,
but for multiple columns it must return a list
of tuples with one entry for each column.
The remote view implementation may optionally
redefine __init__ also, please see introspect.py
"""
A simple (untested!) example might be like:
class MySubclassOfRemoteView(RemoteView):
# static = 0 means reconstruct internal structures for each
# query.
static = 0
# these must be fixed
column_names = [ "user", "extension" ]
# but the rows returned by this can vary.
def listing(self):
return [ ('anthony','7015'),
('rjones','7002'),
('rupert','7014'),
]
Once you've subclassed RemoteView, something like
conn = gadfly()
conn.startup("dbtest", "dbtest") # assume directory "dbtest" exists
conn.add_remote_view("name", MySubclassOfRemoteView() )
will add it to the DB.
For more, check out
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/gadfly/gadfly/gadfly/introspection.py?rev=1.3&content-type=text/vnd.viewcvs-markup
I'm pretty sure this still works with the current state-of-the-art gadfly,
but I can't be sure.
I can't remember where I found this stuff out - I suspect it was from
asking Aaron in an email. It could probably do with being documented and
some examples and the like...
Anthony
Paul Boddie
>
[Legacy database antics]
> Yes, I know I would need to create my own query engine. In reality, that
> engine would only need to map the SQL queries into the legacy system's
> queries.
I've been in a similar situation before (having to write an SQL-based
layer sitting on top of a proprietary, badly specified, non-standard
database system, albeit with various commercial entities playing "pass
the parcel" with it) and my advice is, if you really have to go
through with the activity of writing a query engine, is to find out
exactly which kinds of queries you really need and to concentrate only
on those - this may help to reduce the complexity of the engine quite
considerably.
Of course, my ultimate advice is to rewrite the application to use a
standard, open database system and to migrate the data across to that
system, but I suppose we don't all have that luxury. Sometimes,
however, what might be considered "luxury" is actually "necessity" and
vice versa, but organisational politics may obscure this
realisation...
Paul
Simon Brunning
> I'm working on a python interface to a home-grown legacy flat-file
> database.
> I've gotten to the point where I'm considering adding an SQL layer to the
> API.
> Can anyone give me a pointer to an open source SQL parser callable from
> python?
> I will need to use it to translate standard SQL into python code callbacks
>
> to the functions/methods specific to the legacy DB API.
<http://members.rogers.com/mcfletch/programming/simpleparse/simpleparse.html
>, and you can find a BNF grammar for SQL here -
<http://www.contrib.andrew.cmu.edu/~shadow/sql/sql2bnf.aug92.txt>.
Good luck, and tell us how you get on. ;-)
Cheers,
Simon Brunning
TriSystems Ltd.
sbru...@trisystems.co.uk
-----------------------------------------------------------------------
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else
is unauthorised. If you are not the intended recipient, any disclosure,
copying, distribution, or any action taken or omitted to be taken in
reliance on it, is prohibited and may be unlawful. TriSystems Ltd. cannot
accept liability for statements made which are clearly the senders own.