• 2004
  • November
  • 16
  • Using pyformat queries on non-pyformat databases (Or, why I was getting frustrated with PySQLite)

Using pyformat queries on non-pyformat databases (Or, why I was getting frustrated with PySQLite)
Written by on in Snaking.


I use the DBAPI's pyformat query format exclusively. It's supported by both the PostgreSQL and MySQL drivers, and it's just a superior mechanism for creating queries (especially with PyTable's SQLQuery class). So, of course, as I was writing the SQLite driver for PyTable I was obviously using pyformat queries.

Problem was, SQLite doesn't support them, so every time I would parameterise my query SQLite would start spitting out those opaque syntax error messages. Eventually I realised what was going on, and gave up in disgust to take a nap.

Woke up about 3 hours later with the solution. A class that allows for using pyformat queries even on databases that only support qmark, numeric, named or format paramstyles.

The class (that link should become live once ViewCVS catches up) seems to work very nicely with SQLite in my minimal tests so far.

Since everything I write uses SQLQuery, I can actually introduce this at the SQLQuery level and have the conversion happen automatically based on whether the (connection's) driver has the queryPyformat capability. It's a performance hit for those drivers that don't support pyformat, but PyTable is about reducing coding time, not about to-the-metal speed.

Rather neat hack IMO.

Comments

  1. x

    x on 11/17/2004 11:32 a.m. #


    Haven't looked at the code, but it sounds like something ye olde pygresql needs desperately... if i get a spare moment i might look at stealing it. Spare moment. Ha ha. Don't recall it supporting any parameterization methods last time i looked!<br />
    <br />
    (I just looked. Appears to support 'format' style only.)

  2. Mike Fletcher

    Mike Fletcher on 11/17/2004 11:26 p.m. #


    Hmm, guess I should create a PyGreSQL driver for PyTable too. Don't have any huge need for it, but would be nice to have just to convince D'Arcy to use it some day :) .

  3. Filip Van Raemdonck

    Filip Van Raemdonck on 11/19/2004 4:49 a.m. #


    Which PySQLite are you talking about?<br />
    <br />
    The only one I know, the SF pysqlite project, definitely supports pyformat queries, and from inspecting http://cvs.sourceforge.net/viewcvs.py/pysqlite/pysqlite/sqlite/main.py?annotate=1.2 it seems to have done so since May 25 2002, at least (though I have no idea which was the first release including that support)

  4. Peter Buschman

    Peter Buschman on 11/19/2004 9:07 a.m. #


    For true any-to-any paramstyle conversions, check out PyDal:<br />
    <br />
    http://pydal.sourceforge.net<br />
    <br />
    It's not perfect, but it does implement a full conversion matrix.<br />
    <br />
    --PLB

  5. Mike Fletcher

    Mike Fletcher on 11/19/2004 9:28 a.m. #


    Filip; PySQLite 2.0a1 is the version that doesn't support pyformat (and the one I'm using).

  6. Mike Fletcher

    Mike Fletcher on 11/19/2004 9:36 a.m. #


    Peter; looking at the code for the format support, I notice that you're actually parsing the query-string character-by-character. I'd be worried about the performance of that over the long run.<br />
    <br />
    Still, having the general case supported might make it worthwhile for me to write a parser using SimpleParse, or even regexen, that implements the rules you've captured. Thanks for the pointer.

  7. Peter Buschman

    Peter Buschman on 11/20/2004 8:26 a.m. #


    Hi Mike,<br />
    <br />
    I agree that performance might become an issue, but that hasn't hurt things yet. I wanted to first address all the conversion cases and second to make it efficient. The use of a lookup matrix makes it possible to substitute a more efficient paramstyle-specific conversion routine later on if you need it. Thus you can prototype very quickly without worrying about which paramstyle your driver uses.<br />
    <br />
    If you come up with a faster way of improving my algorithm, I'd love to see it! As it is, I already have several enhancements in mind, particularly around the support for escape characters and some speed optimizations in building-up the final query string.<br />
    <br />
    --Peter

  8. Mike Fletcher

    Mike Fletcher on 11/20/2004 11:39 a.m. #


    I actually just re-implemented my module to parse the query string yesterday. It's still pyformat specific, but it uses regexen to do the parsing (really just trivial tokenising). There's a corner case where a database might have a % operator and call it like this:<br />
    <br />
    someValue%(a+b-c)<br />
    <br />
    but unless there's some freaky syntax I don't know about that lets an "s" happen right after the ) character it should operate correctly.<br />
    <br />
    New class is available at the CVS link above.

Comments are closed.

Pingbacks

Pingbacks are closed.