Today, I found a Python package, "JayDeBeApi" that accesses SQLite via JDBC rather than directly through the C language Call Level Interface (CLI). https://github.com/baztian/jaydebeapi/blob/master/README.rst
This might provide the object oriented interface I have been looking for! Has anyone tried it? The SQLite3 CLI matches the specifications of an interface that was intended for the C programming language. The CLI specs were intended for C and COBOL not designed with an object oriented language in mind. "Developed in the early 1990s, the API was defined only for the programming languages C <https://en.wikipedia.org/wiki/C_(programming_language)> and COBOL <https://en.wikipedia.org/wiki/COBOL>." https://en.wikipedia.org/wiki/Call_Level_Interface By contrast, JDBC was designed for Java (an object oriented language) as a substitute for Microsoft's ODBC. Now that a Python developer is using JDBC instead of CLI we may get a more object oriented interface: "JDBC connections support creating and executing statements. ...Query statements return a JDBC row result set. The row result set is used to walk over the result set <https://en.wikipedia.org/wiki/Result_set>. Individual columns <https://en.wikipedia.org/wiki/Column_(database)> in a row are retrieved either by name or by column number. There may be any number of rows in the result set. The row result set has metadata that describes the names of the columns and their types." https://en.wikipedia.org/wiki/Java_Database_Connectivity As I recall there are at least three JDBC drivers for SQLite3, one by Werner http://www.ch-werner.de/javasqlite/ one by Saito (Xerial) https://github.com/xerial/sqlite-jdbc/blob/master/README.md and Saito's was forked (with attribution from Zentus) https://github.com/crawshaw/sqlitejdbc I am hopeful this new JDBC based interface will provide as satisfactory high level channel between SQLite3 and Python. Jim Callahan On Thu, Jan 7, 2016 at 7:55 PM, Warren Young <wyml at etr-usa.com> wrote: > On Jan 7, 2016, at 5:22 PM, Jim Callahan <jim.callahan.orlando at gmail.com> > wrote: > > > > I believe R has remarkably good interface packages for SQLite > > That?s the appropriate level: the high-level language's DB access layer > should map the low-level C record-at-a-time API to an appropriate > language-level abstraction. > > R almost forces you to do this because of things like data.frame. But, > that?s no argument for other HLL DBMS API writers not to provide similar > affordances. > > I?ve been involved with two different C++ DBMS wrapper libraries, and both > of them provide a way to get a std::vector<> as a result set instead of > iterate over individual rows. As with R?s SQLite wrapper, I felt it was my > C++ code?s responsibility to do this repackaging, not the underlying C DBMS > access API. > > That?s not to say that the SQLite C API has no warts: > > 1. sqlite3_column_*() uses 0-based indices but sqlite3_bind_*() uses > 1-based indices. I can cope with either base, but please pick one! (And > make it the correct base for programming, 0. (Yes, I know I just praised R > above. R?s use of 1-based arrays is WRONG.)) > > 2. There is no ?preview? mechanism. That is, you can?t bind some > parameters to a prepared query string and then get the resulting SQL > because SQLite substitutes the values into the query at a layer below the > SQL parser. This means that if you have an error in your SQL syntax or > your parameters cause a constraint violation, your debug logging layer can > only log the prepared query string, not the parameters that went into it, > which makes it unnecessarily difficult to determine which code path caused > you to get the error when looking at logs of a running system. > > 3. The query finalization code could be less picky. If I prepare a new > query without finalizing the previous one, I?d rather that SQLite didn?t > punish me by throwing errors unless I put it into a ?lint? mode. Just toss > the half-finished prior query and move on, please. > > 4. There are several signs of backwards compatible extensions which make > the API more complex than if it were designed with the features from the > start. (e.g. _v2() APIs, the various ways to get error codes, etc.) > Hopefully those doing the SQLite4 effort will feel free to break the API, > jettisoning this historical baggage. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> This email has been sent from a virus-free computer protected by Avast. www.avast.com <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
